1

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.

Tim Rogers
  • 21,297
  • 6
  • 52
  • 68
Anup
  • 9,396
  • 16
  • 74
  • 138
  • 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 [mcve]. I'd also strongly advise against using strings to store dates. – Jon Skeet Dec 06 '16 at 09:20
  • There are many entries. I have given just a example. There are more than 500 records in the List. – Anup Dec 06 '16 at 09: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 Dec 06 '16 at 09:22
  • Possible duplicate of [Proper Linq where clauses](http://stackoverflow.com/questions/6359980/proper-linq-where-clauses) – ste-fu Dec 06 '16 at 09:25
  • @JonSkeet I am using LINQ to Entities.! – Anup Dec 06 '16 at 09:26
  • You also need to explain what you mean by "matching". Does a "match" occur when *all* of the columns match *all* of the fields in *any one* of the models, or some other rule? – Tim Rogers Dec 06 '16 at 09:26
  • @TimRogers I want to match only 2 columns EmployeeId & EntryDate – Anup Dec 06 '16 at 09:29
  • 1
    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 Dec 06 '16 at 09:31

2 Answers2

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.

Tim Rogers
  • 21,297
  • 6
  • 52
  • 68
  • Thanks for Answer Tim. But in another scenario if i want to query not with table but on a List then will this work? – Anup Dec 06 '16 at 09:54
  • Do you mean "will this query work in memory as well as via SQL"? Then, yes. – Tim Rogers Dec 06 '16 at 09:58
  • I am getting error for above code as :- Delegate System.Func does not take 1 arguments. – Anup Dec 06 '16 at 10:01
  • 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? – Tyson Mar 10 '20 at 09:37
  • @Tyson This answer may be specific to SQL Server. If you're using another database, YMMV. – Tim Rogers Mar 10 '20 at 15:24
  • Strange. I'm also on SQL Server (and EF 6.2). Maybe no longer possible in recent EF versions? Or have you used this recently? – Tyson Mar 11 '20 at 04:28
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.)

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194