LINQ query optimization

Question

I retrieve data from two different repositories:

        List<F> allFs = fRepository.GetFs().ToList();
        List<E> allEs = eRepository.GetEs().ToList();

Now I need to join them so I do the following:

        var EFs = from c in allFs.AsQueryable()
                        join e in allEs on c.SerialNumber equals e.FSerialNumber
                        where e.Year == Convert.ToInt32(billingYear) && 
                        e.Month == Convert.ToInt32(billingMonth)
                        select new EReport
                        {
                            FSerialNumber = c.SerialNumber,
                            FName = c.Name,
                            IntCustID = Convert.ToInt32(e.IntCustID),
                            TotalECases = 0,
                            TotalPrice = "$0"
                        };

How can I make this LINQ query better so it will run faster? I would appreciate any suggestions.

Thanks

what are `fRepository` and `eRepository`? what do they return? — Daniel A. White, Apr 18 '12 at 20:09
Why are you doing the "ToList()" ? That's going to execute the query immediately and bring back allFs and allEs. — Robaticus, Apr 18 '12 at 20:10
Since they were from 2 different repositories I was getting errors and thought putting them in lists can solve that issue — Sev, Apr 18 '12 at 20:11
@Sev: If you use two different repositories / contexts you can not join them on the DB - revisit your repository structure - you need a unit of work that includes both repositories and uses the same context. — BrokenGlass, Apr 18 '12 at 20:13
There might be situations (such as two separate databases or different credentials) which might require a separate repository approach. In that case, calling ToList() is required and as much filtering as possible should be applied to the data being fetched from the database. — jessehouwing, Apr 19 '12 at 08:57

score 5 · Accepted Answer · answered Apr 18 '12 at 21:09

Unless you're able to create one repository that contains both pieces of data, which would be a far preferred solution, I can see the following things which might speed up the process.

Since you'r always filtering all E's by Month and Year, you should do that before calling ToList on the IQueryable, that way you reduce the number of E's in the join (probably considerably)
Since you're only using a subset of fields from E and F, you can use an anonymous type to limit the amount of data to transfer
Depending on how many serialnumbers you're retrieving from F's, you could filter your E's by serials in the database (or vice versa). But if most of the serialnumbers are to be expected in both sets, that doesn't really help you much further

Reasons why you might not be able to combine the repositories into one are probably because the data is coming from two separate databases.

The code, updated with the above mentioned points 1 and 2 would be similar to this:

var allFs = fRepository.GetFs().Select(f => new {f.Name, f.SerialNumber}).ToList();

int year = Convert.ToInt32(billingYear);
int month = Convert.ToInt32(billingMonth);

var allEs = eRepository.GetEs().Where(e.Year == year && e.Month == month).Select(e => new {e.FSerialNumber, e.IntCustID}).ToList();


var EFs = from c in allFs
    join e in allEs on c.SerialNumber equals e.FSerialNumber
    select new EReport
    {
        FSerialNumber = c.SerialNumber,
        FName = c.Name,
        IntCustID = Convert.ToInt32(e.IntCustID),
        TotalECases = 0,
        TotalPrice = "$0"
    };

Just curious... How much faster was it :) – jessehouwing Apr 19 '12 at 07:02 — jessehouwing, Apr 19 '12 at 07:02

LINQ query optimization

1 Answers1