Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Linq Expression: Left Join Duplicating Rows

I'm trying to execute this Linq Expression, but the LEFT JOIN is duplicating the rows.
When I write this exactly query in SQL it works ok, but when I write it in Linq Expression the LEFT JOIN duplicates the row.
I've tried to group by.. but I've got the same result.

var sql = (from project in db.Project
                   join suitT in db.SuitT
                       on project.Id equals suitT.IdProject

                   join inspec in db.Inspec
                       on suitT.Id equals inspec.IdSuitT

                   join listFinalDef in db.ListFinalDef
                       on inspec.Id equals listFinalDef.IdInspec
                   into myListFinalDef
                   from groupListFinalDef in myListFinalDef.DefaultIfEmpty()

                   join artefact1 in db.Artefact
                       on groupListFinalDef.Id equals artefact1.IdListFinalDef
                   into myArtefact1
                   from groupArtefact1 in myArtefact1.DefaultIfEmpty()

                   join artefact2 in db.Artefact
                       on inspec.Id equals artefact2.IdInspec
                   into myArtefact2
                   from groupArtefact2 in myArtefact2.DefaultIfEmpty()

                   join typeArtefact in db.TypeArtefact
                       on inspec.IdTypeArtefact equals typeArtefact.Id
                   where ...
                   select new ArtefactModels
                   {
                       IdArtefact1 = groupArtefact1.Id,
                       IdArtefact2 = groupArtefact2.Id,
                       ...,
                       ...,
                       ...
                   }).ToList();

Whats happening: Duplicating the row
Example

Id: 1 | ProjectName: Project 1 | InspecTitle: Title 1
Id: 1 | ProjectName: Project 1 | InspecTitle: Title 1
Id: 2 | ProjectName: Project 2 | InspecTitle: Title 2
Id: 2 | ProjectName: Project 2 | InspecTitle: Title 2

What I'm trying to do:
Example

Id: 1 | ProjectName: Project 1 | InspecTitle: Title 1
Id: 2 | ProjectName: Project 2 | InspecTitle: Title 2

EDITED:
I'm trying to execute this SQL but with LINQ Expression:

SELECT art1.Id AS IdArtefact1, art2.Id AS IdArtefact2, ...
FROM
Project 
INNER JOIN SuitT ON Project.Id = SuitT.IdProject
INNER JOIN Inspec ON SuitT.Id = Inspec.IdSuitT
LEFT JOIN ListFinalDef ON Inspec.Id = ListFinalDef.IdInspec
LEFT JOIN Artefact AS art1 ON ListFinalDef.Id = art1.IdListFinalDef
LEFT JOIN Artefact AS art2 ON Inspec.Id = art2.IdInspec
INNER JOIN TypeArtefact ON Inspec.IdTypeArtefact = TypeArtefact.Id
WHERE ...
like image 721
Victor Avatar asked Aug 07 '26 22:08

Victor


1 Answers

The groupby goes like this:

var sql = (from project in db.Project
               join suitT in db.SuitT
                   on project.Id equals suitT.IdProject

               join inspec in db.Inspec
                   on suitT.Id equals inspec.IdSuitT

               join listFinalDef in db.ListFinalDef
                   on inspec.Id equals listFinalDef.IdInspec
               into myListFinalDef
               from groupListFinalDef in myListFinalDef.DefaultIfEmpty()

               join artefact1 in db.Artefact
                   on groupListFinalDef.Id equals artefact1.IdListFinalDef
               into myArtefact1
               from groupArtefact1 in myArtefact1.DefaultIfEmpty()

               join artefact2 in db.Artefato
                   on inspec.Id equals artefact2.IdInspec
               into myArtefact2
               from groupArtefact2 in myArtefact2.DefaultIfEmpty()

               join typeArtefact in db.TypeArtefact
                   on inspec.IdTypeArtefact equals typeArtefact.Id
               where ...
               select new ArtefactModels
               {
                   IdArtefact1 = groupArtefact1.Id,
                   IdArtefact2 = groupArtefact2.Id,
                   ...,
                   ...,
                   ...
               })
               //This will return IGroupable<key,value> with as key IdArtefact and as 
               //value an Enumerable of all the anonymous types with the same IdArtefact
               .GroupBy(a => a.IdArtefact1)
               //Select first value per IdArtefact if there's multiple
               .Select(a => a.FirstOrDefault())
               .ToList();
like image 113
Alexander Derck Avatar answered Aug 10 '26 14:08

Alexander Derck



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!