CODEWITHSUNDEEP
c#sql-serverformslinq
Cignitor
Cignitor

Reputation: 1099

how to generate next ID from database

I am using a service-based database (*.mdf) as my database service. I'm using Linq-To-SQL classes.

I want to generate the next ID. Currently I'm trying to get the ID of the last inserted row, then add 1 to get the next ID. My primary key's data type is int.

private string genNextId()
{
    var id = (from a in dc.nasabahs
              orderby a.nomor_nasabah ascending
              select a.nomor_nasabah).Last();

    return id.ToString();
}

I get the error:

The query operator 'Last' is not supported.

Upvotes: 3

Views: 3648

Answers (4)

Prabhdeep Gill
Prabhdeep Gill

Reputation: 41

Just thought about adding my two cents. Everyone above didn't really tell how to configure identity property for service-based database or mdx file. After you create table or during first time creation, go to table definition and then in column properties you will see something like Identity Specification: Is Identity, mark as Yes.Increment set as 1 ( depending upon your desired step size) and Seed set as 1 (Initial value)

Upvotes: 0

softveda
softveda

Reputation: 11066

If you really want to do this you should use a sequence which is available only in SQL Server 2012 although other databases like Oracle has it for ages. Sequence is a database object that generates numeric value and once a value is generated and consumed it will not be re-generated. The advantage is that you can get a value without doing any insert. However I don't think linq-to-sql supports this so you will have to use raw ado.net.

Upvotes: 0

ethorn10
ethorn10

Reputation: 1899

What about a slightly different approach? 

private string genNextId()
{
    var id = (from a in dc.nasabahs
              select a.nomor_nasabah).Max();

    return id.ToString();
}

Upvotes: 3

Dan Drews
Dan Drews

Reputation: 1976

Change your funciton to this:

private string genNextId()
    {
        var id = (from a in dc.nasabahs
                  orderby a.nomor_nasabah descending
                  select a.nomor_nasabah).First();

        return id.ToString();
    }

Upvotes: 2

Related Questions