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
– Daniel A. White, Commented Apr 18, 2012 at 20:09
Why are you doing the "ToList()" ? That's going to execute the query immediately and bring back allFs and allEs. — Robaticus
– Robaticus, Commented Apr 18, 2012 at 20:10
Sorry about that, they return IQueriable<F> and IQueriable<E> s — Sev
– Sev, Commented Apr 18, 2012 at 20:11
Since they were from 2 different repositories I was getting errors and thought putting them in lists can solve that issue — Sev
– Sev, Commented Apr 18, 2012 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
– BrokenGlass, Commented Apr 18, 2012 at 20:13

jessehouwing · Accepted Answer · 2012-04-18 21:09:28Z

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"
    };

Collectives™ on Stack Overflow

LINQ query optimization

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related