Best way to handle large data in entity framework

Question

Hi I am using EF6 where I have large data to select from database where my query as follows:

var EntityInfo = _contextRegister.Entities.Where(x => x.IsDeleted != true).ToList();
foreach (var itm in EntityInfo)
{
    Entity.Entity entity = new Entity.Entity();
    entity.MainActivityId = itm.MainActivityId;
    entity.SubGroupId = itm.SubGroupId;
    entity.Id = Convert.ToInt32(itm.Id);
-------
    entity.UAECityRegion = _contextFRAMEWORK.UAECityRegions.Where(m => m.Id == itm.UAECityRegionId).Select(m => m.RegionName).FirstOrDefault();
    var voucherstautus = _contextRegister.EPayVoucherDatas.Where(m => m.EntityId == itm.Id).ToList();
    foreach (var item in voucherstautus)
    {
        if (item.VoucherStatus == 10)
        {
            entity.PaymentStatus = Convert.ToInt32(item.VoucherStatus);
            break;
        }
     }

     entityList.Add(entity);
}

This query taking more than 10mins where how can I make this query better or should I use stored procedure instead of framework?

All this is in a method where you will see nested loop so it slow for looping the each entity. — Lifewithsun, Sep 10 '18 at 12:12
You are going to pull all non-deleted entities out of the database. And then you iterate locally over all of them to get for each of them a list of another set of data from the database. How do you expect this to be fast? — Oliver, Sep 10 '18 at 12:13
yes @Oliver. I need this query should be simplified or can i use stored procedure to overcome time issue? — Lifewithsun, Sep 10 '18 at 12:14
Just write one query that contains all conditions at once then it will be fast.Then it doesn't make any difference if it is written as LINQ query or stored procedure. — Oliver, Sep 10 '18 at 12:16
@Oliver the problem is I am assigning the values to model variables by creating new object and then in next loop I've multiple condition. How to do this one? — Lifewithsun, Sep 10 '18 at 12:19
If the multiple conditions are met already by the entities within the database, then add them to the `.Where()` and `.Join()` clauses of your query. So all these things are done on the server before the data is send to you. — Oliver, Sep 10 '18 at 12:37

score 1 · Answer 1 · edited Jul 25 '20 at 08:46

As far as I can see there is a 1:1 relationship between Entities and EPayVoucherDatas (otherwise your code _contextRegister.EPayVoucherDatas.Where(m => m.EntityId == itm.Id) wouldn't work). So at first ensure you have modeled this relationship in your EF model.

If that is true you could write something like this:

_contextRegister.EPayVoucherDatas.Where(m => !m.Entity.IsDeleted);

That already eliminates a part of your problems.

Another part is your usage of another database _contextFRAMEWORK, which seems to contains some other data under the same Ids. In your current implementation you ask for a specific region for each item separately. But depending on the size of this table it could be wiser to pull this whole table once (outside of the for-loop) and make the lookup locally. Also you have this if-statment that sets the PaymentStatus to 10 if the VoucherStatus is also 10. What is in all other cases`? Maybe you can always map the PaymentStatus from the VoucherStatus?

A lot of question and not a real answer, cause as usual it depends. Try to minimize the amount of calls to the database and check how many entities each call returns. Filter by some conditions as early as possible to minimize the amount of data that the server returns and try to make as few connections as possible to the server. Even if that results in quite complex queries. This is not a problem for a SQL server normally, cause that's it for what it was made for.

Tony · Answer 2 · 2018-09-10T12:40:09.130

1) Check if the DataBase Indexes are created for each field that is used in the WHERE conditions, for example:

x.IsDeleted
m.Id 
m.EntityId

You can create indexes using the Model Builder:

modelBuilder.Entity<Person>()
    .HasIndex(p => p.Name)
    .IsUnique();

See https://stackoverflow.com/a/47031294/194717

You can also create indexes with IndexAttribute

public class Blog
{
    public int Id { get; set; }
    public string Title { get; set; }

    [Index]
    public int Rating { get; set; }

    public virtual ICollection<Post> Posts { get; set; }
}

See https://blog.oneunicorn.com/2014/02/15/ef-6-1-creating-indexes-with-indexattribute/

2) You shouldn't hit the database two times for each Entities record. Try to write it as a JOIN instead (.Include()).

That's a very generic solution to a problem where the actual problem is that they shouldn't hit the database two times for each `Entities` record. They should write it as a JOIN instead (`.Include()`). — CodeCaster, Sep 10 '18 at 12:32

Best way to handle large data in entity framework

2 Answers2