Linq query to match multiple parameters

Question

I am having a list of model which i populated with items. The below list is populated with items :-

List<Entry> list = new List<Entry>
{
    new Entry { EmployeeId = 1, EntryDate = '2016-11-01', InDate = '2016-11-01' },
    new Entry { EmployeeId = 1, EntryDate = '2016-11-05', InDate = '2016-11-05' },
    new Entry { EmployeeId = 2, EntryDate = '2016-11-01', InDate = '2016-11-01' }
};

Now I want to query a table Entry from database in a such a way that records should be matching with the above List with EmployeeId & EntryDate as parameter.

Entry table has same columns as in the code above.

If it was one field i can use contains but for multiple fields what should i do?

My results from database table should match the above List with 2 Columns matching EmployeeId & EntryDate.

You've only got a single entry in the list - it's not clear why you're using a list at all. It would help if you'd provide a minimal reproducible example. I'd also strongly advise against using strings to store dates. — Jon Skeet
– Jon Skeet, Commented Dec 6, 2016 at 9:20
There are many entries. I have given just a example. There are more than 500 records in the List. — Anup
– Anup, Commented Dec 6, 2016 at 9:21
An example is much more helpful if it actually demonstrates what you need - in this case, just two entries would be enough to make it clear. (Using a collection initializer with object initializers would make it even clearer.) Now, you haven't told us how you're querying the table. Is this LINQ to SQL? EF? Something else? And in-memory table? — Jon Skeet
– Jon Skeet, Commented Dec 6, 2016 at 9:22
I've edited your sample code to use collection and object initializers to make it much more readable - and valid (your previous code wouldn't actually compile, as you were declaring the same local variable multiple times, and you hadn't indented it usefully - please pay attention to this sort of thing when writing questions). — Jon Skeet
– Jon Skeet, Commented Dec 6, 2016 at 9:31

Tim Rogers · Accepted Answer · 2016-12-06 09:49:37Z

3

You can achieve this through the .Contains operator and anonymous types in a way that is compatible with EF.

You need to first project your list to an anonymous type containing just those properties you want to compare.

var projection = from e in list 
                 select new { e.EmployeeId, e.EntryDate };

Then you can compare it to an identical projection in your query.

var query = db.Table.Where(e => projection.Contains(new { e.EmployeeId, e.EntryDate }));

This however generates SQL that has two parameters per item in list. How performant this is for 500 records, you will have to test. If this doesn't work, you can write a stored procedure with a table-valued parameter.

answered Dec 6, 2016 at 9:49

Tim Rogers

21.8k8 gold badges55 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Anup Over a year ago

Thanks for Answer Tim. But in another scenario if i want to query not with table but on a List then will this work?

Tim Rogers Over a year ago

Do you mean "will this query work in memory as well as via SQL"? Then, yes.

Anup Over a year ago

I am getting error for above code as :- Delegate System.Func<Entry, int , bool> does not take 1 arguments.

Tyson Over a year ago

This isn't working for me (EF6) - I get a System.NotSupportedException: 'Unable to create a constant value of type 'Anonymous type'. Only primitive types or enumeration types are supported in this context.' What am I missing?

Tim Rogers Over a year ago

@Tyson This answer may be specific to SQL Server. If you're using another database, YMMV.

|

Jon Skeet · Accepted Answer · 2016-12-06 09:32:21Z

2

Logically, what you want is:

var query = db.Table.Where(e => list.Any(le => e.EmployeeId == le.EmployeeId &&
                                               e.EntryDate == le.EntryDate));

I don't know whether that will work with Entity Framework, but it's worth a try. (It would be fine in LINQ to Objects, for example.)

answered Dec 6, 2016 at 9:32

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

3 Comments

Panagiotis Kanavos Over a year ago

If you can express it in SQL it will work with LINQ to EF. This one won't. You could create a table-valued parameter. This is a clear sign though of ORM overuse. A stored procedure would be better

Jon Skeet Over a year ago

@PanagiotisKanavos: I suggest you add an answer, then I'll delete this one.

Anup Over a year ago

@JonSkeet Thanks. I will try this code & let you know.!

Collectives™ on Stack Overflow

Linq query to match multiple parameters

2 Answers 2

6 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related