Load N records from database in entity framework

Question

I need to load only 5 elements from a list without loading all the list. I have these two entities:

public class Company
{
   public int ID { get; set; }
   public String Name{ get; set; }
   public List<Employee> EmployeeList{ get; set; }       
}

and:

public class Employee
{
   public int ID { get; set; }
   public String Name{ get; set; }       
}

I need to load only the last 5 records of the Employee for a company named "CompanyName".

I tried to use :

Company companySearch =systemDB.Companies
                               .Include("EmployeeList").Take(5)
                               .Where(d => d.Name.Equals("CompanyName"))
                               .SingleOrDefault();

But this code loads all the list and after gives me back only the last 5 records. I need a faster query.

PS: It's code first EF

Have you checked what queries are getting fired by entity framework to the database through sql query profiler tool? — RBT
– RBT, Commented Jun 7, 2016 at 10:53
d variable in your lambda expression d => d.Name.Equals("CompanyName") corresponds to company class. Company class doesn't even have the Name property. — RBT
– RBT, Commented Jun 7, 2016 at 11:09
Agree with @tmg - unless you've turned off lazy loading on your db context, you would not be getting all records back. Perhaps you need an index on the table, so using Profiler to review the SQL query should show the TOP 5 and WHERE in which you could design your index from. — Jason W
– Jason W, Commented Jun 7, 2016 at 11:19

RBT · Accepted Answer · 2016-06-07 22:09:04Z

For loading selective N records of EmployeeList you will have to have some criterion based on which the members of your collection navigation property will be filtered. I've taken that criterion as value of ID property of Employee entity. Here are all the steps required along with code snippet which will do the lazy loading of EmployeeList collection for Company entity

Enable lazy loading in constructor of your inherited dbContext class. I believe systemDB is object of a class which inherits from DbContext
```
public SystemDB()
{
    this.Configuration.LazyLoadingEnabled = true;
}
```
Remove the include clause to avoid eager loading:

Company companySearch =systemDB.Companies .Where(d => d.Name.Equals("CompanyName")) .SingleOrDefault();

After execution of this line of code, If you check the EmployeeList property of companySearch object it will be shown as Null in quick watch window.

Perform the lazy loading of EmployeeList property using the below mentioned call. Put explicit criterion for filtering the records. I've the set the filter criteria to restrict the employees whose ID lies between 1 and 5, both boundaries being inclusive.

db.Entry<Company>(companySearch).Collection(s => s.EmployeeList).Query().Where(p => p.ID >= 1 && p.ID <= 5).Load();

Eldho · Accepted Answer · 2016-06-08 05:48:57Z

Note that it is not currently possible to filter which related entities are loaded. Include will always bring in all related entities. Reference

You could still try anonymous projection without lazyloading

this.Configuration.LazyLoadingEnabled = false;

Anonymous projection.

Company companySearch =systemDB.Companies
                           .Where(d => d.Name.Equals("CompanyName"))
                           .Select(x=> new 
                           {
                              company = x,
                              employees = x.Employees.Take(5),
                           }
                           .FirstOrDefault()

You will get more idea about how to do anonymous projection is here

Collectives™ on Stack Overflow

Load N records from database in entity framework

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related