how to get max id from a table using linq

Question

I have a table Estimation which has an column EstimationNo,i am trying to get the max EstimationNo like this-

var result = cont.SalesEstimateCont.Where(x => x.Org_ID == CurrentOrgId);
var estimationMaxNo = result.Any() ? result.Max(x => x.EstimateNo) + 1 : 1;

  var DigitalEstimate = new SalesEstimate()
        {
            EstimateNo=estimationMaxNo;
        };
    cont.Estimate.Add(DigitalEstimate );
    cont.Savechanges();

but the problem is, if same table is saving by different users at same time its saving the same EstimationNo for both users. like- 10,10

Now, how to handle this issue..please give some solution.

I think you need to handle concurrency and *implement Last Writer Wins* technique https://stackoverflow.com/a/32558609/2946329 — Salah Akbari, Feb 02 '18 at 06:41
use autogeneration of IDs with IDENTITY property for PK column or sequences. — aleha_84, Feb 02 '18 at 06:46
If you really want uniqueness and the least chance of collision and concurrency problems, you might be better off with GUID field and define a UNIQUE constraint. However this really all depends on what you are trying to do and why. — TheGeneral, Feb 02 '18 at 06:57
@Risto M .its a big model thats why i mentioned only the EstimationNo — sumit.spider, Feb 02 '18 at 07:30
@sumit.spider Ok, I propose you (with my answer) to let db engine handle incrementing with identity specification. I'll asssume that EstimateNo is type int field. — Risto M, Feb 02 '18 at 07:51
@S.Akbari which concurrency i need to implement,optimistic or pessimistic? — sumit.spider, Feb 05 '18 at 14:40

Risto M · Answer 1 · 2018-02-02T08:03:26.957

Best strategy is to let db engine (I assume that it is SQL Server) handle incrementing of EstimateNo field. This can be done with identity specification which can be added to normal not primary key field also.

ALTER TABLE SalesEstimateCont drop column EstimateNo 
go
ALTER TABLE SalesEstimateContadd Add EstimateNo int NOT NULL IDENTITY (1,1)

Please note: if you have existing data or some data should be modified, you may need some extra effort to achieve this (i.e with temp tables and by setting IDENTITY INSERT ON)

score 1 · Answer 2 · answered Feb 08 '18 at 09:16

I got a simple answer.I just had to use transacationScope class. and lock the resource table. like this-

 using (TransactionScope scope = new TransactionScope())
      {
cont.Database.ExecuteSqlCommand("SELECT TOP 1 * FROM Sales__Estimate WITH (TABLOCKX, HOLDLOCK)");
var result = cont.SalesEstimateCont.Where(x => x.Org_ID == CurrentOrgId);
var estimationMaxNo = result.Any() ? result.Max(x => x.EstimateNo) + 1 : 1;

  var DigitalEstimate = new SalesEstimate()
        {
            EstimateNo=estimationMaxNo;
        };
    cont.Estimate.Add(DigitalEstimate );
    cont.Savechanges();
}

score 1 · Answer 3 · answered Feb 08 '18 at 20:45

If you can make EstimateNo an Identity column, that is the easiest/best way to fix this. If you can change this to a Guid, that would be another easy way to fix this as PK would be unique regardless of the user.

If you can't do either of these and you must take Max() manually, you might want to consider creating another table that stores the next available number there. Then you can create a new SqlCommnand with a Serializable transaction to lock the table, update the # by 1 and select it back. If two update commands hit at the same time, only one update will run and won't let go until that connection with Serializable transaction gets closed. This allows you to select the newly updated number before the other update runs and get the now "unique" next number.

score -1 · Answer 4 · answered Feb 02 '18 at 07:01

-1

You can OrderByDescending and then Take the the first record

var estimationMaxNo = result.OrderByDescending(x => x.EstimateNo).Take(1);

answered Feb 02 '18 at 07:01

Andre Lombaard

6,985
13
55
96

score -3 · Answer 5 · answered Feb 02 '18 at 06:54

-3

It can be done in a single command. You need to set the IDENTITY property for primary id

ALTER TABLE SalesEstimateCont ADD Org_ID int NOT NULL IDENTITY (1,1) PRIMARY KEY

answered Feb 02 '18 at 06:54

Rai Vu

1,595
1
20
30

OrgId is the organisationId and a organization can have n numbers of users. and if more than one user click at same time for saving, its creating problem by generating same estimationNo. – sumit.spider Feb 02 '18 at 07:03

how to get max id from a table using linq

5 Answers5