How to get Maximum ID value before creating a new record?

Question

I think it is a general problem in many cases, but I have not found a proper answer on the web. In my MVC application I have the following tables:

Issue Table:

---------------------------------------------
IssueID (pk) | CategoryID (fk) | IssueNumber
---------------------------------------------
   1         |     1           |      1
   2         |     1           |      2
   3         |     2           |      1
   4         |     1           |      3

Category Table:

-----------------------------------
CategoryID (pk) | Prefix | Name
-----------------------------------
     1          |   COM  | Computer
     2          |   GEN  | General

At here I use IssueID (primary key) field for both categories (COM and GEN). On the other hand, I want to generate the IssueNumber in turn (1, 2, 3 ...) without any gap for a CategoryID. On the other hand, I also want to create IssueNumber from 1 for another category as shown above. So the questions are:

1) Is there any problem regarding to the approach above? If so, what do you suggest for this?

2) How should I obtain the max value for IssueNumber before creating a record? I want to make this on the SaveChanges() method (I use Entity Framework). As I do not make IssueNumber field as PK, I cannot make the MsSQL Server auto increment this column value.

Any help would be appreciated. Thanks in advance.

Update:

Here is the solution via using lambda:

public int SaveIssue(Issue issue)
{
    int max = context.Issues.Where(m => m.CategoryID == issue.CategoryID ).Max(m => m.IssueNumber); 
    issue.IssueNum = max + 1; 

    context.Issues.Add(issue);
    context.SaveChanges(); 
    return issue.IssueNum ;
}

But I did not make IssueNumber field as PK and I want to create the max value + 1 for a category. So, CategoryID field should be keep in mind before creating a new id. Could you explain how to do this? — Jack, May 06 '15 at 12:46
@DavidW The question is not asking how to auto generate the key, it is asking how to auto generate `IssueNumber` which follows a `RowNumber() over (partition by CategoryID, order by IssueID)` pattern. — Scott Chamberlain, May 06 '15 at 12:46
@ScottChamberlain Absolutely right, Scott. I misread the question. Thanks. — David W, May 06 '15 at 12:47
Thanks David, Actually I want to solve this issue on program side. I just think of to get the max value and plus 1 before creating a new record. I know it does not seem to be good, but I do not know any better solution. Any idea? — Jack, May 06 '15 at 12:49
If your column is not nullable and result of you query can be empty, consider my answer [How go get max value of a unique id column using LINQ](http://stackoverflow.com/a/38396527) — Michael Freidgeim, Jul 15 '16 at 12:55
@MichaelFreidgeim Thanks for reply, voted+ but I am wondering if there is another better solution regarding to this issue? — Jack, Jul 30 '16 at 19:11
I didn't find anything better. See blog https://coding.abel.nu/2012/08/null-sematics-in-linqs-sum/ — Michael Freidgeim, Jul 30 '16 at 23:51
@Jack In case of parallel asynchronous request, i think it would generate same IssueNum for same category. as scott answered "Using the SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ensures that after you read the MAX(IssueNumber) no other query can modify that result until after you commit the transaction." , how can we achieve this using LINQ. — Sandeep Rasgotra, Sep 14 '20 at 13:34

Scott Chamberlain · Answer 1 · 2015-05-06T13:39:49.307

I am not sure how to do this in EF, but via SQL this can be done by the following (I am assuming IssueID is an identity column)

CREATE PROCEDURE usp_CreateNewIssue
    @catagoryId int,
    @issueId int OUTPUT,
    @issueNumber int OUTPUT
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRANSACTION
        declare @maxId int        
        declare @newRow table (IssueID int NOT NULL, IssueNumber int NOT NULL)

        select @maxId = MAX(IssueNumber) from Issue where CategoryID = @catagoryId

        Insert Into Issue (CategoryID, IssueNumber) values (@catagoryId, @maxId + 1)
           OUTPUT INSERTED.IssueID, INSERTED.IssueNumber 
           INTO @newRow

        select @issueId = IssueID, @issueNumber = IssueNumber from @newRow
    COMMIT TRANSACTION
END

^{(Code written in browser and is untested for syntax errors)}

Using the SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ensures that after you read the MAX(IssueNumber) no other query can modify that result until after you commit the transaction.

Note: To get good performance make sure CategoryID is indexed on Issue so the SQL engine can perform a Key-Range lock on it instead of a table lock.

I think the FluentAPI may allow a mechanism to leverage a procedure like this via the MapToStoredProcedures declaration off the DBModelBuilder object, which can be accessed in an override of the OnModelCreating event... — David W, May 06 '15 at 13:12
@Scott Chamberlain: Many thanks for your reply. I solved the problem with lambda expression for simplicity as shown on the updated code. Voted+ — Jack, Jul 23 '15 at 10:03

How to get Maximum ID value before creating a new record?

1 Answers1

Linked