Flattening Complex LINQ to SQL

Question

I have a somewhat complex LINQ to SQL query that I'm trying to optimise (no, not prematurely, things are slow), that goes a little bit like this;

IQueryable<SearchListItem> query = DbContext.EquipmentLives
    .Where(...)
    .Select(e => new SearchListItem {
        EquipmentStatusId = e.EquipmentStatuses.FirstOrDefault(s => s.DateTo == null).Id,
        StatusStartDate = e.EquipmentStatuses.FirstOrDefault(s => s.DateTo == null).DateFrom,
        ...
    });

The where clauses aren't important, they don't filter EquipmentStatuses, happy to include if someone thinks they're required.

This is on quite a large set of tables and returns a fairly details object, there's more references to EquipmentStatuses, but I'm sure you get the idea. The problem is that there's quite obviously two sub-queries and I'm sure that (among some other things) is not ideal, especially since they are exactly the same sub-query each time.

Is it possible to flatten this out a bit? Perhaps it's easier to do a few smaller queries to the database and create the SearchListItem in a foreach loop?

Answer 1

You don't need to repeat the FirstOrDefault. You can add an intermediate Select to select it once and then reuse it:

IQueryable<SearchListItem> query = DbContext.EquipmentLives
    .Where(...)
    .Select(e => e.EquipmentStatuses.FirstOrDefault(s => s.DateTo == null))
    .Select(s => new SearchListItem {
        EquipmentStatusId = s.Id,
        StatusStartDate = s.DateFrom,
        ...
    });

In query syntax (which I find more readable) it would look like this:

var query =
    from e in DbContext.EquipmentLives
    where ...
    let s = e.EquipmentStatuses.FirstOrDefault(s => s.DateTo == null)
    select new SearchListItem {
        EquipmentStatusId = s.Id,
        StatusStartDate = s.DateFrom,
        ...
    });

There is another problem in your query though. If there is no matching EquipmentStatus in your EquipmentLive, FirstOrDefault will return null, which will cause an exception in the last select. So you might need an additional Where:

IQueryable<SearchListItem> query = DbContext.EquipmentLives
    .Where(...)
    .Select(e => e.EquipmentStatuses.FirstOrDefault(s => s.DateTo == null))
    .Where(s => s != null)
    .Select(s => new SearchListItem {
        EquipmentStatusId = s.Id,
        StatusStartDate = s.DateFrom,
        ...
    });

or

var query =
    from e in DbContext.EquipmentLives
    where ...
    let s = e.EquipmentStatuses.FirstOrDefault(s => s.DateTo == null)
    where s != null
    select new SearchListItem {
        EquipmentStatusId = s.Id,
        StatusStartDate = s.DateFrom,
        ...
    });

Answer 2

Given that you don't test for null after calling FirstOrDefault(s => s.DateTo == null) I assume that:

• either for each device there is always a status with DateTo == null or
• you need to see only devices which have such status

In order to do so you need to join EquipmentLives with EquipmentStatuses to avoid subqueries:

var query = DbContext.EquipmentLives
    .Where(l => true)
    .Join(DbContext.EquipmentStatuses.Where(s => s.DateTo == null),
        eq => eq.Id,
        status => status.EquipmentId,
        (eq, status) => new SelectListItem
        {
            EquipmentStatusId = status.Id,
            StatusStartDate = status.DateFrom
        });

However, if you do want to perform a left join replace DbContext.EquipmentStatuses.Where(s => s.DateTo == null) with DbContext.EquipmentStatuses.Where(s => s.DateTo == null).DefaultIfEmpty().

Answer 3

Here's my take given your comments, and with some assumptions I've made

• It may look scary, but give it a try, with and without the ToList() before the GroupBy()
• If you have LinqPad, check the SQL produced, and the number of queries, or just plug in the SQL Server Profiler
• With LinqPad you could even put a Stopwatch to measure things precisely

Enjoy ;)

var query = DbContext.EquipmentLives
    .AsNoTracking() // Notice this!!!
    .Where(...)

    // WARNING: SelectMany is an INNER JOIN
    // You won't get EquipmentLive records that don't have EquipmentStatuses
    // But your original code would break if such a case existed
    .SelectMany(e => e.EquipmentStatuses, (live, status) => new
    {
        EquipmentLiveId = live.Id, // We'll need this one for grouping
        EquipmentStatusId = status.Id,
        EquipmentStatusDateTo = status.DateTo,
        StatusStartDate = status.DateFrom
        //...
    })

    // WARNING: Again, you won't get EquipmentLive records for which none of their EquipmentStatuses have a DateTo == null
    // But your original code would break if such a case existed
    .Where(x => x.EquipmentStatusDateTo == null)

    // Now You can do a ToList() before the following GroupBy(). It depends on a lot of factors...
    // If you only expect one or two EquipmentStatus.DateTo == null per EquipmentLive, doing ToList() before GroupBy may give you a performance boost
    // Why? GroupBy sometimes confuses the EF SQL generator and the SQL Optimizer
    .GroupBy(x => x.EquipmentLiveId, x => new SearchListItem
    {
        EquipmentLiveId = x.EquipmentLiveId, // You may or may not need this?
        EquipmentStatusId = x.EquipmentStatusId,
        StatusStartDate = x.StatusStartDate,
        //...
    })

    // Now you have one group of SearchListItem per EquipmentLive
    // Each group has a list of EquipmenStatuses with DateTo == null
    // Just select the first one (you could do g.OrderBy... as well)
    .Select(g => g.FirstOrDefault())

    // Materialize
    .ToList();