Is it beneficial to use multicolumn (composite) primary keys when using Linq to SQL?

Question

Is it beneficial to use multicolumn (composite) primary keys for a many to many relationship table when using Linq to SQL?

Or should I just add an identity column as a non-clustered primary key and index the FK columns appropriately?

Answer 1

If it makes sense in your domain to have a multi-column composite key, then use one. Otherwise use the usual identity column as the surrogate primary key.

EDIT: that was general advice and not taking into account any technical aspects of implementing using LINQtoSQL. These may be of interest:

How to: Handle Composite Keys in Queries (LINQ to SQL)

LINQ To SQL Samples

Linq to SQL DTOs and composite objects

Answer 2

Not a LINQ issue. If you need them for your schema, then use them. If you don't, don't. Either way, LINQ will handle your schema just fine.

One area that LINQ to SQL doesn't handle well are multy column / key mapping table that are used to connect a many to many relationship but I wouldn't say this strickly falls under the category that your question addresses. You can still perform CRUD operations on a mapping table within LINQ but LINQ cannot walk the relationship presented by a many to many mapping table. (LINQ works fine with one to one and one to many tables.)

I can't speak to any issue with the Entity Framework but again, I would be very surprised if the EF had any issues with multi-column / multi-key tables.