Have two tables:
Catalog
|----------|----------|-----------|
| Id | Name | CreatedBy |
|----------|----------|-----------|
| 1 | Catalog1 | 1 |
| 2 | Catalog2 | 1 |
| 3 | Catalog3 | 1 |
| 4 | Catalog4 | 2 |
|----------|----------|-----------|
TemplateOnCatalog
|------------|-----------|
| TemplateId | CatalogId |
|------------|-----------|
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 6 | 4 |
|------------|-----------|
Using c# and Linq, how to get a list of all the catalogs, where CreatedBy = 1, and count the number of templates in the catalog. If no templates, the count value should be 0 (or empty).
Expected result:
|------------|-------------|-------------------|
| CatalogId | CatalogName | NumberOfTemplates |
|------------|-------------|-------------------|
| 1 | Catalog1 | 2 |
| 2 | Catalog2 | 3 |
| 3 | Catalog3 | 0 |
|------------|-------------|-------------------|
I try the following, but it doesn't take catalogs without templates:
var templateCatalogs =
from templateCatalog in db.Catalog.AsNoTracking()
join totc in db.TemplateOnCatalog
on templateCatalog.Id equals totc.CatalogId
where
templateCatalog.CreatedBy == 1
orderby templateCatalog.Name
group new { templateCatalog, totc }
by new { templateCatalog.Name, templateCatalog.Id } into result
select new
{
CatalogId = result.Key.Id,
CatalogName = result.Key.Name,
NumberOfTemplates = result.Count()
};