Linq到SQL不会产生任何结果。

Question

你好，我想知道为什么这个linq语句没有产生任何结果？

请看下面的代码

sql语句

select MAX(sop.EndDate), ADR.Address1, itm.Name, sum(sop.MadeCount) from SnapshotOrderPositionHistories sop
inner join Addresses adr on adr.id = sop.Address_Id
inner join Items itm on itm.Id = sop.Item_Id
where adr.TraderId = 11 and sop.EndDate >= '2017-04-01 00:00:00.0000000' and sop.EndDate <= '2017-04-30 00:00:00.0000000' GROUP BY adr.Address1, itm.Name

linq语句

    var getDeliveriesByAddress = from snapShots in db.SnapshotOrderPositionsHistories
                                  join addresses in db.Addresses on snapShots.Address_Id equals addresses.Id
                                  join itms in db.Items on snapShots.Item_Id equals itms.Id
                                  where addresses.TraderId == financialDocument.Trader.Id && (snapShots.EndDate >= financialDocument.StartDate & snapShots.EndDate <= financialDocument.EndDate)
                                  group new { addresses, itms } by new { addresses.Address1, itms.Name} into QueryList
                                 select new { List = QueryList };

我正在使用实体框架

Answer 1

你错过了一个&&的位置，投影是不正确的。你需要这个：

select new 
{ 
    MaxEndDate = QueryList.Max(q=>q.snapShots.EndDate),
    Address1 = QueryList.Key.Address1,
    Name = QueryList.Key.Name ,
    TotalMadeCount = QueryList.Sum(q=>q.snapShots.MadeCount) 
};

更新

我再次写了这个问题，以表明联盟和名称是多么重要。实际上，关于名称，我只将QueryList更改为gr，这在组的上下文中更有意义，而不是QueryList。

var getDeliveriesByAddress = from snapShots in db.SnapshotOrderPositionsHistories
                             join addresses in db.Addresses 
                             on snapShots.Address_Id equals addresses.Id
                             join itms in db.Items 
                             on snapShots.Item_Id equals itms.Id
                             where addresses.TraderId == financialDocument.Trader.Id 
                                && snapShots.EndDate >= financialDocument.StartDate 
                                && snapShots.EndDate <= financialDocument.EndDate
                             group new { addresses, itms } 
                             by new { addresses.Address1, itms.Name} into gr
                             select new 
                             {  
                                 MaxEndDate = gr.Max(q=>q.snapShots.EndDate),
                                 Address1 = gr.Key.Address1,
                                 Name = gr.Key.Name ,
                                 TotalMadeCount = gr.Sum(q=>q.snapShots.MadeCount) 
                             };

SQL查询也是如此。

SELECT 
    MAX(sop.EndDate)
    , ADR.Address1
    , itm.Name
    , SUM(sop.MadeCount) 
FROM SnapshotOrderPositionHistories sop
INNER JOIN Addresses adr 
   ON adr.id = sop.Address_Id
INNER JOIN Items itm ON itm.Id = sop.Item_Id
WHERE 
    adr.TraderId = 11 
AND sop.EndDate >= '2017-04-01 00:00:00.0000000' 
AND sop.EndDate <= '2017-04-30 00:00:00.0000000' 
GROUP BY adr.Address1, itm.Name