2

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?

Shackles
  • 1,264
  • 1
  • 19
  • 40
  • The more I think about it the more I'd prefer a solution using a composite key combining ThreadId and DatasourceId. How does that (not) work with Linq? Can I just set the two fields in code and save them to the DB or would I need a shared DataContext between threads and posts? – Shackles Apr 08 '11 at 13:29

1 Answers1

0

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);
Geoff
  • 9,340
  • 7
  • 38
  • 48
  • Should I then just ignore the properties auto-generated by Linq? Is there any better solution? The database schema is just a first draft... – Shackles Apr 08 '11 at 12:31
  • which properties do you mean? Have you considered Entity Framework instead of L2S? – Geoff Apr 08 '11 at 12:59
  • My Linq2Sql architecture is already in use and can therefore not be exchanged. Linq generates a class for each entity, e.g. Post. This class has a property Post.Thread which is a reference to the assigned Thread object. I am btw not sure that Linq can even detect that the foreign key to threads is no longer a 1:1 relationship if I take your advice. – Shackles Apr 08 '11 at 13:11
  • Ah, I see what you mean now. Your foreign keys should be the Uniqueid. That way the references will be unique. I wrote a discussion forum just like this and only had the datasourceid on the Thread table. – Geoff Apr 08 '11 at 13:19
  • The problem with a FK to the UniqueId is that I am doing massive bulk imports in a random order of entities (threads are not always added before posts). I therefore can't get the UniqueId of a thread generated by the DB when I am importing a post. I do have the ThreadId though. – Shackles Apr 08 '11 at 13:24