Query: invalid SQL for queries projecting a single column from a subquery using Take and FirstOrDefault

Question

Query: invalid SQL for queries projecting a single column from a subquery using Take and FirstOrDefault

Closed this issue 8 years ago · 14 comments

This is strange for me. It was working quite in the past months until today.

A simple query like the one below is throwing the error SqlException: Invalid column name 'Id'.

         var book = await _context.Books
                        .Include(x => x.BookAuthors).ThenInclude(x => x.Author)
                        .Include(x => x.Category)
                        .Include(x => x.Publisher)
                        .Include(x => x.Variants).ThenInclude(x => x.Year)
                        .Include(x => x.Variants).ThenInclude(x => x.Month)
                        .Include(x => x.Variants).ThenInclude(x => x.VariantCopies)                        
                        .Select(x => new BookDetailsViewModel
                        {
                            Id = x.Id,
                            Title = x.Title,
                            SubTitle = x.SubTitle,
                            Category = x.Category.Name,
                            Publisher = x.Publisher.Name,
                            Cover = x.Cover != null ? x.Cover.Path : string.Empty,
                            YearOfPublication = x.Variants.OrderBy(y => y.Id).Take(1).Select(y => y.Year.Name).FirstOrDefault(),
                            MonthOfPublication = x.Variants.OrderBy(y => y.Id).Take(1).Select(y => y.Month.Name).FirstOrDefault(),
                            Authors = string.Join(", ", x.BookAuthors.Select(ba => ba.Author.FullName).ToList()),
                        })
                        .Where(x => x.Id == id)
                        .SingleOrDefaultAsync();

Stacktrace

SqlException: Invalid column name 'Id'. Invalid column name 'Id'.

    System.Data.SqlClient.SqlCommand+<>c.<ExecuteDbDataReaderAsync>b__108_0(Task<SqlDataReader> result)
    System.Threading.Tasks.ContinuationResultTaskFromResultTask.InnerInvoke()
    System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, object state)
    System.Threading.Tasks.Task.ExecuteWithThreadLocal(ref Task currentTaskSlot)
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
    System.Runtime.CompilerServices.TaskAwaiter.GetResult()
    Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand+<ExecuteAsync>d__17.MoveNext()
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
    Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable+AsyncEnumerator+<BufferlessMoveNext>d__10.MoveNext()
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
    Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy+<ExecuteAsync>d__7.MoveNext()
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
    Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable+AsyncEnumerator+<MoveNext>d__9.MoveNext()
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
    Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor+AsyncSelectEnumerable+AsyncSelectEnumerator+<MoveNext>d__3.MoveNext()
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
    System.Runtime.CompilerServices.ConfiguredTaskAwaitable+ConfiguredTaskAwaiter.GetResult()
    System.Linq.AsyncEnumerable+<SingleOrDefault_>d__381.MoveNext()
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
    System.Runtime.CompilerServices.TaskAwaiter.GetResult()
    Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider+TaskResultAsyncEnumerable+Enumerator+<MoveNext>d__3.MoveNext()
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
    System.Runtime.CompilerServices.TaskAwaiter.GetResult()
    Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider+ExceptionInterceptor+EnumeratorExceptionInterceptor+<MoveNext>d__5.MoveNext()
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
    System.Runtime.CompilerServices.TaskAwaiter.GetResult()
    Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler+<ExecuteSingletonAsyncQuery>d__23.MoveNext()
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
    System.Runtime.CompilerServices.TaskAwaiter.GetResult()
    Library.Web.Areas.Admin.Controllers.BooksController+<Details>d__14.MoveNext() in BooksController.cs

                var book = await _context.Books

System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
System.Runtime.CompilerServices.TaskAwaiter.GetResult()
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker+<InvokeActionMethodAsync>d__12.MoveNext()
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker+<InvokeNextActionFilterAsync>d__10.MoveNext()
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker+<InvokeInnerFilterAsync>d__14.MoveNext()
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker+<InvokeNextResourceFilter>d__22.MoveNext()
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResourceExecutedContext context)
Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker+<InvokeFilterPipelineAsync>d__17.MoveNext()
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker+<InvokeAsync>d__15.MoveNext()
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
Microsoft.AspNetCore.Builder.RouterMiddleware+<Invoke>d__4.MoveNext()
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
Microsoft.AspNetCore.Authentication.AuthenticationMiddleware+<Invoke>d__6.MoveNext()
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware+<Invoke>d__7.MoveNext()

Please I need to rectify this issue as quickly as possible.

Thanks so much

