Next Identity Key LINQ + SQL Server

To represent our course tree structure in our Linq Dataclasses we have 2 columns that could potentially be the same as the PK.

My problem is that if I want to Insert a new record and populate 2 other columns with the PK that was generated there is no way I can get the next identity开发者_JAVA技巧 and stop conflict with other administrators who might be doing the same insert at the same time.

Case: A Leaf node has right_id and left_id = itself (prereq_id)

**dbo.pre_req:**
prereq_id
left_id
right_id
op_id
course_id
is_head
is_coreq
is_enforced
parent_course_id

and I basically want to do this:

        pre_req rec = new pre_req
        {
            left_id = prereq_id,
            right_id = prereq_id,
            op_id = 3,
            course_id = query.course_id,
            is_head = true,
            is_coreq = false,
            parent_course_id = curCourse.course_id
        };

        db.courses.InsertOnSubmit(rec);

        try
        {
            db.SubmitChanges();
        }

Any way to solve my dilemma? Thanks!

You don't generate the Identity yourself, or directly set the ID values for FKs.

So, simplifying things down (because your structure isn't terribly clear)

If your Person table looks like:

   Id  int IDENTITY(1,1)
   Name nvarchar(MAX)
   MotherId int NULL  (FK -> Person.Id)
   FatherId int NULL  (FK -> Person.Id)

Then your person Class in LINQ should look like:

public class Person 
{
   public int Id { get; set;}
   public string Name { get; set;} 
   public Person Mother { get; set;}
   public int? MotherId { get; set;}
   public Person Father { get; set;}
   public int? FatherId { get; set;}
}

When inserting, you can do this:

var mother = new Person { Name = "April Smith" }; 
var father = new Person { Name = "Bob Smith" }; 
var child = new Person { Name = "Charlie Smith", Mother = mother, Father = father}; 

db.People.InsertOnSubmit(mother);
db.People.InsertOnSubmit(father);
db.People.InsertOnSubmit(child); 

LINQ-to-SQL should then know to grab the generated Ids for mother and father, and insert them into the child record.

If you're trying to self-referential or circular link, then there's no way to do this in a single transaction that I know of.

You would have to insert your records first (and leave their references null initially) and then come back and update the references later.

If you are using LINQ-to-SQL then there should be a corresponding pre_req.left and pre_req.right property that corresponds to the pre_req entity object to set. If that's the case, then just do this:

    pre_req rec = new pre_req
    {
        op_id = 3,
        course_id = query.course_id,
        is_head = true,
        is_coreq = false,
        parent_course_id = curCourse.course_id
    };

    rec.left = rec;
    rec.right = rec;

    db.courses.InsertOnSubmit(rec);

    // ...

This will instruct the LINQ-to-SQL layer to determine the new ID for you.