LinqToSql生成的sql语句自动添加了orderby还缺了个左链接

[romanchaos]

var query = from p in db.TheOrder_merge
             join g in db.TheOrder on p.Id equals g.Pid into lstOrder
             join s in db.The_Order_ShippingFee on p.Id equals s.Orderid into lstShipFee
             where lstOrder.All(q => q.Solddate < DateTime.Now.AddMonths(-3) && (q.Resend == 2 || q.deleteid == 2))
             select new { Merge = p, LstOrder = lstOrder, LstShipFee = lstShipFee };

SELECT [t0].[Id], [t0].[WareHouseId], [t0].[CustomerId], [t0].[CustomerName], [t0].[CountryName], [t0].[CountryCode], [t0].[Address1], [t0].[Address2], [t0].[Province], [t0].[City], [t0].[ZipCode], [t0].[Email], [t0].[MobilePhoneNo], [t0].[ShippedOnDate], [t0].[ShippingServiceId], [t0].[IsComplete], [t0].[TrackingNo], [t0].[Remarks], [t0].[AuditId], [t0].[Isrepeat], [t0].[Isstamp], [t0].[SenderId], [t0].[Freight], [t0].[Cid], [t0].[Oid], [t0].[Modidate], [t0].[Delflag], [t0].[goodprofit], [t0].[Parcelweight], [t0].[SendDate], [t0].[PackageNo], [t1].[Id] AS [Id2], [t1].[IsThird], [t1].[SKUId], [t1].[CustomerId] AS [CustomerId2], [t1].[CustomerName] AS [CustomerName2], [t1].[CountryName] AS [CountryName2], [t1].[CountryCode] AS [CountryCode2], [t1].[Address1] AS [Address12], [t1].[Address2] AS [Address22], [t1].[Province] AS [Province2], [t1].[City] AS [City2], [t1].[ZipCode] AS [ZipCode2], [t1].[Email] AS [Email2], [t1].[MobilePhoneNo] AS [MobilePhoneNo2], [t1].[SalesRecordNumber], [t1].[trackingNo] AS [trackingNo2], [t1].[WareHouseId] AS [WareHouseId2], [t1].[SiteId], [t1].[AccountId], [t1].[TransactionID], [t1].[PayPalTransactionID], [t1].[ShippedOnDate] AS [ShippedOnDate2], [t1].[PaidOnDate], [t1].[SalePrice], [t1].[TotalPrice], [t1].[PaymentMethod], [t1].[ShippingandHandling], [t1].[Quantity], [t1].[ItemTitle], [t1].[ItemNumber], [t1].[Remarks] AS [Remarks2], [t1].[Currid], [t1].[ShippingService], [t1].[Shipfee], [t1].[Resend], [t1].[Returned], [t1].[Refundid], [t1].[IsRefunder], [t1].[Refunder], [t1].[RefundChecker], [t1].[SaleManID], [t1].[CSSId], [t1].[SenderId] AS [SenderId2], [t1].[platformId], [t1].[AuditId] AS [AuditId2], [t1].[isrepeat] AS [isrepeat2], [t1].[isstamp] AS [isstamp2], [t1].[isPicking], [t1].[freight] AS [freight2], [t1].[deleteid], [t1].[reissuing], [t1].[Refunds], [t1].[RefundsDate], [t1].[orderid], [t1].[CustomLabel], [t1].[eBayfees], [t1].[ppfees], [t1].[adid], [t1].[ShippingServiceId] AS [ShippingServiceId2], [t1].[Pickingid], [t1].[egroup], [t1].[goodprofit] AS [goodprofit2], [t1].[msnon], [t1].[caseon], [t1].[eFeedback], [t1].[feedbacktitle], [t1].[StayAssess], [t1].[StayAssessStatus], [t1].[eubon], [t1].[erraddress], [t1].[ebayshiped], [t1].[Pid], [t1].[Cid] AS [Cid2], [t1].[IsSplit], [t1].[goodprice], [t1].[Oname], [t1].[Oid] AS [Oid2], [t1].[Modidate] AS [Modidate2], [t1].[Pickingstatus], [t1].[Linestatus], [t1].[Solddate], [t1].[Packfees], [t1].[PayPalId], [t1].[ResendFatherID], [t1].[exportstatus], [t1].[oldid], [t1].[ReasonResend], [t1].[ReasonRefund], [t1].[ebayshippdate], [t1].[CustomerNote], [t1].[rebefgoodprofit], [t1].[Addtype], [t1].[BbeId], [t1].[RefundType], [t1].[SaleGoodProfit], [t1].[ReBefSaleGoodProfit], [t1].[IsSynTran], [t1].[HasAutoForbi], (
    SELECT COUNT(*)
    FROM [dbo].[TheOrder] AS [t3]
    WHERE ([t0].[Id]) = [t3].[Pid]
    ) AS [value]
