SQL to LINQ EF Core

Question

Помогите привести SQL запрос к рабочему LINQ IQueryable

SELECT 
  MAX([Query].[Price])
FROM 
(SELECT 
  [od].[OrderID],
  SUM([od].[Quantity] * [p].[UnitPrice]) AS [Price]
 FROM [Order Details] AS [od]
 LEFT JOIN [Products] AS[p]
 ON [od].[ProductID] = [p].[ProductID]
 GROUP BY [OrderID]) AS [Query]

Сущности:

public partial class Order
{
    public Order()
    {
        OrderDetails = new HashSet<OrderDetail>();
    }

    public int OrderId { get; set; }
    public string? CustomerId { get; set; }
    public int? EmployeeId { get; set; }
    public DateTime? OrderDate { get; set; }
    public DateTime? RequiredDate { get; set; }
    public DateTime? ShippedDate { get; set; }
    public int? ShipVia { get; set; }
    public decimal? Freight { get; set; }
    public string? ShipName { get; set; }
    public string? ShipAddress { get; set; }
    public string? ShipCity { get; set; }
    public string? ShipRegion { get; set; }
    public string? ShipPostalCode { get; set; }
    public string? ShipCountry { get; set; }

    public virtual Customer? Customer { get; set; }
    public virtual Employee? Employee { get; set; }
    public virtual Shipper? ShipViaNavigation { get; set; }
    public virtual ICollection<OrderDetail> OrderDetails { get; set; }
}

public partial class OrderDetail
{
    public int OrderId { get; set; }
    public int ProductId { get; set; }
    public decimal UnitPrice { get; set; }
    public short Quantity { get; set; }
    public float Discount { get; set; }

    public virtual Order Order { get; set; } = null!;
    public virtual Product Product { get; set; } = null!;
}

public partial class Product
{
    public Product()
    {
        OrderDetails = new HashSet<OrderDetail>();
    }

    public int ProductId { get; set; }
    public string ProductName { get; set; } = null!;
    public int? SupplierId { get; set; }
    public int? CategoryId { get; set; }
    public string? QuantityPerUnit { get; set; }
    public decimal? UnitPrice { get; set; }
    public short? UnitsInStock { get; set; }
    public short? UnitsOnOrder { get; set; }
    public short? ReorderLevel { get; set; }
    public bool Discontinued { get; set; }

    public virtual Category? Category { get; set; }
    public virtual Supplier? Supplier { get; set; }
    public virtual ICollection<OrderDetail> OrderDetails { get; set; }
}

Спасибо.

Answer 1

var query1 = from od in db.OrderDetails
             join p in db.Products
               on od.ProductId equals p.ProductId
             into tmp
             from p in tmp.DefaultIfEmpty()
             group od by od.OrderId
             into g
             select g.Sum(od => od.Quantity * od.UnitPrice)
             ;

var result1 = query1.Max();

Сгенерированный SQL:

SELECT MAX([t].[c])
FROM (
    SELECT COALESCE(SUM(CAST([o].[Quantity] AS decimal(18,2)) * [o].[UnitPrice]), 0.0) AS [c]
    FROM [OrderDetails] AS [o]
    LEFT JOIN [Products] AS [p] ON [o].[ProductId] = [p].[ProductId]
    GROUP BY [o].[OrderId]
) AS [t]

Использовался SQL Server. Провайдеры других СУБД могут выдать другой запрос.

Вариант без подзапроса, с сортировкой:

var query2 = from od in db.OrderDetails
             join p in db.Products
               on od.ProductId equals p.ProductId
             into tmp
             from p in tmp.DefaultIfEmpty()
             group od by od.OrderId into g
             select g.Sum(od => od.Quantity * od.UnitPrice)
             into g2
             orderby g2 descending
             select g2
             ;

var result2 = query2.First();

Сгенерированный SQL:

SELECT TOP(1) COALESCE(SUM(CAST([o].[Quantity] AS decimal(18,2)) * [o].[UnitPrice]), 0.0)
FROM [OrderDetails] AS [o]
LEFT JOIN [Products] AS [p] ON [o].[ProductId] = [p].[ProductId]
GROUP BY [o].[OrderId]
ORDER BY COALESCE(SUM(CAST([o].[Quantity] AS decimal(18,2)) * [o].[UnitPrice]), 0.0) DESC

БЛОГ НА HUSL

SQL to LINQ EF Core

Ответы (1 шт):