将SQL查询转换为应用程序的LINQ

本文关键字：应用程序 LINQ 转换 SQL 查询 | 更新日期: 2023-09-27 18:00:38

我在TSQL中有一个查询，我正试图将其转换为LINQ，以便在我们的web应用程序中使用，但我真的很难解决这个问题。它是带有EF6的MVC5，数据库是SQL Server 2008 R2。感谢您的帮助！

SQL查询

select MAX(ShipFromCompanyName) as Supplier, COUNT(*) as AllSupplierCount, 
SUM(isnull(cast(TransportationCharges as decimal(18,2)),0)) as AllFreightCharges,
SUM(isnull(cast(TransportationCharges as decimal(18,2)),0)) * .45 as FreightSavings
from table
group by ShipFromCompanyName
order by ShipFromCompanyName

ShipFromCompanyName和TransportationCharges都以varchar的形式存储在数据库中，不幸的是，我无法将TransportationCharge的数据类型更改为十进制

LINQ

var Scorecard = (from upsid in _db.table select upsid).GroupBy(x => new { x.ShipFromCompanyName, x.TransportationCharges })
            .Select(x => new
            {
                x.Key.ShipFromCompanyName,
                SupplierCount = x.Count(),
                FreightCharges = x.Key.TransportationCharges.Cast<decimal>().Sum(),
            }).ToList();

将SQL查询转换为应用程序的LINQ

我认为你需要在后处理中完成，而不是让SQL来完成。让SQL尽可能多地完成，然后在内存中完成其余部分

var Scorecard = (from upsid in _db.table select upsid).GroupBy(x => new { x.ShipFromCompanyName, x.TransportationCharges })
        .Select(x => new
        {
            x.Key.ShipFromCompanyName,
            SupplierCount = x.Count(),
            FreightCharges = x.Key.TransportationCharges,
        }).AsEnumerable()
        .Select (x => new 
        {
            ShipFromCompanyName = ShipFromCompanyName ,
            SupplierCount = SupplierCount ,
            FreightCharges = FreightCharges.Cast<decimal>.Sum() ,
        }

没有测试这个代码，但它应该会给你这个想法。

var Scorecard = (from upsid in _db.table select upsid)
.GroupBy(x => new { x.ShipFromCompanyName, x.TransportationCharges })
.Select(x => new
{
    x.Key.ShipFromCompanyName,
    SupplierCount = x.Count(),
    FreightCharges = x.Key.TransportationCharges.Select(tc=>decimal.Parse(tc)).Sum()*0.45,
}).ToList();

由于我没有清楚地理解你的询问，我不确定，但这可能会奏效。

相关文章：