Union/Concat throwing exception when using includes/joins

CorsairRO commented 1 year ago

File a bug

EF Core cannot translate to sql a simple UNION / UNION ALL ? Projection is to the SAME class that is NOT a model one. If not using Include or Join then it translates correctly.

Include your code

I have a class like this and would like to select in a single query from 2 different DbSets

public MyProjectionClass {
       public int Id {get;set;}
       public string Name {get;set;}
}

The following Code works fine ( removed async and await for simplicity )

var aList = ctx.MyDbSet1
            .Select(
                  x=> new MyProjectionClass{
                        Id = x.DbSet1Id
                  }
            )
            .Concat(
                        ctx.MyDbSet2
                                  .Select(
                                          x=> new MyProjectionClass{
                                                       Id = x.DbSet2Id
                                          }
                                   )
            ).ToList()

Generates correctly:

SELECT t1.DbSet1Id FROM DbSet1
UNION ALL
SELECT t2.DbSet2Id FROM DbSet2

Adding ANY include stops working throwing exception with unable to use set operator?

var aList = ctx.MyDbSet1.Include(x=>x.OtherTable1)
            .Select(
                  x=> new MyProjectionClass{
                        Id = x.DbSet1Id,
                        Name = x.OtherTable1.Name
                  }
            )
            .Concat(
                        ctx.MyDbSet2.Include(x=>x.OtherTable2)
                                  .Select(
                                          x=> new MyProjectionClass{
                                                       Id = x.DbSet2Id
                                                       Name = x.OtherTable2.Name
                                          }
                                   )
            ).ToList()

Expected Result is:

SELECT t1.DbSet1Id, lk1.Name FROM DbSet1 JOIN OtherTable1 AS lk1 ON t1.DbSet1Id = lk1.DbSet1Id
UNION ALL
SELECT t2.DbSet2Id, lk1.Name FROM DbSet2 JOIN OtherTable2 AS lk2 ON t2.DbSet2Id = lk2.DbSet2Id

Include stack traces

If i dont use my OWN class for Both projections, i could understand that SQL Server cannot handle the UNIONs. But if i use my own class that HAS the same properties then why does it complain?

Please help me with some ideas, as i have Plenty of code in edmx old version that calls SP to return unioned rows but i would like to use EF Core and Linq to discontinue them as they just do a select from a UNION select FROM b

System.InvalidOperationException : Unable to translate set operations when both sides don't assign values to the same properties in the nominal type. Please make sure that the same properties are included on both sides, and consider assigning default values if a property doesn't require a specific value.

Include verbose output

Include provider and version information

EF Core version: 7.0.5 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: NET 7.0 Operating system: Windows 11 IDE: Visual Studio 2022 17.5

roji commented 1 year ago

@CorsairRO can you please post the code which doesn't work so we can see what you're doing? Please include a minimal model as well to make sure we have a runnable code sample.

CorsairRO commented 1 year ago

OK, i am not very familiar with code first models and migrations as i work with existing DB, so please let me know if the example is ok as it is.

Basically the problem appears with ANY include that i have in any branches of the UNION. If i would expect the DB to automatically do that operation, i would expect the error message. But if i am specifying explicitly to BOTH branches to return the same class ( OrderInfo ) i dont understand why it crashes insted of generating the UNION ALL between the 2 branches with tens of joins if i want, because all what it needs to do is to project only my columns from my Projection class, and not trying to union other properties from other classes found in the heirarchy.

Basically i tried Union and Concat using the same strategy with having a predefined projection class and it quite find them useless if they dont support this scenario.

i mean, why i would like to UNION only the 2 tables? It is Much easier to run 2 queries then and union them clientside. But i was expecting EF Core to help me to be performant and flexible to get rid of legacy stored procedures while moving from old EF.

using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations;

using var ctx = new MyContext( );

// UNION ALL works fine - No Includes or Joins in method syntax
var working = await
    ctx.Orders      
        .Select( x =>
            new OrderInfo {
                Id = x.OrderId              
            }
        )
        .Concat(
            ctx.SalesOrders             
                .Select( x =>
                    new OrderInfo {
                        Id = x.SalesOrderId                     
                    }
                )
        )
        .ToListAsync( );