Answer 1 · 2018-03-19T19:04:34.000Z

@smithaitufe Can you provide code listings for all the entities used in the query, as well as contents of your OnModelCreating? This will help us get to the bottom of this much faster.

Answer 2 · 2018-03-19T19:19:09.000Z

Also, something that might help is putting the Where before the Select:

         var book = await _context.Books
                        .Include(x => x.BookAuthors).ThenInclude(x => x.Author)
                        .Include(x => x.Category)
                        .Include(x => x.Publisher)
                        .Include(x => x.Variants).ThenInclude(x => x.Year)
                        .Include(x => x.Variants).ThenInclude(x => x.Month)
                        .Include(x => x.Variants).ThenInclude(x => x.VariantCopies)                        
                        .Where(x => x.Id == id)
                        .Select(x => new BookDetailsViewModel
                        {
                            Id = x.Id,
                            Title = x.Title,
                            SubTitle = x.SubTitle,
                            Category = x.Category.Name,
                            Publisher = x.Publisher.Name,
                            Cover = x.Cover != null ? x.Cover.Path : string.Empty,
                            YearOfPublication = x.Variants.OrderBy(y => y.Id).Take(1).Select(y => y.Year.Name).FirstOrDefault(),
                            MonthOfPublication = x.Variants.OrderBy(y => y.Id).Take(1).Select(y => y.Month.Name).FirstOrDefault(),
                            Authors = string.Join(", ", x.BookAuthors.Select(ba => ba.Author.FullName).ToList()),
                        })
                        .SingleOrDefaultAsync();

it's effectively the same query, but from EFCore perspective its much easier to translate

Answer 3 · 2018-03-19T20:19:48.000Z

@maumar
book.js

public class Book: BaseEntity
    {  
        public Book () {
            BookAuthors = new HashSet<BookAuthor>();
            Variants = new HashSet<Variant>();
        }      
        [Required]
        [MaxLength(255)]
        public string Title { get; set; }
        [MaxLength(255)]
        public string SubTitle { get; set; }
        [DataType(DataType.MultilineText)]
        public string Description { get; set; }        
        [Required]
        [ForeignKey("Genre")]
        public int GenreId { get; set; }        
        [Required]
        [ForeignKey("Category")]
        public int CategoryId { get; set; } 
        [Required]
        [ForeignKey("Publisher")]        
        public int PublisherId { get; set; }
        public bool Series { get; set; } = false;
        public int? NoInSeries { get; set; }
        public Image Cover { get; set; }
        // Navigation Properties        
        public Term Genre { get; set; }        
        public Term Category { get; set; }
        public Publisher Publisher { get; set; }
        public ICollection<Variant> Variants { get; set; }
        public ICollection<BookAuthor> BookAuthors { get; set; }
    }

public class BaseEntity {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public DateTimeOffset InsertedAt { get; set; } = DateTimeOffset.Now;
        public DateTimeOffset UpdatedAt { get; set; } = DateTimeOffset.Now;
    }

With respect to the second suggestion, the code was initially in that way. I moved the where clause to after the select because I wanted to see if it would help. But it was the same.

Answer 4 · 2018-03-19T20:31:31.000Z

also need listing for related entities (Term, Publisher, Variant etc). What version of EFCore are you using, and which one were you using before it started failing?

Answer 5 · 2018-03-19T21:04:29.000Z

Term.cs

public class Term:BaseEntity
    {
        [Required]
        public int TermSetId { get;set; }
        [Required]
        public string Name { get; set; }
        [ForeignKey("TermSetId")]
        public TermSet TermSet { get; set; }
        public ICollection<Book> CategoryBooks { get; set; }
        public ICollection<Book> GenreBooks { get; set; }
        public ICollection<Variant> DaysAllowedVariants { get; set; }        
        public ICollection<Variant> GrantVariants { get; set; }
        public ICollection<Variant> YearVariants { get; set; }
        public ICollection<Variant> FormatVariants { get; set; }
        public ICollection<Variant> LanguageVariants { get; set; }
        public ICollection<VariantCopy> AvailabilityVariantCopies { get; set; }
        public ICollection<VariantCopy> SourceVariantCopies { get; set; }
        
    }

