title: Retrieving a sum of a left joined table in c# entity query
body: <p>I've got an EF query and in the query I get data from the parent table, and potentially data from 3 child tables, they are left joined.</p>
  
  <p>The parent has a 1 to many relationship with one of the child tables. I am trying to get the count of the number of children rows in the table</p>
  
  <pre><code>var exhibitorContentItems = await (from contentitems in _dc.ContentItems.AsNoTracking()
                              join itemcategory in _db.ContentItemCategories.AsNoTracking() on contentitems.ContentItemGuid equals itemcategory.ContentItemID into joinedCatItem
                              from itemcategory in joinedCatItem.DefaultIfEmpty()
                              join category in _db.ContentCategories.AsNoTracking() on itemcategory.ContentCategoryID equals category.ContentCategoryID into joinedCat
                              from category in joinedCat.DefaultIfEmpty()
                              join association in _db.ContentAssociations.AsNoTracking() on contentitems.ContentItemGuid equals association.ContentItemID into joinedAssoc
                              from association in joinedAssoc.DefaultIfEmpty() // want number of records in association
                              where contentitems.AccountID == session.AccountId 
                              &amp;&amp; contentitems.StatusID == (int)DbRowStatus.Enabled
                              select new DataTypes.ViewModels.Content.ContentGridItemViewModel()
                              {
                                   AccountId = contentitems.AccountID,
                                   ContentItemGuid = contentitems.ExhibitorContentItemGuid,
                                   DisplayName = contentitems.DisplayName,
                                   MobileAvailability = contentitems.MobileAvailability,
                                   FileType = contentitems.Filetype,
                                   CategoryName = category.CategoryName ?? "",
                                   ContentItemUrl = contentitems.ContentItemUrl,
                                   AssignedToNumberDevices = association.Count() // what to put here?
                              }).ToDataSourceResultAsync(request);
  </code></pre>