Count after Take throws "No column name was specified for column 1 of 't'."

[ditchcode]

I found similar closed issues surrounding Count with subqueries.

I discovered this passing a Linq query into a 3rd party library which internally performs a Count on the query it is given.

EF Core 7.0 Repro: Perform a Take(25).CountAsync() on any context DbSet.

You wind up with

exec sp_executesql N'SELECT COUNT(*)
FROM (
    SELECT TOP(@__p_0) 1
    FROM [SomeTable] AS [c]
) AS [t]',N'@__p_0 int',@__p_0=25

Which throws database error "No column name was specified for column 1 of 't'"

This could easily be resolved by adding a column name AS [t](x), such as:

exec sp_executesql N'SELECT COUNT(*)
FROM (
    SELECT TOP(@__p_0) 1
    FROM [SomeTable] AS [c]
) AS [t](x)',N'@__p_0 int',@__p_0=25

EF Core 6.0 does not have this issue as it produces SELECT TOP(@__p_0) [c].[Column1],[c].[Column2],..... It seems in 7.0, it was decided to simplify the Count by removing the SELECT fields and just using a hard-coded 1. SELECT TOP(@__p_0) 1

[roji]

Which version of EF and SQL Server are you using? If you're on a version under 6.0, please upgrade to at least 6.0.

I can't reproduce this with either 6.0 or 7.0 on modern SQL Server; please see the code below, and tweak it to make the failure apparent.

Attempted repro

await using var ctx = new BlogContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

_ = await ctx.Blogs.Take(25).CountAsync();

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; }
}

[ditchcode]

It's EF 7. I'll get a better repro together.

[ditchcode]

I discovered the issue affects specifically Keyless Entities.

Here's a Repro: SQL 2019 Net Core 7.0

#nullable disable

using System;
using System.Reflection;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using Microsoft.Extensions.Logging;

var ctx = new ApiContext();


//Error: No column name was specified for column 1 of 't'.
var x = new ApiContext().Contacts.Take(25).Count();

Console.WriteLine(x);

public class ApiContext : DbContext
{
    protected override void OnConfiguring
   (DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("server=.;DATABASE=MyTestDataBase;Integrated Security=true;TrustServerCertificate=True;");
        optionsBuilder.LogTo(m => Console.WriteLine(m), Microsoft.Extensions.Logging.LogLevel.Debug);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());
    }

    public DbSet<Contact> Contacts { get; set; }

}

public class Contact
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}


public partial class ContactMap : IEntityTypeConfiguration<Contact>
{
    public void Configure(EntityTypeBuilder<Contact> entity)
    {
        entity.ToTable("Contacts", "dbo");

        //Make it a Keyless Entity
        //entity.HasKey(e => e.Id);
        entity.HasNoKey();

        entity.Property(e => e.FirstName)
            .HasMaxLength(50)
            .IsUnicode(false);

        entity.Property(e => e.LastName)
            .HasMaxLength(50)
            .IsUnicode(false);
    }
}

[ditchcode]

Apparently when the entity has a key the SQL produced has a valid column name:

SELECT COUNT(*)
      FROM (
          SELECT TOP(@__p_0) [t].[Id]
          FROM [dbo].[Contacts] AS [t]
      ) AS [t0]

[roji]

Thanks, this indeed repros for keyless entity types. When there's a key we just select that in the subquery, but there isn't we select nothing, which is syntactically incorrect. We could always select out star instead for this case.

[roji]

Note that this is a regression from 6.0, where we'd always select all columns, regardless of key/keyless:

SELECT COUNT(*)
FROM (
    SELECT TOP(@__p_0) [b].[Id], [b].[Name]
    FROM [Blogs] AS [b]
) AS [t]

[StefanOssendorf]

With milestone 8.0.0 this will be fixed with EFCore 8.0? Is that correct?

[StefanOssendorf]

Furthermore this also holds for e.g. table valued functions used in a query which results in a SELECT TOP(@__p_0) 1.

And is there a workaround possible until the bug is fixed? This is sadly a blocker for us to update :-/

[ajcvickers]

@maumar to look for workaround.

[maumar]

sadly there is no good workaround - Count is effectively pruning the projection, so we can't do any tricks to circumvent the issue. Either entity must have a key, or Count must be performed on the client.

Note that

exec sp_executesql N'SELECT COUNT(*)
FROM (
    SELECT TOP(@__p_0) 1 as foo
    FROM [SomeTable] AS [c]
) AS [t](x)',N'@__p_0 int',@__p_0=25

works just fine. i.e. we just need to add alias to the "empty" projection to satisfy the sql engine, but that's not something we can force from the user side at the moment.

We should either revert to old behavior or force a dummy alias on the empty projection

[maumar]

reopen for potential servicing