When using Union/Concat and with a projected class not all columns get selected in union

[julian-altech]

Given this object:

private record UserGroupInfo
{
    public string ID { get; init; }
    public string Name { get; init; }
}

The following query works (all properties are strings):

var usersQuery = _dbContext.Users
    .Where(u => u.UserName.Contains(search))
    .Select(u => new UserGroupInfo { ID = u.Id, Name = u.UserName });
var groupQuery = _dbContext.Roles
    .Where(r => r.Name.Contains(search))
    .Select(r => new UserGroupInfo { ID = r.Id, Name = r.Name });
var results = await usersQuery.Concat(groupQuery).ToArrayAsync();

However it fails if we change the 3rd line to use the UserName property for both the ID and Name properties like so:

var usersQuery = _dbContext.Users
   .Where(u => u.UserName.Contains(search))
   .Select(u => new UserGroupInfo { ID = u.UserName, Name = u.UserName });
var groupQuery = _dbContext.Roles
    .Where(r => r.Name.Contains(search))
    .Select(r => new UserGroupInfo { ID = r.Id, Name = r.Name });
var results = await usersQuery.Concat(groupQuery).ToArrayAsync();

The exact error is "Unable to translate set operation when matching columns on both sides have different store types." but this happens because the generated SQL for the 2 queries has a different column count, it generates the following:

SELECT [a].[UserName] AS [Name]
FROM [AspNetUsers] AS [a]
WHERE (@__search_0 LIKE N'') OR (CHARINDEX(@__search_0, [a].[UserName]) > 0)

UNION ALL

SELECT [a0].[Id] AS [ID], [a0].[Name]
FROM [AspNetRoles] AS [a0]
WHERE (@__search_0 LIKE N'') OR (CHARINDEX(@__search_0, [a0].[Name]) > 0)

Which SQL server fails on. To work around this I am just selecting 2 different properties as the working query and then changing the value in memory.

[smitpatel]

Duplicate of #19129 #15586

[julian-altech]

I'm not convinced this is the same issue? Those issues seem related to the types of the data and the SQL column types. In the case I posted all columns are simply nvarchar(max) and all properties are string.

The only failing part is the generated SQL selects 1 column union 2 columns which sql can't do, that isn't a type of data issue but a query generation issue itself.

[smitpatel]

They are not all nvarchar(max). Name is nvarchar(max) but ID being a key is of type nvarchar(450) since SqlServer doesn't allow unlimited size keys.

[julian-altech]

You're right, my mistake. In my actual use case they did differ with nvarchar(max) and nvarchar(100) Thanks :)