在asp.net mvc中对LINQ进行SQL查询

本文关键字：进行 SQL 查询 LINQ 中对 asp net mvc | 更新日期: 2023-09-27 18:23:55

请任何人帮助我将此sql查询写入Linq。我试过了。。

这是我的sql查询

select  o.OrderID,o.Nature,o.Date,od.TotalPrice,os.OrderStatus,lo.FirstName,lo.EmailAddress,lo.PhoneNumber
from [dbo].[Order] o
    inner join [dbo].[tbl_OrderDetails] od on  od.OrderID = o.OrderID
    inner join [dbo].[tbl_OrderHistory] oh on oh.OrderID = o.OrderID
    inner join [dbo].[tbl_Login] lo on o.UserID = lo.UserID
    inner join dbo.tbl_OrderStatus os on oh.OrderStatusID= os.OrderStatusID
group by o.OrderID,o.Nature,od.TotalPrice,o.Date,os.OrderStatus,lo.FirstName,lo.EmailAddress,lo.PhoneNumber

这是我尝试的

public override orderDetailModel orderDetails(int id)
{
    var results = from o in obj.Orders
        join od in obj.tbl_OrderDetails on o.OrderID equals od.OrderID
        join oh in obj.tbl_OrderHistory on o.OrderID equals oh.OrderID
        join l in obj.tbl_Login on o.UserID equals l.UserID
        join os in obj.tbl_OrderStatus on oh.OrderStatusID equals os.OrderStatusID
        where (od.OrderID == id)
        group o by new { o.Nature, o.OrderID } into 
        select new orderDetailModel
        {
            OrderID = o.OrderID,
            OrderStatus = os.OrderStatus,
            Date = o.Date,
            DeliveryNature = o.Nature,
            EmailAddress = l.EmailAddress,
            FirstName = l.FirstName,
            PhoneNumber = l.PhoneNumber,
            TotalPrice = od.TotalPrice
        };
    //group o by new {o.OrderID};
    orderDetailModel data = (orderDetailModel)results.FirstOrDefault();
    return data;
}

但这是错误的查询，它不能正常工作，请帮助我

在asp.net mvc中对LINQ进行SQL查询

您需要更正group by子句，就像您在SQL查询中所做的那样：-

group new { o, l } by new {  o.OrderID,o.Nature,od.TotalPrice,o.Date,os.OrderStatus,
                             l.FirstName, l.EmailAddress,l.PhoneNumber } into g
                      select new orderDetailModel
                      {
                          OrderID = g.Key.OrderID,
                          OrderStatus = g.Key.OrderStatus,
                          Date = g.Key.Date,
                          ..and so on
                      };

由于需要在两个表CCD_ 2&tbl_Login您必须首先将它们投影为匿名类型group new { o, l }，然后指定所有分组，最后在投影时使用Key来获得相应的项。

我想实际上，SQL查询也是不正确的。我只需要使用SELECT DISTINCT。。。而不是对所有列进行分组。

无论如何，首先要做的是：

  1. 检查数据库的设计是否正确。据我所见，如果你用他们的ID加入表格，我不明白为什么你需要对所有数据进行分组。如果有重复项，则可能是数据库设计中的错误

  2. 如果你不能更改你的数据库，或者你对此很满意，那么使用以下LINQ方法：

    var distinctKeys = allOrderDetails.Select(o => new { o.OrderID, o.Nature, o.TotalPrice, o.Date,o.OrderStatus,o.FirstName, o.EmailAddress,o.PhoneNumber }).Distinct();
var joined = from e in allOrderDetails
             join d in distinctKeys
             on new { o.OrderID, o.Nature,o.TotalPrice, o.Date,o.OrderStatus, o.FirstName, o.EmailAddress, o.PhoneNumber }   equals d select e;
joined.ToList(); // gives you the distinct/grouped list
相关文章：