4

I have a table with a composite key eg:

modelBuilder.Entity<MyEntity>().HasKey(e=> new { e.Part1, e.Part2 });

I also have a list of keys in memory for which I want to load entities eg:

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

How can I do a single query to load the entities in the keys array?

I've tried obvious things like ctx.MyEntities.Where(e => keys.Any(k => k.Part1 == e.Part1 && k.Part2 == e.Part2))

This answer suggests it isn't possible but surely this cant be the case.

Community
  • 1
  • 1
undefined
  • 33,537
  • 22
  • 129
  • 198
  • 1
    Can you tell more about your schema information? I think it is possible, but I would appreciate some example of tables and keys to start with. You have a primary key in one table that is combined of n columns in that one table, right? And: Which version of entity framework do you use? There are lots of changes between versions, perhaps one is relevant for your case. – Mare Infinitus Dec 17 '14 at 21:21
  • @gertarnold thanks, your answer in the linked question is brilliant. Do you know if there is a ticket in the EF backlog to resolve this issue in a more robust way? if not ima add one. – undefined Dec 17 '14 at 21:46
  • Not that I know of. But I doubt it, because the underlying language (40 year old SQL fcol) doesn't have any support for it. – Gert Arnold Dec 17 '14 at 21:48
  • @MareInfinitus yes I have a composite PK (thats what `.HasKey(e=> new { e.Part1, e.Part2 })` will set up) I am using 6.1.1 of EF – undefined Dec 17 '14 at 21:49
  • 1
    @GertArnold You can do this pretty tidally in SQL, you need to leverage the table value constructor like so: `SELECT * FROM (VALUES (5,1006), (5,1007)) as k(Part1,Part2) JOIN MyEntities e on k.Part1 = e.Part1 and e.Part2 = r.Part2` Not sure if this is supported in all databases though but certainly in SQL Server – undefined Dec 17 '14 at 21:55
  • I ventured into this solution once. The problem is that is collapses under its own weight when the number of row constructors gets "large" (a couple of hundreds). But if EF would be willing to offer a solution, it should probably be in this direction. – Gert Arnold Dec 17 '14 at 21:58
  • @GertArnold hmmm yeah i see what you mean the query planner takes quite a while to process the query with that many variables. – undefined Dec 17 '14 at 22:08

0 Answers0