Entity Framework Core 2.2 use scalar DBFunction to get property on list of foreign keys

c# entity-framework-core


I have a model with a linked list of foreign keys i.e.

public class A {
    public int Id { get; set; }

    public List<B> Bs { get; set; } = new List<B>();

public class B {
    public int Id { get; set; }
    public string MyFunctionValue { get; set; }

    public A A { get; set; }

I've then defined a function which links to a scalar sql function like so...

public static class MySqlFunctions {
    [DbFunction("MyFunction", "dbo")]
    public static string MyFunction(int bId) {
        throw new NotImplementedException();

and registered in my context like so...

modelBuilder.HasDbFunction(() => MySqlFunctions.MyFunction(default));

What I want to be able to do in my repository class is to grab the A records with the linked B records in a List with their MyFunctionValue value set to the return value of the function when ran against the id of B. Something like...

    .Include(a => a.Bs.Select(b => new B {
        Id = b.Id,
        MyFunctionValue = MySqlFunctions.MyFunction(b.Id)

However with all the options I've tried so far I'm getting either a InvalidOperationException or NotImplementedException I guess because it can't properly convert it to SQL?

Is there any way I can write a query like this or is it too complex for EF to generate SQL for? I know there's a possibility I could use .FromSql but I'd rather avoid it if possible as it's a bit messy.


So I've managed to get it working with the following code but it's obviously a bit messy, if anyone has a better solution I'd be grateful.

    .Include(a => a.Bs)
    .Select(a => new {
        A = a,
        MyFunctionValues = a.Bs.Select(b => MySqlFunctions.MyFunction(b.Id))
    .Select(aWithMfvs => {
        for (int i = 0; i < aWithMfvs.MyFunctionValues.Count(); i++) {
            aWithMfvs.A.Bs[i].MyFunctionValue = aWithMfvs.MyFunctionValues[i];

        return aWithMfvs.A;
9/25/2019 10:25:59 AM

Popular Answer

There are several things you should consider with db functions:

  • When you declare a DbFunction as static method, you don't have to register it with the modelBuilder
  • Registering is only needed, when you would use Fluent API (which IMHO I recommend anyway in order to have you entities free of any dependencies)
  • The return value, the method name and the count, type and order of the method parameters must match your code in the user defined function (UDF)
    • You named the method parameter as bId. Is it exactly the same in your UDF or rather as in the table like b_id?
9/24/2019 7:08:06 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow