EF7 generating incorrect SQL for the Concat/Union All #30273
Comments
|
This issue is lacking enough information for us to be able to fully understand what is happening. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate. |
|
Minimal repro: await using var ctx = new BlogContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();
_ = ctx.Blogs
.Distinct()
.Concat(ctx.Blogs.Distinct())
.Concat(ctx.Blogs.Distinct())
.Select(s => s.Name)
.ToList();
public class BlogContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseSqlServer(@"Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging();
}
public class Blog
{
public int Id { get; set; }
public string? Name { get; set; }
}Resulting SQL: SELECT [t].[Name]
FROM (
SELECT DISTINCT [b].[Id], [b].[Name]
FROM [Blogs] AS [b]
UNION ALL
SELECT DISTINCT [b0].[Id], [b0].[Name]
FROM [Blogs] AS [b0]
) AS [t]
UNION ALL
SELECT DISTINCT [b1].[Id], [b1].[Name]
FROM [Blogs] AS [b1]Note that we only project out the name from the subquery, and attempt to union that with id and name. Ideally we'd filter the projection down into the 3 different joined queries (and avoid the subquery altogether). |
|
As a workaround specifically for the above issue, it's possible to perform the concatenation on the client side; where the concatenation is performed (server- or client-side) doesn't impact performance in a significant way, since the same data has to be delivered across in any case. This can be done by adding AsEnumerable() (causing it to get executed separately) at the end of each sub-query, and pass the results to Concat on the client side. For example, the original code can be modified as follows: public static string M1()
{
BDbContext db = new BDbContext();
var DelhiStudents = (from N in db.students where N.City== "Delhi" select N.Name).Distinct().AsEnumerable();
var BlrStudents = (from N in db.students where N.City == "Bangalore" select N.Name).Distinct().AsEnumerable();
var KolStudents = (from N in db.students where N.City == "Kolkata" select N.Name).Distinct().AsEnumerable;
var StudentNames = DelhiStudents.Concat(BlrStudents).Concat(KolStudents);
return "Success";
} |
|
Although there does seem to be a bug here I just wanted to note that OP's example doesn't do |
|
@stevendarby thanks, yeah, that's possible - if Distinct isn't needed (due to the primary key be unique), then removing it should indeed be the better workaround. |
|
I have the same issue Entity Framework version 7.0.2, behaves differently than the version 6.0.6 we are using until 7 is fixed. Our code is IQueryable orgParties = IQueryable individuals = IQueryable latestParties = null!; IList latestCentralPartyIds = null!; What we expect (which was previously working on the older release) if for the 3 lists wpParties, orgParties, individuals to be combined, then get all the CentralPartyIds into a list. EF Core 7.0.2 --> this is not correct. Below is what is happening giving an error since it's trying to union before the last concat Old Version - EF 6.0.6 this is correct |
|
Confirmed that this is a regression from 6.0, which produces the following SQL: SELECT [t0].[Name]
FROM (
SELECT DISTINCT [b].[Id], [b].[Name]
FROM [Blogs] AS [b]
UNION ALL
SELECT DISTINCT [b0].[Id], [b0].[Name]
FROM [Blogs] AS [b0]
UNION ALL
SELECT DISTINCT [b1].[Id], [b1].[Name]
FROM [Blogs] AS [b1]
) AS [t0] |
This would around would not work for us. It is bring back much more data than is needed. |
@lvendramini I'm not sure how that could be. Concat brings the same amount of data regardless of whether it's evaluated at the client or at the server. |
|
Note from triage: given that this looks like a regression from EF Core 6 to EF Core 7 (and not a difference in behavior between EF6 and EF Core as originally reported), we will investigate a patch fix. /cc @maumar |
|
problem is in SelectExpression pruning logic. For set ops we prune both sources individually, however in the problematic case, the second source is distinct, so we can't prune it (we would change the result if we did), but the first source is UNION ALL, so it's ok to prune. To mitigate this, we can check if any of the sources have Distinct on them before we attempt to prune either. |
|
@onlypritam as alternative workaround you can try removing 3 distincts applied to individual cities: public static string M1()
{
BDbContext db = new BDbContext();
IQueryable<Student> DelhiStudents = (from N in db.students where N.City== "Delhi" select N);// removed distinct
IQueryable<Student> BlrStudents = (from N in db.students where N.City == "Bangalore" select N);// removed distinct
IQueryable<Student> KolStudents = (from N in db.students where N.City == "Kolkata" select N);// removed distinct
IQueryable<Student> AllStudents= DelhiStudents.Concat(BlrStudents).Concat(KolStudents);
IList<string>Students= AllStudents.Select(x=>x.Name).ToList();
return "Success";
}those 3 distincts you had don't do anything anyway - students are entities so they are already distinct within DbSet. |
…, The same code was working fine for EF6.4.4 Problem was that during SelectExpression pruning for set operations we would try to prune both sources independently. However, if a source is Distinct we can't prune (that could affect the result). So the problematic case is where we prune one source but can't prune the second due to Distinct, and we end up with both sources having not matching projections. Fix is to check if either source has Distinct before attempting to prune them. Fixed #30273
…, The same code was working fine for EF6.4.4 Problem was that during SelectExpression pruning for set operations we would try to prune both sources independently. However, if a source is Distinct we can't prune (that could affect the result). So the problematic case is where we prune one source but can't prune the second due to Distinct, and we end up with both sources having not matching projections. Fix is to check if either source has Distinct before attempting to prune them. Fixed #30273
maumar
added
the
closed-fixed
…, The same code was working fine for EF6.4.4 (#30452) Problem was that during SelectExpression pruning for set operations we would try to prune both sources independently. However, if a source is Distinct we can't prune (that could affect the result). So the problematic case is where we prune one source but can't prune the second due to Distinct, and we end up with both sources having not matching projections. Fix is to check if either source has Distinct before attempting to prune them. Fixed #30273
|
reopening for potential patch |
ajcvickers
changed the title
It seems the same code that used to work in EF core 6.4.4 is not breaking in EF 7 and creating an incorrect SQL statement.
Because of this incorrect SQL we are getting an error: "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."
Include stack traces
The text was updated successfully, but these errors were encountered: