c# - Using custom method inside Linq Select with Entity Framework -

i trying use custom function inside linq select used ef. want project each item of tblmitarbeiter onto 1 tblmitarbeiterpersonalkostenstellehistories valid ad given date. should done extension method not repeat myself ;) can work when used directly on dbset, not inside select.

how can teach ef recognize method (3.) if writing out (1.)?

void main() {     var date = datetime.now;      // 1. works, returns ienumerable<tblmitarbeiterpersonalkostenstellehistories>     tblmitarbeiters     .select(m => m.tblmitarbeiterpersonalkostenstellehistories.where(p => p.zuordnunggültigab <= date).orderbydescending(p => p.zuordnunggültigab).firstordefault())     .dump();      // 2. works, returns 1 tblmitarbeiterpersonalkostenstellehistories     tblmitarbeiterpersonalkostenstellehistories     .getvaliditemfordate(p => p.zuordnunggültigab, date)     .dump();      // 3. throws notsupportedexception     tblmitarbeiters     .select(m => m.tblmitarbeiterpersonalkostenstellehistories.getvaliditemfordate(p => p.zuordnunggültigab, date))     .dump();      // 4. throws notsupportedexception     tblmitarbeiters     .select(m => m.tblmitarbeiterpersonalkostenstellehistories.asqueryable().getvaliditemfordate(p => p.zuordnunggültigab, date))     .dump(); }   public static class queryableextensions {     public static t getvaliditemfordate<t>(this iqueryable<t> source, expression<func<t, datetime>> selector, datetime date)     {         var dateaccessor = expression.lambda<func<t, datetime>>(expression.constant(date), selector.parameters);         var lessthanorequal = expression.lessthanorequal(selector.body, dateaccessor.body);         var lambda = expression.lambda<func<t, bool>>(lessthanorequal, selector.parameters);         return source.where(lambda).orderbydescending(selector).firstordefault();     }      public static t getvaliditemfordate<t>(this ienumerable<t> source, func<t, datetime> selector, datetime date) =>         source.where(i => selector(i) <= date).orderbydescending(selector).firstordefault(); } 

you can, extent, split complex linq expressions using linqkit. if you'll excuse me, i'll use example model that's less germanic:

public class employee {     public long id { get; set; }     public virtual icollection<employeehistoryrecord> historyrecords { get; set; }  }  public class employeehistoryrecord {     public long id { get; set; }     public datetime validfrom { get; set; }     public long employeeid { get; set; }     public employee employee { get; set; } } 

if understood question correctly, should identical yours matters.

when using linqkit, , linq in general, must understand tool have @ disposal when reusing query code, without using stored procedures, breaking apart , stitching expressions.

your utility method translate this:

private static expression<func<ienumerable<titem>, titem>> getvaliditemfordate<titem>(             expression<func<titem, datetime>> dateselector,              datetime date) {     return linq.expr((ienumerable<titem> items) =>         items.where(it => dateselector.invoke(it) <= date)             .orderbydescending(it => dateselector.invoke(it))             .firstordefault())         .expand(); } 

what method dynamically create expression input ienumerable<titem> returns titem. can see it's pretty similar code you're extracting. few things note:

• the source collection not parameter of utility method, of expression returned.
• you have call invoke() extension method linqkit on expressions you're "plugging into" one.
• you should call expand() on result if used invoke()s inside it. make linqkit replace calls invoke() in expression tree expression being invoked. (this isn't 100% necessary, makes easier fix errors when expansion fails reason. if don't expand() in every helper method, error happens during expansion manifest in method expansion, , not in method contains offending code.)

you use similarly, again using invoke():

var db = new employeehistorycontext();  var getvaliditemfordate = getvaliditemfordate((employeehistoryrecord cab) => cab.validfrom, datetime.now);  var historyrecords = db.employees.asexpandable().select(emp => getvaliditemfordate.invoke(emp.historyrecords)); 

(i've tested code against empty database, insofar doesn't make entityframework throw notsupportedexception.)

here, should note:

• the subexpression you're plugging 1 you're passing select() needs saved in local variable, linqkit doesn't support method calls during expansion.
• you need call asexpandable() on first iqueryable in chain, linqkit gets work magic.
• you're not going able use extension method call syntax inside expression in question.
• all subexpressions have determined before expansion occurs.

these limitations stem fact you're doing isn't calling methods. you're building 1 ginormous expression bunch of smaller ones, resulting expression still has linq-to-entities understand. on other hand, input has linqkit understand, , handles expressions of form localvariable.invoke(). dynamism has in code outside expression tree. basically, it's doing same solution 2, using syntax more intuitive building expression tree programmatically.

last, not least: when doing this, not go overboard. complex ef queries hard debug when goes wrong, because you're not told in code problem is. if query assembled dynamically bits , pieces on codebase, debugging errors (like delightful "unable cast type x type y") become nightmare.

---

(for future questions: think it's idea when if make code sample scratch, instead of using bits actual codebase. might overly domain-specific, , understanding names might require context take granted. identifiers should ideally simple english names can understand. can maybe speak enough german interview job in it, "mitarbeiterpersonalkostenstellehistorie" hard keep in head , reason when haven't worked on project long enough familiar it's supposed mean.)