Understanding .Asenumerable() in Linq to SQL

Understanding .AsEnumerable() in LINQ to SQL

The reason for AsEnumerable is to

AsEnumerable(TSource)(IEnumerable(TSource))
can be used to choose between query
implementations when a sequence
implements IEnumerable(T) but also has
a different set of public query
methods available

So when you were calling the Where method before, you were calling a different Where method from the IEnumerable.Where. That Where statement was for LINQ to convert to SQL, the new Where is the IEnumerable one that takes an IEnumerable, enumerates it and yields the matching items. Which explains why you see the different SQL being generated. The table will be taken in full from the database before the Where extension will be applied in your second version of the code. This could create a serious bottle neck, because the entire table has to be in memory, or worse the entire table would have to travel between servers. Allow SQL server to execute the Where and do what it does best.

c# Understanding AsEnumerable in LINQ query

You understanding is correct. After calling AsEnumerable the data source can no longer be queried and it falls back to a simplistic "give me everything" mode, which means all the data is transferred to the client and any further operations are done locally.

That's also the reason why the query doesn't work as written: in order for it to work, all the expressions you use in LINQ methods must be translatable to whatever language is understood by your data source -- and since it is the query provider's responsibility to do the translation, you will also be constrained by whatever it is programmed to support.

In this specific case (assuming EF) the query can be fixed to work in queryable mode by manually substituting property accesses to the canonical function TruncateTime:

.Where(m => m.All(s => s.Status == 1) 
    && EntityFunctions.TruncateTime(m.Key.CreatedDate) ==
       EntityFunctions.TruncateTime(CurrentDateTime()))

Whether to use AsEnumerable() in LINQ or not?

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.

Am I misunderstanding LINQ to SQL .AsEnumerable()?

Calling AsEnumerable() does not execute the query, enumerating it does.

IQueryable is the interface that allows LINQ to SQL to perform its magic. IQueryable implements IEnumerable so when you call AsEnumerable(), you are changing the extension-methods being called from there on, ie from the IQueryable-methods to the IEnumerable-methods (ie changing from LINQ to SQL to LINQ to Objects in this particular case). But you are not executing the actual query, just changing how it is going to be executed in its entirety.

To force query execution, you must call ToList().

What is the effect of AsEnumerable() on a LINQ Entity?

AsEnumerable() is effectively a cast to IEnumerable<T>, which makes member resolution find members of Enumerable instead of Queryable. It's usually used when you want to force part of a query to run as SQL (or similar), and the remainder to run using LINQ to Objects.

See my Edulinq blog post on it for more information.

Now you've actually got two calls to AsEnumerable. I can see how removing the first but not the second could cause problems, but have you tried removing both?

var results = from p in pollcards
              join s in spoils
                 on new { Ocr = p.OCR, fileName = p.PrintFilename } 
                 equals new { Ocr = s.seq, fileName = s.inputFileName }
              where p.Version == null
              orderby s.fileOrdering, s.seq
              select new ReportSpoilsEntity
              {
                  seq = s.seq,
                  fileOrdering = s.fileOrdering,
                  inputFileName = s.inputFileName,
                  Ocr = p.OCR,
                  ElectorName = p.ElectorName
              };

Significane of using AsEnumerable() in query to take anonomous value in to view model

Your first select is querying the database and returns IQueryable<T>. .AsEnumerable() make it IEnumerable<T>. IQueryable<T> extends IEnumerable<T> so whatever you can do with IEnumerable<T>, you can also do with IQueryable<T>.

Another big difference is that IEnumerable<T> fetches the entire table and applies the query on that afterwards, whereas IQueryable<T> only fetches the filtered data.

Note that AsEnumerable() does not materialise the query yet. You often use .ToList() to materialize your query so other non sql statements can be performed on the result set as per this example

You can simplify your code to

var data = (from temp in context.UserDetails select temp)
  .Select(d => new UserDetailsModel
  {
    Fullname = d.fullName,
    Email = d.Email
  }).ToList());

LINQ 'AsEnumerable' and the Entity Framework

I'd do

var ids = from o in dbcontext.Orders
          where o.ID==1
          select new { ID = o.ID }; 

var names = from i in ids.AsEnumerable()
            select new Order { Name = GetName(i.ID) };

i.e. do as much querying as possible in the database, and then only perform the ID-to-name transformation in C#.

Linq Queries containing AsEnumerable() equivalence

Are this 2 queries functionally equivalent?

If by equivalent you means to the final results, then probably yes (depending how the provider implements those operations), the difference is in the second query you are using in-memory extensions.

I mean, does the order of "AsNumerable()" in the query change the
number of data items retrieved from the client, or the way they are
retrieved?

Yes, in the first query, Where and OrderBy will be translated to SQL and the Select will be executed in memory.

In your second query all the information from the database is brought to memory, then is filtered and transformed in memory.

---

Categories is probably an IQueryable, so you will be using the extensions in Queryable class. this version of the extensions receive a Expression as parameter, and these expression trees is what allows transform your code to sql queries.

AsEnumerable() returns the object as an IEnumerable, so you will be using the extensions in Enumerable class that are executed directly in memory.

---

Related Topics