Whether to use AsEnumerable() in LINQ or not?

Question

I understand AsEnumerable() is used to switch from "LINQ to SQL" to "LINQ to Object", so we can use some extra (mostly user defined) methods in our LINQ queries. But from my experience I've seen, using AsEnumerable() makes the query much slower. In that case I can enumerate the list later to apply my own methods, but still the result is pretty slow.

Can anyone suggest any better approach?

Here is some code sample of what I'm trying to do?

With AsEnumerable():

var Data = (from r in _context.PRD_ChemProdReq.AsEnumerable()
                    //where r.RecordStatus == "NCF"
                    orderby r.RequisitionNo descending
                    select new PRDChemProdReq
                    {
                        RequisitionID = r.RequisitionID,
                        RequisitionNo = r.RequisitionNo,
                        RequisitionCategory = DalCommon.ReturnRequisitionCategory(r.RequisitionCategory),
                        RequisitionType = DalCommon.ReturnOrderType(r.RequisitionType),
                        ReqRaisedOn = (Convert.ToDateTime(r.ReqRaisedOn)).ToString("dd'/'MM'/'yyyy"),
                        RecordStatus= DalCommon.ReturnRecordStatus(r.RecordStatus),
                        RequisitionFromName = DalCommon.GetStoreName(r.RequisitionFrom),
                        RequisitionToName = DalCommon.GetStoreName(r.RequisitionTo)
                    }).ToList();

without AsEnumerable():

var Data = (from r in _context.PRD_ChemProdReq
                    //where r.RecordStatus == "NCF"
                    orderby r.RequisitionNo descending
                    select new PRDChemProdReq
                    {
                        RequisitionID = r.RequisitionID,
                        RequisitionNo = r.RequisitionNo,
                        RequisitionCategory = r.RequisitionCategory,
                        RequisitionType = (r.RequisitionType),
                        ReqRaisedOnTemp = (r.ReqRaisedOn),
                        RecordStatus= (r.RecordStatus),
                        RequisitionFrom = (r.RequisitionFrom),
                        RequisitionTo = (r.RequisitionTo)
                    }).ToList();

        foreach (var item in Data)
        {
            item.RequisitionCategory = DalCommon.ReturnRequisitionCategory(item.RequisitionCategory);
            item.RequisitionType = DalCommon.ReturnOrderType(item.RequisitionType);
            item.ReqRaisedOn = (Convert.ToDateTime(item.ReqRaisedOnTemp)).ToString("dd'/'MM'/'yyyy");
            item.RecordStatus = DalCommon.ReturnRecordStatus(item.RecordStatus);
            item.RequisitionFromName = DalCommon.GetStoreName(item.RequisitionFrom);
            item.RequisitionToName = DalCommon.GetStoreName(item.RequisitionTo);
        }

Answer 1

It looks like you confuse these two interfaces as two totally different things. In fact IQueryable is inherited from IEnumerable, so whatever worked for you using with latter, would work with former as well, so don't need to use AsEnumerable.

Behind the scenes though these interfaces are implemented quite differently - IEnumerable will process your collection in memory, and IQueryable would pass the query to the underlying data provider. You can imagine that if a database table contains millions of records and you try to sort it, DB server can do it very quickly (using indexes) so Queryable will shine. For IEnumerable, all the data need to be loaded to your computer memory and sorted there.

For a longer answers search for "IEnumerable IQueryable difference" on SO, you will see a plenty of details:

Random Link 1 Random Link 2

Update: If you remove call .ToList from your second example, then the result won't be automatically loaded to memory. At this point you need to decide which items you want to store in memory and call your functions for them only.

var Data = (from r in _context.PRD_ChemProdReq
           orderby r.RequisitionNo descending
           select new PRDChemProdReq
           {
               // do your initialization
           });

var subsetOfData = Data.Take(100).ToList(); // Now it's loaded to memory
foreach (var item in subsetOfData)
{
    item.RequisitionCategory = DalCommon.ReturnRequisitionCategory(item.RequisitionCategory);
    item.RequisitionType = DalCommon.ReturnOrderType(item.RequisitionType);
    item.ReqRaisedOn = (Convert.ToDateTime(item.ReqRaisedOnTemp)).ToString("dd'/'MM'/'yyyy");
    item.RecordStatus = DalCommon.ReturnRecordStatus(item.RecordStatus);
    item.RequisitionFromName = DalCommon.GetStoreName(item.RequisitionFrom);
    item.RequisitionToName = DalCommon.GetStoreName(item.RequisitionTo);
}

Now if you actually need to assign these properties for all your data and data can be arbitrary large, you need to work out a strategy how you can do it. The very simple option is to save them to database to a new table, then the size of processed data will be only limited by the capacity of your database.

Answer 2

AsEnumerable() will be slower if you add any query elements after it.

Even though AsEnumerable() doesn't execute the query directly, applying a where or orderby after the AsEnumerable() means that the Sql will get all items and then apply filtering and ordering to the collection in memory.

In short:

• Without AsEnumerable() = filtering and ordering done in SQL
• With AsEnumerable() and then Where or orderby = applied to whole collection brought into memory.

You can only run user defined functions on a collection in memory (as your Linq to SQL will not be able to interpret your functions to SQL code). So your second code snippet (without AsEnumerable()) is probably best.

The only other alternative is to apply your user defined functions in SQL itself.

Answer 3

OK guys, I took notice of different points from all of you & came up with this:

var Data = (from r in _context.PRD_ChemProdReq.AsEnumerable()
                    //where r.RecordStatus == "NCF"

                    join rf in _context.SYS_Store on (r.RequisitionFrom==null?0: r.RequisitionFrom) equals rf.StoreID into requisitionfrom
                    from rf in requisitionfrom.DefaultIfEmpty()

                    join rt in _context.SYS_Store on (r.RequisitionTo == null ? 0 : r.RequisitionTo) equals rt.StoreID into requisitionto
                    from rt in requisitionto.DefaultIfEmpty()

                    orderby r.RequisitionNo descending
                    select new PRDChemProdReq
                    {
                        RequisitionID = r.RequisitionID,
                        RequisitionNo = r.RequisitionNo,
                        RequisitionCategory = DalCommon.ReturnRequisitionCategory(r.RequisitionCategory),
                        RequisitionType = r.RequisitionType == "UR" ? "Urgent" : "Normal",
                        ReqRaisedOn = (Convert.ToDateTime(r.ReqRaisedOn)).ToString("dd'/'MM'/'yyyy"),
                        RecordStatus = (r.RecordStatus=="NCF"? "Not Confirmed": "Approved"),
                        RequisitionFromName = (rf==null? null: rf.StoreName),
                        RequisitionToName = (rt == null ? null : rt.StoreName)
                    });

First of all I removed my ToList() which does nothing but executes the query which is already done when I called AsEnumerable(). No points to execute the same query twice. Also my custom method calls within the select block was also playing a major part slowing down things. I tried lessen the method calls, rather used join where possible. It makes things pretty faster. Thank You all.