I am trying to convert a 10+ year old database to our new format and using EFCore to do the ETL. And here is the method that is throwing me an error on.
internal void GetAllUsers(string? userName = null) {
List<AspNetUser> oldUsers = new List<AspNetUser>();
if (userName is not null)
{
oldUsers = _oldContext.AspNetUsers.Where(x => x.UserName == userName).ToList();
//if (!oldUsers.Any()) Console.WriteLine("No Users Found Exiting");
//return;
}
if (userName is null) oldUsers.AddRange(_oldContext.AspNetUsers.OrderBy(x => x.Id));
foreach (var user in oldUsers) {
var newUser = ConvertAspNetUserToConnexUser(user);
Console.WriteLine($"Converting user: {0}", newUser.UserName);
CreateUserConnections(ref newUser);
CreateLicenseKey(newUser);
CreateTaxCodes(newUser);
if (_newContext.ConnexUsers.Any(x => x.Id == newUser.Id))
newUser.Id = Guid.NewGuid().ToString();
_newContext.ConnexUsers.Add(newUser);
AnsiConsole.WriteLine($"Inserting user: {0} to the new database.", newUser.UserName);
try {
_newContext.Database.OpenConnection();
_newContext.Database.ExecuteSqlRaw("SET IDENTITY_INSERT [AspNetUsers] ON");
_newContext.SaveChanges();
_newContext.Database.ExecuteSqlRaw("SET IDENTITY_INSERT [AspNetUsers] OFF");
} catch (Exception ex) {
Console.WriteLine(ex.ToString());
AnsiConsole.WriteLine($"Problem Inserting {user. Email}");
throw new Exception($"Problem inserting the user {newUser.Email}: ", ex);
} finally {
_newContext.Database.CloseConnection();
}
}
}
This is the user object that I have for our users.
public class CustomUser : IdentityUser
{
[Display(Name = "First Name")]
public string FirstName { get; set; }
[Display(Name = "Last Name")]
public string LastName { get; set; }
[Display(Name = "Company")]
public string Company { get; set; }
[Display(Name = "Title")]
public string Title { get; set; }
public DateTime DateInserted { get; set; }
public DateTime DateModified { get; set; }
public ICollection<RuleSet> RuleSets { get; set; } = new List<RuleSet>();
public ICollection<UserCompany> UserCompanies { get; set; } = new List<UserCompany>();
public bool IsQuickBooksSandBox { get; set; }
public string? ConnectionName { get; set; }
}
If I don't set IDENITITY_INSERT OFF then I will get another error as well
SqlException: Cannot insert explicit value for identity column in table 'UserConnections' when IDENTITY_INSERT is set to OFF.
Does anyone have any suggestions?