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 ...
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();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With