LINQ to SQL: Self-referencing entity - ParentID/ChildID association

Question

I have a Task entity in my Linq to SQL dbml. It's self referencing with ID and ParentID columns. I have an Association that associates the two IDs together.

it seems like everything works fine in the intellisense. It'll let me type out Task.Parent.ID and even Task.Parent.Parent.ID, etc. However, it gives me the ol' "Object reference not set to an instance of an object." error.

All my other associations work fine with my other entities. Only the self referencing entity errors.

Is there something special I need to do to get it to work or am I better off just adding a second Task entity and call it ParentTask and make the association that way?

Answer 1

I have not had any problem with single-table relationships being modeled correctly in LINQ to SQL. I ran a quick test case to reproduce what you are describing and it worked fine (Item.Parent.ID, Item.Parent.Parent.Parent.Parent.ID, etc.)

Set up SQL profiler and see what SQL the LINQ provider is trying to generate. Also take a peek at the entity class in the designer.cs file and see if the getter for the Parent property on Task looks strange.

Answer 2

The error will happen if you are attempting to get the Parent for an entity when it in fact does not have a Parent. Everything you have specified is perfectly fine and I have implemented similar relationships many times.

You mentioned an exception but you did not mention when it occurs. During an update? Perhaps there is a FK violation? During an INsert? Or just a query?

Task.Parent.Parent.Parent.Parent is not something you can just do infinitely. Sure it will compile, the code doesn't know that there is data backing up that statement. But at runtime, each .Parent call needs to be matched by an equivalent ParentID column.