Query Linq Join Many to Many

Question

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
rson
                     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()},
                                //Other
                                });

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.

Example

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?

Thanks

Solution 1

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()
};

Solution 2

With the right properties on Person and Car and database relationships, this can be setup in the OnModelCreating method of your context so you don't need a Person_Cars model in your context:

modelBuilder.Entity<Car>()
                .HasMany(x => x.People)
                .WithMany(y => y.Cars)
                .Map(z =>
                {
                    z.MapLeftKey("CarID");
                    z.MapRightKey("PersonID");
                    z.ToTable("Person_Cars");
                });

Then you should be able to get your Person's Cars via:

from p in context.People
where p.PersonID = pID
select p.Cars

Or return the Person with their populated Car list:

context.People.Where(p => p.PersonID == pID).Include(c => c.Cars).FirstOrDefault();