How do you insert multiple records at once in LINQ to SQL while calling a sequence retrieval function on every insert

I am trying to insert multiple records into a table where each record has a sequence number that relies on the sequence number of a previously inserted record.

For example:

var db = new StoreDataContext();
var inserts = payments.Select(p => new payment 
{
    order_fk = p.order_fk, line_no = DbMethods.GetNextLine(p.order_fk), 
    amount = p.amount
});

db.payments.InsertAllOnSubmit(inserts);
db.SubmitChanges(ConflictMode.FailOnFirstConflict);

DbMethods.GetNextLine is a SQL scalar function that goes and gets the MAX(line_no) + 1 for that particular order.

My problem is that the function gets performed before the actual inserts happen, so all开发者_高级运维 of the values for line_no are the same. Is there anyway of doing this without submitting each record seperately?

---

The only way I know to do something like this with Linq to SQL is to wrap it in a transaction.

var db = new StoreDataContext();
using(var scope = new TransactionScope()){        
    foreach(var p in payments){
        db.payments.Insert(new payment 
                          {    order_fk = p.order_fk, 
                               line_no = DbMethods.GetNextLine(p.order_fk),     
                                amount = p.amount
                          });
        db.SubmitChanges(ConflictMode.FailOnFirstConflict);
    }
    scope.Complete();
}

---

try this

var db = new StoreDataContext();
Int32 lineNo = DbMethods.GetNextLine(p.order_fk);
var inserts = payments.Select(p => new payment 
{
  order_fk = p.order_fk,
  amount = p.amount
});
foreach(var item on inserts)
{
  item.line_no = LineNo;
  LineNo++;
}
db.payments.InsertAllOnSubmit(inserts);
db.SubmitChanges(ConflictMode.FailOnFirstConflict);

---

The easiest (and probably most robust) way around this would be to leverage the database. For example, you could create a trigger that sets the line number whenever a payment is inserted. That way, the data stays consistent even if you use other means of entering payments into this table.

However, if you aren't allowed to modify the schema, the next-best way would probably be to group the payments you're about to insert by order_fk, and then increment them appropriately before attempting to do the insert. You should be able to use a Transaction scope to ensure atomicity, but it's going to slow things down quite a bit if you're doing this very often.