A very interesting problem with SQL Server Relationships (SQL Server 2008R2, LINQ To SQL, Visual Studio 2010, C#)

Question

I'm working on a web project. I'm using a Sql Server 20008 R2 database via LINQ To SQL, and today I faced a very strange problem with relationships. I've been using them for a while and never had any problems so far.

Let's say I have a table Stores that has the following fields: ID, Name, LastUserID where LastUserID is a "reference" to the Users table.

The Users table has the following fields: ID, Name, FavoriteStoreID where FavoriteStoreID is a "reference" to the Stores table.

So I kind of have recursive relationship.

Store.LastUser.Name // shows the name of the last user of the store

User.FavoriteStore.Name // shows the name of user's favorite store.

In Visual Studio's designer this looks like this:

----------
-        -
- Users  -
-        -
----------


 |   / \
 |    |
 |    |
\ /   |


----------
-        -
- Stores -
-        -
----------

Now the problem is, when there are two arrows (two relationships), only one of them works. When I use the other, I get the Object reference not set to an instance of an object. error.

When I re-create the table, add that relationship (the one that raised an exception) first, it works. But when I add another one, it doesn't work.

So obviously I messed up something, and SQL Server doesn't understand how to interpret what I want it to do.

How can I fix this problem?

Answer 1

It sounds like you're setting up two entity associations when you only need one, and that may be confusing LINQ to SQL. In your dbml designer try setting up only one association between your Users and Stores tables, and in the the association properties, set the cardinality to OneToOne and that should create a Child Property and Parent Property on your User and Store entities. Even with only one association between the two tables, you should still be able to get the Store from the User object and the User from the Store object.

Answer 2

The problem is that the designer can't figure out the circular reference. I would handle this by creating the missing reference inside the designer.

Answer 3

I'm not sure what was the problem, but I re-created the table, and now it works fine. So LINQ To SQL DOES work with circular references.