public async Task<IEnumerable<Prdouct>> GetAllAsync()
    {
        var result = (from P in dbContext.Products
                      where P.CompanyID == 3 &&
                            P.DisplayOrder == 1 &&
                            P.Demo != 1 &&
                            P.Status == "1" &&
                            !P.ModelNumber.Contains("P-%")
                      orderby P.ProductID
                      **select new Product
                      {
                          ProductID = P.ProductID,
                          ModelNumber = P.ModelNumber,
                          ModelName = P.ModelName,
                          Description = P,Description,
                          Image = P.Image,
                          UnitCost = P.UnitCost,
                          Price = P.Price,
                          Weight = P.Weight,
                          Dimension = P.Dimension,
                          Color = P.Color,
                          Source = P.Source,
                          CategoryID = P.CategoryID,
                          Style = P.Style,
                          Class = P.Class,
                          Type = P.Type
                      }**
                      ).ToListAsync();
     }

and the Product class

public partial class Product
{
public long ProductID { get; set; }

public int? CategoryID { get; set; }

public string ModelNumber { get; set; }

public string ModelName { get; set; }

public string Image { get; set; }

public decimal? UnitCost { get; set; }

public string Description { get; set; } .....

The Product table contains around 10K records and the LINQ is only return around 500 records.

The bottleneck is the construction of the new Product object into the list of Products (which is highlighted in Bold).

It take around 2 minutes to return the data from the ASP.Net Core API. I use swagger to test it!

enter image description here

I checked with SQL Server, it take less than 3 seconds.