EF Core converts Linq Union to SQL with error

[AlexanderLapshin]

LINQ Union to SQL converting problem

Problem description:

• The problem is EF generates wrong query with extra columns PosterUrl and Rating for wrong table. You can see the evidence in output.

• I have a Persons table

• Person connected one-to-one with Actor and Director

• Actor and Director connected many-to-many with Movies

• The task is to get unique movies of Person as Director and Actor

Database schema (for understanding)

Link

Entities

    public class PersonEntity : BaseEntity<Guid>
    {
        public string Name { get; set; }
        public string Surname { get; set; }
        public DateTime Birthday { get; set; }
        public string Hometown { get; set; }
        public string Bio { get; set; }
        public string AvatarUrl { get; set; }

        public ActorEntity Actor { get; set; }
        public DirectorEntity Director { get; set; }

        public IList<PersonImageEntity> Images { get; set; } = new List<PersonImageEntity>();
    }

    public class ActorEntity : BaseEntity<Guid>
    {
        public Guid PersonId { get; set; }
        public PersonEntity Person { get; set; }

        public IList<MovieActorEntity> Movies { get; set; } = new List<MovieActorEntity>();
    }

    public class MovieActorEntity
    {
        public Guid ActorId { get; set; }
        public ActorEntity Actor { get; set; }

        public Guid MovieId { get; set; }
        public MovieEntity Movie { get; set; }

        public string RoleInFilm { get; set; }

        public int Order { get; set; }
    }

    public class DirectorEntity : BaseEntity<Guid>
    {        
        public Guid PersonId { get; set; }
        public PersonEntity Person { get; set; }

        public IList<MovieDirectorEntity> Movies { get; set; } = new List<MovieDirectorEntity>();
    }

    public class MovieDirectorEntity
    {
        public Guid DirectorId { get; set; }
        public DirectorEntity Director { get; set; }

        public Guid MovieId { get; set; }
        public MovieEntity Movie { get; set; }
    }

    public class MovieEntity : BaseEntity<Guid>
    {
        public string Name { get; set; }
        public double Rating { get; set; }
        public string Description { get; set; }
        public DateTime ReleaseDate { get; set; }
        public int DurationInMins { get; set; }
        public int Budget { get; set; }
        public int Revenue { get; set; }
        public string PosterUrl { get; set; }

        public IList<MovieDirectorEntity> Directors { get; set; } = new List<MovieDirectorEntity>();
        public IList<MovieGenreEntity> Genres { get; set; } = new List<MovieGenreEntity>();
        public IList<MovieActorEntity> Actors { get; set; } = new List<MovieActorEntity>();
        public IList<MovieImageEntity> Images { get; set; } = new List<MovieImageEntity>();
        public IList<TrailerEntity> Trailers { get; set; } = new List<TrailerEntity>();
    }

Not working query

  var person = await Context.Persons
                .Include(p => p.Images)
                .Include(p => p.Actor)
                .ThenInclude(a => a.Movies)
                .ThenInclude(p => p.Movie)
                .Include(p => p.Director)
                .ThenInclude(a => a.Movies)
                .ThenInclude(p => p.Movie)
                .Select(x => new PersonModel
                {
                    Id = x.Id,
                    Name = x.Name,
                    Surname = x.Surname,
                    Birthday = x.Birthday,
                    Hometown = x.Hometown,
                    Bio = x.Bio,
                    AvatarUrl = x.AvatarUrl,


                    Images = x.Images
                        .Select(i => new ImageModel
                        {
                            Id = i.Id,
                            ImageUrl = i.ImageUrl,
                            Height = i.Height,
                            Width = i.Width
                        }).ToList(),

                    KnownByFilms = x.Actor.Movies
                        .Select(m => m.Movie)
                        .Union(x.Director.Movies
                        .Select(m => m.Movie))
                        .Select(m => new ShortMovieModel
                        {
                            Id = m.Id,
                            Name = m.Name,
                            PosterUrl = m.PosterUrl,
                            Rating = m.Rating
                        }).ToList()

                })
                .FirstOrDefaultAsync(x => x.Id == id);

Output

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (55ms) [Parameters=[@__id_0='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
      SELECT [t].[Id], [t].[Name], [t].[Surname], [t].[Birthday], [t].[Hometown], [t].[Bio], [t].[AvatarUrl], [t].[Id0], [t].[Id1], [p0].[Id], [p0].[ImageUrl], [p0].[Height], [p0].[Width], [t].[Id], [t].[Name], [t].[PosterUrl], [t].[Rating]
      FROM (
          SELECT TOP(1) [p].[Id], [p].[Name], [p].[Surname], [p].[Birthday], [p].[Hometown], [p].[Bio], [p].[AvatarUrl], [a].[Id] AS [Id0], [d].[Id] AS [Id1]
          FROM [Persons] AS [p]
          LEFT JOIN [Actors] AS [a] ON [p].[Id] = [a].[PersonId]
          LEFT JOIN [Directors] AS [d] ON [p].[Id] = [d].[PersonId]
          WHERE [p].[Id] = @__id_0
      ) AS [t]
      LEFT JOIN [PersonImages] AS [p0] ON [t].[Id] = [p0].[PersonId]
      OUTER APPLY (
          SELECT [m0].[Id], [m0].[Budget], [m0].[Description], [m0].[DurationInMins], [m0].[Name], [m0].[PosterUrl], [m0].[Rating], [m0].[ReleaseDate], [m0].[Revenue]
          FROM [MovieActorEntity] AS [m]
          INNER JOIN [Movies] AS [m0] ON [m].[MovieId] = [m0].[Id]
          WHERE [t].[Id0] IS NOT NULL AND ([t].[Id0] = [m].[ActorId])
          UNION
          SELECT [m2].[Id], [m2].[Budget], [m2].[Description], [m2].[DurationInMins], [m2].[Name], [m2].[PosterUrl], [m2].[Rating], [m2].[ReleaseDate], [m2].[Revenue]
          FROM [MovieDirectorEntity] AS [m1]
          INNER JOIN [Movies] AS [m2] ON [m1].[MovieId] = [m2].[Id]
          WHERE [t].[Id1] IS NOT NULL AND ([t].[Id1] = [m1].[DirectorId])
      ) AS [t0]
      ORDER BY [t].[Id], [t].[Id0], [t].[Id1], [p0].[Id], [t].[Id]
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'KinoDbAPI.Database.ApplicationDbContext'.
      Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'PosterUrl'.
      Invalid column name 'Rating'.
      A column has been specified more than once in the order by list. Columns in the order by list must be unique.
         at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__169_0(Task`1 result)
         at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
         at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
      --- End of stack trace from previous location where exception was thrown ---
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
         at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
      --- End of stack trace from previous location where exception was thrown ---
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
      ClientConnectionId:5f383636-6809-4331-b114-79e4a704064f
      Error Number:207,State:1,Class:16
Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'PosterUrl'.
Invalid column name 'Rating'.
A column has been specified more than once in the order by list. Columns in the order by list must be unique.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__169_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

Working Ugly Query

I had tried to figure out the problem and founded an ugly solution

var person = await Context.Persons
    .Select(x => new PersonModel
    {
        Id = x.Id,
        Name = x.Name,
        Surname = x.Surname,
        Birthday = x.Birthday,
        Hometown = x.Hometown,
        Bio = x.Bio,
        AvatarUrl = x.AvatarUrl,


        Images = Context.PersonImages
            .Where(pi => pi.PersonId == id)
            .Select(i => new ImageModel
            {
                Id = i.Id,
                ImageUrl = i.ImageUrl,
                Height = i.Height,
                Width = i.Width
            }).ToList(),

        KnownByFilms = Context.Persons
            .Include(p => p.Actor)
            .ThenInclude(a => a.Movies)
            .ThenInclude(p => p.Movie)
            .Where(p => p.Id == id)
            .SelectMany(p => p.Actor.Movies.Select(m => m.Movie))
            .Union(Context.Persons
            .Include(p => p.Director)
            .ThenInclude(a => a.Movies)
            .ThenInclude(p => p.Movie)
            .Where(p => p.Id == id)
            .SelectMany(p => p.Director.Movies.Select(m => m.Movie)))
            .Select(m => new ShortMovieModel
            {
                Id = m.Id,
                Name = m.Name,
                PosterUrl = m.PosterUrl,
                Rating = m.Rating
            }).ToList()

    })
    .FirstOrDefaultAsync(x => x.Id == id);

Correct Output

     SELECT [t].[Id], [t].[Name], [t].[Surname], [t].[Birthday], [t].[Hometown], [t].[Bio], [t].[AvatarUrl], [t0].[Id], [t0].[ImageUrl], [t0].[Height], [t0].[Width], [t3].[Id], [t3].[Name], [t3].[PosterUrl], [t3].[Rating]
      FROM (
          SELECT TOP(1) [p].[Id], [p].[Name], [p].[Surname], [p].[Birthday], [p].[Hometown], [p].[Bio], [p].[AvatarUrl]
          FROM [Persons] AS [p]
          WHERE [p].[Id] = @__id_2
      ) AS [t]
      OUTER APPLY (
          SELECT [p0].[Id], [p0].[ImageUrl], [p0].[Height], [p0].[Width]
          FROM [PersonImages] AS [p0]
          WHERE [p0].[PersonId] = @__id_0
      ) AS [t0]
      OUTER APPLY (
          SELECT [t1].[Id], [t1].[Budget], [t1].[Description], [t1].[DurationInMins], [t1].[Name], [t1].[PosterUrl], [t1].[Rating], [t1].[ReleaseDate], [t1].[Revenue]
          FROM [Persons] AS [p1]
          LEFT JOIN [Actors] AS [a] ON [p1].[Id] = [a].[PersonId]
          INNER JOIN (
              SELECT [m0].[Id], [m0].[Budget], [m0].[Description], [m0].[DurationInMins], [m0].[Name], [m0].[PosterUrl], [m0].[Rating], [m0].[ReleaseDate], [m0].[Revenue], [m].[ActorId], [m].[MovieId]
              FROM [MovieActorEntity] AS [m]
              INNER JOIN [Movies] AS [m0] ON [m].[MovieId] = [m0].[Id]
          ) AS [t1] ON [a].[Id] = [t1].[ActorId]
          WHERE [p1].[Id] = @__id_1
          UNION
          SELECT [t2].[Id], [t2].[Budget], [t2].[Description], [t2].[DurationInMins], [t2].[Name], [t2].[PosterUrl], [t2].[Rating], [t2].[ReleaseDate], [t2].[Revenue]
          FROM [Persons] AS [p2]
          LEFT JOIN [Directors] AS [d] ON [p2].[Id] = [d].[PersonId]
          INNER JOIN (
              SELECT [m2].[Id], [m2].[Budget], [m2].[Description], [m2].[DurationInMins], [m2].[Name], [m2].[PosterUrl], [m2].[Rating], [m2].[ReleaseDate], [m2].[Revenue], [m1].[DirectorId], [m1].[MovieId]
              FROM [MovieDirectorEntity] AS [m1]
              INNER JOIN [Movies] AS [m2] ON [m1].[MovieId] = [m2].[Id]
          ) AS [t2] ON [d].[Id] = [t2].[DirectorId]
          WHERE [p2].[Id] = @__id_2
      ) AS [t3]
      ORDER BY [t].[Id], [t0].[Id], [t3].[Id]

EF Core version: 5.0
Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (e.g. .NET 3.1)
Operating system: Win 10
IDE: (e.g. Visual Studio 2019 16.8.3)

[smitpatel]

#17337

[smitpatel]

Issues here, the table graph is lost after union is generated plus the identifier for collection projections are not found and should be updated to use the Movie identifiers