1

I am using an IList<Employee> where i get the records more then 5000 by using linq which could be better? empdetailsList has 5000

Example : 

foreach(Employee emp in empdetailsList)
{
       Employee employee=new Employee();
   employee=Details.GetFeeDetails(emp.Emplid);
}

The above example takes a lot of time in order to iterate each empdetails where i need to get corresponding fees list.

suggest me anybody what to do?

Tomas Jansson
  • 22,767
  • 13
  • 83
  • 137
kumar
  • 1,117
  • 13
  • 34
  • 58
  • 1
    Can you show more details about what is in the loop? – Daniel A. White Apr 28 '11 at 12:44
  • 1
    And the method by which `empdetailslist` is filled - can you paste the LINQ code used to obtain the list in the first place/ – Daniel Renshaw Apr 28 '11 at 12:46
  • `foreach` is certainly not the cause of your problem... the bottleneck must be in the body of the loop – Thomas Levesque Apr 28 '11 at 12:46
  • 1
    GetFeeDetails looks like the kind of function that could be triggering another database call. Looks like a related foreign key type property? That should be done with joins, and in your initial query. Hit the database once and once only, and get from it everything you need in 1 go. That's an example of the "Select n + 1" anti-pattern (http://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem) – RichardW1001 Apr 28 '11 at 12:57
  • 1
    31% after 49 questions? Come on... – Oleks Apr 28 '11 at 12:58

2 Answers2

3

Linq to SQL/Linq to Entities use a deferred execution pattern. As soon as you call For Each or anything else that indirectly calls GetEnumerator, that's when your query gets translated into SQL and performed against the database.

The trick is to make sure your query is completely and correctly defined before that happens. Use Where(...), and the other Linq filters to reduce as much as possible the amount of data the query will retrieve. These filters are built into a single query before the database is called.

Linq to SQL/Linq to Entities also both use Lazy Loading. This is where if you have related entities (like Sales Order --> has many Sales Order Lines --> has 1 Product), the query will not return them unless it knows it needs to. If you did something like this:

Dim orders = entities.SalesOrders

For Each o in orders
   For Each ol in o.SalesOrderLines
      Console.WriteLine(ol.Product.Name)
   Next
Next

You will get awful performance, because at the time of calling GetEnumerator (the start of the For Each), the query engine doesn't know you need the related entities, so "saves time" by ignoring them. If you observe the database activity, you'll then see hundreds/thousands of database roundtrips as each related entity is then retrieved 1 at a time.

To avoid this problem, if you know you'll need related entities, use the Include() method in Entity Framework. If you've got it right, when you profile the database activity you should only see a single query being made, and every item being retrieved by that query should be used for something by your application.

RichardW1001
  • 1,985
  • 13
  • 22
2

If the call to Details.GetFeeDetails(emp.Emplid); involves another round-trip of some sort, then that's the issue. I would suggest altering your query in this case to return fee details with the original IList<Employee> query.

ataddeini
  • 4,931
  • 26
  • 34