Where clause with a list of field combinations

Question

I have a list of field combinations that I want to search for. The list can realistically contain up to 100 items, the table I'm querying has a little over 1 million records at this time.

An example:

create table mytable
(
    foo int not null
    ,bar int not null
    ,baz int not null
)

insert into
    mytable
values
    (1, 1, 11)
    ,(1, 2, 12)
    ,(1, 3, 13)
    ,(2, 1, 21)
    ,(2, 2, 22)
    ,(2, 3, 23)
    ,(3, 1, 31)
    ,(3, 2, 32)
    ,(3, 3, 33)

One possible way to retrieve data:

select
    foo
    ,bar
    ,baz
from
    mytable
where
    (foo = 1 and bar = 3)
    or (foo = 2 and bar = 1)
    or (foo = 3 and bar = 2)

Another possible way:

declare @filtercombos table
(
    foo int not null
    ,bar int not null
)

insert into
    @filtercombos
values
    (1, 3)
    ,(2, 1)
    ,(3, 2)

select
    mytable.foo
    ,mytable.bar
    ,mytable.baz
from
    @filtercombos fc
    left join mytable on mytable.foo = fc.foo and mytable.bar = fc.bar

Both will return this data:

foo         bar         baz
----------- ----------- -----------
1           3           13
2           1           21
3           2           32

Now, if this was a list of single values, I could do .Where(item => myList.Contains(item.foo)). How can I do a query like above? The only thing I can think of is to execute SQL on the DbContext, but I'd like to avoid that if possible.

score 3 · Accepted Answer · edited May 23 '17 at 11:58

3

LINQKit's PredicateBuilder is what you need!

var query = from u in context.Users select u;
var pred = Predicate.False<User>();

foreach(var filter in combofilers)
    pred = pred.Or(u => u.Username == filter.Foo && u.Language == filter.Bar);

return query.Where(pred.Expand()).FirstOrDefault();
// or return query.AsExpandable().Where(pred).FirstOrDefault();

Dynamic where clause (OR) in Linq to Entities

edited May 23 '17 at 11:58

Community

1
1

answered Jun 11 '13 at 15:16

Bertzor

153
8

This works, the Profiler shows that the query is expanded to `where (foo = 1 and bar = 3) or (foo = 2 and bar = 1) or (foo = 3 and bar = 2)`. – user247702 Jun 11 '13 at 15:26

score 0 · Answer 2 · answered Jun 11 '13 at 14:45

0

If you already have the list of combinations in another list you could do something like this.

var query = from m in Context.mytables
    select m;

foreach (var foobar in foobars)
{
    query = query.Where(x => x.foo == foobar.foo && x.bar == foobar.bar);
}

return query.ToList();

answered Jun 11 '13 at 14:45

Johann Strydom

1,482
14
18

Chaining `Where` is an `and` operation. – user247702 Jun 11 '13 at 14:49
Sorry I didn't look closely. You can use the Predicate Builder solution from this question for the Or. http://stackoverflow.com/questions/782339/how-to-dynamically-add-or-operator-to-where-clause-in-linq – Johann Strydom Jun 11 '13 at 14:54
Here's a better example. http://stackoverflow.com/questions/2101540/linq-or-equivalent-of-where – Johann Strydom Jun 11 '13 at 14:56

score 0 · Answer 3 · edited May 23 '17 at 11:52

0

Alternatively something similar to the answers on this question might help.

Entity Framework - Join to a List

edited May 23 '17 at 11:52

Community

1
1

answered Jun 11 '13 at 14:47

Johann Strydom

1,482
14
18

The first solution in that question is a stored procedure (similar to executing SQL on the DbContext), the second solution, like explained, pulls all records in memory, which I can't do with over a million records. – user247702 Jun 11 '13 at 14:52

Where clause with a list of field combinations

3 Answers3