SqlServerCompiler - ORDER BY uses wrong moniker

[SeattleDiver]

When generating a query that uses aliased column names AND paged queries, the ORDER BY syntax uses the aliased column, which SQL Server does not like...

Example Query from Northwind database using aliased column names (see the ORDER BY statement below):

SELECT *
FROM (
SELECT
[XT1].[ProductID] AS [XT1_Products_ProductID],
[XT1].[ProductName] AS [XT1_Products_ProductName],
[XT1].[SupplierID] AS [XT1_Products_SupplierID],
[XT1].[CategoryID] AS [XT1_Products_CategoryID],
[XT1].[QuantityPerUnit] AS [XT1_Products_QuantityPerUnit],
[XT1].[UnitPrice] AS [XT1_Products_UnitPrice],
[XT1].[UnitsInStock] AS [XT1_Products_UnitsInStock],
[XT1].[UnitsOnOrder] AS [XT1_Products_UnitsOnOrder],
[XT1].[ReorderLevel] AS [XT1_Products_ReorderLevel],
[XT1].[Discontinued] AS [XT1_Products_Discontinued],
[XT2].[SupplierID] AS [XT2_Suppliers_SupplierID],
[XT2].[CompanyName] AS [XT2_Suppliers_CompanyName],
[XT3].[CategoryID] AS [XT3_Categories_CategoryID],
[XT3].[CategoryName] AS [XT3_Categories_CategoryName],
ROW_NUMBER() OVER (ORDER BY [XT1_Products_ProductID]) <<- should be [XT1].[ProductID]
AS [row_num] FROM [Products] AS [XT1]
INNER JOIN [Suppliers] AS [XT2] ON [XT1].[SupplierID] = [XT2].[SupplierID]
INNER JOIN [Categories] AS [XT3] ON [XT1].[CategoryID] = [XT3].[CategoryID]) AS [results_wrapper] WHERE [row_num] BETWEEN 16 AND 30

SQL Server does not like the XT1_Products_ProductID column name in the ORDER BY statement.

[ahmad-moussawi]

Could you post the C# code related to this query?

[SeattleDiver]

Could you post the C# code related to this query?

Here is the CS code for the ProductModel. The SQL query is built in the Query() method (L85..130). The compiled SQL is created and used at L118-119.

If you need the whole solution, I can send it to you; let me know if you need it.

// ------------------------------------------------------------------------------------------------
// <auto-generated> 
// This code was generated by a tool. 
// Runtime Version: 4.0.30319.42000
// 
// Changes to this file may cause incorrect behavior and will be lost if the code is regenerated. 
// </auto-generated> 
//
// Code Generator   : ApiStudio 1.0a
// Code Template    : Default
// Template Language: CSharp
// Date Modified    : 5/12/2021 11:55 AM
// ------------------------------------------------------------------------------------------------

using BlazorStudio.ProjectName96.DataServices.Contracts;
using BlazorStudio.ProjectName96.DataServices.Models;
using Microsoft.EntityFrameworkCore;
using SqlKata;
using SqlKata.Compilers;
using System.Collections.Generic;
using System.Diagnostics.CodeAnalysis;
using System.Linq;
using System.Threading.Tasks;

namespace BlazorStudio.ProjectName96.DataServices.Services
{
    [ExcludeFromCodeCoverage]
    public partial class ProductsModelDataService : IProductsModelDataService
    {
        private IProductsDataService ProductsDataService { get; set; }
        private DatabaseContext Context { get; set; }

        /// <summary>
        /// Initializes a new instance of the <see cref="ProductsModelDataService"/> class.
        /// </summary>
        public ProductsModelDataService(DatabaseContext databaseContext, IProductsDataService productsDataService)
        {
            this.Context = databaseContext;
            ProductsDataService = productsDataService;
        }

        /// <summary>
        /// GetById returns the requested ProductsModel entity
        /// </summary>
        /// <returns>Returns a ProductsModel object from the database</returns>
        public async Task<ProductsModel> GetById(int Products_ProductID)
		{
            var compiler = new SqlServerCompiler();
            var query = GetBaseQuery()
                .Where("XT1.ProductID", $"{Products_ProductID}");
            var result = compiler.Compile(query);
            ProductsModel viewModel = await Context.ProductsModels.FromSqlRaw<ProductsModel>(result.Sql, result.Bindings.ToArray()).SingleOrDefaultAsync();

            return viewModel;
		}

        /// <summary>
        /// Creates a new the ProductsModel data model
        /// </summary>
        /// <param name="productsModel"></param>
        /// <returns></returns>
        public async Task<ProductsModel> Create(ProductsModel productsModel)
        {
            Products products = productsModel.GetProducts();
            products = await ProductsDataService.Create(products);
            return await GetById(products.ProductID);
        }

