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

Sergey Berezovskiy · Accepted Answer · 2013-04-03 11:28:40Z

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

jaybro · Accepted Answer · 2016-08-09 17:57:17Z

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

Collectives™ on Stack Overflow

Query Linq Join Many to Many

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related