SQL double 'IN' clause in lambda expression

Question

I have a table (and Dto) with two primary keys: int and string types:

public class Foo
{
    public int Id { get; set; }
    public string Data { get; set; }

    // other members...
}

So, I have a List<Dto> dtoList, which I got from one DbContext. I need to get the same dtos in other DbContext. The problem is that the PK is a pair of two columns.

Here's how I did it (and it seems it doesn't work):

public IEnumerable<FooDto> GetFooByPrimaryKeys(List<int> ids, List<string> data)
{
    return FooContext.Foo.Where(f => ids.Contains(f.Id) && data.Contains(f.Data));
}

But it returns a little bit more rows that I need. I mean, I'm passing 1300ish pairs and getting 1500ish dtos, so something's not right.

I'm not sure If I took the correct approach.

`The problem is that the PK is a pair of two columns.` - If this is related to your question, can you expand on this and why it's a problem? And how your solution addresses that problem? — Mark C., Apr 19 '17 at 15:53
I wonder if this would work `var compound = ids.Zip(data, (i,d) => new{Id = i, Data=d}).ToList(); return FooContext.Foo.Where(f => compound.Contains(new{f.Id, f.Data}));` — juharr, Apr 19 '17 at 15:55
Although the problem is obvious I do not see an easy fix for you without writing your own Sql given the size of the list you are passing in. The issue is you are doing an OR match on either list when you should be doing a AND on a tuple (a set of keys). I am not sure it is possible to translate this into an EF store query without having to write some manual sql. — Igor, Apr 19 '17 at 16:00
Thing is you cannot easily do that with EF, because you need a join and you cannot just join with in-memory structure in EF. — Evk, Apr 19 '17 at 16:02

score 2 · Accepted Answer · edited May 23 '17 at 12:17

2

You need to pass in the unique pairs of ints and strings to the method. Instead of passing in two separate lists you could pass in a single list with tuples:

public IEnumerable<FooDto> GetFooByPrimaryKeys(List<Tuple<int, string>> keys)
{
    return FooContext.Foo.Where(f => keys.Any(x => x.Item1 == f.Id && x.Item2 == f.Data));
}

If Entity Framework can't handle this you could either choose to fetch all records and then filter the in-memory list of realized records:

public IEnumerable<FooDto> GetFooByPrimaryKeys(List<Tuple<int, string>> keys)
{
    return FooContext.Foo.ToList().Where(f => keys.Any(x => x.Item1 == f.Id && x.Item2 == f.Data));
}

If you have a lot of records you may want to consider writing a stored procedure:

How to pass an array into a SQL Server stored procedure

Passing an array/table to stored procedure through Entity Framework

Anyway, the main issue here is that you need to compare against pairs of values.

edited May 23 '17 at 12:17

Community

1
1

answered Apr 19 '17 at 15:55

mm8

163,881
10
57
88

1

I was thinking the same thing when I read this but does this translate correctly to a proper data store expression by EF? – Igor Apr 19 '17 at 15:56
1

And this will be transformed into valid sql query by EF? I doubt that. – Evk Apr 19 '17 at 15:56
See my edited answer. – mm8 Apr 19 '17 at 16:02
Unfortunately I can't write SP, but I will try those two other solutions. I think that fetching all items and doing "the math" in memory isn't that bed idea – Louisa Bickley Apr 19 '17 at 16:11
Did you mark this as the answer with the possibility that it doesn't even work? Or did this fit your needs @LouisaBickley – Mark C. Apr 19 '17 at 16:50
1

@MarkC.What? I've not marked any answer yet, what do you mean? – Louisa Bickley Apr 19 '17 at 16:54

score 1 · Answer 2 · answered Apr 19 '17 at 15:55

The parameters in your method don't seem to be treating your compound key like a compound key.

GetFooByPrimaryKeys(List<Tuple<int, string>> ids){ ... }

That'll give you the key pairs you're looking for.

Then .Where(f => ids.Contains(new Tuple<int, string> (f.Id, f.Data)))...

NOTE: I haven't tried this code, not sure if it'll be compatible with EF's lambda engine. Might be a good place to start tho.

SQL double 'IN' clause in lambda expression

2 Answers2