How to avoid "select n+1" pattern in Linq

Question

I have a query (including LinqKit) of the form:

Expression<Func<Country, DateTime, bool>> countryIndepBeforeExpr = 
  (ct, dt) => ct.IndependenceDate <= dt;
DateTime someDate = GetSomeDate();
var q = db.Continent.AsExpandable().Select(c =>
  new 
  {
    c.ID,
    c.Name,
    c.Area,
    Countries = c.Countries.AsQueryable()
                 .Where(ct => countryIndepBeforeExpr.Invoke(ct, someDate))
                 .Select(ct => new {ct.ID, ct.Name, ct.IndependenceDate})
  });

Now I want to iterate through q... but since the Countries property of each element is of type IQueryable, it will be lazy loaded, causing n+1 queries to be executed, which isn't very nice.

What is the correct way to write this query so that all necessary data will be fetched in a single query to the db?

EDIT

Hm, well it might have helped if I had actually run a Sql trace before asking this question. I assumed that because the inner property was of type IQueryable that it would be lazy-loaded... but after doing some actual testing, it turns out that Linq to Entities is smart enough to run the whole query at once.

Sorry to waste all your time. I would delete the question, but since it already has answers, I can't. Maybe it can serve as some kind of warning to others to test your hypothesis before assuming it to be true!

This really has more to do with the Linq provider than Linq itself. You should probably tag this question with the appropriate provider (EF, Linq-to-SQL, etc.). — Ocelot20, Mar 04 '14 at 14:30
Why do you reference countries in a continent object? Shouldn't it be the other way around? It would be much simpler for the request. You gather all the countries from `countryIndepBeforeExpr` then select the continents and do whatever you want to do. — Keysharpener, Mar 04 '14 at 14:32
@keysharpener - don't be distracted by the particular implementation. My project has nothing to do with geography; I'm just using the continent/country relationship to make it intuitively obvious to you what I'm trying to do without having to explain the business rules of my project domain. — Shaul Behr, Mar 04 '14 at 14:34
You can make this much simpler by capturing the `someDate` variable in the expression, which will then become a `Expression>` that you can use as `Where(countryIndepBeforeExpr )`, without LinqKit. It will probably also get the countries in one query. I also think you left out too much, because the expression has apparently been compiled, and when I use `AsExpandable()` this way I get the infamous "LINQ to Entities does not recognize the method". — Gert Arnold, Mar 04 '14 at 15:11
Sorry @GertArnold, you're right, I was writing the code from memory. AsExpandable belongs outside. Will edit. — Shaul Behr, Mar 04 '14 at 15:16

score 1 · Answer 1 · answered Mar 04 '14 at 14:42

1

Include countries to your model when you call for continents. With something like this:

var continents = db.Continent.Include(c => c.Countries).ToArray();

Then you can make your linq operations without iQueryable object.

answered Mar 04 '14 at 14:42

Forster

81
1
8

+1 fair suggestion, but it has the unwanted side effect of dragging along *all* data from the Country records. I only want a small number of fields. And perhaps I'll also want to take some data from tables to which Country has a FK... if I start including all related tables, it's going to get ugly. – Shaul Behr Mar 04 '14 at 14:49
@Shaul If you wanna avoid both problems (retrieve all which is needed => no n+1, but not more that what's is needed => no Include), I don't see any other solution than using anonymous types or probably rather "ViewModel" classes (well custom "non-entity" classes), and select only what you need... – Raphaël Althaus Mar 04 '14 at 17:11

score 0 · Answer 2 · answered Mar 04 '14 at 15:07

I think this should work (moving AsExpandable() to root of IQueryable):

var q = db.Continent
          .AsExpandable()
          .Select(c => new 
          {
              c.ID,
              c.Name,
              c.Area,
              Countries = c.Countries
                  .Where(ct => countryIndepBeforeExpr.Invoke(ct, someDate))
                  .Select(ct => new {ct.ID, ct.Name, ct.IndependenceDate})
});

If not, create two IQueryable and join them together:

var continents = db.Continents;
var countries = db.Countries
                  .AsExpandable()
                  .Where(c => countryIndepBeforeExpr.Invoke(c, someDate))
                  .Select(c => new { c.ID, c.Name, c.IndependenceDate });

var q = continents.GroupJoin(countries,
    continent => continent.ID,
    country => country.ContinentId,
    (continent, countries) => new
    {
        continent.ID,
        continent.Name,
        continent.Area,
        Countries = countries.Select(c => new
        {
            c.ID,
            c.Name,
            c.IndependenceDate
        })
    });

How to avoid "select n+1" pattern in Linq

2 Answers2