Variant.cs

    public class Variant: BaseEntity
    {
        public Variant()
        {
            VariantCopies = new HashSet<VariantCopy>();
            VariantPrices = new HashSet<VariantPrice>();
        }
        [Required]
        [ForeignKey("Book")]
        public int BookId { get; set; }
        [Required]
        [MaxLength(30)]
        public string ISBN { get; set; }
        [StringLength(50)]
        public string Edition { get; set; }
        [StringLength(50)]
        public string Volume { get; set; }
        [ForeignKey("Language")]
        public int LanguageId { get; set; }        
        [Required]
        public int Pages { get; set; }
        [Required]
        [ForeignKey("Format")]
        public int FormatId { get; set; }      
        [Required]
        [ForeignKey("Grant")] 
        public int GrantId { get; set; }
        [Required]
        [ForeignKey("Year")]
        public int YearId { get; set; }
        [ForeignKey("Month")]
        public int? MonthId { get; set; }
        public Term Month { get; set; }
        [Required]
        [ForeignKey("DaysAllowed")]
        public int DaysAllowedId { get; set; }
        [ForeignKey("CollectionMode")]
        public int CollectionModeId { get; set; }
        [ForeignKey("Fine")]
        public int FineId { get; set; }
        public string CallNumber { get; set; }        
        // Navigation Properties
        public Term Language { get; set; }
        public Term DaysAllowed { get; set; }               
        public Term CollectionMode { get; set; }        
        public Item Fine { get; set; }        
        public Term Grant { get; set; }        
        public Term Year { get; set; }        
        public Term Format { get; set; }
        public Book Book { get; set; }        
        public ICollection<CheckOut> CheckOuts { get; set; }           
        public ICollection<VariantPrice> VariantPrices { get; set; }          
        public ICollection<VariantCopy> VariantCopies { get; set; }        
        public ICollection<Inventory> Inventories { get; set; }
    }

Publisher.cs

public class Publisher: BaseEntity
    {     
        [Required]
        [MaxLength(255)]
        public string Name { get; set; }
        public string PhoneNumber { get; set; }       
        public int? CountryId { get; set; }
        public ICollection<Book> Books { get; set; }
        public Country Country { get; set; }
    }

Image.cs

    public class Image: BaseEntity {
        public string Path { get; set; }        
        public string ContentType { get; set; }
        public string Extension { get; set; }
        public string Base64 { get; set; }
    }

BookAuthor.cs

public class BookAuthor: BaseEntity
    {
        public int BookId { get; set; }
        public int AuthorId { get; set; }

        [ForeignKey("BookId")]
        public Book Book { get; set; }
        [ForeignKey("AuthorId")]
        public Author Author { get; set; }
    }

EF Core Version

  <ItemGroup>
    <!-- <PackageReference Include="Microsoft.AspNetCore" Version="1.1.2" /><PackageReference Include="Microsoft.AspNetCore.Authentication.Cookies" Version="1.1.2" /><PackageReference Include="Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore" Version="1.1.2" /><PackageReference Include="Microsoft.AspNetCore.Identity.EntityFrameworkCore" Version="1.1.2" /><PackageReference Include="Microsoft.AspNetCore.Authorization" Version="1.1.2" /><PackageReference Include="Microsoft.AspNetCore.Mvc" Version="1.1.3" /><PackageReference Include="Microsoft.AspNetCore.StaticFiles" Version="1.1.2" /><PackageReference Include="Microsoft.AspNetCore.Mvc.Razor.ViewCompilation" Version="1.1.1" /><PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="1.1.2" PrivateAssets="All" /><PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="1.1.1" PrivateAssets="All" /><PackageReference Include="Microsoft.Extensions.Configuration.UserSecrets" Version="1.1.2" /><PackageReference Include="Microsoft.Extensions.Logging.Debug" Version="1.1.2" /><PackageReference Include="Microsoft.VisualStudio.Web.BrowserLink" Version="1.1.2" /><PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="1.1.2" />  -->
    <PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.1" PrivateAssets="All" />
    <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="2.0.1" PrivateAssets="All" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="2.0.0" />
    <PackageReference Include="NewtonSoft.Json" Version="10.0.1" />
    <PackageReference Include="AutoMapper" Version="6.0.2" />
    <PackageReference Include="Autofac.Extensions.DependencyInjection" Version="4.1.0" />
    <PackageReference Include="RazorLight" Version="2.0.0-alpha3" />
    <PackageReference Include="RazorLight.MVC" Version="1.0.4" />
  </ItemGroup>

.NET Command Line Tools (2.0.3)

Product Information:
 Version:            2.0.3
 Commit SHA-1 hash:  eb1d5ee318

Runtime Environment:
 OS Name:     ubuntu
 OS Version:  16.10
 OS Platform: Linux
 RID:         ubuntu.16.10-x64
 Base Path:   /home/smith/dotnet/sdk/2.0.3/

