SQLKata Join Query in SQL Server #751

Hi,

I'm working with a SQL Server database and trying to execute a query that joins two tables: ParameterClasses and EquipmentClasses. Both tables contain a column named Id. However, the query fails with the following errors:

Invalid column name 'ParameterClasses.EquipmentClassId'
Invalid column name 'EquipmentClasses.Id'
Invalid column name 'ParameterClasses.Id'
Invalid column name 'ParameterClasses.Name'

Here’s the code I'm using to build the query:

query = factory.Query("ParameterClasses")
.Join("EquipmentClasses", "ParameterClasses.EquipmentClassId", "EquipmentClasses.Id")
.Select(
"ParameterClasses.Id as ParameterClassId",
"ParameterClasses.Name",
"EquipmentClasses.Id as EquipmentClassId",
"EquipmentClasses.Name as EquipmentClassName"
);

To verify the generated SQL, I also compiled the query using the following code:

var compiler = new SqlServerCompiler();
var sqlResult = compiler.Compile(query);
Console.WriteLine(sqlResult.Sql);

The generated SQL (sqlResult.Sql) looks correct and executes successfully when run directly in SQL Server:

SELECT [ParameterClasses].[Id] AS [ParameterClassId],
[ParameterClasses].[Name],
[EquipmentClasses].[Id] AS [EquipmentClassId],
[EquipmentClasses].[Name] AS [EquipmentClassName]
FROM [ParameterClasses]
INNER JOIN [EquipmentClasses]
ON [ParameterClasses].[EquipmentClassId] = [EquipmentClasses].[Id]

I’d appreciate any guidance you can provide to resolve this issue within the application.

Best regards,

Hamila