LINQ to Entities optimization and efficiency

Question

0.00/5 (No votes)

See more:

Hello,
I'm using C#/ASP.NET. I'm wondering on how to use in a correct way LINQ technology.

Premising I'm using a simple database with 2 tables, Customer and Project, and both are associated one to many, if I do:

C#

 var allCustomers = from data in database.CustomerSet
              select data;

foreach ( var c in allCustomers)
{
var lastdate = (from data in database.ProjectSet
select data).Max();

 var lastproject = from data in database.ProjectSet
 where data.Customer.ID == c.ID && data.DateofProject == lastdate
 select data;

//logic code here
}

Is it the correct way to access the data? I'm thinking that queries inside the foreach block are both making several accesses to the database, so it makes the website slow. If I do something like:

C#

var allCustomers = from data in database.CustomerSet
                   select data;

var allProjects = from data in database.ProjectSet
                  select data;

foreach ( var c in allCustomers)
{
var lastdate = (from data in database.ProjectSet
select data).Max();

 var lastproject = from data in allProjects
 where data.Customer.ID == c.ID && data.DateofProject == lastdate
 select data;

//logic code here
}

Will be a solution?

Posted 22-Aug-11 5:28am

foxrox_1

Add a Solution

2 solutions

Solution 2

Yes, it is, if you can fit allProjects into memory with no problems. (And assuming LINQ-to-SQL caches its results sensibly so enumerating allProjects doesn't create a new database query each time ... if it does, call ToList on it once outside the loop to get a IList<Project> and use that inside the loop.) This is a classic tradeoff between database access and memory usage. Considering the likely memory available to your web process and size of those tables, it is probably a good solution, but it does depend on your use case. In the first case you are only ever looking at projects which are relevant to an existing customer; if you have 10 million projects but only 100,000 of them are related to a customer who will be selected in the enumeration, don't pull the whole table.

A related approach which I have taken to reduce database hits for this kind of lookup (where it isn't over all customers, but a list of customers) is to first select the customers you're interested in, then create an ID match list and do a query like 'select from ProjectSet where customerID in the list you just made'. That avoids getting a lot of irrelevant rows. I'm not sure if there is a LINQ syntax for creating an in list but it feels like there should be.

Posted 22-Aug-11 5:51am

BobJanova

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Simon Bang Terkildsen · Accepted Answer · 2011-08-22T05:48:00

C#

eachCustomersLatestProject
    = from project in database.ProjectSet
      join customer in database.CustomerSet
      on project.Customer.ID = customer.ID
      where project.DateofProject == 
      database.ProjectSet.Where(p => p.Customer.ID == customer.ID)
                         .Max(p => p.DateofProject)
      select project;

Now you can loop over the lastest projects.

Note You may be able optimized this futher, but right on top of my head this should be a huge performance gain for you.