3

Using EF6 I can get an entity by compound primary key using Find(key1.a, key1.b) method.

Is it possible to do the same for multiple keys, ie

var keys = new [] {new {a = 1, b = 2}, new {a = 2, b = 3}, /*etc*/};
var entities = context.Items.Find(key1.a, key1.b, key2.a, key2.b, ..., keyN.a, keyN.b);

UPD:

Also, I would like to know why EF has two methods for search by key: Find and LINQ's Where, Single, etc.

Dzmitry Martavoi
  • 6,867
  • 6
  • 38
  • 59
  • The DbContext class has no Find method. The DbSet.Find method is documented and it will only return a single entity – Sir Rufo Dec 21 '17 at 10:02
  • @SirRufo: fixed, thanks. I would like to know a workaround for that then. – Dzmitry Martavoi Dec 21 '17 at 10:06
  • Some RDBMs do allow `IN` with Tuples (such as a compound primary key) with a syntax [like this](https://stackoverflow.com/a/8011075/314291). However, [not so SqlServer](https://connect.microsoft.com/SQLServer/feedback/details/299231/add-support-for-ansi-standard-row-value-constructors). You would need to break up the query with OR statements `(KeyCol1 = Value1 AND KeyCol2 = Value2) OR (KeyCol1 = .. )` – StuartLC Dec 21 '17 at 10:07
  • 2
    Your update is a new question. Please remove it and maybe post it as a new one. – Daniel Hilgarth Dec 21 '17 at 10:09
  • 1
    BTW EF does **not** have multiple methods to search for keys. Only Find can search for keys. All others can select by any field and you have to name them. It is a different story – Sir Rufo Dec 21 '17 at 10:13
  • 2
    The second / new question has [already been asked](https://stackoverflow.com/questions/16966213/entity-framework-find-vs-where) – StuartLC Dec 21 '17 at 10:13

2 Answers2

1

The documentation is pretty clear here: That is not possible

  1. It only returns a single entity
  2. It actually throws an exception if multiple entities with a given primary key exist, which could easily be the case if you could pass multiple PK values

The best course of action will be to use LINQ's Where:

context.Items.Where(x => keys.Any(y => y.a == x.a && y.b == x.b))
Daniel Hilgarth
  • 171,043
  • 40
  • 335
  • 443
  • Ok. is there another way of doing that? I guess that is a pretty popular use case: find by an array of such keys. – Dzmitry Martavoi Dec 21 '17 at 10:04
  • Please see my edit. Not 100% sure if this can correctly be translated to SQL. If it can't you will get an exception. If you do, please come back here and we will look for another solution – Daniel Hilgarth Dec 21 '17 at 10:06
1

If you can do anything for one, you can do it for many as well by repeating the same for one many times.

You can write an extension method for this

public static class IDbSetExtensions
{
    public static ICollection<T> FindMany( this IDbSet<T> @this, IEnumerable<object[]> keys )
    {
        return keys.Select( key => @this.Find( key ) ).ToList();
    }
}
Sir Rufo
  • 18,395
  • 2
  • 39
  • 73