开发者

Linq2SQL database design: mapping composite/surrogate keys

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. Fo开发者_Go百科r 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?


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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