Nested Linq performance

Question

We have 2 tables (tblSerials and tblRequests). The question is how can we make this code to execute faster? As you can see "from request in ctx.tblRequests" parts are very similar. Any other better solution?

var list = from s in ctx.tblSerials
        orderby s.CreateDate descending
        select new
        {
         SerialGUID = s.SerialGUID,
         CreateDate = s.CreateDate,
         NumberOfAllRequests = 
            (from request in ctx.tblRequests
              where request.SerialGUID.ToLower().Equals(s.SerialGUID.ToLower())
              select request.RequestGUID).Count(),
         NumberOfAllRequestsDistinctByMachine = 
            (from request in ctx.tblRequests
              where request.SerialGUID.ToLower().Equals(s.SerialGUID.ToLower())
              select request.MachineCode).Distinct().Count(),                    
        };

Answer 1

If you join with tblRequests, you could change your select into this:

select new
{
    SerialGUID = s.SerialGUID,
    CreateDate = s.CreateDate,
    RequestGUID = request.RequestGUID,
    MachineCode = request.MachineCode
};

Then, you have a single query. After retrieving these results, you can then 'manually' aggregate them over the result set and do you Count() and Distinct().Count() yourself.

Answer 2

I'm not sure if it's smart enough to create joins using the nested queries, you could try using joins instead to see if it helps.

like so:

    var requestsByMachine = (from request in ctx.tblRequests
                            group request by request.MachineCode into g
                            select new { serial = request.SerialGuid, amount = g.Distinct().Count() }).ToArray();

    var requestsByGuid = (from request in ctx.tblRequests
                         group request by request.requestGUID into g
                         select new { serial = request.SerialGuid, amount = g.Count()}).ToArray();

    var list = from s in ctx.tblSerials.ToArray()
               join r1 in requestsByGuid on s.SerialGUID equals r1.serial
               join r2 in requestsByMachine on s.SerialGUID equals r2.serial
               orderby s.CreateDate descending
               select new
               {
                   SerialGUID = s.SerialGUID,
                   CreateDate = s.CreateDate,
                   NumberOfAllRequests = r1.amount,
                   NumberOfAllRequestsDistinctByMachine = r2.amount
               };

Can't try it myself at the moment, but you could check in sql profiler to see if creates a single query.

Edit: Modified to query in memory