CODEWITHSUNDEEP
c#linqentity-framework
Tx3
Tx3

Reputation: 6916

Where statement with list of keys

My problem is that I cannot get Where statement working correctly with Entity Framework.

I have a class that has int and long properties that define one row in the database. Let's call those properties Id & Parent

public class Update
{
    public long Id { get; set; }
    public int Parent { get; set; }
}

Then I have list of those Updates and I want that each database row should match one those updates (both properties) in the list.

What I have tried is that I convert those updates to an anonymous type and try to find only those rows that have a perfect match: both int & long are same.

// anon type
var mapping = updates.Select(o => new { id = o.Id, parent = o.Parent}).ToList();
var results = from fa in Uvw_MyTable 
              // PROBLEMATIC WHERE
              where mapping.Contains(new { id = fa.Id, parent = fa.Parent })
              select new { fa };

But unfortunately I always get an error: Unable to create a constant value of type 'Anonymous type'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

I have also tried to use Any like this:

where mapping.Any(a => fa.Id == a.id && fa.Parent == a.parent)

But I get the same error. The same thing happens also when I use the List of Update objects. Unable to create a constant value of type 'xxx.Update'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

Any thoughts how to solve this issue?

Update

This exactly same query works very well in the LINQPad 4

Update 2

Is the problem related to this question?

Update 3

As Maarten pointed out this problem needs solution that uses different approach to re-produce the same results.

Upvotes: 0

Views: 133

Answers (3)

Maarten
Maarten

Reputation: 22945

You are actually executing two queries in your code.

The first query is:

var mapping = updates.Select(o => new { id = o.Id, parent = o.Parent}).ToList();

The second query is (ok, the query is not iterated over, so technically the query is not yet executed here - nitpicking):

var results = from fa in Uvw_MyTable 
              where mapping.Contains(new { id = fa.Id, parent = fa.Parent })
              select new { fa };

The first query is defined and executed in your single statement since you are using the ToList() method. Since this is now a list of objects, instead of the IQueryable, EF cannot translate this into SQL. You have to leave it as an IQueryable. If you change it to the following, it should work.

var mapping = updates
    .Select(o => new { id = o.Id, parent = o.Parent}); // NOT ToList() !!
var results = from fa in Uvw_MyTable 
              where mapping.Contains(new { id = fa.Id, parent = fa.Parent })
              select new { fa };

To explain the error that EF gives - EF can only translate either IQueryable's into SQL, or primitive types (ints, strings, etc) into parameters. EF cannot translate objects into SQL, since it has no way of transporting the value of objects to the database in a meaningful way.

UPDATE:

The first query (I see now) is not a query at all, it's source is a variable called updates. If this is a list of objects in-memory, than this query cannot be translated as such, and you have to redesign you query (for example, loading Uvw_MyTable is memory). If 'updates' is an IQueryable (from the same context), than my answer given above should work.

Upvotes: 1

Tinku
Tinku

Reputation: 1608

In entity framework or Linq to sql, linq queries will ultimately be translated to sql queries. your linq query cant be translated to sql queries as you are using types or classes which is not convertible to sql .

Upvotes: 0

Dan Puzey
Dan Puzey

Reputation: 34198

I'm not sure I've understood your code/question correctly, but it would seem to me that something like this should work:

var results = from fa in Uvw_MyTable 
                    where updates.Any(u => u.Id fa.Id && u.Parent = fa.Parent)
                    select fa;

I'm not clear on what your desired behaviour with the OtherTable is, but the general form of the linq expression above is a lot more efficient than the one you're using.

The key point is that you don't need to create anonymous types in order to compare multiple fields; therefore the first select (into mapping) is redundant.

Upvotes: 1

Related Questions