CODEWITHSUNDEEP
c#entity-frameworklinq
Kevin Rodriguez
Kevin Rodriguez

Reputation: 301

Linq to SQL not yielding any results

Hello i wonder why this linq to sql statement is not yielding any results?

please see my code below

sql statement

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 statement

    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 };

I am using entity framework

Upvotes: 0

Views: 75

Answers (1)

Christos
Christos

Reputation: 53958

You have missed a && in the where stament and the projection is not correct. You need this:

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

UPDATE

I wrote again the query, in order to show how important is the allignment and the namings. Actually, regarding the namings I changed only the QueryList to gr, which is more meaningfull in the context of a group by rather than 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) 
                             };

The same holds also for the SQL query. 

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

Upvotes: 1

Related Questions