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

asenumerableentity-framework-4.1lambdalinq

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

Best Answer

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.

Related Topic