CODEWITHSUNDEEP
c#linqjoin
dstewart101
dstewart101

Reputation: 1112

counting in linq on join

I have two tables, site and customer. I want to count the number of customers registered with each site. I have join and I am returning counts with this:

var siteData = (
            from site in db.Site
            join cust in db.Customer on site.SiteID equals cust.SiteID
            group cust by site into g
            select new vm_SiteList
            {

                SiteName = g.Key.SiteName,
                Customers = g.Count()

            }).ToList();

My problem is those sites with 0 customers registered are not showing. Can someone tell me what I'm missing to include sites with 0 customers?

Upvotes: 0

Views: 62

Answers (2)

jignesh patel
jignesh patel

Reputation: 982

You can try this. Insted of using join you can get count using let keyword. Also it will be faster then using join.

var siteData = (
                from site in db.Site
                let customerCount = db.Customer.Where(p=>p.SiteID == site.SiteID).Count()
                select new vm_SiteList
                {
                    SiteName = site.SiteName,
                    Customers = customerCount 
                }).ToList();

Upvotes: 1

CodeNinja
CodeNinja

Reputation: 94

You need to use a Left Outer Join by using DefaultIfEmpty method to get all the customers for which there are no sites.

var siteData = (
            from site in db.Site
            join cust in db.Customer on site.SiteID equals cust.SiteID
            group cust by site into g
            from subCust in g.DefaultIfEmpty()
            select new vm_SiteList
            {

                SiteName = subCust?.Key.SiteName ?? string.Empty,
                Customers = g.Count()

            }).ToList();

Upvotes: 0

Related Questions