Microsoft .NET Core Shared Framework Host

  Version  : 2.0.3
  Build    : a9190d4a75f4a982ae4b4fa8d1a24526566c69df

Answer 6 · 2018-03-19T21:56:55.000Z

I was able to reproduce the problem. Try the following:

         var book = await _context.Books
                        .Include(x => x.BookAuthors).ThenInclude(x => x.Author)
                        .Include(x => x.Category)
                        .Include(x => x.Publisher)
                        .Include(x => x.Variants).ThenInclude(x => x.Year)
                        .Include(x => x.Variants).ThenInclude(x => x.Month)
                        .Include(x => x.Variants).ThenInclude(x => x.VariantCopies)                        
                        .Where(x => x.Id == id)
                        .Select(x => new BookDetailsViewModel
                        {
                            Id = x.Id,
                            Title = x.Title,
                            SubTitle = x.SubTitle,
                            Category = x.Category.Name,
                            Publisher = x.Publisher.Name,
                            Cover = x.Cover != null ? x.Cover.Path : string.Empty,
                            YearOfPublication = x.Variants.OrderBy(y => y.Id).Select(y => y.Year.Name).FirstOrDefault(),
                            MonthOfPublication = x.Variants.OrderBy(y => y.Id).Select(y => y.Month.Name).FirstOrDefault(),
                            Authors = string.Join(", ", x.BookAuthors.Select(ba => ba.Author.FullName).ToList()),
                        })
                        .SingleOrDefaultAsync();

Basicaly removing Take(1) calls from YearsOfPublication and MonthsOfPublication, it is not needed anyways because FirstOrDefault does the same thing under the covers.

Answer 7 · 2018-03-19T22:17:29.000Z

If I take that away, will it solve the problem? I will try that tomorrow. About to sleep. Thanks for your help

Answer 8 · 2018-03-19T22:19:06.000Z

It solves it on my machine, so it should work for you also.

Answer 9 · 2018-03-20T00:28:43.000Z

Problem is in HandleFirst() after the subquery already has a limit, e.g.

SELECT TOP(1) [o].[CustomerID]
FROM [Orders] AS [o]
WHERE [c].[CustomerID] = [o].[CustomerID]
ORDER BY [o].[OrderID]

we try to pushdown subuqery:

SELECT [t].*
FROM (
    SELECT TOP(1) [o].[CustomerID]
    FROM [Orders] AS [o]
    WHERE [c].[CustomerID] = [o].[CustomerID]
    ORDER BY [o].[OrderID]
) AS [t]

and then we try to lift order by. Currently as a result we generate the following, incorrect sql:

SELECT [t].*
FROM (
    SELECT TOP(1) [o].[CustomerID]
    FROM [Orders] AS [o]
    WHERE [c].[CustomerID] = [o].[CustomerID]
    ORDER BY [o].[OrderID]
) AS [t]
ORDER BY [t].[OrderID]

problem is that the outer query doesn't have OrderID available. We would have to modify the inner projection to include the order column(s), and then manually re-project only the column(s) that were originally there:

SELECT TOP(1) [t].CustomerID
FROM (
    SELECT TOP(1) [o].[CustomerID], o.OrderID
    FROM [Orders] AS [o]
    WHERE [c].[CustomerID] = [o].[CustomerID]
    ORDER BY [o].[OrderID]
) AS [t]
ORDER BY [t].[OrderID]

Alternatively, we could optimize QM and prune all Take calls when FirstOrDefault is present. We can't do that for case like this though:

var i = 0;
ctx.Customers.Select(c => c.Orders.OrderBy(o => o.Id).Take(i).FirstOrDefault())

Answer 10 · 2018-03-20T02:56:37.000Z

Bug in SelectExpression.LiftOrderBy function. It should ensure that ordering which is being pulled outside is referenced by the projection.

Answer 11 · 2018-03-20T11:23:26.000Z

@maumar

Removing the .Take(1) did solve the problem.
I know I arrived at using Take(1) due to an issue I had then. I guess it has been resolved.

Can this issue be closed?

Answer 12 · 2018-03-20T18:05:46.000Z

@smithaitufe this is a legitimate EFCore bug, while Take + FirstOrDefault is redundant and workaround is easy, it still should produce a working query.

Answer 13 · 2018-03-20T19:09:14.000Z

Noted.

I recall that it was an issue that led me into using .Take in the first place. Guess that was resolved.

Answer 14 · 2018-03-20T21:33:15.000Z

added tests for this issue in SimpleQueryTestBase.Select (currently disabled - please enable when the issue is fixed)