Linq2SQL database design: mapping composite/surrogate keys

Question

Image that I have a huge database which stores threads and posts from different datasources like two different forums. For each datasource, the Id of the entity (e.g. ThreadId, PostId...) is unique but it may collide with the Id of an entity obtained by another datasources. For example, both forum1 and forum2 can have a thread with Threadid=1:

I am using Linq2Sql and a repository pattern to connect my application to the database. I read that composite keys (between ThreadId and DatasourceId) should be avoided when using Linq2Sql. Therefore I guess that a surrogate primary key is my only option (is it?):

Table Threads:

• UniqueId - int, PK
• DatasourceId - int
• ThreadId - int
• ...

Table Posts:

• UniqueId - int, PK
• DatasourceId - int
• PostId - int
• ThreadId - int, FK to Threads.ThreadId

Now, my question is: Will Linq2Sql be able to map 1:1 relations between posts and threads in its generated classes? What happens if a post has a foreign key to Thread.ThreadId and if there are two entities with the same ThreadId (but different DatasourceIds, of course)? I guess this will return a collection of assigned threads on the post - which I don't want! Can I somehow still return a single thread for each post which is the one which shares the same DatasourceId?

Answer 1

You are right that LinqToSql won't know that a certain Threadid is unique. You can do this by including the Datasourceid in your where clause in each query

var myThreads = DataContext.Threads.Where(t => t.Datasourceid == 1);