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();
There are several things you should consider with db functions:
bId
. Is it exactly the same in your UDF or rather as in the table like b_id
?