Increment number by 1 insert into database using Linq

Question

If possible I would like to increment a number on the application side and save to the database using c# and Linq.

So for example I have a table like so:

|id|text|version|
-----------------
 1  hello   1
 2  hello   1

When inserting a new record into this table I want to get the current version number and increment by 1 then insert a new row

|id|text|version|
-----------------
 1  hello   1
 2  hello   1
 1  hello   2

I can do a standard insert using linq but obviously stuck with what to do with the version

DataContext db = new DataContext

using (db)
{
    table t = new table
      {
         text = TextBox1.Text
         version = ??
      };

db.table.InsertOnSubmit(t);
db.SubmitChanges();
}

Can I somehow select the row and get the current version number and then just increment by 1? Or should I just re-think my approach?

Thanks

When I've done this I've either used a stored procedure to do the insert or otherwise controlled the value of the version column in the database itself. — Tom Chantler
– Tom Chantler, Commented Aug 22, 2012 at 7:18
"should I just re-think my approach?" Yes, you should let the database increment. Here is a sql-server only link that shows how to set IDENTITY. — Tim Schmelter
– Tim Schmelter, Commented Aug 22, 2012 at 7:19
Usually, systems built with asp.net will be accessed by multiple users, in parallel. Is there something inherent in your design that means that there's no possibility that two users will attempt this same operation at the same time? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Aug 22, 2012 at 7:19
@TimSchmelter can't use the database increment because I insert a new row and it could be of any id. Damien, I was just going to use optimistic concurrency that is included in linq. I'll definitely re-think, thanks for the comments. — luke2012
– luke2012, Commented Aug 22, 2012 at 7:28
@TimSchmelter , According to the above given data Version is not a unique column so you can not add the auto increment property to that column. — Krishna Thota
– Krishna Thota, Commented Aug 22, 2012 at 7:43

RePierre · Accepted Answer · 2012-08-22 07:37:41Z

3

First of all, you'll need to wrap this logic inside a transaction; it will lock the table and you'll avoid concurency issues. After that, you can just select the maximum value of version and increment it by one.

using(var scope = new TransactionScope(TransactionScopeOption.Required,
                      new TransactionOptions{IsolationLevel = IsolationLevel.Serializable}))
{
    using(var dataContext = new DataContext())
    {
        var version = dataContext.Table.Where(item => item.Text == textBox1.Text)
                                       .Max(item => item.Version);
        var t = new Table
        {
            Text = textBox1.Text,
            Version = version + 1
        };
        dataContext.Table.InsertOnSubmit(t);
        dataContext.SaveChanges();
        scope.Complete();
    }
}

answered Aug 22, 2012 at 7:37

RePierre

9,5862 gold badges24 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

luke2012 Over a year ago

Thanks for your help, I especially like the use of the TransactionScope, although I am going to try something different.

Craig Tullis Over a year ago

Bearing in mind that using a transaction then doing significant work inside the transaction can impact scalability because it will force serialization...

Krishna Thota · Accepted Answer · 2012-08-22 07:39:37Z

0

Using SQL Stored Procedure

I can suggest you a method to do this in sql server. Use SQL Stored Procedure. If you use link there will be a disadvantage, the control will be running so many no of times.

Try this if you want to use Stored Procedure

DECLARE @sno INT
IF((SELECT MAX(version) FROM Tbl) IS NULL)
SET @sno=0;
ELSE SET @sno=(SELECT MAX(version) FROM Tbl)

INSERT INTO Tbl (Id,Text,version)
VALUES (@id, @text, @sno)

In the above code, @sno is a available and it is set to 0 in the start and if any previous version nos doesn't exist, then version is set as 0 . If any version exist, then the version is incremented by one and then inserted.

Using LINQ

By using LINQ,

var maxVer = dc.Logs.Max(s => s.Version);

This givesthe max version no. and using this you can insert into DB in your way.

edited Aug 22, 2012 at 7:39

answered Aug 22, 2012 at 7:32

Krishna Thota

7,11617 gold badges58 silver badges79 bronze badges

4 Comments

Damien_The_Unbeliever Over a year ago

This is still subject to race conditions, unless you're inside a transaction with high isolation. Two connections could both detect no rows in Tbl, then both attempt to insert the 0 row (and similarly, both detect the same current max and attempt to insert max+1)

Krishna Thota Over a year ago

I'm sorry I wrote in harder way. I didn't get your Idea

luke2012 Over a year ago

Thanks for your help, I'm going to try something different though.

Krishna Thota Over a year ago

Okay @luke2012 . After you succeeded doing it please post it here and we will be knowing and will learn if it is unknown to us.

luke2012 · Accepted Answer · 2012-08-29 10:43:17Z

0

I had the liberty to change the database structure so I just used IDENTITY col.

answered Aug 29, 2012 at 10:43

luke2012

1,7341 gold badge13 silver badges20 bronze badges

Collectives™ on Stack Overflow

Increment number by 1 insert into database using Linq

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related