Sql – Linq to SQL Entity Splitting

asp.net-mvclinq-to-sql

I've recently started playing around with the ASP.NET MVC NerdDinner sample, and as part of that you get to play around with Linq to SQL classes. This looks cool, so I decided to break away (perhaps a tad prematurely!) and try and create my own MVC application, but this time modelling my own simple database with Linq to SQL.

The problem I'm having is this. Consider these tables:

  • Car
  • Person
  • Passengers

The table CarPassengers is a mapping table between Car and Person (it has two columns: CarId, PersonId).

In Linq to SQL, I get a class for every table. But, what I really want is a class for Car that has the property CarPassengers, with each child being a Person, rather than the actual FK's (it's ok for selecting results, but to update Car with new Person objects, I have to first construct them – I can't just add an EntitySet of CarPassengers without first instantiating the child Person objects, I think).

I've been looking around and I've seen that the ADO.NET Entity Framework will allow me to achieve this using Entity Splitting, but I wondered if there's an easy way to accomplish the same thing in Linq to SQL, and if not, what the affects are in terms of integrating the .NET EF with my MVC app.

Any advice, comments, tips would be much appreciated.

Thank you!

Best Answer

If you define the FKs prior to adding them to the designer surface you will get the EntitySets added to the class in a manner close to what you expect. CarPassengers will exist but it will be an EntitySet of CarPassenger objects, not Persons. Those objects, however, will have an associated Person EntityRef. This will allow you to find the passengers in the car.

 var car = db.Car.Where( c => c.CarId == id );
 var passengers = db.Car.CarPassengers.Select( cp => cp.Person );

Adding new passengers works as well, but also involves another step.

 var car = db.Car.Where( c => c.CarId == id );
 var person = new Person { ... };
 car.CarPassengers.Add( new CarPassenger { Person = person } );

If you don't have the FK relationships defined ahead of time, you can always add the associations manually in the designer. MSDN has a HowTo article on this topic.

Related Topic