A simple Where clause stopped working in EF5

Question

I had several queries that look like the two examples below:

// Example 1:
var dataSeries = (from d in DataSeries
                  where d.Symbol == symbol
                  select d).FirstOrDefault();

// Example 2:
return Markets.Where(m => m.DataSeries == dataSeries).ToArray();

Which was working just fine until I ran "Update model from Database...". Now, I'm getting a NotSupportedException:

Unable to create a constant value of type 'MyTest.Symbol'. Only primitive types or enumeration types are supported in this context.

Yes, I verified that symbol and d.Symbol (and m.DataSeries and dataSeries) are of the same types.

And yes, I can change my query to use P/F key relationships like so:

var dataSeries = (from d in DataSeries
                  where d.Symbol.Id == symbol.Id
                  select d).FirstOrDefault();

But I really don't want to have to change all my code to P/F key relationships when object relationships was working just fine.

Question: How do I get my first examples working again?

I don't know how to get the first example working specifically, but I think your application is spending a lot of time doing unnecessary comparisons. The only comparison it would need to do is the key relationship, which should be faster, I believe. — Corey Adler, Feb 06 '13 at 14:50
I think you have to bring your query from 'linq to entities' to 'linq to objects'. You can do so by select from DataSeries.ToList(). Hope that the table DataSeries is not too big. The reason is that the database server dont' know about type. What surprises me is that it seems to work before !! — tschmit007, Feb 06 '13 at 15:01

ken2k · Accepted Answer · 2013-02-06T15:50:12.867

2

You can't write a where clause like that in a LINQ query that is executed against a database. Remember that the code will be translated to SQL and will run on the database engine.

So, for your first example, how would Entity Framework know how to compare two instances of the complex type MyTest.Symbol? EF simply can't, because there is no suitable SQL for such a comparison.

You must specify in the where clause something that the DB engine will understand, which will basically be a SQL WHERE clause based on an ID in a primitive type (int, bigint,...).

You could fetch all your data from database using .ToList(), and apply the where clause on the in-memory resulting list, so the where clause won't have to be translated to SQL:

Markets.ToList().Where(m => m.DataSeries == dataSeries);

but you will lose all the benefits of the DB server: huge usage of memory (everything is loaded in the context), poor performances....etc.

You really should execute the where against the DB, which means you'll have to use primitive types for the comparison:

Markets.Where(m => m.DataSeries.ID == dataSeries.ID);

edited Feb 06 '13 at 15:50

answered Feb 06 '13 at 15:38

ken2k

48,145
10
116
176

You're right. But it's really just an EF restriction that entity objects can't be used like that in predicates. It is possible in linq-to-sql and NHibernate, for instance. EF has the potential to get the primitive key value from an object that's part of its conceptual model and translate it to SQL. It's just not implemented (yet?). – Gert Arnold Feb 06 '13 at 16:13
@IranMan84 - The code was working perfectly again SQL before I updated my model. EF (as Arnold stated) should be able to do this, since it isn't hard to "lookup" the primary Key for the given type and replace the query from obj1 == obj2 to obj1.Id == obj2.Id. Others can do it and it appeared that EF5 was doing it at one point, therefore, I would like the functionally "turned back on". – Kabua Feb 06 '13 at 19:18
What I'm seeing is almost as if EF has lost which property is associated with the primary key and thus can't convert the object compare to an identity compare operation and thus triggering the exception. – Kabua Feb 06 '13 at 19:26
@arnold - thanks for your comments on http://stackoverflow.com/questions/13332002/how-to-mock-the-limitations-of-entityframeworks-implementation-of-iqueryable/13352779#13352779 – Kabua Feb 07 '13 at 02:39

phnkha · Answer 2 · 2013-02-06T15:10:01.760

Your example won't work because EF does not support object comparison in the LINQ query, it doesn't know how to translate it to SQL statement.

Even when you fetch all DataSeries into memory first using DataSeries.ToList(), the comparison d.Symbol == symbol still won't work unless symbol belongs to the loaded list or you must override the Equal method. It's because by default, 2 objects are equal only when they refer to the same instance.

A simple Where clause stopped working in EF5

2 Answers2