Teresa Teresa - 1 year ago 75
C# Question

Query Linq Join Many to Many

I'm using Linq-SQL Entity for my MVC2 Application.

I have those tables / entities

Person ( ID , Name , Surname )
Car (ID , Model , Name)
Reseller ( ID , Name)

And i have those 2 Many to Many Tables

Persons_Cars ( ID , ID_Person , ID_CAR) Persons_Resellers ( ID,
ID_Person, ID_Reseller)

public MyModel {

//Some Fields

public List<CarPerson> carList;

public CarPerson
String detail;
int id;
var query=(from person in data.Person

join PerCar in data.Persons_Cars on person.ID equals Persons_Cars.ID_Person into JoinedPerCar
from PerCar in JoinedPerCar.DefaultIfEmpty()

join car in data.car on PerCar .id_car equals car.ID into JoinedCarPe
from car in JoinedCarPerson.DefaultIfEmpty()

join PerReseller in data.Persons_Resellers on person.ID equals PerReseller .id_person into JoinedPersReseller
from PerReseller in JoinedPersReseller.DefaultIfEmpty()

join Reseller in data.Reseller on PerReseller.id_reseller equals Reseller.ID into JoinedResellerPers
from Resller in JoinedFormazioneComp.DefaultIfEmpty()

where person.ID_USER == USER.ID
select new MyModel
carList = JoinedPerCar.Select(m=>new CarPerson {detail=m.car.Model,id = m.ID}).ToList()},

I know that it's totally wrong but i'm new into Linq-SQL
I have a problem now :

The Query works but my result is wrong. Infact if a person has 2 cars i recive the same person two times with 2 list of the cars.


Mr Brown has 2 Cars
i Recive
Mr Brown ---> List of car(Car1 , Car2)
Mr Brown ---> List of car(Car1, Car2)

So i need that if a person has 2 car i get only 1 result of person with his own cars

Is there a way to write this query better ?
How could resolve my problem?


Answer Source

I think simple subquery will do the job (same for resellers):

from p in data.Person
select new MyModel {
  carList = (from pc in data.Persons_Cars
             join c in data.car on pc.id_car equals c.ID
             where pc.ID_Person == p.ID 
             select c).ToList()

If you have navigation properties defined, then query will be even more simple (Linq will do join for you):

from p in data.Person
select new MyModel {
   carList = p.Person_Cars.Select(pc => pc.Car).ToList()