Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to insert only new records using Linq-to-SQL?

I have to insert some data periodically in my SQL Server database. But the feeds where I read the data repeats some data that was inserted before. When I use Linq-to-SQL to insert into the DB either some data is duplicated, or a primary key violation exception is raised, depending on the primary key.

How to insert the data without duplications and without exceptions? I don't want to avoid the exception with a try-catch, because once the exception is raised the rest of the data isn't inserted.

update I also found my own solution: I wrote a duplicated entries deletion stored procedure, which is run right after the InsertAllOnSubmit + SubmitChanges

like image 870
Jader Dias Avatar asked Apr 07 '09 13:04

Jader Dias


People also ask

What is the SQL code to insert a new record?

If you want to add data to your SQL table, then you can use the INSERT statement. Here is the basic syntax for adding rows to your SQL table: INSERT INTO table_name (column1, column2, column3,etc) VALUES (value1, value2, value3, etc); The second line of code is where you will add the values for the rows.

How can you insert a new record in table?

To insert records into a table, enter the key words insert into followed by the table name, followed by an open parenthesis, followed by a list of column names separated by commas, followed by a closing parenthesis, followed by the keyword values, followed by the list of values enclosed in parenthesis.

How do you update a record in LINQ?

You can update rows in a database by modifying member values of the objects associated with the LINQ to SQL Table<TEntity> collection and then submitting the changes to the database. LINQ to SQL translates your changes into the appropriate SQL UPDATE commands.

How do I insert a record using LINQ to SQL?

When using LINQ to SQL, you insert new records by calling the InsertOnSubmit () method. After calling the InsertOnSubmit () method, you must call SubmitChanges () to make the insertion happen.

How to use LINQ to modify database data in SQL?

We recommend  In this final section, we’ll examine how you can use LINQ to SQL to modify database data. In this section, you’ll build a page that you can use to insert new records into the Products database table. When using LINQ to SQL, you insert new records by calling the InsertOnSubmit () method.

What is the syntax of using LINQ to insert operation query?

Following are the syntax of insert or update or delete operations using in LINQ to SQL. Following is the syntax of using LINQ to Insert operation query in c#, vb.net to insert data in database. emp.EmpName = txtname.Text emp.Location = txtlocation.Text

How do I add a row to a LINQ query?

Use the following procedure to insert a row into the database: Create a new object that includes the column data to be submitted. Add the new object to the LINQ to SQL Table collection associated with the target table in the database. Submit the change to the database.


2 Answers

All you have to do is create a new instance of your class and then call InsertOnSumbit() on the table:

var foo = new MyFoo { Name = "foo1" };
var dc = new MyDataContext();
dc.Foos.InsertOnSubmit(foo);
dc.SubmitChanges();

The other thing you need to be sure of is how you're incrementing your ID column. In general, I always make sure to use the IDENTITY(1,1) setting on my ID columns. This is declared on your LINQ entity's id column like so:

[Column(AutoSync = AutoSync.OnInsert, IsPrimaryKey = true, IsDbGenerated = true)]
public Int32 Id { get; set; }

To avoid duplicates, what you really need is what we call in my shop an "append" functionality. IMHO, this is most easily accomplished with a stored procedure - we even have a template we use for it:

USE [<Database_Name, sysobject, Database_Name>]
GO

CREATE PROCEDURE [<Schema, sysobject, dbo>].[<Table_Name, sysobject, Table_Name>__append]
(
    @id INT OUTPUT,
    @<Key_Param, sysobject, Key_Param> <Key_Param_Type, sysobject, VARCHAR(50)>
)
AS
BEGIN

        SELECT @id = [id] FROM [<Schema, sysobject, dbo>].[<Table_Name, sysobject, Table_Name>s] (NOLOCK) WHERE [<Key_Param, sysobject, Key_Param>] = @<Key_Param, sysobject, Key_Param>

IF @id IS NULL  
BEGIN       
    INSERT INTO [<Schema, sysobject, dbo>].[<Table_Name, sysobject, Table_Name>s] ([<Key_Param, sysobject, Key_Param>]) 
    OUTPUT INSERTED.[id] INTO @inserted_ids
    VALUES (@<Key_Param, sysobject, Key_Param>)

    SELECT TOP 1 @id = [id] FROM @inserted_ids;
END
ELSE
BEGIN
    UPDATE [<Schema, sysobject, dbo>].[<Table_Name, sysobject, Table_Name>s]
    SET
        [<Key_Param, sysobject, Key_Param>] = @<Key_Param, sysobject, Key_Param>
    WHERE [id] = @id
END
END
GO

It is possible to do it in linq though, just query for a list of existing IDs (or whatever column you're keying off of):

var dc = new MyDataContext();
var existingFoos = dc.Foos.ToList();
var newFoos = new List<Foo>();
foreach(var bar in whateverYoureIterating) {
// logic to add to newFoos 
}
var foosToInsert = newFoos.Where(newFoo => !existingFoos.Any(existingFoo => newFoo.Id == existingFoo.Id));

dc.Foos.InsertAllOnSubmit(foosToInsert);
dc.SubmitChanges();
// use the next line if you plan on re-using existingFoos. If that's the case I'd wrap  dc.SubmitChanges() in a try-catch as well.
existingFoos.AddRange(foosToInsert);
like image 197
Daniel Schaffer Avatar answered Sep 21 '22 01:09

Daniel Schaffer


Unfortunately, there's no way around it as Linq to SQL does not check the database before it performs the insert. The only way to do this is to query the database first to determine if the duplicate record exists and then add the record if it does not.

Ideally Linq to SQL would support the Ignore Duplicate Keys property on a SQL column. But unfortunately it does not at the moment.

like image 35
Keltex Avatar answered Sep 20 '22 01:09

Keltex