How can i group by in LINQ statment and inculude NULL values

Question

I want to group by "fournisseur.Id" and also get values that have no types or categories

var result =
    from fournisseur in _context.Fournisseurs
    join type in _context.FournisseurTypeFournisseurs on fournisseur.Id equals type.FournisseurId
    join categorie in _context.FournisseurCategories on fournisseur.Id equals categorie.FournisseurId
    orderby fournisseur.Nom
    select new
    {
        fournisseur.Id,
        fournisseur.Siren,
        Contacts = fournisseur.Agences.Select(a => a.Contacts),
        categorie = fournisseur.FournisseurCategories.Select(a => a.CategorieId),
        type = fournisseur.FournisseurTypeFournisseurs.Select(a => a.TypeFournisseurId),
        fournisseur.Agences,
        fournisseur.Nom
    };

    return Ok(result);

i have tried result = result.DistinctBy(i => i.Id); but it does not seem work i get an error

You need to convert your joins to left joins. See [this](https://stackoverflow.com/questions/3404975/left-outer-join-in-linq) — Eldar, Oct 07 '22 at 10:37
Your `result` is an `IQueryable` - you need to materialize it first by using `await ToListAsync`. Also, you cannot use anonymous-types (`new { ... }`) as Models in ASP.NET views because you cannot specify them in `@model`. — Dai, Oct 07 '22 at 10:53

score 0 · Answer 1 · edited Oct 12 '22 at 07:40

0

use left join and take distinct.

var result =
    (from fournisseur in _context.Fournisseurs
    join type in _context.FournisseurTypeFournisseurs on fournisseur.Id equals type.FournisseurId into ft from ftResult in ft.DefaultIfEmpty()

    join categorie in _context.FournisseurCategories on ftResult.Id equals categorie.FournisseurId into cft from cftResult in cft.DefaultIfEmpty()
orderby fournisseur.Nom
 
select  fournisseur
).Distinct()

I've not tested the above code but had worked on similar functionality. I've just added the left join part so that it will return all details of Fournisseurs + other table fields

edited Oct 12 '22 at 07:40

D J

845
1
13
27

answered Oct 07 '22 at 12:41

long_Singh

11
1

var result = (from fournisseur in _context.Fournisseurs join type in _context.FournisseurTypeFournisseurs on fournisseur.Id equals type.FournisseurId into ft from ftResult in ft.DefaultIfEmpty() join categorie in _context.FournisseurCategories on ftResult.Id equals categorie.FournisseurId into cft from cftResult in cft.DefaultIfEmpty() orderby fournisseur.Nom select fournisseur ).Distinct() – long_Singh Oct 07 '22 at 12:43

How can i group by in LINQ statment and inculude NULL values

1 Answers1