sqlstored-proceduresentity-framework-coreiqueryable

ASP.NET Core Web API + EF: how to convert the list of entities to queryable?

I need to rewrite the code for getting and filtering the list of products. The older version used directly the SQL table Products, and filtered the result by code or name of a product, category, etc, like this:

public async Task<List<Product>> GetAllAsync(
    string? filterProd = null, string? filterKat = null,
    string? sortBy = null, bool isAscending = true,
    int pageNumber = 1, int pageSize = 10)
{
    var products = dbContext.Products.AsQueryable();

    // Filtering by part of the code or part of the name.
    if (!string.IsNullOrWhiteSpace(filterProd))
    {
        // filterProd can contain more words separated by spaces.
        var lst = filterProd.Split(' ');

        foreach (string s in lst)
        {
            products = products.Where(x => x.Nazev.Contains(s)
                                        || x.KodZbozi.Contains(s));
        }
    }

    // Similarly filtering by other parameters, ordering...
    // and finally getting the range of items for the page.
    var skipResults = (pageNumber - 1) * pageSize;

    return await products.Skip(skipResults).Take(pageSize).ToListAsync();
}

The new endpoint should return only the subset of products for currently logged customer, but the more user-dependent values are added -- like special prices for the customer. The API can call the usp_products @login=@login that does that. The functionality is more complex (not simple JOIN or the like that); so, the call to the SQL Server stored procedure cannot be replaced by working directly with EF and the tables.

public async Task<List<Product2>> GetAll2Async(
    string? filterProd = null, string? filterKat = null,
    string? sortBy = null, bool isAscending = true,
    int pageNumber = 1, int pageSize = 10)
{
    string xlogin = ...get the user login here...;

    SqlParameter login = new SqlParameter("@login", xlogin);
    var productsLst = await dbContext.Products2
        .FromSql($"EXEC usp_products @login={login}")
        .ToListAsync();

    var products = productsLst.AsQueryable();

    // Filtering by part of the code or part of the name.
    if (!string.IsNullOrWhiteSpace(filterProd))
    {
        // filterProd can contain more words separated by spaces.
        var lst = filterProd.Split(' ');

        foreach (string s in lst)
        {
            products = products.Where(x => x.Nazev.Contains(s)
                                        || x.KodZbozi.Contains(s));
        }
    }

    // Similarly filtering by other parameters, ordering...
    // and finally getting the range of items for the page.
    var skipResults = (pageNumber - 1) * pageSize;

    // Had to remove await and replace the .ToListAsync()
    return products.Skip(skipResults).Take(pageSize).ToList();
}

Is there a better or more usual way to implement it?

Solution

  • EXEC is not a composable statement. In other words, you can't put EXEC into a FromSql and then add more operators to it, because in T-SQL you can't join to the result of an EXEC.

    You have two options. Which you choose depends on your preference or environment, and what you are allowed to modify: