sql-server - 如何将 SQL 命令转换为实体框架和 Lambda 表达式

Question

我想将一些 SQL 代码转换为实体框架

select *
into #invoice
from Invoice
where Track = 2

select Fuel.ID, SUM(ISNULL(#invoice.Price, 0)) as Price, SUM(ISNULL(#invoice.Price, 0))
from Fuel left outer join #invoice
on Fuel.ID = #invoice.Fuel
group by Fuel.ID

尝试编写此代码，但结果为零 (0)（价格和升）

var data = fules.GroupJoin(model,
          f => f.ID,
          m => m.ID,
           (f, m) => new { f, m })
         .SelectMany(x => x.m.DefaultIfEmpty(),
         (x, m) => new { Fuel = x.f, Price = m?.Price, Liter = m?.Liter })
          .GroupBy(A => A.Fuel)
          .Select(A => new { Fuel = A.Key.Name, Liter = A.Sum(B => B.Liter)
, Price = A.Sum(B => B.Price) })
              .ToList();

标签： sql-serverasp.net-mvcentity-frameworklinqlambda

Answer 1

我想你正在寻找这样的东西：

var query = from f in db.Feuls
            from i in f.Invoices.Where(x => x.Track == 2).DefaultIfEmpty()
            group i by f.ID into g
            select new
            {
               ID = g.Key,
               Liter = g.Sum(x => g.Liter),
               Price = g.Sum(x => g.Price)
            };