        /// <summary>
        /// Update the ProductsModel data model
        /// </summary>
        /// <param name="productsModel"></param>
        /// <returns></returns>
        public async Task<ProductsModel> Update(ProductsModel productsModel)
        {
            Products products = productsModel.GetProducts();
            products = await ProductsDataService.Update(products);
            return await GetById(products.ProductID);
        }

		/// <summary>
        /// Return all records for the specified page
        /// </summary>
        /// <returns>Returns a list of ProductsModel objects matching the query parameters</returns>
        public async Task<PagedQueryResults<ProductsModel>> Query(PagedQueryRequest pagedQueryRequest)
        {
            var compiler = new SqlServerCompiler();
            SqlResult sqlResult = null;

            int itemCount = Context.Products.Count();
            Query query = GetBaseQuery();

            // Query/filter parameter
            if (!string.IsNullOrWhiteSpace(pagedQueryRequest.QueryParameter))
            {
                query = query.Where(pagedQueryRequest.QueryParameter, pagedQueryRequest.QueryValue);

                // Get item count
                var countQuery = query.Clone();
                countQuery.SelectRaw("count(*) as ItemCount");
                sqlResult = compiler.Compile(countQuery);
                var sqlCount = await Context.SqlCounts.FromSqlRaw<SqlCount>(sqlResult.Sql, sqlResult.Bindings.ToArray()).ToListAsync();
                itemCount = sqlCount.FirstOrDefault().ItemCount;
            }

            // Sort Order
            if (pagedQueryRequest.SortDirection.ToLower().StartsWith("asc"))
                query.OrderBy(pagedQueryRequest.SortColumn);
            else
                query.OrderByDesc(pagedQueryRequest.SortColumn);

            // Pagination
            query = query
                .Limit((int)pagedQueryRequest.PageSize)
                .Offset((int)(pagedQueryRequest.PageNumber * pagedQueryRequest.PageSize));

            // Get the rows and return the PagedQueryResults
            sqlResult = compiler.Compile(query);
            List<ProductsModel> viewModels = await Context.ProductsModels.FromSqlRaw<ProductsModel>(sqlResult.Sql, sqlResult.Bindings.ToArray()).ToListAsync();
            PagedQueryResults<ProductsModel> pagedQueryResults = new PagedQueryResults<ProductsModel>()
            {
                ItemCount = itemCount,
                PageNumber = pagedQueryRequest.PageNumber,
                PageSize = pagedQueryRequest.PageSize,
                SortColumn = pagedQueryRequest.SortColumn,
                SortDirection = pagedQueryRequest.SortDirection,
                Items = viewModels
            };
            return pagedQueryResults;
        }

        /// <summary>
        /// Common query builder parameters
        /// </summary>
        /// <returns>Returns the base query for the primary table and all joined tables</returns>
        public Query GetBaseQuery()
        {
            return new Query("Products as XT1")
                       .Select(
                           "XT1.ProductID AS XT1_Products_ProductID",
                           "XT1.ProductName AS XT1_Products_ProductName",
                           "XT1.SupplierID AS XT1_Products_SupplierID",
                           "XT1.CategoryID AS XT1_Products_CategoryID",
                           "XT1.QuantityPerUnit AS XT1_Products_QuantityPerUnit",
                           "XT1.UnitPrice AS XT1_Products_UnitPrice",
                           "XT1.UnitsInStock AS XT1_Products_UnitsInStock",
                           "XT1.UnitsOnOrder AS XT1_Products_UnitsOnOrder",
                           "XT1.ReorderLevel AS XT1_Products_ReorderLevel",
                           "XT1.Discontinued AS XT1_Products_Discontinued",
                           "XT2.SupplierID AS XT2_Suppliers_SupplierID",
                           "XT2.CompanyName AS XT2_Suppliers_CompanyName",
                           "XT3.CategoryID AS XT3_Categories_CategoryID",
                           "XT3.CategoryName AS XT3_Categories_CategoryName")
                       .Join("Suppliers as XT2", "XT1.SupplierID", "XT2.SupplierID")
                       .Join("Categories as XT3", "XT1.CategoryID", "XT3.CategoryID");
        }
    }
}

[chrisgate]

I am curious why you always go back to DbContext?
How have seen or used EF and Sqlkata/Dapper is to use EF to do all saving related and use Sqlkata/Dapper for all query related

[SeattleDiver]

I have an entire code generator built around EFCore, and currently I don't have any plans on refactoring it. All I wanted was to generate a few queries, as shown above, and execute them directly. I'd rather drop SqlKata altogether and write my own query builder than remove EFCore and move to Dapper-and I don't want to have both, nor do I want to include the SqlKata execute assemblies in my generated solutions. However, I was able to refactor my code generator and build the actual SQL in my code generator instead of creating the query in the generated output, so this issue is now moot - at least for me.

[generik0]

Maybe close issue? No others have come with same issue

We also use ef and sqlkata.
Ef for simple, kata when ef makes a mess of it.

No issues here