Encapsulate queries to Navigation Properties

Question

To make my top layers more readable I usually make extension methods to encapsulate long hard-to-read queries into something as simple as db.Matches.By(period)

This 'By' method looks something like this:

public static IQueryable<PlayedMatch> By(this IQueryable<PlayedMatch> matches, Period period)
{
    return matches.Where(pm => pm.Details.DateTime >= period.Start && pm.Details.DateTime < period.End);
}

Problem is that I would like to have something similar for querying Navigation Properties, so I could do something like this:

var query = Db.Players.Select( p => new 
{ 
    Player      = p, 
    TotalPoints = p.Matches.By(period).Sum(m => m.Points)
});

Problem is that first of all Navigation Properties are of type ICollection<>. Second is that when I change the extension method to use IEnumerable<> or ICollection<> I get the following exception while running the query:

LINQ to Entities does not recognize the method 'System.Collections.Generic.IEnumerable'1[Match] By(System.Collections.Generic.ICollection`1[Match], Period)' method, and this method cannot be translated into a store expression.

Question:

Is there any other way for me to encapsulate queries on navigation properties like I do with my normal queries?

score 0 · Answer 1 · answered Dec 04 '13 at 10:25

You'll need to add an extension method for each type:

public static IQueryable<PlayedMatch> By(this IQueryable<PlayedMatch> matches, Period period)
{
    return matches.Where(pm => pm.Details.DateTime >= period.Start && pm.Details.DateTime < period.End);
}

public static ICollection<PlayedMatch> By(this ICollection<PlayedMatch> matches, Period period)
{
    return matches.Where(pm => pm.Details.DateTime >= period.Start && pm.Details.DateTime < period.End);
}

public static IEnumerable<PlayedMatch> By(this IEnumerable<PlayedMatch> matches, Period period)
{
    return matches.Where(pm => pm.Details.DateTime >= period.Start && pm.Details.DateTime < period.End);
}

The compiler will pick the most appropriate at compile time.

The problem lies with exception being thrown: **LINQ to Entities does not recognize the method method, and this method cannot be translated into a store expression.** — Dirk Boer, Dec 04 '13 at 11:24

score 0 · Answer 2 · answered Dec 04 '13 at 13:48

Linq-to-Entities cannot translate your By method into sql. It would work if you brought all the players into memory because then you'd be using Linq-to-Objects and it can work with your C# code:

var query = Db.Players
              .AsEnumerable //pulls all players into memory
              .Select( p => new 
              { 
                  Player      = p, 
                  TotalPoints = p.Matches.By(period).Sum(m => m.Points)
              });

But you probably don't want to pay the price of bringing all that data into memory....

If you want to encapsulate long hard-to-read queries, you could declare them as fields. Then you could do something like this:

        Func<Bar, bool> NameIsTom = b => b.Name == "Tom";

        Foos.Select(f => new { Foo = f, Toms = f.Bars.Where(NameIsTom) });

Encapsulate queries to Navigation Properties

2 Answers2