SqlException: "No column name was specified for column 1 of 't'" on IQueryable.Count() with Skip and Take

[hvkooten]

Description

Retrieving the number of records via Count() in a IQueryable with Skip() and Take() results in an exception:

Reproduction Steps

var query = context.Set<TestView>().Where(q => q.ItemType== itemType).AsQueryable();
var total = query.Count();
query =query.Skip(0).Take(1000);
var subTotal = query.Count();

Expected behavior

First Count() results in SQL statement:

exec sp_executesql N'SELECT COUNT(*)
FROM [View] AS [a]
WHERE [a].[ItemType] = @__Type_0',N'@__Type_0 nvarchar(4000)',@__Type_0=N'PO'

expected: returns rowcount

Second Count() results in SQL statement:

exec sp_executesql N'SELECT COUNT(*)
FROM (
    SELECT 1 AS column1
    FROM [View] AS [a]
    WHERE [a].[ItemType] = @__Type_0
    ORDER BY (SELECT 1)
    OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY
) AS [t]',N'@__Type_0 nvarchar(4000),@__p_1 int,@__p_2 int',@__Type_0=N'PO',@__p_1=0,@__p_2=1000

expected: returns 1000

Actual behavior

Second Count() results in SQL statement:

exec sp_executesql N'SELECT COUNT(*)
FROM (
    SELECT 1
    FROM [View] AS [a]
    WHERE [a].[ItemType] = @__Type_0
    ORDER BY (SELECT 1)
    OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY
) AS [t]',N'@__Type_0 nvarchar(4000),@__p_1 int,@__p_2 int',@__Type_0=N'PO',@__p_1=0,@__p_2=1000

and throws a sqlexception: "No column name was specified for column 1 of 't'"

Regression?

In .net 6.0 this works, but while moving to .net 7.0 we got this exception.

Known Workarounds

query.ToList().count;
But this will materialize the query and send all data to memory

Configuration

.net 7.0
development: Windows
production: Azure

Other information

The problem is, there is no column name in the query. By adding "AS column1" in the select statement the query will work.

[dotnet-issue-labeler]

I couldn't figure out the best area label to add to this issue. If you have write-permissions please help me learn by adding exactly one area label.

[hvkooten]

area-System.Linq.Expressions

[ghost]

Tagging subscribers to this area: @cston
See info in area-owners.md if you want to be subscribed.

Issue Details

---

Description

Retrieving the number of records via Count() in a IQueryable with Skip() and Take() results in an exception:

Reproduction Steps

var query = context.Set<TestView>().Where(q => q.ItemType== itemType).AsQueryable();
var total = query.Count();
query =query.Skip(0).Take(1000);
var subTotal = query.Count();

Expected behavior

First Count() results in SQL statement:

exec sp_executesql N'SELECT COUNT(*)
FROM [View] AS [a]
WHERE [a].[ItemType] = @__Type_0',N'@__Type_0 nvarchar(4000)',@__Type_0=N'PO'

expected: returns rowcount

Second Count() results in SQL statement:

exec sp_executesql N'SELECT COUNT(*)
FROM (
    SELECT 1 AS column1
    FROM [View] AS [a]
    WHERE [a].[ItemType] = @__Type_0
    ORDER BY (SELECT 1)
    OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY
) AS [t]',N'@__Type_0 nvarchar(4000),@__p_1 int,@__p_2 int',@__Type_0=N'PO',@__p_1=0,@__p_2=1000

expected: returns 1000

Actual behavior

Second Count() results in SQL statement:

exec sp_executesql N'SELECT COUNT(*)
FROM (
    SELECT 1
    FROM [View] AS [a]
    WHERE [a].[ItemType] = @__Type_0
    ORDER BY (SELECT 1)
    OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY
) AS [t]',N'@__Type_0 nvarchar(4000),@__p_1 int,@__p_2 int',@__Type_0=N'PO',@__p_1=0,@__p_2=1000

and throws a sqlexception: "No column name was specified for column 1 of 't'"

Regression?

In .net 6.0 this works, but while moving to .net 7.0 we got this exception.

Known Workarounds

query.ToList().count;
But this will materialize the query and send all data to memory

Configuration

.net 7.0
development: Windows
production: Azure

Other information

The problem is, there is no column name in the query. By adding "AS column1" in the select statement the query will work.

Author:	hvkooten
Assignees:	-
Labels:	area-System.Linq.Expressions, untriaged
Milestone:	-

[hvkooten]

@jaredpar @cston @333fred any update on the issue?

[cston]

Thanks @hvkooten, I'll investigate.

[cston]

@ajcvickers, should this issue be assigned to area-System.Data?

From a simple test, it looks like the IQueryable instance created for .Skip(0).Take(1000) is unchanged from .NET 6, but it's quite possible I've overlooked something. Thanks.

[hvkooten]

Hi @cston,
Because of your comment, I've tested the same code in .net 6 and saw in the profiler this sql statement:

exec sp_executesql N'SELECT COUNT(*)
FROM (
    SELECT [p].[Field1], [p].[Field2], [p].[Field3]
    FROM [View] AS [p]
    WHERE [p].[ItemType] = @__Type_0
    ORDER BY (SELECT 1)
    OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY
) AS [t]',N'@__Type_0 nvarchar(4000),@__p_1 int,@__p_2 int',@__Type_0=N'PO',@__p_1=0,@__p_2=1000

So, there definitive is a change somewhere ;-)

[maumar]

looks like a dupe of #29667, which is now fixed in main and approved for 7.0.4 patch