Given LINQ to Entities does not support "Custom methods" how do you stay DRY?

Question

I have run into this problem:

Custom Methods & Extension Methods cannot be translated into a store expression

Basically I have some complicated LINQ queries, so wanted to break them down into subqueries which are implemented as methods that return IQueryables. My hope was then these IQueryables could be composed together in a LINQ statement (as I am pretty sure you can do in LINQ to SQL).

The problem is if you try this you get (for example):

LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[Thread] GetThreadsByMostReccentlyPosted(Int32)' method, and this method cannot be translated into a store expression.

It seems pretty fundamental to me that if you use a LINQ ORM then you need to be able to compose LINQ queries. Otherwise any common query logic has to be copy & pasted.

Given this limitation, how am I supposed to stay DRY with LINQ to Entities?

Answer 1

EF can't compose a query out of a LINQ expression that includes a method. EF needs literal values to compose the SQL.

You will have to make do with "common" queries that return a superset of the Entities you need for a given case, then use extension methods and LINQ to narrow down the return set once it has been returned from the database.

Answer 2

Two ways:

1. Methods which return expressions can be used
2. Separate the queryable and enumerable bits

For #1, consider:

public Expression<Func<Foo, bool>> WhereCreatorIsAdministrator()
{
    return f => f.Creator.UserName.Equals("Administrator", StringComparison.OrdinalIgnoreCase);
}

public void DoStuff()
{
    var exp = WhereCreatorIsAdministrator();
    using (var c = new MyEntities())
    {
        var q = c.Foos.Where(exp); // supported in L2E
        // do stuff
    }
 }

For an example of number 2, read this article: How to compose L2O and L2E queries. Consider the example given there:

var partialFilter = from p in ctx.People
                    where p.Address.City == “Sammamish”
                    select p;

var possibleBuyers = from p in partiallyFilter.AsEnumerable()
                     where InMarketForAHouse(p);
                     select p;

This might be less efficient, or it might be fine. It depends on what you're doing. It's usually fine for projections, often not OK for restrictions.

Update Just saw an even better explanation of option #1 from Damien Guard.