How do I get last inserted ID(String) from the table

Question

I want to use last inserted id from database, but that Id is in string format. Is there any way I can get last inserted string ID?

Every time I am using

p.UserId = db.AspNetUsers.Max(m => m.Id);

but it is returning wrong results. I think it is string so max won't work. Can anyone please suggest how to solve this problem?

Table structure:

• Patient: ID (PK), UID (fk of UserLogin), Name

AspNetUsers table:

CREATE TABLE [dbo].[AspNetUsers] 
(
    [Id]    NVARCHAR(128) NOT NULL,
    [Email] NVARCHAR (256) NULL,
)

Patient table:

CREATE TABLE Patient
(
     PatientId INT IDENTITY(1,1) PRIMARY KEY,
     PId AS 'P' + RIGHT('00000' + CAST(PatientId AS NVARCHAR(10)), 10) PERSISTED,
     UserId NVARCHAR(128) FOREIGN KEY REFERENCES AspNetUsers(Id)
)

Example ID is in format like this : 62d8d072-5261-4aaf-b552-d75453cc3421

This is the code , first I am getting value from view in AspNetUsers table , therefore it will generate id in table and I want to use that Id for my Patient table.

[HttpPost]
[ValidateAntiForgeryToken]
[AllowAnonymous]
public async Task<ActionResult> SignUp(Patientview pv)
{
        ClinicDbContext db = new ClinicDbContext();

        if (ModelState.IsValid)
        {
            try
            {
                var user = new ApplicationUser { UserName = pv.Email, Email = pv.Email };
                var result = await UserManager.CreateAsync(user, pv.Password);

                if (result.Succeeded)
                {
                    await SignInManager.SignInAsync(user, isPersistent: false, rememberBrowser: false);

                }
                else
                {
                    return View(pv);
                }

                Patient p = new Patient();
                p.FirstName = pv.FirstName;
                p.LastName = pv.LastName;
                p.DateOfBirth = pv.DateOfBirth;
                p.Email = pv.Email;
                p.PhoneNumber = pv.PhoneNumber.ToString();
                p.StreetAddress = pv.StreetAddress.ToString();
                p.City = pv.City;
                p.State = pv.State;
                p.ZipCode = pv.ZipCode;
                p.UserId = db.AspNetUsers.Max(m => m.Id);

                _context.Patients.Add(p);
                _context.SaveChanges();

                return RedirectToAction("Index", "Admin");
            }
            catch (DbEntityValidationException e)
            {
                foreach (var eve in e.EntityValidationErrors)
                {
                    System.Diagnostics.Debug.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
                        eve.Entry.Entity.GetType().Name, eve.Entry.State);
                    foreach (var ve in eve.ValidationErrors)
                    {
                        System.Diagnostics.Debug.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
                            ve.PropertyName, ve.ErrorMessage);
                    }
                }
                throw;
            }
        }
        else
        {
            return View(pv);
        }
}

Answer 1

Normally with EF you would map the relationship between entities with the FKs and let EF manage the assignment of the FK when the primary record and associated records are saved. However, in your case the relationship is relatively loosely coupled.

Are all Patient records going to be associated to ASP.Net authenticated users? If so you can look at changing the PK type on Patient to match the ASPNet table then set up the Patient as a 1-to-1 relationship to ASPNetUser.

Alternatively, if Patients can be ASPNetUsers (but can also be dissociated) then I would look at adding a PatientId column as an autoincrementing integer or sequential GUID (newsequentialId())to ASPNetUser and mapping that to the PatientId in patient as either optional or required a many-to-1. This gets around the ugliness of the string PK on ASPNetUser and allows you to use a FK type that is more suited to your application. (Int or GUID)

A third option you can use to get that newly inserted PK; The PK will only be created after the DbContext SaveChanges so this leaves you with calling SaveChanges twice to get that first id:

//...
var user = new ApplicationUser { UserName = pv.Email, Email = pv.Email };
var result = await UserManager.CreateAsync(user, pv.Password);

if (result.Succeeded)
  await SignInManager.SignInAsync(user, isPersistent: false, rememberBrowser: false);
else
  return View(pv);

_context.SaveChanges();
Patient p = new Patient();
p.PatientId = Guid.Parse(user.UserId); // User ID will be populated after the above SaveChanges call.                
p.FirstName = pv.FirstName;
//...
_context.SaveChanges(); //Save the Patient.

Typically though this isn't ideal as you can get the situation where the first SaveChanges succeeds, but the second fails. These records should likely go through together, so this can be achieved with a tranasaction scope, or a unit or work such as DbContextScope.

Using a TransactionScope:

using (var tx = new TransactionScope())
{ 
    var user = new ApplicationUser { UserName = pv.Email, Email = pv.Email };
    var result = await UserManager.CreateAsync(user, pv.Password);

    if (result.Succeeded)
      await SignInManager.SignInAsync(user, isPersistent: false, rememberBrowser: false);
    else
      return View(pv);

    _context.SaveChanges();
    Patient p = new Patient();
    p.PatientId = Guid.Parse(user.UserId); // User ID will be populated after the above SaveChanges call.                
    p.FirstName = pv.FirstName;
    //...
    _context.SaveChanges(); //Save the Patient.

  tx.Complete()
}

In general though, mapping the relationship between entities would be a better option because then the FK constraints can be set up to manage things like cascade deletes.

Answer 2

I understand that you want to get the the last inserted field in the database.

Why don't you use Last() function. :)

p.UserId = db.AspNetUsers.Select(X=>x.id).Last(); 

Will give you the Userid from the last row of the table.

Max is selecting the maximum value which may or may not yield you the last value.