CODEWITHSUNDEEP
c#linq-to-sql
Heinrich
Heinrich

Reputation: 2214

LINQ Query Not Querying as Expected

I was wandering if someone can explain to me why this query doesn't act like the SQL Left join, I cant seem to work out why. I have been doing a bit of hunting and I can't seem to work it out. As far as I can tell it should.
I.e. In a table with 5 Active Customers it will only return 2 of those Customers instead of all 5; 2 with values and 3 with either null or 0?

var results = from c in DataContext.Customers
              where c.Active
              join j1 in
              (from i in DataContext.Invoice where i.State== "Pending" &&
               i.InvoiceDate.Date >= From.Date && i.InvoiceDate.Date <= To.Date 
               group i by i.Customer into x 
                      select new { x.Key, Total = x.Count() }) on a equals j1.Key
                      select new { c, j1.Total };

Thanks

Upvotes: 0

Views: 76

Answers (2)

kundan
kundan

Reputation: 73

By using DefaultIfEmpty() method you can do it. Try this code i have implementd your problem's solution revert me if it worked 

var results = from c in DataContext.Customers
                  where c.Active
                  join j1 in
                  (from i in DataContext.Invoice where i.State== "Pending" &&
                   i.InvoiceDate.Date >= From.Date && i.InvoiceDate.Date <= To.Date 
                   group i by i.Customer into x 
                          select new { x.Key, Total = x.Count() }) on a equals j1.Key into j3
                                from k in j3.DefaultIfEmpty()
                          select new { c, k.Total };

Upvotes: 1

Axarydax
Axarydax

Reputation: 16603

I would try DefaultIfEmpty() method on your j1 subquery, because you aren't allowing the query to join on null values.

See the similar question for examples.

Upvotes: 0

Related Questions