// Union All - Crashing - Any Include in any branch, even if i specify explicitly same projection class in both
var fail = await
    ctx.Orders
        .Include( x => x.Current_Account )
        .Select( x =>
            new OrderInfo {
                Id = x.OrderId ,
                AccountName = x.Current_Account.VendorName
            }
        )
        .Concat(
            ctx.SalesOrders
                .Include(x=>x.Current_Account)
                .Select( x =>
                    new OrderInfo {
                        Id = x.SalesOrderId ,
                        AccountName = x.Current_Account.VendorName
                    }
                )
        )
        .ToListAsync( );

public class MyContext : DbContext {

    public DbSet<Order> Orders { get; set; }

    public DbSet<SalesOrder> SalesOrders { get; set; }

    public DbSet<Account> Accounts { get; set; }

    protected override void OnModelCreating( ModelBuilder modelBuilder ) {
        base.OnModelCreating( modelBuilder );

        modelBuilder.Entity<Order>( )
            .HasOne( x => x.Current_Account )
            .WithMany( x => x.Orders )
            .HasForeignKey( x => x.AccountId );

        modelBuilder.Entity<SalesOrder>( )
            .HasOne( x => x.Current_Account )
            .WithMany( x => x.SalesOrders )
            .HasForeignKey( x => x.AccountId );

    }

}

/// <summary>
/// My Projection Class that i expect in the UNION result of the selects
/// </summary>
public class OrderInfo {

    public int Id { get; set; }

    public string AccountName { get; set; }

}

public class Order {

    [Key]
    public int OrderId { get; set; }

    public int AccountId { get; set; }

    public virtual Account Current_Account { get; set; }

}

public class SalesOrder {

    [Key]
    public int SalesOrderId { get; set; }

    public int AccountId { get; set; }

    public virtual Account Current_Account { get; set; }

}

public class Account {

    [Key]
    public int AccountId { get; set; }

    public string VendorName { get; set; }

    public List<Order> Orders { get; set; } = new( );

    public List<SalesOrder> SalesOrders { get; set; } = new( );

}

ajcvickers commented 1 year ago

@CorsairRO I am not able to reproduce this. I put your code into a runnable application and it does not crash. The code and output is below.

using var ctx = new MyContext( );

ctx.Database.EnsureDeleted();
ctx.Database.EnsureCreated();

// UNION ALL works fine - No Includes or Joins in method syntax
var working = await
    ctx.Orders
        .Select(
            x =>
                new OrderInfo { Id = x.OrderId }
        )
        .Concat(
            ctx.SalesOrders
                .Select(
                    x =>
                        new OrderInfo { Id = x.SalesOrderId }
                )
        )
        .ToListAsync();

// Union All - Crashing - Any Include in any branch, even if i specify explicitly same projection class in both
var fail = await
    ctx.Orders
        .Include(x => x.Current_Account)
        .Select(
            x =>
                new OrderInfo { Id = x.OrderId, AccountName = x.Current_Account.VendorName }
        )
        .Concat(
            ctx.SalesOrders
                .Include(x => x.Current_Account)
                .Select(
                    x =>
                        new OrderInfo { Id = x.SalesOrderId, AccountName = x.Current_Account.VendorName }
                )
        )
        .ToListAsync();

public class MyContext : DbContext
{
    public DbSet<Order> Orders { get; set; }
    public DbSet<SalesOrder> SalesOrders { get; set; }
    public DbSet<Account> Accounts { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>()
            .HasOne(x => x.Current_Account)
            .WithMany(x => x.Orders)
            .HasForeignKey(x => x.AccountId);

        modelBuilder.Entity<SalesOrder>()
            .HasOne(x => x.Current_Account)
            .WithMany(x => x.SalesOrders)
            .HasForeignKey(x => x.AccountId);
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Database=AllTogetherNow")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
    }
}

public class OrderInfo
{
    public int Id { get; set; }
    public string AccountName { get; set; }
}

