Union fails if one query has returned no results #19705
Comments
|
/cc @roji |
|
Reproduced this, it doesn't seem related to whether any query returns empty results or not, and is somehow related to the second GroupBy/Select query. Simpler queries work with empty results. class Program
{
static void Main(string[] args)
{
using (var ctx = new BlogContext())
{
ctx.Database.EnsureDeleted();
ctx.Database.EnsureCreated();
ctx.Blogs.Add(new Blog { Name = "foo" });
ctx.Blogs.Add(new Blog { Name = "bar" });
ctx.SaveChanges();
}
using (var ctx = new BlogContext())
{
var results = ctx.Blogs
.Where(b => b.Name == "foo").Select(b => new { b.Name, Sequence = 0 })
.Union(ctx.Blogs
.GroupBy(b => new { b.Name, b.SomeInt })
.Select(g => new { g.Key.Name, Sequence = 1}))
.ToList();
}
}
}
public class BlogContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder.UseSqlServer(@"...");
}
public class Blog
{
public int Id { get; set; }
public string Name { get; set; }
public int SomeInt { get; set; }
} |
|
I tested it with query1 and query3 returning results and it worked fine. I then forced query1 to return no results and the union failed. |
|
The select is not applied to GroupBy before comparing shapes for set operations causing the shape to mismatch and throw the exception. |
If all components are NewExpression are default then just make it wholy default so that it can match to GroupBy-Aggregate shape Resolves #19705
smitpatel
added
the
closed-fixed
If all components are NewExpression are default then just make it wholy default so that it can match to GroupBy-Aggregate shape Resolves #19705
|
Hi, We encountered this issue when migrating to .NET Core 3.1. Thanks in advance. |
|
Given that EF6 has been ported to .Net Core I decided to go that route. EF
Core is far from an industrial strength product.
…On Tue, Apr 7, 2020 at 3:13 AM Jacques Kang ***@***.***> wrote:
Hi,
We encountered this issue when migrating to .NET Core 3.1.
Since we cannot yet migrate to 5.0. Is there any walkaround for .NET Core
3 please?
Thanks in advance.
—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
<#19705 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/ALZAM75NFOAPZNAGKTGKRB3RLLVEZANCNFSM4KLQPOLA>
.
|
|
Just ran into this issue with 3.1. It occurs if the union attempts to include another entity in the results that is not available in one of the selects. Sudo-code below |
|
The only 'fix' I found was to use EF6.4. EFCore is definitely not
industrial strength - it needs a lot of work
…On Wed, Apr 22, 2020 at 2:49 PM Jeremy Foster ***@***.***> wrote:
Just ran into this issue with 3.1.
Is there a fix or a workaround?
—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
<#19705 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/ALZAM75WU6V6CUDKVLIEFVDRN5J7DANCNFSM4KLQPOLA>
.
|
Well we ended up by refactoring our codes and union with in-memory lists. |
|
Note that this has already been fixed in 5.0.0-preview1 - you can give that a try as well. It would be great to get confirmation that the bug is gone. |
|
Sorry, but I abandoned efcore after my experience. I tried to migrate an
existing project but encountered too may problems.
…On Thu, Apr 23, 2020 at 5:45 AM Shay Rojansky ***@***.***> wrote:
Note that this has already been fixed in 5.0.0-preview1 - you can give
that a try as well. It would be great to get confirmation that the bug is
gone.
—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
<#19705 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/ALZAM7YTSSL4IJVNEKO56SDROAS6RANCNFSM4KLQPOLA>
.
|
|
Sorry to hear that @FormerMarine. It would be good to know exactly which problems you ran into, and hopefully you'll try again at some point. |
|
I tested a very small subset of a large data access layer.
In addition to this problem I also encountered a problem with
DefaultIfEmpty not accepting a default value.
The performance of various queries was significantly worse than EF6.
I will definitely try again when we get a little closer to the 5.0 release
date.
…On Fri, Apr 24, 2020 at 7:04 AM Shay Rojansky ***@***.***> wrote:
Sorry to hear that @FormerMarine <https://github.com/FormerMarine>. It
would be good to know exactly which problems you ran into, and hopefully
you'll try again at some point.
—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
<#19705 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/ALZAM764NMA4ERD7H4TVRFTROGE5XANCNFSM4KLQPOLA>
.
|
|
Testing with the latest EF Core 5.0 Preview 4 we appear to have the same error on union where one of the queries returns no data. Our query looks something like the below code. Specifics aside, you can see that we have the same var q = DbContext.TakeOffLineItemGroup
.Include(e => e.TakeOffLineItems.Where(e => e.DeletionDate == null))
.ThenInclude(e => e.CalculationLineItem)
.ThenInclude(e => e.CalculationType)
.Where(e => e.DeletionDate == null)
.AsQueryable();
var y = q
.Where(x => x.IsAllowLineItems && x.DeletionDate == null)
.SelectMany(h => h.TakeOffLineItems.Where(x => x.DeletionDate == null)
.Select(i => i.CalculationLineItem)
)
.AsQueryable();
var z = q
.Where(x => x.IsAllowLineItems == false && x.DeletionDate == null)
.Select(i => i.CalculationLineItem)
.AsQueryable();
// y.ToList() returns > 1 count
// z.ToList() returns 0 count
query = y.Union(z);
var calculations = await query.ToListAsync(); // InvalidOperationException errorPerforming the operation in-memory as suggested in this issue works: /////////////////////////////////////////////////////////////////////////////
// Workaround
var yList = await y.ToListAsync();
var zList = await z.ToListAsync();
calculations = yList.Union(zList).ToList();
///////////////////////////////////////////////////////////////////////////// |
|
@davidyee I can confirm that the code sample above, which was failing in 3.1, now works with 5.0.0-preview4. Can you please open a new issue with your problem, and include a fully runnable code sample? We need your model - not just the query - as well as a bit of data in the database which is needed to trigger the exception. |
|
Same query fails with same error - see my original input |
|
@FormerMarine If this is still failing in 5.0 RC1, then please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate. |
|
My bad - using wrong version of EF Core - aplogies galore |
|
Everything worked fine until I tried to compare the strings in the query that started all this using the 'ToLower()' function. eg cl.ClubMnemonic.ToLower() == clubMnemonic.ToLower(). The result was: System.InvalidOperationException Inner Exception 1: |
|
@FormerMarine can you please post a full, runnable code sample which shows the failure on 5.0.0-rc1? |
|
This code is a portion of a very large data access layer so producing a full runnable code sample is not possible. If look at the very first code sample above, this now works. However if any of the comparisons include a function (ToUpper(), ToLower(), ToString() ) the new error occurs. cl.ClubMnemonic.ToLower() == clubMnemonic.ToLower(). I would have thought it would have been relatively easy for you to use the code used to correct the initial problem to investigate this one. |
|
I have done a bit of further investigation. Just as in the original submission the exception is only thrown if one of the queries in the union returns no result. |
|
@FormerMarine we really need you to narrow down the issue into a minimal repro - there's unfortunately very little we can do with textual descriptions and fragments... It's hard to understand from the information given above how exactly to reproduce the problem. |
|
To be clear, I'm not trying to say there isn't a bug here, or to avoid work by placing the burden on you. It's just that we get a very large amount of bugs reports, and in a very significant number of cases, when users work on producing an actual code sample it turns out there was some sort of unrelated issue in their code which is the culprit, and which they did not communicate to us (because it simply wasn't obvious). |
|
Shay, I have over 50 years of software development experience and only submit bug reports when I have tested my software exhaustively. This problem is the same as the original except that now it only occurs if a function (in my case ToLower() is appended in the comparisons. I turned on SensitiveDataLogging and got slightly more information: System.InvalidOperationException: 'An exception was thrown while attempting to evaluate the LINQ query parameter expression 'value(PGSDataAccessLayer.EntityFramework.Course+<>c__DisplayClass150_0).clubMnemonic.ToLower()'.' Having worked in the industry for over 50 years, when bugs appeared in software I was responsible for I expected to customer to provide sufficient information to enable me to replicate the bug. I belive I have done that in this case. |
|
Given your reluctance to investigate the problem I decided to do further invesigation. Here are the results. |
|
@FormerMarine just to make it clear what we're missing, your original code sample above is missing the actual model (the classes for Club, Course, Tee), there's an unknown function (ForGolferAsync), and we don't know anything about how you're configuring your model with the Fluent API, because the context definition is missing. That means we have to guess at your code and hopefully get it right, rather than you simply providing a simple code sample. In any case, I've taken a look, and from what I understand you're calling ToLower() on a parameter (clubMnemonic), which has a null value. If so, then the exception you're seeing is expected and can be reproduced easily as follows: string clubMnemonic = null;
var results = context.Clubs
.Where(cl => cl.ClubMnemonic.ToLower() == clubMnemonic.ToLower())
.ToList();Since clubMnemonic is a parameter, EF Core evaluates the ToLower function call on the client inside of sending it to the database; but since the variable contains null, you're seeing the exception; you would get exactly the same exception in a regular LINQ to Objects query (without EF). To do this correctly, you need to check your variable for null: string clubMnemonic = null;
var results = context.Clubs
.Where(cl => cl.ClubMnemonic.ToLower() == (clubMnemonic == null ? null : clubMnemonic.ToLower()))
.ToList();Hope the above is clear, please post back if you need any further guidance. |
|
Your first example above would work fine in EF6 if clubMnemonic were null. A lot of additional coding is required to achieve the same result if EF Core. This is the type of software engineering I would expect from Oracle not Microsoft. The following is my implementation. clubMnemonic = clubMnemonic?.ToLower(); //Note - clubMnemonic is a parameter |
|
There are many programming patterns which are supported in EF6 and unsupported in EF Core - we generally don't aim to provide full compatibility with EF6. AFAIK this has been the EF Core behavior since the beginning. Your implementation definitely looks fine (and terser than what I proposed above). |
|
Just a suggestion. Given that the ToLower() function is required if you plan to use Oracle (I know it's not suppored fully yet) it might be worth thinking about implementing this programming pattern. |
Union fails if one query in the union returns no result. An InvalidOperationException is thrown with the message "When performing a set operation, both operands must have the same include operations. Investigation revealed that if one of the queries in the union is empty (returned no results) the union operation fails. In code example below any or the queries (1, 2, or 3) may return no results.
Further technical details
EF Core version: 3.1.1
Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (e.g. .NET Core 3.1)
Operating system: Windows 10
IDE: (e.g. Visual Studio 2019 164..3)
The text was updated successfully, but these errors were encountered: