Tolist() takes so long to convert, how can I improve this?

Question

I have following code in which I am using tolist method to convert my data from db to list. The reason why I have to convert whole data to list is that, I have to perform search operations after that for which I am using where and lambda statement, for which we need the list.

Is there any alternative for this?

// This takes less than 2 seconds to execute
var wdata = (from s in db.VIEW_ADDED_LOT
             select new LotModel
             {
                CREATION_DATE = s.CREATION_DATE,
                LOT_NO_SPL = s.LOT_NO_SPL,
                LOT_TYPE = s.LOT_TYPE,
                ITEM = s.ITEM,
                BUSINESS_UNIT = s.BUSINESS_UNIT,
                INSPECTOR = s.INSPECTOR,
                NCRNO = s.NCRNO,
                BUILDING_NO = s.BUILDING_NO,
                CELL = s.CELL,
                NCR_DT = s.NCR_DT,
                INVENTORY_ROUTER = s.INVENTORY_ROUTER,
                DOC_ISSUE = s.DOC_ISSUE,
                COMMENTS = s.COMMENTS,
                AGING = s.AGING,
                ARCHIVAL_DATE = s.ARCHIVAL_DATE,
                NCR_COMPLETION_STATUS = s.NCR_COMPLETION_STATUS,
                FLAG_LINK = s.FLAG_LINK,
                P_KEY = s.P_KEY
             });

// This takes around 1 minute to convert to list as there is 500 000 rows
var data = wdata.ToList();

// The reason why I am converting to list is that I have to perform n number of
// search on the basis of the filter chosen by user
if (NCR != null && NCR != "")
{
    data = data.Where(a => a.NCRNO == NCR).ToList();
}
if (LOT != null && LOT != "")
{
    data = data.Where(a => a.LOT_NO_SPL == LOT).ToList();
}

ToList execute the query on the database, not before creating an IQueryable. Could you please precise the data source and the data size ? (Sql Server ? Other ?) — Jérémie Leclercq, Feb 18 '22 at 14:03
What happens if you remove all your ToList and execute the search on wdata. And only at the end assign the list? — Steve, Feb 18 '22 at 14:07
Add where to the query : from s in db.VIEW_ADDED_LOT.Where(a => a.NCRNO == NCR) Code should run a lot quicker putting the where at beginning of search. — jdweng, Feb 18 '22 at 14:08
Exactly, querys are not executed when declared, they are executed when enumeration is needed like in the ToList method execution. Move the ToList execution after the filters were specified if possible, no reason to bring the whole table to the server for this query as far as I understand. You can also check on this post for info: https://www.c-sharpcorner.com/UploadFile/rahul4_saxena/deferred-vs-immediate-query-execution-in-linq/ — Emiliano Javier González, Feb 18 '22 at 14:19
Do whatever you want with wdata. Use sql. So you will cut the result list as small as possible and any future operations will be faster. — redParrot, Feb 19 '22 at 05:34

score 10 · Accepted Answer · edited Feb 18 '22 at 17:01

10

In your example the wdata.ToList() call evaluates the query and loads the entirety of wdata into memory. Your initial assignment of wdata only creates an IQueryable object, it does not actually query the database.

To avoid the slow performance you should apply all your filters to the IQueryable and then call ToList() at the end, for example:

var data = wdata; // at this point its a queryable of your initial linq
if (NCR != null && NCR != "")
{
    data = data.Where(a => a.NCRNO == NCR); // appends one filter condition
}
if (LOT != null && LOT != "")
{
    data = data.Where(a => a.LOT_NO_SPL == LOT); // appends another filter condition
}
var finalResult = data.ToList();

This will append your conditions to the IQueryable which will eventually be resolved once you call .ToList(), which will mean you'll not have to load all your entities into memory, as the filters will be evaluated in the DB.

edited Feb 18 '22 at 17:01

David Tansey

5,813
4
35
51

answered Feb 18 '22 at 14:15

domi

362
4
6

Every time I use tolist.. no matter where, no matter what is the size of data, it takes time. I tried using it after all the filter conditions though the size reduced to 1 lakh rows but still it is taking time, for about 40 seconds. also I am returning this data to be displayed in a datatable. – Developer .net Feb 21 '22 at 08:08
In that case its probably up to a few other factors. Try to find out what query your code generates https://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework, run it separately on your database to find out if the performance stems from a bad query, or from your application. If its a bad query, try optimizing it with proper indexing. If the query on its own is fast, check how much data you're loading, bandwidth might be a limiting factor. – domi Feb 21 '22 at 17:49

Tolist() takes so long to convert, how can I improve this?

1 Answers1