Enterprise Framework LINQ Queries failing
So I'm just playing around with some simple LINQ queries against EF model I created. And I'm finding that for many query operations it plain out fails.
For example something as simple as this:
var result =from cin context.tt_customers
where c.Entered >DateTime.UtcNow.AddDays(-180)
select c;
fails with:
The 'System.DateTime AddDays(Double)' method is not recognized by LINQ to Entities, and cannot be translated into a store expression.
I can understand the SQL parser not understanding certain operations that can't be translated into SQL, but why is it failing on a right side expression which should translate to a value to compare against?
In order to get this to work I'd have to what, create the value first and then use that value in the query? Of course that wouldn't work if hte expression was based on something in the cust object.
One big question that comes to mind is there any synergy between the LINQ parsing the LINQ to SQL does and the Entity Framework does at this point, or is this basically completely reinventing the wheel?
+++ Rick
Hi Rick,
If you put the time creation outside the linq query it works. Here's an example that I used against the Northwind Orders table.
DateTime time = DateTime.UtcNow.AddYears(-12);
NorthwindEntities context = new NorthwindEntities();
var result = from o in context.Orders
where o.ShippedDate > time
select o;
foreach (Order ord in result)
{
// just dump something.
Console.WriteLine(ord.OrderID);
}// foreach
I hope this helps.
regards,
Brian
Hey Brian, thanks for the workaround.
As only a start in responding to Rick's last question, which is really important...
LINQ to SQL parses directly to a TSQL query.
LINQ to Entities query is first parsed to an EntitySQL query and then object services creates a TSL query.
LINQ to Objects can handle that DateTime function on the fly as can LINQ to SQL. So I too am curious where the line is? Does LINQ to Entities just start at point 0 for it's parsing, or does it start with LINQ's parsing and then finish up with it's own?
Since one of the benefits of LINQ is that there is supposed to be a short learning curve from one "flavor" to the next - and that there are a LOT of commonalities between querying in one vs. the other, (and with a nod to the complexity of what LINQ to Entities is pulling off) - how close is it to the rest?
P.S.
Just as an aside, in the docs there is a section on "Supported and Unsupported Methods (LINQ to Entities)". That won't likely address the particular issue rick has pointed out, but will be interesting to see which LINQ query operators won't work.
I might cull out the unsupported list and blog it.
P.P.S.
Rick: check the topic "CLR MEthod to canonical Function Mapping' in the docs that came with beta 2.
"Certain CLR methods are converted to command tree canonical functions, which can be executed on the database. If a CLR method cannot be mapped to a command tree canonical function, an exception will be thrown when translation occurs."
Hi Brian,
Thinking about this a little more I can actually understand the difficulty in the query generation a little better. But it also makes your workaround fail in other scenarios.
What if I actually need to compare two database fields with data syntax?
where
c.Entered > c.Updated.AddDays(-15) That's failing and there's no way to get a static value into the query for this. (and this does work in LINQ TO SQL BTW).
Ok - that'll come later I suspect, but things like this that really worry me because it's a fairly simple scenario and it fails to parse into SQL. Worse though - it fails at RUNTIME, not compile time. So how would you express that?
I notice that explicit declarations with .Where allow for string predicates and it appears that you can provide some raw SQL there. But I couldn't get that to work either:
string where = " it.entered > DateAdd( 'day', -30, it.updated)"; var result = context.tt_customers.Where(where); (although it does work fine with a static value or parameterized value).
No overload of canonical function 'Edm.DateAdd' is compatible with given argument types in 'DateAdd(SqlServer.varchar, SqlServer.int, SqlServer.smalldatetime)', consult provider specific functions for potential store functions with similar functionality, near function 'DateAdd()', line 6, column 22.
And what the heck is 'it'? <bg>
@Julie - thanks for the clarification on EntitySql - I'm just starting out experimenting and haven't gotten around yet to figuring out how it works behind the scenes.I remember reading about this before though.
I guess my big question is (as it is with LINQ to SQL). What do you do if these meta expressions can't express your SQL properly? IOW, what's the proper way to express SQL that gives you the widest possible SQL compatibility.
Going to have to read up on Entity SQL but first thought is - yet another freaking SQLesque syntax? There's nothing worse than several similar syntaxes that are all incompatible with each other and have their own special variations.
+++ Rick
Rick
You should also be clear on the difference between LINQ to Entities and Entity SQL. You can use Entity SQL directly and have more control over your queries.
LINQ to Entities will be limited by the strongly typed nature of the queries. Entity SQL, while it makes you go back to the world of string based queries and hoping they work at runtime 
, allows you to write much more declarative queries. Yet it is still not T-SQL.
There are a few ways to write Entity SQL queries. Most typically you'll want to use ObjectServices and create ObjectQueries.
@Julie - yes I'm not really worried about the runtime aspect - more pointing that out, that it occured even when you run a 'typed' LINQ query.
As to the string queries I'm really glad to see that .Where supports string query expressions for example, although I'm pretty put off by the additional different syntax. So if you want to use this tool correctly you'll be using 3 different SQL syntaxes (is that a word? <g>):
T-Sql for 'raw' queries
LINQ queries
Entity SQL string queries
That's a bear to keep straight especially since the syntax is all similar but still different.
I'll shut up now until I have a closer look at the Entity Sql syntax. Know the right place to find out more info? Most of the stuff in the overview category is kind of out of date.
Thanks for your help,
+++ Rick
Yes and now you see some of the challenges we have had trying to teach or write about Entity Framework. It takes about 1000 words just to list the options!
But wait there's more!
There are TWO ways to call Entity SQL quereies. One returns objects, another, EntityClient, returns good ol' datareaders.
I have an Entity Framework Overview article on Rod's desk. It'll be in CoDe in the next issue or so.
Of course, you can come to all of the EF sessions in teh Data Track at DevConnections in November! 
> Session at DevConnections
I probably will <s>. But I'll have to be up on some of this at least in passing seeing that I am doing a LINQ, data, middle tier session myself...
+++ Rick
To answer Julie's questions around "where is the line" for translation: there is a set of .NET operations (i.e. known functions) that we'll happily translate. That set will be properly documented (it's already in a document, but I don't know if it made it to the public documentation).
I also wanted to clarify the translation process for LINQ queries in LINQ to Entities: we do not translate LINQ queries into Entity SQL. The entity framework internally uses an algebraic representation of queries called "cannonical query trees" or CQTs for short. The Entity SQL parser translates Entity SQL strings into CQTs, which then the query pipeline takes it for execution (including view expansion, simplification, etc.). In the LINQ case, the LINQ to Entities translator converts the LINQ expression tree into a CQT, and then hands it to the query pipeline the same way. So once translated it's the same code all the way down, the only thing that's different is how we produce CQTs. We do not go LINQ->Entity SQL->CQTs because it would be extra cost (and also because we already have CQTs do we don't need to use Entity SQL as another intermediate representation).
Pablo Castro
Technical Lead
Microsoft Corporation
http://blogs.msdn.com/pablo
Thanks Pablo. I have definitely been under the wrong impression about LINQ to Entities by way of Entity SQL for a while. I'm not sure where I got that information (or misnformation or perhaps I somehow misinterpreted all by myself, but I'm glad to have (and now be able to share) this better understanding.
Thanks Pablo for the clarification. Just out of curiosity is there any synchronicity between what LINQ to SQL does and what Entity Framework does? I'm curious because there are number of things that don't appear to work with EF that does work with LINQ to SQL in terms of expressions (like the above example).
This is one of those things on which the whole system hinges I think. If confidence is low that you can't express a query either through LINQ to Entities or even with Entity SQL the whole thing kind of falls apart. It looks that in LINQ to SQL at least you can get a raw SQL statement to the backend, but that's a lot more difficult to do with EF because it has to be backend agnostic.
So assuming for a seond there are queries that can't be expressed what's the workaround scenario if any?
(Stored Proc/View creation I presume)
+++ Rick
To give a sense of what is shared and what isn’t between LINQ to SQL and LINQ to Entities (both in terms of modules and behavior) I’ll repeat some information that’s probably familiar to most people on this thread…
LINQ providers support integrated query via the IQueryable and IQueryProvider interfaces. The provider’s job is basically to translate LINQ expression trees into native queries. LINQ to SQL and LINQ to Entities accomplish this translation in roughly the same way, though nothing is shared beyond the System.Linqcomponents in System.Core.dll.
Matt Warren’s IQueryable series gives a more detailed overview (http://blogs.msdn.com/mattwar/archive/2007/07/30/linq-building-an-iqueryable-provider-part-i.aspx) but I’ll include the highlights here to provide some context:
1. At compile time, user code is translated into (or directly uses) expression builder calls.
2. At runtime, calls to System.Linq.Queryable methods yield additional expression trees with argument expressions inline.
3. The provider is passed a LINQ expression which it translates into a “query” expression. LINQ to SQL and LINQ to Entities use different internal representations of the expression (a variant of LINQ expressions and Canonical Query Trees respectively). This step includes the replacement of method calls with query operators and the replacement of member accesses with store function calls.
4. Each provider then compensates for non-relational structures in the query (e.g. types, nested collections in results, etc.)
5. Native queries are generated and executed.
6. Query results are reshaped (basically the inverse of step 4).
As I mentioned, there is really nothing shared between LINQ to SQL and LINQ to Entities beyond step 2. The example in this thread illustrates a couple of differences between the two stacks in the handling of step 3 and step 4.
Implicit vs. explicit client evaluation
LINQ to SQL is a hybrid provider in the sense that it supports evaluation of parts of the query in the client and parts in the store. As a result, the following query succeeds in LINQ to SQL but fails in LINQ to Entities:
var query = from o in context.Orders
where GetCutoffDate() > o.OrderDate
select o;
As Brian suggested, it must be rewritten as:
DateTime cutoffDate = GetCutoffDate();
var query = from o in context.Orders
where cutoffDate > o.OrderDate
select o;
This makes the boundary between client and store evaluation explicit. There is a tradeoff between self-containment of queries and predictability of behavior for non-expert users.
Canonical functions vs. store functions
Of course, the sub-expression mentioned in this thread could be entirely translated into Transact-SQL functioncalls:
DateTime.UtcNow.AddDays(-180)
becomes
DATEADD(day, -180, GETUTCDATE())
Note that although LINQ to SQL could translate the above expression into Transact-SQL, it chooses to evaluate it on the client because it is uncorrelated. The behavior is different depending on whether results are correlated or not.
In Beta 2, LINQ to Entities does not support the DateTime.AddDays method simply because a corresponding “canonical function” does not exist. Canonical functions are store agnostic and can be implemented by arbitrary Entity Frameworks providers. We plan on introducing a few more DateTime-related functions in the next release (the usual disclaimers apply) at which point this expression will be supported by LINQ to Entities.
Regarding Rick’s question: Entity-SQL supports both canonical and store-specific functions. For functions not supported by LINQ to Entities, this provides the cleanest workaround. Where the gap is due to unsupported CLR methods – in either LINQ to SQL or LINQ to Entities – consider explicitly breaking up the query into client- and store- side elements. For instance, to extend the above example:
DateTime cutoffDate = GetCutoffDate();
var query = from o in context.Orders
where cutoffDate > o.OrderDate
select o;
var hybridQuery = from row in query.AsEnumerable()
select ShapeResult(row.Oid, row.Description, row.OrderDate);
Recommendation:
When working with LINQ to Entities, keep in mind that boundaries between client- and store- execution are explicit. To use a value computed on the client within a query, assign the value to a local variable before executing (see the assignment to cutoffDate above). To perform client-side processing of results, use the AsEnumerable method to yield results from the store query and then apply client-side logic (see the assignment to hybridQuery above).
Thanks,
-Colin
