Rushabh Verma 0 Newbie Poster

I created a worked sql query:

select CONVERT(date, C.tDate) as dt, count(O.Id) as cnt from [Calendar] C
left outer join [Order] O
on C.tDate = CONVERT(date, O.Dt)
group by CONVERT(date, C.tDate)
order by CONVERT(date, C.tDate)

And I want to rewrite it use LINQ:

var res = (from c in db.Calendars
join o in db.Orders on c.tDate equals DbFunctions.TruncateTime(o.Dt.Value) into pp
from pl in pp.DefaultIfEmpty()
group c by DbFunctions.TruncateTime(pl.Dt.Value) into g
orderby g.FirstOrDefault().tDate
select new { date = g.FirstOrDefault().tDate, cnt = g.Count() }).ToList();

I get all rows from calendar in sql query, but only not null rows (right part) in LINQ query. How can I get all calendar's rows in LINQ.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.