CODEWITHSUNDEEP
c#linqlinq-to-entitiesentity-framework-5
Shaul Behr
Shaul Behr

Reputation: 38003

Fluent syntax for IQueryable extension method?

I'll let the code speak for itself:

public interface ISoftDeletable {
  bool IsDeleted {get; set;}
}

public static class Extensions {
  public IQueryable<T> Active<T>(this IQueryable<T> q) where T : ISoftDeletable {
    return q.Where(t => !t.IsDeleted);
  }
}

public partial class Thing : ISoftDeletable {
  ...
}

...
var query = from tc in db.ThingContainers
            where tc.Things.Active().Any(t => t.SatisfiesOtherCondition)
            select new { ... }; // throws System.NotSupportedException

The error is:

LINQ to Entities does not recognize the method 'System.Collections.Generic.IQueryable`1[Thing] ActiveThing' method, and this method cannot be translated into a store expression.

You get the idea: I'd like a fluent kind of way of expressing this so that for any ISoftDeletable I can add on a 'where' clause with a simple, reusable piece of code. The example here doesn't work because Linq2Entities doesn't know what to do with my Active() method.

The example I gave here is a simple one, but in my real code, the Active() extension contains a much more intricate set of conditions, and I don't want to be copying and pasting that all over my code.

Any suggestions?

Upvotes: 2

Views: 1066

Answers (2)

Shaul Behr
Shaul Behr

Reputation: 38003

While @felipe has earned the answer credit, I thought I would also post my own answer as an alternative, similar though it is:

var query = from tc in db.ThingContainers.Active() // ThingContainer is also ISoftDeletable!
            join t in db.Things.Active() on tc.ID equals t.ThingContainerID into things
            where things.Any(t => t.SatisfiesOtherCondition)
            select new { ... };

This has the advantage of keeping the structure of the query more or less the same, though you do lose the fluency of the implicit relationship between ThingContainer and Thing. In my case, the trade of works out that it's better to specify the relationship explicitly rather than having to specify the Active() criteria explicitly.

Upvotes: 0

felipe
felipe

Reputation: 682

You have two unrelated problems in the code. The first one is that Entity Framework cannot handle casts in expressions, which your extension method does.

The solution to this problem is to add a class restriction to your extension method. If you do not add that restriction, the expression is compiled to include a cast:

.Where (t => !((ISoftDeletable)t.IsDeleted))

The cast above confuses Entity Framework, so that is why you get a runtime error.

When the restriction is added, the expression becomes a simple property access:

 .Where (t => !(t.IsDeleted))

This expression can be parsed just fine with entity framework.

The second problem is that you cannot apply user-defined extension methods in query syntax, but you can use them in the Fluent syntax:

db.ThingContainers.SelectMany(tc => tc.Things).Active()
    .Any(t => t.SatisfiesOtherCondition); // this works

To see the problem we have to look at what the actual generated query will be:

 db.ThingContainers
       .Where(tc => tc.Things.Active().Any(t => t.StatisfiesOtherCondition))
       .Select(tc => new { ... });

The Active() call is never executed, but is generated as an expression for EF to parse. Sure enough, EF does not know what to do with such a function, so it bails out.

An obvious workaround (although not always possible) is to start the query at the Things instead of the ThingContainers:

db.Things.Active().SelectMany(t => t.Container);

Another possible workaround is to use Model Defined Functions, but that is a more involved process. See this, this and this MSDN articles for more information.

Upvotes: 2

Related Questions