[c#]在 asp.net mvc 中 LINQ 到 SQL 查询

标签: Linq SQLServer C#
发布时间: 2017/2/26 15:31:19
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

请任何人可以帮我到 Linq 编写这个 sql 查询。我用过。

这是我的 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;
}

但这是错误的查询,它不工作的很好请帮助我

解决方法 1:

您需要更正 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
                      };

因为你需要对两个表进行分组 Order & tbl_Login 你将不得不首先项目他们为匿名类型 group new { o, l } 然后指定所有分组和最后虽然突出使用 Key 来获取各自项目。

官方微信
官方QQ群
31647020