EF Core get records where two properties match a pair of properties from a list

Question

I have a List of Foo, where Foo is:

public class Foo
{
  public string Id { get; set; }
  public string Name { get; set; }
}

and a database entity Bar:

public partial class Bar
{
  public string Id { get; set; }
  public string Name { get; set; }
  public int Count { get; set; }
  etc...
}

I want to get Bars where the Id and Name match a Foo in the List. Something like:

var records = databaseContext.Bar
  .Where(r => FooList.Contains(*a Foo with Id and Name matching r.Id and r.Name*))
  .ToArray()

How can I get these records in an efficient and clean way?

Christos · Accepted Answer · 2021-09-23T15:55:41.787

2

You could try using Any method:

var records = databaseContext.Bar
  .Where(r => FooList.Any(item => item.Id == r.Id && item.Name == r.Name))
  .ToArray();

Update

Including the comment I had left, when we spotted some issues:

One fast solution, not optimal it would be to load all Bar and then perform the Any call,

databaseContext.Bar.ToList()
                   .Where(r => FooList.Any(item => item.Id == r.Id && item.Name == r.Name))
                   .ToArray();

edited Sep 23 '21 at 15:55

answered Oct 16 '20 at 05:53

Christos

53,228
8
76
108

1

Yes, Any is what I needed here, thanks! – aforest-ccc Oct 16 '20 at 05:58
@aforest-ccc You are very welcome ! I am glad that I helped. – Christos Oct 16 '20 at 06:13
2

Negative. This would have been an answer for LINQ to Objects. For EF Core this would lead to either client evaluation (EFC 1.x, EFC2.x if enabled) or runtime exception (EFC3.x +). – Ivan Stoev Oct 16 '20 at 06:48
@IvanStoev fair...provided that aforest-ccc verify that this **didn't** work. If he verifies that is working, I could definitely improve the quality of the answer by adding that you wrote, but in that case the negative vote doesn't make any sense for me IMHO. – Christos Oct 16 '20 at 07:23
@aforest-ccc could you please verify that this solution worked ? Thanks in advance. – Christos Oct 16 '20 at 07:23
Unfortunately everything that Gert Arnold wrote in the linked EF post still applies to EF Core (as of now, also in the latest EFC 5.0 RC2, so I guess it won't be fixed soon). – Ivan Stoev Oct 16 '20 at 10:47
@Christos I'm afraid I can verify that it doesn't work, which is a shame. The posted link is informative but disappointing. – aforest-ccc Oct 20 '20 at 04:13
@aforest-ccc So, when you wrote.."is what I needed", you didn't verify that works. Correct ? One fast solution, _not optimal_ it would be to load all Bar and then perform the Any call, `databaseContext.Bar.ToList().Where(r => FooList.Any(item => item.Id == r.Id && item.Name == r.Name)).ToArray();`. – Christos Oct 20 '20 at 08:24
Yeah this won't work without enumerating with a ToList() call beforehand. – WiseGuy Sep 23 '21 at 15:42

EF Core get records where two properties match a pair of properties from a list

1 Answers1