CODEWITHSUNDEEP
c#entity-frameworkentity-framework-5where-clause
Kabua
Kabua

Reputation: 1019

A simple Where clause stopped working in EF5

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?

Upvotes: 2

Views: 760

Answers (2)

ken2k
ken2k

Reputation: 48985

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

Upvotes: 2

phnkha
phnkha

Reputation: 7872

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.

Upvotes: 1

Related Questions