public class Order
{
    public int OrderId { get; set; }
    public int AccountId { get; set; }
    public virtual Account Current_Account { get; set; }
}

public class SalesOrder
{
    public int SalesOrderId { get; set; }
    public int AccountId { get; set; }
    public virtual Account Current_Account { get; set; }
}

public class Account
{
    public int AccountId { get; set; }
    public string VendorName { get; set; }
    public List<Order> Orders { get; set; } = new( );
    public List<SalesOrder> SalesOrders { get; set; } = new( );
}

warn: 4/26/2023 19:34:12.651 CoreEventId.SensitiveDataLoggingEnabledWarning[10400] (Microsoft.EntityFrameworkCore.Infrastructure)
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
info: 4/26/2023 19:34:13.096 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (19ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
info: 4/26/2023 19:34:13.145 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      IF SERVERPROPERTY('EngineEdition') <> 5
      BEGIN
          ALTER DATABASE [AllTogetherNow] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
      END;
info: 4/26/2023 19:34:13.158 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      DROP DATABASE [AllTogetherNow];
info: 4/26/2023 19:34:13.339 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (159ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      CREATE DATABASE [AllTogetherNow];
info: 4/26/2023 19:34:13.370 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (30ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      IF SERVERPROPERTY('EngineEdition') <> 5
      BEGIN
          ALTER DATABASE [AllTogetherNow] SET READ_COMMITTED_SNAPSHOT ON;
      END;
info: 4/26/2023 19:34:13.373 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
info: 4/26/2023 19:34:13.459 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Accounts] (
          [AccountId] int NOT NULL IDENTITY,
          [VendorName] nvarchar(max) NOT NULL,
          CONSTRAINT [PK_Accounts] PRIMARY KEY ([AccountId])
      );
info: 4/26/2023 19:34:13.461 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Orders] (
          [OrderId] int NOT NULL IDENTITY,
          [AccountId] int NOT NULL,
          CONSTRAINT [PK_Orders] PRIMARY KEY ([OrderId]),
          CONSTRAINT [FK_Orders_Accounts_AccountId] FOREIGN KEY ([AccountId]) REFERENCES [Accounts] ([AccountId]) ON DELETE CASCADE
      );
info: 4/26/2023 19:34:13.462 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [SalesOrders] (
          [SalesOrderId] int NOT NULL IDENTITY,
          [AccountId] int NOT NULL,
          CONSTRAINT [PK_SalesOrders] PRIMARY KEY ([SalesOrderId]),
          CONSTRAINT [FK_SalesOrders_Accounts_AccountId] FOREIGN KEY ([AccountId]) REFERENCES [Accounts] ([AccountId]) ON DELETE CASCADE
      );
info: 4/26/2023 19:34:13.463 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX [IX_Orders_AccountId] ON [Orders] ([AccountId]);
info: 4/26/2023 19:34:13.464 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX [IX_SalesOrders_AccountId] ON [SalesOrders] ([AccountId]);
info: 4/26/2023 19:34:13.709 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (17ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [o].[OrderId] AS [Id]
      FROM [Orders] AS [o]
      UNION ALL
      SELECT [s].[SalesOrderId] AS [Id]
      FROM [SalesOrders] AS [s]
info: 4/26/2023 19:34:13.786 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [o].[OrderId] AS [Id], [a].[VendorName] AS [AccountName]
      FROM [Orders] AS [o]
      INNER JOIN [Accounts] AS [a] ON [o].[AccountId] = [a].[AccountId]
      UNION ALL
      SELECT [s].[SalesOrderId] AS [Id], [a0].[VendorName] AS [AccountName]
      FROM [SalesOrders] AS [s]
      INNER JOIN [Accounts] AS [a0] ON [s].[AccountId] = [a0].[AccountId]

CorsairRO commented 1 year ago

Hello to you and to shay. First of all, thank you very much for finding time to test this. I was watching fascinated all your presentations

Get Outlook for Androidhttps://aka.ms/AAb9ysg

From: Arthur Vickers @.> Sent: Wednesday, April 26, 2023 9:38:17 PM To: dotnet/efcore @.> Cc: Cosmin Palade @.>; Mention @.> Subject: Re: [dotnet/efcore] Union/Concat throwing exception when using includes/joins (Issue #30771)

@CorsairROhttps://github.com/CorsairRO I am not able to reproduce this. I put your code into a runnable application and it does not crash. The code and output is below.

using var ctx = new MyContext( );

ctx.Database.EnsureDeleted(); ctx.Database.EnsureCreated();

// UNION ALL works fine - No Includes or Joins in method syntax var working = await ctx.Orders .Select( x => new OrderInfo { Id = x.OrderId } ) .Concat( ctx.SalesOrders .Select( x => new OrderInfo { Id = x.SalesOrderId } ) ) .ToListAsync();

// Union All - Crashing - Any Include in any branch, even if i specify explicitly same projection class in both var fail = await ctx.Orders .Include(x => x.Current_Account) .Select( x => new OrderInfo { Id = x.OrderId, AccountName = x.Current_Account.VendorName } ) .Concat( ctx.SalesOrders .Include(x => x.Current_Account) .Select( x => new OrderInfo { Id = x.SalesOrderId, AccountName = x.Current_Account.VendorName } ) ) .ToListAsync();

public class MyContext : DbContext { public DbSet Orders { get; set; } public DbSet SalesOrders { get; set; } public DbSet Accounts { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>()
        .HasOne(x => x.Current_Account)
        .WithMany(x => x.Orders)
        .HasForeignKey(x => x.AccountId);

    modelBuilder.Entity<SalesOrder>()
        .HasOne(x => x.Current_Account)
        .WithMany(x => x.SalesOrders)
        .HasForeignKey(x => x.AccountId);
}

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Database=AllTogetherNow")
        .LogTo(Console.WriteLine, LogLevel.Information)
        .EnableSensitiveDataLogging();
}

}

public class OrderInfo { public int Id { get; set; } public string AccountName { get; set; } }

public class Order { public int OrderId { get; set; } public int AccountId { get; set; } public virtual Account Current_Account { get; set; } }

public class SalesOrder { public int SalesOrderId { get; set; } public int AccountId { get; set; } public virtual Account Current_Account { get; set; } }

public class Account { public int AccountId { get; set; } public string VendorName { get; set; } public List Orders { get; set; } = new( ); public List SalesOrders { get; set; } = new( ); }

warn: 4/26/2023 19:34:12.651 CoreEventId.SensitiveDataLoggingEnabledWarning[10400] (Microsoft.EntityFrameworkCore.Infrastructure) Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development. info: 4/26/2023 19:34:13.096 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (19ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT 1 info: 4/26/2023 19:34:13.145 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='60'] IF SERVERPROPERTY('EngineEdition') <> 5 BEGIN ALTER DATABASE [AllTogetherNow] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; END; info: 4/26/2023 19:34:13.158 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='60'] DROP DATABASE [AllTogetherNow]; info: 4/26/2023 19:34:13.339 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (159ms) [Parameters=[], CommandType='Text', CommandTimeout='60'] CREATE DATABASE [AllTogetherNow]; info: 4/26/2023 19:34:13.370 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (30ms) [Parameters=[], CommandType='Text', CommandTimeout='60'] IF SERVERPROPERTY('EngineEdition') <> 5 BEGIN ALTER DATABASE [AllTogetherNow] SET READ_COMMITTED_SNAPSHOT ON; END; info: 4/26/2023 19:34:13.373 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT 1 info: 4/26/2023 19:34:13.459 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE TABLE [Accounts] ( [AccountId] int NOT NULL IDENTITY, [VendorName] nvarchar(max) NOT NULL, CONSTRAINT [PK_Accounts] PRIMARY KEY ([AccountId]) ); info: 4/26/2023 19:34:13.461 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE TABLE [Orders] ( [OrderId] int NOT NULL IDENTITY, [AccountId] int NOT NULL, CONSTRAINT [PK_Orders] PRIMARY KEY ([OrderId]), CONSTRAINT [FK_Orders_Accounts_AccountId] FOREIGN KEY ([AccountId]) REFERENCES [Accounts] ([AccountId]) ON DELETE CASCADE ); info: 4/26/2023 19:34:13.462 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE TABLE [SalesOrders] ( [SalesOrderId] int NOT NULL IDENTITY, [AccountId] int NOT NULL, CONSTRAINT [PK_SalesOrders] PRIMARY KEY ([SalesOrderId]), CONSTRAINT [FK_SalesOrders_Accounts_AccountId] FOREIGN KEY ([AccountId]) REFERENCES [Accounts] ([AccountId]) ON DELETE CASCADE ); info: 4/26/2023 19:34:13.463 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE INDEX [IX_Orders_AccountId] ON [Orders] ([AccountId]); info: 4/26/2023 19:34:13.464 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE INDEX [IX_SalesOrders_AccountId] ON [SalesOrders] ([AccountId]); info: 4/26/2023 19:34:13.709 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (17ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT [o].[OrderId] AS [Id] FROM [Orders] AS [o] UNION ALL SELECT [s].[SalesOrderId] AS [Id] FROM [SalesOrders] AS [s] info: 4/26/2023 19:34:13.786 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT [o].[OrderId] AS [Id], [a].[VendorName] AS [AccountName] FROM [Orders] AS [o] INNER JOIN [Accounts] AS [a] ON [o].[AccountId] = [a].[AccountId] UNION ALL SELECT [s].[SalesOrderId] AS [Id], [a0].[VendorName] AS [AccountName] FROM [SalesOrders] AS [s] INNER JOIN [Accounts] AS [a0] ON [s].[AccountId] = [a0].[AccountId]

— Reply to this email directly, view it on GitHubhttps://github.com/dotnet/efcore/issues/30771#issuecomment-1523878324, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AJPH7YWG3T7MQEE5U3LJUIDXDFTRTANCNFSM6AAAAAAXMQS6EI. You are receiving this because you were mentioned.Message ID: @.***>

CorsairRO commented 1 year ago

Sorry, I am at home now, writing from my phone. Sorry that I didn't send an examle that crahsez

I can update it tomorrow and send it. I tried union in 2 cases in my app and I quit using it and having 2 separate queries with 2 roundtrils to database instead.

I am sure that it is an issue as long as one branch of the union joins other tables or if the joined tables from one branch has different properties

Get Outlook for Androidhttps://aka.ms/AAb9ysg

From: Arthur Vickers @.> Sent: Wednesday, April 26, 2023 9:38:17 PM To: dotnet/efcore @.> Cc: Cosmin Palade @.>; Mention @.> Subject: Re: [dotnet/efcore] Union/Concat throwing exception when using includes/joins (Issue #30771)

@CorsairROhttps://github.com/CorsairRO I am not able to reproduce this. I put your code into a runnable application and it does not crash. The code and output is below.

using var ctx = new MyContext( );

ctx.Database.EnsureDeleted(); ctx.Database.EnsureCreated();

// UNION ALL works fine - No Includes or Joins in method syntax var working = await ctx.Orders .Select( x => new OrderInfo { Id = x.OrderId } ) .Concat( ctx.SalesOrders .Select( x => new OrderInfo { Id = x.SalesOrderId } ) ) .ToListAsync();

// Union All - Crashing - Any Include in any branch, even if i specify explicitly same projection class in both var fail = await ctx.Orders .Include(x => x.Current_Account) .Select( x => new OrderInfo { Id = x.OrderId, AccountName = x.Current_Account.VendorName } ) .Concat( ctx.SalesOrders .Include(x => x.Current_Account) .Select( x => new OrderInfo { Id = x.SalesOrderId, AccountName = x.Current_Account.VendorName } ) ) .ToListAsync();

public class MyContext : DbContext { public DbSet Orders { get; set; } public DbSet SalesOrders { get; set; } public DbSet Accounts { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>()
        .HasOne(x => x.Current_Account)
        .WithMany(x => x.Orders)
        .HasForeignKey(x => x.AccountId);

    modelBuilder.Entity<SalesOrder>()
        .HasOne(x => x.Current_Account)
        .WithMany(x => x.SalesOrders)
        .HasForeignKey(x => x.AccountId);
}

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Database=AllTogetherNow")
        .LogTo(Console.WriteLine, LogLevel.Information)
        .EnableSensitiveDataLogging();
}

}

public class OrderInfo { public int Id { get; set; } public string AccountName { get; set; } }

public class Order { public int OrderId { get; set; } public int AccountId { get; set; } public virtual Account Current_Account { get; set; } }

public class SalesOrder { public int SalesOrderId { get; set; } public int AccountId { get; set; } public virtual Account Current_Account { get; set; } }

public class Account { public int AccountId { get; set; } public string VendorName { get; set; } public List Orders { get; set; } = new( ); public List SalesOrders { get; set; } = new( ); }

warn: 4/26/2023 19:34:12.651 CoreEventId.SensitiveDataLoggingEnabledWarning[10400] (Microsoft.EntityFrameworkCore.Infrastructure) Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development. info: 4/26/2023 19:34:13.096 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (19ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT 1 info: 4/26/2023 19:34:13.145 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='60'] IF SERVERPROPERTY('EngineEdition') <> 5 BEGIN ALTER DATABASE [AllTogetherNow] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; END; info: 4/26/2023 19:34:13.158 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='60'] DROP DATABASE [AllTogetherNow]; info: 4/26/2023 19:34:13.339 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (159ms) [Parameters=[], CommandType='Text', CommandTimeout='60'] CREATE DATABASE [AllTogetherNow]; info: 4/26/2023 19:34:13.370 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (30ms) [Parameters=[], CommandType='Text', CommandTimeout='60'] IF SERVERPROPERTY('EngineEdition') <> 5 BEGIN ALTER DATABASE [AllTogetherNow] SET READ_COMMITTED_SNAPSHOT ON; END; info: 4/26/2023 19:34:13.373 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT 1 info: 4/26/2023 19:34:13.459 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE TABLE [Accounts] ( [AccountId] int NOT NULL IDENTITY, [VendorName] nvarchar(max) NOT NULL, CONSTRAINT [PK_Accounts] PRIMARY KEY ([AccountId]) ); info: 4/26/2023 19:34:13.461 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE TABLE [Orders] ( [OrderId] int NOT NULL IDENTITY, [AccountId] int NOT NULL, CONSTRAINT [PK_Orders] PRIMARY KEY ([OrderId]), CONSTRAINT [FK_Orders_Accounts_AccountId] FOREIGN KEY ([AccountId]) REFERENCES [Accounts] ([AccountId]) ON DELETE CASCADE ); info: 4/26/2023 19:34:13.462 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE TABLE [SalesOrders] ( [SalesOrderId] int NOT NULL IDENTITY, [AccountId] int NOT NULL, CONSTRAINT [PK_SalesOrders] PRIMARY KEY ([SalesOrderId]), CONSTRAINT [FK_SalesOrders_Accounts_AccountId] FOREIGN KEY ([AccountId]) REFERENCES [Accounts] ([AccountId]) ON DELETE CASCADE ); info: 4/26/2023 19:34:13.463 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE INDEX [IX_Orders_AccountId] ON [Orders] ([AccountId]); info: 4/26/2023 19:34:13.464 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE INDEX [IX_SalesOrders_AccountId] ON [SalesOrders] ([AccountId]); info: 4/26/2023 19:34:13.709 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (17ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT [o].[OrderId] AS [Id] FROM [Orders] AS [o] UNION ALL SELECT [s].[SalesOrderId] AS [Id] FROM [SalesOrders] AS [s] info: 4/26/2023 19:34:13.786 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT [o].[OrderId] AS [Id], [a].[VendorName] AS [AccountName] FROM [Orders] AS [o] INNER JOIN [Accounts] AS [a] ON [o].[AccountId] = [a].[AccountId] UNION ALL SELECT [s].[SalesOrderId] AS [Id], [a0].[VendorName] AS [AccountName] FROM [SalesOrders] AS [s] INNER JOIN [Accounts] AS [a0] ON [s].[AccountId] = [a0].[AccountId]

— Reply to this email directly, view it on GitHubhttps://github.com/dotnet/efcore/issues/30771#issuecomment-1523878324, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AJPH7YWG3T7MQEE5U3LJUIDXDFTRTANCNFSM6AAAAAAXMQS6EI. You are receiving this because you were mentioned.Message ID: @.***>

CorsairRO commented 1 year ago

Hello again,

Now I know exactly how to reproduce the issue… Sorry for sending an example that cannot reproduce. All works fine in your/mine tests with string columns for example with no length restriction, literally being NVARCHAR(MAX). Of course this is a VERY bad DB Design, and our DBA has set MaxLengths to 99.99 percent of the string columns in DB.

Therefore the problems starts unioning on tables with different maxlengths for same column. I understand this should be an issue if I would like to project back to a DbSet row, as it will be hard to identify which of them should be used. BUT, if I specify in my Projection class a string column, I don’t care if in one table is maxlength(50) and in other is maxlength(100). As the generated SQL should have NOTHING to do with the types of the columns that it returns, it should just spawn a sql with UNION ALL. I can understand also that UNION might be hard to generate to exclude duplicates, but UNION ALL I will expect to work.

In my modified example, I have set a MaxLength(100) to the Account->VendorName Column Then in Sales Order Branch of the UNION, I removed the join to Accounts, and I just want to return NULL or string.empty for that column. I have tried to cast null to string as I have read on stackoverflow. No success, the error is the same.

Unable to translate set operation when matching columns on both sides have different store types.

Thank you VERY very much for having the time to look at those issues.

Modified Example:

using Microsoft.EntityFrameworkCore; using Microsoft.Extensions.Logging; using System.ComponentModel.DataAnnotations;

using var ctx = new MyContext( );

ctx.Database.EnsureDeleted( ); ctx.Database.EnsureCreated( );

// UNION ALL works fine - No Includes or Joins in method syntax var working = await ctx.Orders .Select( x => new OrderInfo { Id = x.OrderId } ) .Concat( ctx.SalesOrders .Select( x => new OrderInfo { Id = x.SalesOrderId } ) ) .ToListAsync( );

// Union All - Crashing - Any Include var fail = await ctx.Orders .Include( x => x.Current_Account ) .Select( x => new OrderInfo { Id = x.OrderId , AccountName = x.Current_Account.VendorName } ) .Concat( ctx.SalesOrders //.Include( x => x.Current_Account ) .Select( x => new OrderInfo { Id = x.SalesOrderId , AccountName = null ,//x.Current_Account.VendorName , // AccountName = (string)null;// Still same error } ) ) .ToListAsync( );

public class MyContext : DbContext {

  public DbSet<Order> Orders { get; set; }

  public DbSet<SalesOrder> SalesOrders { get; set; }

  public DbSet<Account> Accounts { get; set; }

  protected override void OnModelCreating( ModelBuilder modelBuilder ) {
        base.OnModelCreating( modelBuilder );

        modelBuilder.Entity<Order>( )
              .HasOne( x => x.Current_Account )
              .WithMany( x => x.Orders )
              .HasForeignKey( x => x.AccountId );

        modelBuilder.Entity<SalesOrder>( )
              .HasOne( x => x.Current_Account )
              .WithMany( x => x.SalesOrders )
              .HasForeignKey( x => x.AccountId );

  }

  protected override void OnConfiguring( DbContextOptionsBuilder optionsBuilder ) {

        optionsBuilder
              .UseSqlServer( @"Data Source=localhost;Database=EFCoreTests;Integrated Security=true;Trust Server Certificate=true" )
              .LogTo( Console.WriteLine , LogLevel.Information )
              .EnableSensitiveDataLogging( );
  }

}

///

/// My Projection Class that i expect in the UNION result of the selects ///

public class OrderInfo {

  public int Id { get; set; }

  public string AccountName { get; set; }

}

public class Order {

  [Key]
  public int OrderId { get; set; }

  public int? AccountId { get; set; }

  public virtual Account Current_Account { get; set; }

}

public class SalesOrder {

  [Key]
  public int SalesOrderId { get; set; }

  public int? AccountId { get; set; }

  public virtual Account Current_Account { get; set; }

}

public class Account {

  [Key]
  public int AccountId { get; set; }

  [MaxLength( 100 )]
  public string VendorName { get; set; }

  public List<Order> Orders { get; set; } = new( );

  public List<SalesOrder> SalesOrders { get; set; } = new( );

}

Thank you / Takk / Multumesc

Cosmin Palade Senior Developer Phone: +40 744 535 995 Email: @.**@.> Skype: cosmin.palade Teams: @.**@.> Web: http://www.lundin.ro http://www.lundin.ro/

roji commented 1 year ago

That sounds like #19129, can you please try the latest 8.0 preview and see if that solves the issue for you?

All works fine in your/mine tests with string columns for example with no length restriction, literally being NVARCHAR(MAX). Of course this is a VERY bad DB Design, and our DBA has set MaxLengths to 99.99 percent of the string columns in DB.

I'm certainly not aware of a reason to systematically always avoid NVARCHAR(MAX), you may want to revisit that idea.

CorsairRO commented 1 year ago

Hello Shay,

Thank you for the answer and for your time to look and analyze the issue
I am now on my way to move my apps from edmx to ef core, therefore I don’t have time to test preview features.

But, I am sure that you will fix this in EF8 like you did all the VERY ice things until now.

So you can close that issue, I can live for the moment with running 2 queries and unioning clientside.

Now, coming to nvarchar(MAX).

I understand the philosophy of EF core becoming accessible for every level of developer, but it doesn’t mean that if I know how to create 3 pocos and a dbcontext for a mobile app with 3 tables I am a developer.

Sorry for that or for looking like I am patronizing here, but I really didn’t expect to get such a suggestion from a highly qualified person like you are in the database domain.

Especially because you are insisting always with perf, which I totally agree with.

I know that you are more into Postgres then into SQL Server.

In SQL Server

Indexes on such columns are very slow and adds a lot of extra memory consumption on the Server
Huge difference in memory usage of the Server ( meaning responsiveness / perf )
All varchar/nvarchar (x) will be used directly from the memory.
All varchar/nvarchar(MAX) are used from PAGED on disk memory.

Which is WAY less performant then the first. It might be that SQL server changed, BUT… I have 200+ customers all over the world that are using my apps for 20+ years, since EF didn’t even exist, we were using Datasets/DataTables They bought SQL Server licenses at that time, so we have to be Backwards compatible with SQL 2008R2 at a minimum.

            Why should a column in a currency table for example have the CurrencyCode stored as NVARCHAR(MAX) when it is VARCHAR(3) in reality

            A Line Of Business Application doesn’t mean 3 tables.
            For example one of mine has 700+ tables in the database.
            If all the columns will be VARCHAR(MAX) with indexes on 10000+ string columns it will KILL the server performance.

Many thanks again for your time, Have a nice day

Thank you / Takk / Multumesc

Cosmin Palade Senior Developer Phone: +40 744 535 995 Email: @.**@.> Skype: cosmin.palade Teams: @.**@.> Web: http://www.lundin.ro http://www.lundin.ro/

roji commented 1 year ago

Indexes on such columns are very slow and adds a lot of extra memory consumption on the Server

Indexes over nvarchar(max) aren't slow; they're not supported at all. If you need to index a text column, that's indeed a good reason to use nvarchar(x) over nvarchar(max).

All varchar/nvarchar (x) will be used directly from the memory. All varchar/nvarchar(MAX) are used from PAGED on disk memory.

I don't think you're suggesting that SQL Server caches all varchar/nvarchar(x) data in memory - that definitely wouldn't be possible for large databases. There are indeed some performance implications of using varchar/nvarchar(max), but whether that's actually relevant depends on your specific application. In any case, I won't go into a long discussion on this - I'd just advise carefully understanding exactly why you think this is the case rather than just systematically doing it this way.

Regardless, I'll go ahead and close this as a duplicate of #19129,

roji commented 1 year ago

Duplicate of #19129

dotnet / efcore