Am I Misunderstanding Linq to SQL .Asenumerable()

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

Why use .AsEnumerable() rather than casting to IEnumerableT?

Readability is the main issue here. Consider that

Table.AsEnumerable().Where(somePredicate)

is far more readable than

((IEnumerable<TableObject>)Table).Where(somePredicate).

Or imagine wanting to execute part of the query on the SQL Server and the rest in memory:

Table.Where(somePredicate)
     .Select(someProjection)
     .AsEnumerable()
     .SomethingElse()

versus

((IEnumerable<SomeProjectionType>)Table.Where(somePredicate)
                                       .Select(someProjection))
                                       .SomethingElse()

Now, as for why such a method is useful at all think of the example of a Table in a LINQ to SQL DataContext. As Table is an IQueryable it implements IEnumerable. When you invoke a Where method on such a Table and enumerate through the results, code is executed that eventually causes a SQL statement to be executed on a SQL Server. What AsEnumerable does is says, no, I don't want to use the LINQ to SQL provider to execute the Where, I want to use the LINQ to Objects implementation of Where.

Thus enumerating over

Table.Where(somePredicate)

causes a query to be executed on a SQL Server whereas enumerating over

Table.AsEnumerable().Where(somePredicate)

brings the table represented by Table into memory and executes the Where functionality in memory (and not on the SQL Server!)

This is the point of AsEnumerable: to allow you to hide a specific implementation of IEnumerable methods and instead use the standard implementation.

Returning IEnumerableT vs. IQueryableT

Yes, both will give you deferred execution.

The difference is that IQueryable<T> is the interface that allows LINQ-to-SQL (LINQ.-to-anything really) to work. So if you further refine your query on an IQueryable<T>, that query will be executed in the database, if possible.

For the IEnumerable<T> case, it will be LINQ-to-object, meaning that all objects matching the original query will have to be loaded into memory from the database.

In code:

IQueryable<Customer> custs = ...;
// Later on...
var goldCustomers = custs.Where(c => c.IsGold);

That code will execute SQL to only select gold customers. The following code, on the other hand, will execute the original query in the database, then filtering out the non-gold customers in the memory:

IEnumerable<Customer> custs = ...;
// Later on...
var goldCustomers = custs.Where(c => c.IsGold);

This is quite an important difference, and working on IQueryable<T> can in many cases save you from returning too many rows from the database. Another prime example is doing paging: If you use Take and Skip on IQueryable, you will only get the number of rows requested; doing that on an IEnumerable<T> will cause all of your rows to be loaded in memory.

What happens when my LINQ query gets database records as Enumerable and accesses records in a foreach loop?

Which of the following scenarios is correct?

• Application fetches entire records from Persons table by one request to database, then access each record from memory.
• In each step of foreach loop, application fetches only one record from database.

None of these statements is entirely correct. But first, let me say that in your code .AsEnumerable() doesn't actually do anything. You can remove it without changing anything logically. IQueryable implements IEnumerable and foreach executes IEnumerable methods under the hood. So it addresses IQueryable "as enumerable".

Now for the reading part. From the application's point of view, the second statement is closest to the truth. It receives all entities in persons one-by-one. It's not before the end of the loop that all persons have been made available.

But the actual lower-level reading happens in chunks. As described here, the client stores raw data from the database in network buffers. Depending on the size of these buffers and that of the result set (i.e. the number and size of persons) it is possible that all records are read at once. "Large" amounts of data will require multiple reads.

For the application, that doesn't really matter though. When it comes to performance optimization, I think the last thing we should think of is playing with network buffer size. So to reword the second statement more correctly:

• In each step of foreach loop, only one record from the database is sent to the application's scope.

Does Enumerable.AsEnumerable() force the evaluation of the expression?

In short, no. It does absolute nothing at all, it just return the input collection, typed with a different interface.

It's only practical purpose it to switch from IQueryable<T> to IEnumerable<T>, so that further LINQ methods are performed with the enumerable version instead of the queryable, making evaluation to go to "client-side".

But, in itself, it doesn't do anything.

Linq edit result without need of reconstruct(.AsEnumerable() ) body

Option 1:

you can make your code look better by only selecting your tables

select new 
{
    islem = islem,
    hasta = hasta,
}

Option 2:

Depending on your schema, it might be possible to avoid the explicit join (and therefore the intermediate model) altogether by using navigation properties (EF will still do a db join behind the scenes)

Option 3:

You can also just slap a helper property on your model

class MyModel
{
    public int KurumAdi
    {
        ...
        get
        {
            return getKurumAdiByKod(Convert.ToInt32(this.KurumKodu));
        }   
    }
}

Do i really need use AsQueryable() on collection?

IQueryable is required/recommended for objects coming from remote source (like from database).

For in memory collections it is of no use.

AsQueryable is used when expression tree is to be constructed.

I can think of scenario where it is best fit. In your example let say you require some information from database based on student ID.

Now student is in memory collection. You need to fire database query based on student ID.

  var studentList = Students.Select(s => s.Id).AsQueryAble().Select(i => remoteDBProvider.GetInfo(i));

Any further operation on the studentList will be invoked from IQueryAble interface ( query expression), and will fetch only those records from data source, which should be returned as final query result (as long as data source, return value of remoteDBProvider.GetInfo in the example, supports QueryProvider).

LINQ query retrieves whole record instead of one field

It's supposed to read only the fileName field

No, it does not. Here is what is going on: your query execution happens in two places - in RDBMS and in memory. The db.GetTable<RawFile>() happens in RDBMS; everything after it happens in memory, because you use AsEnumerable().

The portion of the query where the projection happens (i.e. row.fileName column is extracted from the whole row) is happening in memory. The RDBMS part of the query does not know about this projection. db.GetTable<RawFile>() is all the SQL-generating LINQ provider sees, so naturally it returns the entire row.

If you write a combined query against your SQL source, the projection will happen in SQL:

var linqNameList =
    from row in db.GetTable<RawFile>()
    select row.fileName;

Why LINQ to Entities does not recognize the method 'System.String ToString()?

That can't be converted to SQL. I guess, in theory, it could, but isn't implemented.

You just need to perform your projection after you have your results:

var pricingSecurityID = (from m in dbContext.Reporting_DailyNAV_Pricing
                                     select m.PricingSecurityID).AsEnumerable()
    .Select(x => new SelectListItem{ Text = x.ToString(), Value = x.ToString() });

---

Related Topics