Insert Record (LINQ)

[JCDenton]

Hello All

 

I'm attempting to parse a datasource into individual entities and inserting those into a database. However, some of those entities might already exist so I need to check my primary keys.

 

I failed at my first attempt - trying to simply catch the exception thrown when the PK constraint is violated.

 

try { dc.SubmitChanges(); }
catch { }

 

However, on any consecutive calls of dc.SubmitChanges the exception is still thrown. Obviously not the solution. (Is there a way to discard those changes that cause an exception?)

 

So my next option is checking whether the entity already exists. Preferably in a single operation. Does Linq provide this? Currently I'm entertaining the idea of creating a static, synchronised method which checks the existence before inserting but will this work across page requests? i.e. when the same page is accessed by two or more requests concurrently do they access the same, static method or do the requests live in seperate 'spaces' ?

 

i.e. would this work and ensure an atomic operation?

 

[MethodImpl(MethodImplOptions.Synchronized)]
   public static void InsertQuote(Quote q)
   {
       if (q != null)
       {
           if (dc.Quotes.Select(x => x.id == q.id).Count() == 0)
           {
               dc.Quotes.InsertOnSubmit(q);
               dc.SubmitChanges();
           }
       }
   }

 

thanks all

[PlausiblyDamp]

The datacontext (dc in your snippets) should have a .ChangeConflicts method - this will return all conflicting records.

 

Each record will also have a resolve method you could potentially use to handle the resolution.

 

If you use a synchronised method you would still get the problem if you ever went to multiple servers, or even during application pool recycling when multiple app domains/ processes are in existence.

 

Would it not be possible to get the database to generate the key value when the insert takes place?

[JCDenton]

The datacontext (dc in your snippets) should have a .ChangeConflicts method - this will return all conflicting records.

 

Each record will also have a resolve method you could potentially use to handle the resolution.

 

If you use a synchronised method you would still get the problem if you ever went to multiple servers, or even during application pool recycling when multiple app domains/ processes are in existence.

 

Would it not be possible to get the database to generate the key value when the insert takes place?

 

Thanks for your help. The problem is when importing the data it is practically guaranteed there is some duplicate data in there. Luckily, the data comes with unique identifier provided so all that's left for me to do is check the existence before inserting and I'm having trouble coming up with a way other then checking synchronously or, as you suggested, attempt the insert and check the conflicting objects.

 

I don't have any experience with the application pool but this article proved somewhat helpful: http://www.developer.com/net/asp/article.php/10917_2245511_2 .

 

Also, it appeared there is a bug in the code I posted. To check the existence of a quote object I used:

 

if (dc.Quotes.Select(x => x.id == q.id).Count() == 0)

which should have been, of course:

 

dc.Quotes.Where(x => x.id == q.id).Count() == 0)

 

Reading documentation helps :)