9

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?

Jack Ukleja
  • 13,061
  • 11
  • 72
  • 113
  • so what you really want to know is "How do I do delayed execution in EF, like I can in L2S?". – Allen Rice Jan 11 '10 at 15:18
  • the only way i can see to do this so far is to convert my IQueryable into an IEnumerable, at which point I suspect you lose the ability for an optimal SQL statement to be generated – Jack Ukleja Jan 11 '10 at 15:20
  • No - nothing to do with delayed execution. Basically it wont let me write linq statements that include methods – Jack Ukleja Jan 11 '10 at 15:21

2 Answers2

12

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.

Craig Stuntz
  • 125,891
  • 12
  • 252
  • 273
  • I prefer option one. [I wrote a blog on this very subject](http://www.codetunnel.com/blog/post/64/how-to-simplify-complex-linq-expressions) because I had some insanely complex LINQ expressions that were getting hard to maintain. Something had to be done. – CatDadCode Oct 02 '11 at 18:54
0

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.

Dave Swersky
  • 34,502
  • 9
  • 78
  • 118
  • do you mean IEnumerables when you say "Common queries"? e.g. something that has a result set as opposed to an IQueryable? – Jack Ukleja Jan 11 '10 at 15:47
  • By "common queries" I mean parameterized queries that can be used to return manageably-sized supersets of the entities you want. The queries you design will depend on your data and how much you have. – Dave Swersky Jan 11 '10 at 16:05
  • I see. But this implies in memory joining etc? – Jack Ukleja Jan 11 '10 at 16:23