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

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();
}

CodePudding user response：

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.

CodePudding user response：

Use more sql. Assuming you are doing this in a function so ...

If(LOT.IsNullOrEmpty() || NCR.IsNullOrEmpty()) return 0; //or null or just return
// Otherwise select rows
var wdata = (from s in db.VIEW_ADDED_LOT
         where s.NCRNO == NCR and s.LOT_NO_SPL == 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
         });

If you still need some tolist operations. It's smaller and faster now