wertzui February 2016

Using custom method inside Linq Select with Entity Framework

I am trying to use a custom Function inside a Linq Select that is used with EF. I want to project each item of tblMitarbeiter onto one tblMitarbeiterPersonalkostenstelleHistories that is valid ad the given date. This should be done with an extension method so that I do not repeat myself ;) I can only get it to work when used directly on the DbSet, but not inside a Select.

How can I teach EF to recognize my Method (3.) as if I would be writing it out (1.)?

void Main()
    var date = DateTime.Now;

    // 1. works, returns IEnumerable<tblMitarbeiterPersonalkostenstelleHistories>
    .Select(m => m.tblMitarbeiterPersonalkostenstelleHistories.Where(p => p.ZuordnungGültigAb <= date).OrderByDescending(p => p.ZuordnungGültigAb).FirstOrDefault())

    // 2. works, returns one tblMitarbeiterPersonalkostenstelleHistories
    .GetValidItemForDate(p => p.ZuordnungGültigAb, date)

    // 3. throws NotSupportedException
    .Select(m => m.tblMitarbeiterPersonalkostenstelleHistories.GetValidItemForDate(p => p.ZuordnungGültigAb, date))

    // 4. throws NotSupportedException
    .Select(m => m.tblMitarbeiterPersonalkostenstelleHistories.AsQueryable().GetValidItemForDate(p => p.ZuordnungGültigAb, date))

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).OrderByD        


millimoose February 2016

You can, to some extent, split up complex LINQ expressions using LINQKit. If you'll excuse me, I'll use an 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 I understood your question correctly, it should be identical to yours where it matters.

When using LINQKit, and LINQ in general, you must understand that the only tool you have at your disposal when reusing query code, without using stored procedures, is breaking apart and stitching together expressions.

Your utility method would translate to something like 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))

What this method does is dynamically create an expression whose input is an IEnumerable<TItem> that returns a TITem. You can see it's pretty similar to the code you're extracting. A few things to note:

  • The source collection is not a parameter of the utility method, but of the expression returned.
  • You have to call the Invoke() extension method from LinqKit on any expressions you're "plugging into" this one.
  • You should call Expand()

Post Status

Asked in February 2016
Viewed 2,149 times
Voted 13
Answered 1 times


Leave an answer