EF7 fires several queries instead of 1 query with smart join -- and worse

[jancg]

HAS BEEN SOLVED IN 1.0.0

var enrichedProducts = this.productContext.Products
  .AsNoTracking()
  .Where(p => productNumbers.Contains(p.SAPProductNumber))
  .Select(p => new
    {
      SAPProductNumber = p.SAPProductNumber,
      DisplayName = p.DisplayName,
      Brand = p.Brand,
      Type = p.Type,
      MultimediaValueEpochTime = p.ProductMultimedias
        .Where(pm => pm.Visible == true
                  && pm.ValueEpochTime != null)
        .OrderByDescending(pm => pm.MainItem == true)
        .ThenBy(pm => pm.SortOrder)
        .Select(pm => pm.ValueEpochTime)
        .FirstOrDefault()
    })
  .ToList();

productNumbers is a List and contains 8 product numbers.

EF7 fires 9 queries to SQL Server. The first query actually translates neatly to IN (...8 numbers...). Next EF7 fires 8 identical queries to SQL Server that aren't even correlated to the 8 numbers. 8 times tens of thousands of records from the ProductMultimedias table are fetched!

[maumar]

This happens because of

MultimediaValueEpochTime = p.ProductMultimedias
        .Where(pm => pm.Visible == true
                  && pm.ValueEpochTime != null)
        .OrderByDescending(pm => pm.MainItem == true)
        .ThenBy(pm => pm.SortOrder)
        .Select(pm => pm.ValueEpochTime)
        .FirstOrDefault()

part in the projection. EF currently can't handle collection projections well. Queries you see are not correlated with the main query because that correlation is happening on the client currently. You can try using Include on that navigation, projecting the entire collection and then performing the rest of the query on the client. It will still fetch all the rows from ProductMultimedias but should do so only once, and not 8 times.

[maumar]

Something like this:

var enrichedProducts = this.productContext.Products
  .Include(p => p.ProductMultimedias)
  .AsNoTracking()
  .Where(p => productNumbers.Contains(p.SAPProductNumber))
  .ToList()
  .Select(p => new
    {
      SAPProductNumber = p.SAPProductNumber,
      DisplayName = p.DisplayName,
      Brand = p.Brand,
      Type = p.Type,
      MultimediaValueEpochTime = p.ProductMultimedias
        .Where(pm => pm.Visible == true
                  && pm.ValueEpochTime != null)
        .OrderByDescending(pm => pm.MainItem == true)
        .ThenBy(pm => pm.SortOrder)
        .Select(pm => pm.ValueEpochTime)
        .FirstOrDefault()
    });

[rowanmiller]

Improving this translation is already tracked by #4007, so I'll close this one out.

[jancg]

'not planned'? Why? We always intent to fix bugs in our code. I guess I should stick to EF6 forever? EF Core is still not up to par compared to EF6 when it comes down to query generation.

[ajcvickers]

@jancg This is a really old issue that was closed 6 years ago. All that is happening here is that the GitHub closed-reason is changing. If you think there is still a bug here, then please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.