dotnet
Document how to rewrite subquery equality when composite key
@Flaflo it's tracked and in the Backlog milestone, but other issues have a higher priority at the moment.
So how it can be rewritte? Are there any plans to add this functionality?
@JStepien90 instead of directly comparing entity instances (e.g. ctx.Blogs.Where(b => b == someBlog), compare their key properties (e.g. ctx.Blogs.Where(b => b.Id1 == someBlog.Id1 && b.Id2 == someBlog.Id2).
@roji That works well when matching a single entity, but not when you have a list of entities:
ctx.Blogs.Where(b => myBlogs.Contains(b))
A workaround would be to use .Any:
ctx.Blogs.Where(b => myBlogs.Any(m => b.Id1 == m.Id1 && b.Id2 == m.Id2))
but that is not supported either (at least not with Sqlite).
@dnmh-psc that's true, but there's an important SQL translation problem here. ctx.Blogs.Where(b => myBlogs.Contains(b)) can be translated to a simple SQL IN construct: WHERE b IN (x, y, z) (though we're currently working on improving that). If b has a composite key, this would require something like WHERE (b1, b2) IN ((x1, x2), (y1, y2), (z1, z2)) which isn't supported on all databases (and in any case isn't currently supported by EF).
You can still use dynamic LINQ generation to generate a tree yourself, e.g. WHERE b1 = x1 AND b2 = b2 OR b1 = y1 AND b2 = y2 OR b1 = z2 AND b2 = z3, but EF definitely won't do that for you (this sort of dynamic SQL is also bad for query plan caching etc.).