SubQuery and Group by in linq

Question

This query was written in the our system some time ago but the performance of this query is getting poor with a little increase in data. My investigation shows (CodeCount) where query firing another subquery causing a massive delay in execution. I need to optimize this Linq query. any help will be highly appreciated

  from batch in Context.VoucherCodeBatch.ToList()
                    join type in Context.VoucherCodeType on batch.VoucherTypeId equals type.VoucherTypeId
                    join voucher in Context.Voucher on batch.VoucherCodeBatchId equals voucher.VoucherCodeBatchId

                    where batchIds.Contains(batch.BatchCode)
                    group new
                    {
                        batch.BatchCode,
                        batch.CreationDate,
                        type.VoucherTypeName,
                        voucher.AllowedCount,
                        voucher.ValidFrom,
                        voucher.ValidTo,
                        batch.VoucherCodeBatchId,
                        voucher.VoucherCode
                    }
                        by new { batch.BatchCode }
                        into uniquebatch
                        select new Batch
                        {
                            BatchCode = uniquebatch.FirstOrDefault().BatchCode,
                            CreationDate = uniquebatch.FirstOrDefault().CreationDate,
                            TimesAllowed = uniquebatch.FirstOrDefault().AllowedCount,
                            ValidFrom = uniquebatch.FirstOrDefault().ValidFrom,
                            CodeCount = ((from c in Context.Voucher.ToList()
                                          where
                                              c.VoucherCodeBatchId ==
                                              uniquebatch.FirstOrDefault().VoucherCodeBatchId
                                          select c).Count()),
                            ValidTo = uniquebatch.FirstOrDefault().ValidTo,
                            CodeType = uniquebatch.FirstOrDefault().VoucherTypeName,
                            VoucherCodeBatchId = uniquebatch.FirstOrDefault().VoucherCodeBatchId
                        });

Answer 1

The first big problem is the ToList() right in front of an ObjectSet<> (the table collection in EF).

Never do that, the ToList() force EF to bring all data to memory before process the query. (Like @Daniel Hilgarth coments).

Other detail is use a get property in front of FirstOrDefault() like in line:

BatchCode = uniquebatch.FirstOrDefault().BatchCode,

Use First() instead FirstOrDefault in this case. like:

BatchCode = uniquebatch.First().BatchCode,

Your query will be like this:

from batch in Context.VoucherCodeBatch/*.ToList()*/
join type in Context.VoucherCodeType on batch.VoucherTypeId equals type.VoucherTypeId
join voucher in Context.Voucher on batch.VoucherCodeBatchId equals voucher.VoucherCodeBatchId
where batchIds.Contains(batch.BatchCode)
group new
    {
        batch.BatchCode,
        batch.CreationDate,
        type.VoucherTypeName,
        voucher.AllowedCount,
        voucher.ValidFrom,
        voucher.ValidTo,
        batch.VoucherCodeBatchId,
        voucher.VoucherCode
    }
by new { batch.BatchCode }
into uniquebatch
select ( delegate
    {
        // If you put a operation in a query that operation will be
        // processed all times. Bacause that i removed this line from
        // the where statement.
        var vcBatchId = uniquebatch.First().VoucherCodeBatchId;

        return new Batch
            {
                BatchCode = uniquebatch.First().BatchCode,
                CreationDate = uniquebatch.First().CreationDate,
                TimesAllowed = uniquebatch.First().AllowedCount,
                ValidFrom = uniquebatch.First().ValidFrom,
                CodeCount = ((
                    from c in Context.Voucher/*.ToList()*/
                    where c.VoucherCodeBatchId == vcBatchId
                    select c).Count()),
                ValidTo = uniquebatch.First().ValidTo,
                CodeType = uniquebatch.First().VoucherTypeName,
                VoucherCodeBatchId = uniquebatch.First().VoucherCodeBatchId
            }
    });

If this improvement not be good enough, You will need change this query to SQL. But I believe this improvement will perform much better.