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 :-/