LinqToSql is trying to insert a foriegn key child when it already exists over WCF. What can I do?

Question

Hi guys,

I'm having trouble inserting a new LinqToSql object over WCF.

What I'm doing is just sending an Orders Batch to the service. Inside the batch are Orders that have already been sent previously. When I do

data.batches.InsertOnSubmit(newbatch)

I get a sql error:

"Violation of PRIMARY KEY constraint 'PK_HTOrder'. Cannot insert duplicate key in object 'dbo.HTOrder'. The statement has been terminated."

Here's the code where it inserts the batch (obj):

Dim tableproperty As PropertyInfo = dataProperties.Find(Function(p As PropertyInfo) (p.PropertyType.FullName.Contains("[" + obj.GetType.FullName + ",")))
Dim tableMethod As MethodInfo = tableproperty.GetGetMethod()
Dim tab As Object = tableMethod.Invoke(data, New Object() {})

tab.GetType.GetMethod("InsertOnSubmit", New Type() {obj.GetType}).Invoke(tab, New Object() {obj})
data.SubmitChanges(ConflictMode.FailOnFirstConflict)

Dim checkMatch As Object = GetMatchingEntities(obj, data).SingleOrDefault
If checkMatch Is Nothing Then Throw New UpdateNotVerfiedExecption(obj)
Return checkMatch

I've tried attaching the parent to the context first, but it naturally told me it existed when I tried to Insert it right after. Attaching the child orders doesn't seem to help either.

Any suggestions?

Thanks,

Del

Answer 1

Well, basically, for each order that you receive over the WCF service in your batch, you'd have to check first whether or not it exists already. If it does, you shouldn't call InsertOnSubmit on it - since it's in the database already. You can't just insert the whole batch if elements in it are already in the database.

Something like:

foreach(Order order in newbatch)
{
   if(data.Orders.Any(o => o.OrderID = order.ID))
   {
     // order already exists - update it
     data.Orders.Attach(order);
   }
   else
   { 
     // order does not exist already --> insert it
     data.Orders.InsertOnSubmit(order);
   }
}

When an order is new and doesn't exist yet -> insert it using InsertOnSubmit. If it already exists, re-attach it to the data context and thus provide Linq-To-SQL a change to do its change tracking and update the row.

After all this, of course, you need to call SubmitChanges() on the context to make all these changes and inserts stick!

Marc