Linq. How to use AsEnumerable within Linq (sql syntax) query? EF4.1

Question

See two functionally identical queries below, sql and lambda version:

from a in Lines.AsEnumerable()
where a.LineId == SomeGuid
select a

-

Lines.AsEnumerable()
.Where(a => a.LineId == SomeGuid)
.Select(a => a)

Both queries will be translated into SQL that doesn't have WHERE statement, something like

SELECT * FROM Line

In lambda, I can conveniently put AsEnumerable after Where clause and resulting SQL will have WHERE clause. So, lambda query would be like:

Lines
.Where(a => a.LineId == SomeGuid)
.AsEnumerable()
.Select(a => a)

And resulting SQL is SELECT * FROM Line WHERE LineId = @param

Question: How do I do this using Linq SQL syntax? In other words, I would like my resulting SQL statement to have WHERE clause. I want to avoid pulling all records from the table Line. I tried to put AsEnumerable on different places within the query, but I failed to make it work.

EDIT:
In simple statements putting AsEnumerable on the end will work, but if you use projection, then EF complains (NotSupported Exception: Complex type can't be constructed ...) So,

 (from a in Lines
    where a.LineId == SomeGuid
    select new Line
    {
        LineId = a.LineId
    }).AsEnumerable()

Won't work

How do you know that LINQ query does not have WHERE clause? In my experience using LINQ you get the exactly same result, except it is an IQueryable, not IEnumerable. Using extension methods you will get WHERE clause only if you put .AsEnumerable() after .Where(). — LeffeBrune, Aug 13 '12 at 19:31
And Yes, that was my question. I didn't know how to put AsEnumerable after WHERE clause when I would be using Linq SQL syntax. Ladislav answered the question. Seems lambda syntax would provide easier to read code in this case. For some reason (I guess because it looks similar to T-SQL syntax) I tend to stick to Linq SQL syntax. — bobetko, Aug 13 '12 at 20:04

Ladislav Mrnka · Accepted Answer · 2012-08-13T19:34:30.597

4

You will simply do this:

var query (from a in context.Lines
           where a.LineId == SomeGuid
           select a).AsEnumerable();

But in most cases this is not needed. It makes your query enumerable but does not execute your query. The query will be executed only when iterating.

Btw. .Select(a => a) in your examples is not needed because it happens automatically.

Edit:

Linq-to-entities forbid projecting to mapped types so as I mentioned it comment you must first project to anonymous type, call AsEnumerable and project to the real mapped type.

var query =  (from a in Lines
              where a.LineId == SomeGuid
              select new {
                  LineId = a.LineId
              }).AsEnumerable()
              .Select(a => new Line { LineId = a.LineId });

In this case you even don't need anonymous type because you can select a.LineId directly.

edited Aug 13 '12 at 19:34

answered Aug 13 '12 at 19:17

Ladislav Mrnka

360,892
59
660
670

What if I use projection? For example following query throws an error: (from a in context.Lines where a.LineId == SomeGuid select new Line { LineId = a.LineId }).AsEnumerable(); – bobetko Aug 13 '12 at 19:21
It is same with projection until your projection contains some .NET code or tries to project to mapped type (your example). In such case you must project to anonymous type, call `AsEnumerable` and project again to your real type. – Ladislav Mrnka Aug 13 '12 at 19:23
I tried it. It works... Learned something new today. :-) Thanks – bobetko Aug 13 '12 at 19:59

Linq. How to use AsEnumerable within Linq (sql syntax) query? EF4.1

1 Answers1