LINQ-to-SQL References to Entities in other DataContexts

Question

In my database designs, I tend to have "clusters" of tables. These clusters will typically support one application or group of tightly-functionally-related applications. Often, these clusters also relate to each other through a relatively small number of foreign keys; this helps otherwise independent applications in the business integrate with each other.

So, for example: imagine I have applications "Foo", "Bar", and "Baz", with several tables for each of them. Here's a list of the tables and their foreign key references:

• FooTableOne
• FooTableTwo -> FooTableOne
• BarTableOne -> FooTableTwo
• BarTableTwo -> BarTableone
• BazTableOne
• BazTableTwo -> FooTableTwo, BazTableOne

Currently, I'm using LINQ-to-SQL to access these tables. I have a separate project for each clusters (Foo, Bar, and Baz), and each of these projects have a .dbml for all of the tables in the cluster. The idea here is that each (one or more) application that uses the cluster can import a shared library containing the LINQ classes that it needs.

This may or may not be a good idea; it looks like the jury is still out. What I'm really wondering is whether or not I can have the references between the clusters expressed by the classes in another cluster, even though they exist in a different context class. (And, more specifically, how to create this relationship in Visual Studio).

Going back to the example, I'd like to have:

• Project Foo
  • Foo.dbml
  • FooDataContext
  • FooTableOne
    • FooTableOne.FooTableTwos
  • FooTableTwo
    • FooTableTwo.FooTableOne
    • FooTableTwo.BarTableOnes (this one isn't as important)
    • FooTableTwo.BazTableTwos (this one isn't as important)
• Project Bar
  • Bar.dbml
  • BarDataContext
  • BarTableOne
    • BarTableOne.FooTableTwo
    • BarTableOne.BarTableTwos
  • BarTableTwo
    • BarTableTwo.BarTableOne
• Project Baz
  • Baz.dbml
  • BazDataContext
  • BazTableOne
    • BazTableOne.BazTableTwos
  • BazTableTwo
    • BazTableTwo.FooTableTwo
    • BazTableTwo.BazTableOne

Out of the gate, all of the references to out-of-context entities are simple IDs (ints), not objects, and the collections to out-of-context entities don't exist at all. I know I can add my own methods to these classes to do the appropriate lookups (given an instance of the context), but I'd like to get things a little more streamlined & consistent.

Note that, with this separation of contexts/clusters, I get modularity between applications. So, for example, the Baz application would only have to import the Baz and Foo contexts (since Baz depends on Foo), but not Bar. (This assumes that I don't have the collections of Bar entities in Foo, which is fine by me). This is a nice thing, but it's not critical: if LINQ/VS doesn't make this easy then I'll consider scrapping the modularity and going with one-big-context.

Answer 1

L2S cannot model relationships across DBML files. IMO, this is a major shortcoming of L2S. I struggled with this in our application. We have all entities in separate DBML files. Each DBML file represents a namespace in our application, and a Schema in our SQL Server database.

So, what I ended up doing is for each entity that has a property that represents a foreign relationship in a different namespace, I put a custom Association attribute on the property in a partial class, that mimics the L2S Association attribute. This custom attribute allows us to manage the relationships ourselves. Not quite as efficient as having L2S do it, but it works pretty well for us.

Randy