I can't seem to get a self-referencing save to work. Is there any trick to it?

The line of code that I thought would work is:

partner.PartnerPartnerId = partner;

I also thought I would try saving the relation the other way:

partner.PartnerParentPartnerId.Add(partner);

Interestingly, BOTH of these work flawlessly if I save twice:

//this works:
_adapter.SaveEntity(partner);
_adapter.SaveEntity(partner);

//this doesn't:
_adapter.SaveEntity(partner);

Any insight would be greatly appreciated.

You're assigning an entity to itself, may I ask why?

As a quick aside, we were unable to accomplish the original task in query analyzer with a single INSERT statement.

Otis wrote:

You're assigning an entity to itself, may I ask why?

It's a self-referencing table that allows for parent-child trees. If the parent's id is the same as the child's id, that indicates the top of the tree (the row has no parent).

We considered doing it a number of alternative ways:

1. We could leave the column null if it has no parent.
2. We could set the column to a dummy row in the table (like -100 or 0).

We prefer the self-relating method better because:

Alternative #1 relies on NULLs Alternative #2 relies on hard-coded business logic ("if xxx = -100")

However, we are not hard-wired into this solution yet, so suggestions are welcome.

Isn't the row refrencing itself just another for of "Alternative #2 relies on hard-coded business logic ("if xxx = -100")" i.e. Alternative #2 relies on hard-coded business logic ("if ParentID = ID")

if someone doesn't honor that rule, wouln't you get in a nasty loop iterating up the tree?

psandler wrote:

As a quick aside, we were unable to accomplish the original task in query analyzer with a single INSERT statement.

That's logical, as you need the value of the PK at insert time, but you only know that after insert time . Classical example of a loop in the model

Otis wrote:

You're assigning an entity to itself, may I ask why?

It's a self-referencing table that allows for parent-child trees. If the parent's id is the same as the child's id, that indicates the top of the tree (the row has no parent).

We considered doing it a number of alternative ways:

1. We could leave the column null if it has no parent.
2. We could set the column to a dummy row in the table (like -100 or 0).

We prefer the self-relating method better because:

Alternative #1 relies on NULLs Alternative #2 relies on hard-coded business logic ("if xxx = -100")

Your situation also relies on NULLs. You can't save a row which has an FK to itself and requires the same value for the FK field as the PK field if you don't use a nullable field for the fk IF you have an autonumber PK.

However, we are not hard-wired into this solution yet, so suggestions are welcome.

Well, pointing to self with a parent/child hierarchy is an infinite loop, and therefore not supported. (the recursive save sees that the entity to save is already in progress, so quits, and therefore the FK side doesn't get saved)

It's also semantically wrong. (sorry ). -> the 'root' does have a parent, namely itself. And that parent does have a parent as well (itself) till oblivion.

The root is the one with NO parent, i.e.: parent is undefined. So I'd suggest you go for option 1): the root's Parent is NULL.

arschr wrote:

Isn't the row refrencing itself just another for of "Alternative #2 relies on hard-coded business logic ("if xxx = -100")" i.e. Alternative #2 relies on hard-coded business logic ("if ParentID = ID")

if someone doesn't honor that rule, wouln't you get in a nasty loop iterating up the tree?

Exactly, good analysis!

With a parentID of NULL, you can't make that mistake, because there isn't a parent defined, so the routine stops.

arschr wrote:

Isn't the row refrencing itself just another for of "Alternative #2 relies on hard-coded business logic ("if xxx = -100")" i.e. Alternative #2 relies on hard-coded business logic ("if ParentID = ID")

I was trying to draw a distinction between "business logic" and "hard-coded business logic". "If x = -100" relies on a contant in the code, where "if ParentID = ID" or "if ParentID = NULL" does not.

Otis wrote:

Well, pointing to self with a parent/child hierarchy is an infinite loop, and therefore not supported. (the recursive save sees that the entity to save is already in progress, so quits, and therefore the FK side doesn't get saved)

Yep, definitely see that now. At one point I thought I had this working with LLBL, but as it turned out I was updating and not inserting a new record.

I do what I can to avoid nulls, and at the same time avoid assigning a row in a table a specific constant meaning (-100 = "no parent"). But in some cases (like this one) I guess you simply have to choose one or the other.

Otis wrote:

It's also semantically wrong. (sorry ). -> the 'root' does have a parent, namely itself. And that parent does have a parent as well (itself) till oblivion.

The root is the one with NO parent, i.e.: parent is undefined. So I'd suggest you go for option 1): the root's Parent is NULL.

I think this is the route we're going to take. Thanks for your input.