Document how to rewrite subquery equality when composite key

[smitpatel]

Also subquery.Contains

[Flaflo]

is this still tracked?

[roji]

@Flaflo it's tracked and in the Backlog milestone, but other issues have a higher priority at the moment.

[PatryxCShark]

So how to rewrite?:)

[ctolkien]

Note that the exception message links to this issue....

[JStepien90]

So how it can be rewritte? Are there any plans to add this functionality?

[roji]

@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).

[dnmh-psc]

@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).

[roji]

@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.).

[Charlieface]

@roji Maybe we can just rewrite them into such an Any , doesn't seem like much extra work. Just need to factor out RelationalSqlTranslatingExpressionVisitor.TryRewriteContainsEntity so that the primary key comparisons are fed into a function one by one, come back as Equals(a.Id1, b.Id1) etc, and then roll them up into a bunch of Ands.