How to retrieve(get) huge amount of data using linq(Entity Framework 5) with faster time

asp.net-mvc-4 c# entity-framework-6 linq visual-studio

Question

One of the order in our database has huge amount of records(> 500,000). When I attempted to get all records and map them to domain model, Its taking forever (linq call). The following sample code will describe what I did...

int OrderId = 123456;
int pagesize = 100;

using (var dbContext = new MyDbContext())
{
    var orderDetails = dbContext.orderDetailEntity
        .Where(i => i.Orders.OrderID == OrderId)
        .OrderBy(o => o.orderDetailStatus);

    var orderDetailModel = Mapper
        .Map<IEnumerable<OrderDetailModel>>(orderDetails.Take(PageSize));         
 }

For more information Orders entity and OrderDetail entity has relationships. So on the above query I am trying to get all OrderDetail for the specific. But It is taking forever cos of huge amount of data(More than half a million records).

Do you have any idea how to solve this issue? I really appreciate you

Thank you in advance.

1
0
3/7/2016 9:36:11 PM

Popular Answer

From experience, I'd never use Linq to get anything more than 100k records again. I was in a similar situation not too long ago with a similar amount of records, the one thing I would do differently is to get this amount of data using raw SQL queries.

That said, here are 2 things I did that minimized execution time dramatically: 1) Paging - Whatever you plan to do with these order objects - use paging as best you can (.Skip .Take())

2) If the order objects are used only to display info on screen, adding .AsNoTracking() can help minimize execution time. (A quick google search can outline reasons why)

    dbContext.orderDetailEntity
.AsNoTracking()
.Where(i => i.Orders.OrderID == OrderId)
.OrderBy(o => o.orderDetailStatus);

TL;DR Don't use Linq to Entities - use a simple SQL query instead.

1
3/7/2016 8:45:07 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow