Performace issue using Foreach in LINQ

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?

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.

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.