-
Notifications
You must be signed in to change notification settings - Fork 3.2k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
System.InvalidOperationException: Unable to translate a collection subquery in a projection... using Union or Concat #26703
Comments
@marianosz Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate. |
Ok, i will try to build something to reproduce... |
@ajcvickers I did more research, looks like the issue is related to the Includes, I tested the query removing the Include for related collections, and is working. |
@smitpatel Simplified query. Does not fail if var asignedCandidates =
from cs in context.Candidates
join an in context.CandidateAssignations on cs.Id equals an.AssignedId
select cs;
var result = asignedCandidates
.Union(context.Candidates)
.Include(x => x.Technologies)
.ToList(); |
@marianosz This change is by-design for 6.0. This query now throws because, depending on the results, the query could easily return bad data in 5.0. As the message says, "This can also happen for some cases of projection before 'Distinct' or some shapes of grouping key in case of 'GroupBy'. These should either contain all key properties of the entity that the operation is applied on, or only contain simple property access expressions." |
We should add fwlink to the exception message and explain on the doc about set operations limitation when they can contain repeating elements. |
I have a similar but more complex query and it works only if "include" are just before executing (ToList()). |
You cannot include a collection navigation after performing a set operation like that since your set operation result can contain duplicate items. |
Just ran into this... So what is the correct way to try and perform a var result = asignedCandidates.Include(x => x.Technologies)
.Union(context.Candidates.Include(x => x.Technologies))
.ToList(); Still throws the error though... maybe this isn't possible 🤷♂️ |
@StevenRasmussen It depends how the queries are created. For example, this is fine: var filteredBlogs = context.Blogs.Where(e => e.Id == 1);
var blogs = filteredBlogs.Union(context.Blogs).Include(e => e.Posts).ToList(); But this is not: var filteredBlogs = context.Blogs.Where(e => e.Id == 1).Select(e => new Blog { Id = e.Id });
var blogs = filteredBlogs.Union(context.Blogs).Include(e => e.Posts).ToList(); Full repro code: public class Blog
{
public int Id { get; set; }
public int SomeOtherId { get; set; }
public string Name { get; set; }
public List<Post> Posts { get; } = new();
}
public class Post
{
public int Id { get; set; }
public string Title { get; set; }
public string Content { get; set; }
public Blog Blog { get; set; }
}
public class SomeDbContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseSqlServer(Your.ConnectionString)
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging();
public DbSet<Blog> Blogs { get; set; }
public DbSet<Post> Posts { get; set; }
}
public class Program
{
public static void Main()
{
using (var context = new SomeDbContext())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
context.Add(new Blog {Posts = {new Post(), new Post()}});
context.Add(new Blog {Posts = {new Post(), new Post(), new Post()}});
context.SaveChanges();
}
using(var context = new SomeDbContext())
{
// var filteredBlogs = context.Blogs.Where(e => e.Id == 1).Select(e => new Blog { Id = e.Id });
// var blogs = filteredBlogs.Union(context.Blogs).Include(e => e.Posts).ToList();
var filteredBlogs = context.Blogs.Where(e => e.Id == 1);
var blogs = filteredBlogs.Union(context.Blogs).Include(e => e.Posts).ToList();
foreach (var blog in context.Blogs)
{
Console.WriteLine($"Blog {blog.Id} with {blog.Posts.Count} posts.");
}
}
}
} |
@ajcvickers - Thanks for taking the time to respond... and for the demo project! You have pointed me in the right direction. It appears based on your project and from me updating mine that you can't really do a projection on the results of the union as part of the |
@ajcvickers - Looks like I spoke too soon using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
public class Blog
{
public int Id { get; set; }
public int SomeOtherId { get; set; }
public string Name { get; set; }
public List<Post> Posts { get; } = new();
}
public class SuperBlog : Blog
{
public string SomeOtherProperty { get; set; }
}
public class Post
{
public int Id { get; set; }
public string Title { get; set; }
public string Content { get; set; }
public int BlogId { get; set; }
public Blog Blog { get; set; }
}
public class SomeDbContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseSqlServer(Your.ConnectionString)
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging();
public DbSet<Blog> Blogs { get; set; }
public DbSet<SuperBlog> SuperBlogs { get; set; }
public DbSet<Post> Posts { get; set; }
}
public class Program
{
public static void Main()
{
using (var context = new SomeDbContext())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
context.Add(new Blog { Posts = { new Post(), new Post() } });
context.Add(new Blog { Posts = { new Post(), new Post(), new Post() } });
context.SaveChanges();
}
using (var context = new SomeDbContext())
{
var filteredBlogs = context.Blogs.Where(e => e.Id == 1);
var superBlogs = from b in context.Blogs
join sb in context.SuperBlogs on b.Id equals sb.Id
select b;
var blogsWithJoin = from b in context.Blogs
join p in context.Posts on b.Id equals p.BlogId
select b;
try
{
// This doesn't work :(. I thought it was because of the inheritance ob 'Blog' -> 'SuperBlog'
var blogs = filteredBlogs.Union(superBlogs).Include(e => e.Posts).ToList();
}
catch (Exception ex) { }
try
{
// This proves that if there is any 'join' then the query fails
var blogs = filteredBlogs.Union(blogsWithJoin).Include(e => e.Posts).ToList();
}
catch (Exception ex) { }
foreach (var blog in context.Blogs)
{
Console.WriteLine($"Blog {blog.Id} with {blog.Posts.Count} posts.");
}
}
}
} Should this be supported? |
Have you tried to remove the 2 "join" lines from superBlogs and blogsWithJoin? Just a thought looking at yoyr code. |
@angelochiello - Yes, I know that in this particular case you could remove the |
@StevenRasmussen In my case, wheres were OK. Problem was on the include part.
If I put include on line 1, it doesen't work. on line 4, id does. |
So to be clear, what I’m looking for is the ability to use |
We faced the same exception as the OP when using With EF 5.0.12 both, |
In EF Core, in order to do collection include, it requires each record of the parent (on which the collection will be populated) uniquely identifiable. Since then we can fetch related records and fix up the navigations properly. This is by design and cannot be changed else it can cause duplicated records in the collection/improperly filled collection/collection not marked as loaded. The concept of uniquely identifiable is at row level which means we do track origin, and even if the parent entity is duplicated, if the row have unique identifier we still work with above principal. An example would be With above things in mind
from c in Customers
join o in Orders on c.CustomerId equals o.CustomerId
select c Above query will have duplicated customers in the result. By tracking origin, EF Core will also include But once you apply set operation with above query to something else which has different origin, there is no way to uniquely identify rows anymore. So we cannot do collection include. This is something which cannot be changed unless we come up with altogether different idea in how to do collection include.
|
@smitpatel - Thanks for the detailed explanation! I think I understand the issue now and how to move forward in my particular scenario. I appreciate the effort into providing such a well thought out response! |
@ajcvickers Is there a more detailed explanation of this anywhere? How would it return invalid data if |
Hello team
I have this piece of code that is working fine on EF Core 5, and previously on v3:
This is a property defined on the DbContext:
Basically, if the current user has the "all:candidates" permission, he can query using the Candidates DbSet, if not, he can query the union of candidates created by him, plus the candidates assigned to him.
Then when I try to run the following query, if the user doesn't have the "all:candidates" permission, and uses the code with the
Union()
I have the following exception:The same exception occurs with
Concat()
instead of union, and is not happening when I remove theUnion
and return one query or anotherEF Core version: 6.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: MacOs
IDE: VS Code
The text was updated successfully, but these errors were encountered: