0

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()
    };
Batar
  • 19
  • 1
  • 5

0 Answers0