How to search for entities based on collection of composite primary keys

Question

I have an entity which has composite primary key

public class Allocation
{
    public Guid WarehouseId { get; set; }
    public Guid ProductId { get; set; }
    public DateTime Date { get; set; }
    public int? Quantity { get; set; }
}

Entity configuration

public class AllocationConfiguration : IEntityTypeConfiguration<Allocation>
{
    public void Configure(EntityTypeBuilder<Allocation> builder)
    {
        builder.HasKey(e => new { e.WarehouseId, e.ProductId, e.Date }); // Primary key

        builder.Property(e => e.Date).HasColumnType("date");

        builder.HasOne<Warehouse>().WithMany().HasForeignKey(e => e.WarehouseId);
        builder.HasOne<Product>().WithMany().HasForeignKey(e => e.ProductId);
    }
}

I can search for one entity by using Find method which accepts multiple arguments as primary key

var allocation = context.Allocations.Find(warehouseId, productId, date);

For entity with one value for primary key I can search for multiple entities based on collection of primary keys by using givenkeys.Contains()

var keys = new[] { 1, 2, 3, 4 };

var entities = context.Products.Where(product => keys.Contains(product.Id)).ToList();

How I can search for multiple entities based on multiple composite keys?

// Given keys
var keys = new[]
{
    new { WarehouseId = warehouse1.Id, ProductId = product1.Id, Date = 12.January(2020) },
    new { WarehouseId = warehouse1.Id, ProductId = product2.Id, Date = 12.January(2020) },
    new { WarehouseId = warehouse2.Id, ProductId = product1.Id, Date = 13.January(2020) },
    new { WarehouseId = warehouse2.Id, ProductId = product2.Id, Date = 13.January(2020) }  
}

Possible duplicate of [EntityFramework - contains query of composite key](https://stackoverflow.com/questions/26198860/entityframework-contains-query-of-composite-key) — Pietro, Nov 17 '19 at 09:14

Besher Tobeh · Answer 1 · 2019-11-17T09:00:45.510

-1

context.Products.Where(p => keys.Any( k => k.ProductId == p.ProductId 
                                     &&  k.WarehouseId == p.WarehouseId
                                     && k.Date == p.Date
                                     ) 
                       ) 
                 .ToList();

edited Nov 17 '19 at 09:00

answered Nov 17 '19 at 07:49

Besher Tobeh

239
3
6

Afraid this will not work, because if given keys contains same values as in one of the records, but values located in different keys, this record will be returned, which is not expected behaviour – Basin Nov 17 '19 at 08:27
yes you are right , I've edited the answer , this should work now as expected – Besher Tobeh Nov 17 '19 at 09:01
1

Please test code before answering. This will throw an exception in EF core 3. – Gert Arnold Nov 17 '19 at 11:43
what is the exception @GertArnold? – Besher Tobeh Nov 17 '19 at 14:19

How to search for entities based on collection of composite primary keys

1 Answers1