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

c# entity-framework-core

Question

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

[Table("a"]
public class A {
    [Key]
    [Column("a_id")]
    public int Id { get; set; }

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

[Table("b"]
public class B {
    [Key]
    [Column("b_id")]
    public int Id { get; set; }
    [NotMapped]
    public string MyFunctionValue { get; set; }

    [ForeignKey("a_id")]
    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...

myContext.A
    .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.

EDIT:

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.

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

        return aWithMfvs.A;
    })
    .AsQueryable();
1
3
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?
1
9/24/2019 7:08:06 PM


Related Questions





Related

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