FROM [dbo].[TheOrder_merge] AS [t0]
LEFT OUTER JOIN [dbo].[TheOrder] AS [t1] ON ([t0].[Id]) = [t1].[Pid]
WHERE NOT (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[TheOrder] AS [t2]
    WHERE ((
        (CASE 
            WHEN ([t2].[Solddate] < '2014/3/3 17:04:21') AND (([t2].[Resend] = 2) OR ([t2].[deleteid] = 2)) THEN 1
            ELSE 0
         END)) = 0) AND (([t0].[Id]) = [t2].[Pid])
    ))
ORDER BY [t0].[Id], [t1].[Id]

第一个是linq，第二个是生成的sql语句，现在有两个问题：1、sql语句中缺少对he_Order_ShippingFee的左链接：2、生成的sql语句多了ORDER BY [t0].[Id], [t1].[Id]，请问原因是什么呢？

[q107770540]

你写的LINQ语句里又没有用LEFT JOIN,那它生成的SQL语句中怎么会有呢?

[q107770540]

var query = from p in db.TheOrder_merge              join g in db.TheOrder on p.Id equals g.Pid into lstOrder from g in lstOrder.DefaultIfEmpty()              join s in db.The_Order_ShippingFee on p.Id equals s.Orderid into lstShipFee from s in lstShipFee.DefaultIfEmpty()            .... 这才是左连接

[romanchaos]

引用 2 楼 q107770540 的回复:

var query = from p in db.TheOrder_merge              join g in db.TheOrder on p.Id equals g.Pid into lstOrder from g in lstOrder.DefaultIfEmpty()              join s in db.The_Order_ShippingFee on p.Id equals s.Orderid into lstShipFee from s in lstShipFee.DefaultIfEmpty()            .... 这才是左连接

单独使用

join g in db.TheOrder on p.Id equals g.Pid into lstOrder

也是会生成left join的，如theOrder表就是左链了；问题是同样的写法The_Order_ShippingFee表就走不通，而且整句sql后还添加了ORDER BY [t0].[Id], [t1].[Id]这让我十分疑惑。

[q107770540]

join g in db.TheOrder on p.Id equals g.Pid into lstOrder 这是GROUP JOIN，不是LEFT JOIN

[romanchaos]

引用 4 楼 q107770540 的回复:

join g in db.TheOrder on p.Id equals g.Pid into lstOrder 这是GROUP JOIN，不是LEFT JOIN

谢谢，那么为何The_Order_ShippingFee在sql中体现不出group join出来呢？其实最重要的还是最后那个ORDER BY [t0].[Id], [t1].[Id]，排这个序对分页的效率有影响。

[threenewbee]

sql没有对应group join的语法。 group join需要orderby，然后再分出组来

[romanchaos]

引用 6 楼 caozhy 的回复:

sql没有对应group join的语法。 group join需要orderby，然后再分出组来

请问缺了的The_Order_ShippingFee是否会以第二句sql来执行获取数据呢？因为在vs和linqpad我都只能找到上面那句sql