Do you also want to move to EF Core but are unsure if it's possible? Here is our story from migrating Bokio to EF Core.
At Bokio Entity Framework 6 has been a core piece of our infrastructure since the beginning in 2015. It has served us well but we have been eyeing to migrate to .net core and EF core for a while for many different reasons. One is that migrations never get more fun down the line. Another one is performance, in particular we are very interested in the in memory database for EF Core to use for testing. Anyway, earlier this year we decided to start to test if we could move to EF Core or not. Our application has 100+ tables so it's not the largest system around but it has some weight.
Yesterday we merged EF Core into our development branch and plan to run on this in production in a few weeks so it's time share these lessons to others.
Quick overview of the content because this post is loooong.
- An overview of our approach
- Deep dives on each step in the process with details on how to solve a bunch of problems we had.
And if you have any question. Ping me on Twitter @MikaelEliasson.
An overview of our approach
First of all it's important to know that we aimed for an in-place upgrade to EF Core, without any production downtime (yet to be proven). It's also worth knowing that we were already using Code First.
The way we decided to approach this was to ensure schema equality between EF Core and EF6. In other words, not a single column in any table could have significant differences in the database EF Core would create and the database EF6 was creating. The reason this is important is that if we get schema equality the only thing we need to get running with core instead is to manipulate the migration tables EF uses. But before even getting to this stage we had a bunch of other issues to tackle.
Our steps
I'll go into details of each step below, this is more for you to get overview of what's needed on a high level.
- Create a new branch from
development2. (Whydevelopment2? Well, have your ever broken a git branch beyond repair? Because I have.) - Uninstall EF6 + EFUtilities on all projects. To make the nest step easier I made a list of projects that had it installed.
- Install EF Core to the projects where I uninstalled EF6. More specifically we installed
Microsoft.EntityFrameworkCore.SqlServer. - By now everything is broken, now it's just grunt work to fix that. I will come back to some tips and lessons around fixing this. The solution needs to build! Throw out all old migrations!.
- Once it builds make sure your tests can run. We do a lot of our testing against a real database so we will be able to catch most errors. At this stage the important thing is not for all tests to pass. It's just to make sure you can run them. The reason is that we want the tests to be able to create a new database easily. In our case we started trying to fix too many tests before doing the next step though.
- Start aligning the schema between Core and EF6. Thanks to the previous step we can just run one test to get a new test database. We then use Sql Schema Compare in Visual .Studio to spot the differences between this database and our EF6 database.
- Get all tests passing.
- Run the application and do manual testing (use another database name than you normally do in dev).
- If that looks OK it's time re-enable migrations by creating an initial EF core migration. Thanks to EF Core using another table name for the migration history this is very easy.
- Create a script to apply to existing EF6 databases that you want to upgrade that manually creates the new migration table and the row for the initial migration.
- Communicate to the team on how to handle the new changes and merge back to
development2. - Apply the scripts to your live databases. Because it's using a different table this can be done way ahead of releasing the code to that environment. The new table can easily sit there idly until it's needed.
- Welcome to EF Core!
Complications to be aware of
There are some assumptions that needs to be fulfilled for this to work. In particular our strategy depended on being able to have our production databases in the same state at some time. Otherwise we would've had to look at another solution than just throwing out all old migrations. I don't have a solution for this because we didn't have to look into it, at Bokio we only run one version of the system so we have no version drift to deal with.
Another problem we had was EFUtilities not supporting EF Core. Luckily for us I'm the author for that so I could just create a new version of that. But you will have to check your dependencies before you start to avoiding spending a lot of effort and later get blocked.
Another problem is if EF Core supports your particular feature usage?. We didn't do this research properly and we almost had to abandon this project in the end because of it. In particular EF Core (as of 2.2.4) is fairly incompetent when it comes to GroupBy and OwnedEntities if you compare it to EF6. I'll dedicate a section to this later.
Merges! Just be prepared for the fact that you will be doing a bunch or really nasty merges. Particularly annoying is to deal with new projects being added because they will be added with EF6. In our cases I normally did these merges, we are 14 developers. If we had been more developers that would have been a problem and in that case I would set a policy where anyone adding a new project also made sure to merge this into the new branch to spread that burden. Another things that require some handling are when merging in migrations. Depending on which step above you are in they need to be handled differently. In steps 1 - 8 you can just delete the new migration, you might want to see if there are any schema alignment you need to redo here. If you rely on Schema Compare for this you might need to update the comparison database you used. In step 9-11 the same things applies + you need to recreate that initial migration. This is why this is done late and preferably you are only in this state for a short time. It's not hard to recreate the initial migration. Just delete the migrations folder again and re-run the command, but it's easy to forget. For step 12+ It's now the problem of the merger, but they will need help and information.
Deep dives
Let's dig into the challenges we faced and solutions we used for each interesting step.
Step 4, fix non compiling code
Working with thousands of errors in Visual Studio is not a great experience because most of these errors are just an effect of a dependency failing to build rather than an actual error. My trick in this case to build project by project. Extremely simplified Bokio contains a structure something like this.
Bokio (web)
Bokio.Core
Bokio.Model
Where each project depends on the ones below it. In this case I start by building only Bokio.Model (right click project in SV and build it). I also filter the error list on Current Project and make sure to open a file in Bokio.Model. Fix these errors. Then repeat this step for Bokio.Core and finally for Bokio. This way I get to focus on real error rather than follow up errors.
Here are some errors you will likely need to fix.
Namespace and type name changes
System.Data.Entity => Microsoft.EntityFrameworkCore This is likely your first error to fix because the namespace has been changed so you need to update your using statements. I simply did ctrl + shift + f, replace in files and did a full replace. (Remove "keep modified files open" unless you like to torture your computer).
Besides updating the System.Data.Entity ones you likely need to add using Microsoft.EntityFrameworkCore; in more files because for example .AsNoTracking() no longer exist on DbSet<T>, instead you always use the extension method found in the new namespace.
Other straight forward changes but that are not obvious
IDbContextFactory<T> => IDesignTimeDbContextFactory<T>IDbSet<T> => DbSet<T>DbModelBuilder => ModelBuilder
Updating the constructor of your DbContext
The base class constructor no longer takes a connection string. The update we did was this:
In EF6
public Context(string nameOrConnectionString): base(nameOrConnectionString){}
In EF Core
private string nameOrConnectionString { get; set; }
public Context(string connectionString) : base()
{
this.nameOrConnectionString = connectionString;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(nameOrConnectionString);
base.OnConfiguring(optionsBuilder);
}
Updating mapping APIs
Mapping in EF core generally much nicer but the are a bunch of breaking changes. We stay away from things like TPH, entity splitting and other complicated things so these will not be included here. There are also many small changes I didn't include. In particular when you try to map more advanced thing the whole Metadata API layer has changed.
Many to many mappings now require a join entity. This is IMO a super good decision because to automatic solution EF6 had often creates problems when your model evolves.
So if we previously had
public class Company {
public ICollection<User> Users { get; set; }
}
public class User {
public ICollection<Company> Companies { get; set; }
}
You will now want to change this to
public class Company {
public int Id { get; set; }
public ICollection<CompanyUser> Users { get; set; }
}
public class User {
public int Id { get; set; }
public ICollection<CompanyUser> Companies { get; set; }
}
public class CompanyUser {
public int CompanyId { get; set; }
public int UserId { get; set; }
public User User { get; set; }
public Company Company { get; set; }
}
And in your model mapping you want to map the PK for CompanyUser. Note that the Foreign keys and indexes should be set up automatically from the model.
modelBuilder.Entity<CompanyUser>.HasKey(x => new { x.CompanyId, x.UserId });
While not strictly needed I also add public DbSet<CompanyUser> CompanyUsers { get; set; } to my DbContext. This way I can super easily break and insert new company => user mappings.
Complex Types are now Owned Entities. You need to change from x.ComplexType<Period>(); to x.Owned<Period>();. Unfortunately Owned Entities work way worse than Complex Entities (written for EF Core 2.2.4). I'll come back to that later. This is what we need to make sure we compile though.
Relationship mapping have changed significantly. And to the better I would say. We mostly have 1-n relationship.
Typically these mappings would change from this in EF6.
x.Entity<Company>()
.HasMany(c => c.StatementOfEarnings)
.WithRequired(e => e.Company)
.WillCascadeOnDelete(false);
To this in EF Core
x.Entity<Company>()
.HasMany(c => c.StatementOfEarnings)
.WithOne(e => e.Company)
.OnDelete(DeleteBehavior.Restrict);
Worth noting is that WithRequired or WithOptional is replaced by looking at the type in Foreign Key property. If you want optional you make the Foreign Key property nullable.
Index definitions have changed. This change is an amazing improvement over EF6.
To define an index in EF6 😱
x.Entity<Token>()
.Property(d => d.ServiceKey)
.HasColumnAnnotation(
IndexAnnotation.AnnotationName,
new IndexAnnotation(new IndexAttribute("IX_ServiceKey_ExternalId", 1) {IsUnique = true}));
x.Entity<Token>()
.Property(d => d.ExternalId)
.HasMaxLength(36)
.HasColumnAnnotation(
IndexAnnotation.AnnotationName,
new IndexAnnotation(new IndexAttribute("IX_ServiceKey_ExternalId", 2) {IsUnique = true}));
To define an index in EF Core
x.Entity<Token>()
.HasIndex(d => new { d.ServiceKey, d.ExternalId })
.HasName("IX_ServiceKey_ExternalId")
.HasFilter(null)
.IsUnique(true);
Database Initializers doesn't exist in EF Core
We had code like this
var migrator = new MigrateDatabaseToLatestVersion<Context, Core.Migrations.Configuration>(useSuppliedContext: true);
Database.SetInitializer(migrator);
In EF Core this is no longer possible. You could do this instead.
db.Database.Migrate();
We actually removed this code at all and started applying migrations manually instead as that's much better for our continuos deployments.
Microsoft.AspNet.Identity.EntityFramework need to be replaced (When not on .Net Core)
We are using Asp.net Identity and using EF to store this data. That meant that our context was defined like this.
public class Context : IdentityDbContext<ApplicationUser, CustomRole, Guid, CustomUserLogin, CustomUserRole, CustomUserClaim>
There are some support for this IF you are running .Net Core. We are still on 4.7.2, and for that there is no built in solution. The good thing is that Identity packages did nothing fancy really. It was just an easier way to set up the tables and classes needed.
You can see the implementation here https://github.com/aspnet/AspNetIdentity/tree/master/src/Microsoft.AspNet.Identity.EntityFramework. What I then did was to copy all properties from the base classes to our custom classes. That way we didn't need to care about this package at all.
This method map these classes to how they were stored in EF6.
private void SetupIdentityTables(ModelBuilder modelBuilder)
{
//Much of this setup is customized to match EF6 model
var user = modelBuilder.Entity<ApplicationUser>()
.ToTable("AspNetUsers");
user.HasMany(u => u.Roles).WithOne(c => c.User).HasForeignKey(ur => ur.UserId).HasConstraintName("FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId");
user.HasMany(u => u.Claims).WithOne(c => c.User).HasForeignKey(uc => uc.UserId).HasConstraintName("FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId");
user.HasMany(u => u.Logins).WithOne(c => c.User).HasForeignKey(ul => ul.UserId).HasConstraintName("FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId");
user.Property(u => u.UserName)
.IsRequired()
.HasMaxLength(256);
user.HasIndex(x => x.UserName).HasName("UserNameIndex").IsUnique();
user.HasKey(x => x.Id).HasName("PK_dbo.AspNetUsers");
user.Property(x => x.TermsVersion).HasDefaultValueSql("0");
// CONSIDER: u.Email is Required if set on options?
user.Property(u => u.Email).HasMaxLength(256);
var userRole = modelBuilder.Entity<CustomUserRole>()
.ToTable("AspNetUserRoles");
userRole.HasKey(r => new { r.UserId, r.RoleId }).HasName("PK_dbo.AspNetUserRoles");
userRole.HasIndex(r => r.UserId).HasName("IX_UserId");
var login = modelBuilder.Entity<CustomUserLogin>()
.ToTable("AspNetUserLogins");
login.Property(x => x.LoginProvider).HasMaxLength(128);
login.Property(x => x.ProviderKey).HasMaxLength(128);
login.HasKey(l => new { l.LoginProvider, l.ProviderKey, l.UserId }).HasName("PK_dbo.AspNetUserLogins");
var claim = modelBuilder.Entity<CustomUserClaim>()
.ToTable("AspNetUserClaims");
claim.HasKey(x => x.Id).HasName("PK_dbo.AspNetUserClaims");
var role = modelBuilder.Entity<CustomRole>()
.ToTable("AspNetRoles");
role.Property(r => r.Name)
.IsRequired()
.HasMaxLength(256);
role.HasKey(x => x.Id).HasName("PK_dbo.AspNetRoles"); //To keep EF6 model
role.HasIndex(x => x.Name).HasName("RoleNameIndex").IsUnique();
role.HasMany(r => r.Users).WithOne(r => r.Role).HasForeignKey(ur => ur.RoleId).HasConstraintName("FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId");
}
This takes care of the model side. But the package also included a UserStore that was used by the UserManager that Asp.Net Identity requires. We simply copied the UserStore code from the same github pages and instead of having a UserStore<T> we simply changed it to be our specific types like this.
public class UserStore :
IUserLoginStore<ApplicationUser, Guid>,
IUserClaimStore<ApplicationUser, Guid>,
IUserRoleStore<ApplicationUser, Guid>,
IUserPasswordStore<ApplicationUser, Guid>,
IUserSecurityStampStore<ApplicationUser, Guid>,
IQueryableUserStore<ApplicationUser, Guid>,
IUserEmailStore<ApplicationUser, Guid>,
IUserPhoneNumberStore<ApplicationUser, Guid>,
IUserTwoFactorStore<ApplicationUser, Guid>,
IUserLockoutStore<ApplicationUser, Guid>
{
}
Step 5, Getting tests up and running
You will already have fixed the compilation errors. So the only thing remaining is to make sure we can recreate the database between test runs. We do this in a static constructor of a test helper we have. On each test we only clean the database.
In our code, what used to be this in EF6
private static void SetupDb()
{
Database.SetInitializer<Context>(new MigrateDatabaseToLatestVersion<Context, Configuration>());
DB.ContextFactory = () =>
{
var db = new Context("TestConnection");
db.Configuration.LazyLoadingEnabled = false;
db.Configuration.ProxyCreationEnabled = false;
return db;
};
DB.Use(db =>
{
var exists = db.Database.Exists();
if (exists)
{
var compatible = db.Database.CompatibleWithModel(false);
if (compatible)
{
return;
}
db.Database.ForceDelete();
}
db.Database.Create();
});
}
Is now changed to this to set up the database for tests in EF Core.
private static void SetupDb()
{
DB.ContextFactory = () =>
{
var db = new Context(ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString);
return db;
};
DB.Use(db =>
{
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
});
}
One thing worth noticing is that this code will drive you crazy because it's slow to initialize. About 10s slower actually. You will want to add ConnectRetryCount=0 to the connection string. See more details at https://www.tabsoverspaces.com/233746-faster-ms-sql-database-existence-checking-with-entity-framework-core-and-entity-framework
Step 6, Aligning the model
You number #1 tool here will be Sql Schema Compare. You can find it in Visual Studio under "Tools" > "Sql Server" > "New Schema Comparison". When you run this against your test database and your reference database you will likely see that almost every table have changed. For us the column order had changed, we want to ignore these changes because it doesn't matter. You can do that in the options by checking this box.
Now it's just to start aligning things. Here are some tricks I found out.
Conventions doesn't exist in EF Core. Use code to set this up instead
In EF6 we could create custom conventions like these: https://docs.microsoft.com/en-us/ef/ef6/modeling/code-first/conventions/custom. This functionality doesn't exist in EF Core. But because the ModelBuilder and metadata APIs are much nicer it's almost as easy to do in code.
We start our OnModelCreating with this. You can easily adopt these examples.
protected override void OnModelCreating(ModelBuilder x)
{
base.OnModelCreating(x);
SetupDecimalPrecision(x);
SetupDefaultDateTimeType(x);
The reason we call these methods first is so that we have a chance to override them later on a case by case basis. These methods look like this:
//Set EF Core to use default datetime to match our original DB because Core by default uses datetime2
//We should change to datetime2 later.
private void SetupDefaultDateTimeType(ModelBuilder x)
{
foreach (var property in x.Model.GetEntityTypes().SelectMany(t => t.GetProperties()).Where(p => p.ClrType == typeof(DateTime) || p.ClrType == typeof(DateTime?)))
{
property.Relational().ColumnType = "datetime";
}
}
//EF uses decimal(18, 2) by default, but we need the extra precision
private static void SetupDecimalPrecision(ModelBuilder x)
{
foreach (var property in x.Model.GetEntityTypes().SelectMany(t => t.GetProperties()).Where(p => p.ClrType == typeof(decimal) || p.ClrType == typeof(decimal?)))
{
property.Relational().ColumnType = "decimal(18, 4)";
}
}
Index/Constraint names are different in EF Core
This one is REALLLLLLY important to fix. Far more important than the defaults above. The reason is that you will have to drop indexes to do certain updates to tables later. And if EF doesn't have the correct name for that index/constraint that will not work out so well.
To get EF Core to use the EF 6 naming convention we did a hack that works decently for us. You will likely have to adapt this code.
//Hack some index names to be compliant with EF6 database. THis code is FAR from perfect.
//But it's OK because we can override the faulty ones on that type
private void SetupDefaultIndexNames(ModelBuilder x)
{
//This code has to run after all tables have been set up so we need if the name is already overriden and in that case avoid doing that.
foreach (var entity in x.Model.GetEntityTypes())
{
foreach (var key in entity.GetKeys())
{
// PK_dbo. is the pattern from EF6, if we have that we have set a manual name
key.Relational().Name = key.Relational().Name.StartsWith("PK_dbo.") ? key.Relational().Name : key.Relational().Name.Replace("PK_", "PK_dbo."); // Example: [PK_Absences] => [PK_dbo.Absences].
}
foreach (var key in entity.GetForeignKeys())
{
if (key.Relational().Name.StartsWith("FK_dbo.")) //FK_dbo. is the pattern from EF6, if we have that we have set a manual name
{
continue;
}
var principalTable = key.PrincipalEntityType.Relational().TableName;
var dependentTable = key.DeclaringEntityType.SqlServer().TableName;
var fk = key.Properties.First().Relational().ColumnName;
key.Relational().Name = $"FK_dbo.{dependentTable}_dbo.{principalTable}_{fk}"; // Example: [FK_Absences_Employees_EmployeeId] => [FK_dbo.Absences_dbo.Employees_EmployeeId].
}
foreach (var ix in entity.GetIndexes())
{
ix.Relational().Name = ix.Relational().Name.Replace($"IX_{ix.DeclaringEntityType.Relational().TableName}_", $"IX_"); // Example: [IX_Absences_EmployeeId] => [IX_EmployeeId].
}
}
}
This code is called after configuration all entities. This is the problem with the code based approach, order those matter. We call it last because otherwise all indexes might not be defined. But because this code is not perfect we still to manually set some names. In particular for the Foreign keys we bail out if we see "FK_dbo." as that is sure way to know that this index was manually set. This code really is a big hack. Maybe it would have been better to just set the name manually on all indexes. It's up to you do decide.
Setting the index/constraint name manually is very easy. For constraints x.Entity<Token>().HasOne(s => s.User).WithMany().HasConstraintName("FK_dbo.Token_dbo.Users_UserId")
and for indexes x.Entity<YearlyEmployeeSummary>().HasIndex(s => new { s.EmployeeId, s.Year }).IsUnique().HasName("IX_Employee_Year");.
Fixing default values and null/not null
If you are like us you have been configuring these things in migrations. Which seemed like a good idea at the time. But when doing this migration we realized that was a big mistake because now the model doesn't contain this info. So we had to go through the model and add these annotations there. Which took some time but in the long it's probably much better because now the metadata is correct.
One problem we had was that we use a lot of Owned Entities. We didn't figure out how to configure the defaults for these Owned Entities once based on type. So instead we added this extension method to make it easier to configure.
public static EntityTypeBuilder<T> AddDefaultToOwned<T, TOwned, TProp>(this EntityTypeBuilder<T> source, Expression<Func<T, TOwned>> ownedExpression, Expression<Func<TOwned, TProp>> propertyExpression, TProp value) where T : class where TOwned : class
{
source.OwnsOne(ownedExpression).Property(propertyExpression).HasDefaultValue(value).ValueGeneratedNever();
return source;
}
This is then called like this modelBuilder.Entity<Invoice>().AddDefaultToOwned(x => x.CustomerData, x => x.CustomerNumber, 0).
Indexes will not work on properties in Owned Entities in EF Core.
We only had one index like this, we simply created it with less columns because luckily the property we couldn't map was the last one anyway and not very important.
Never ever inherit an Owned Entity and include both the parent and child in the model
EF Core will start to randomly add discriminator columns to the these items. See https://stackoverflow.com/questions/54816745/ef-core-adding-discriminator-column-to-owned-entity for more info. I find the whole situation quite hilarious. As an answer to that question I post a solution. What you will need to do is to create a common base that the Owned Entities inherit from but is not included in itself.
Step 7 and 8, Fixing code that doesn't work
The first thing you should do is to break more of your code by not allowing ClientEvaluation like this. Two reasons, 1st you will run into performance issues otherwise, 2nd EF Core 3.0 will run in this mode anyway.
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(nameOrConnectionString);
optionsBuilder.ConfigureWarnings(warnings => warnings.Throw(RelationalEventId.QueryClientEvaluationWarning));
base.OnConfiguring(optionsBuilder);
}
Other than that I can give no absolute recommendations but I can give you an overview of some problems we ran into.
GroupBy in EF Core is very primitive
- You cannot only do basic aggregations in the
GroupByin EF Core while EF6 was able to translate about anything.
For example this query will work.
var items = db.Person
.GroupBy(x => x.Name).Select(x => new
{
x.Key,
Count = x.Sum(s => s.Count - s.Count2)
}).ToList();
While this one doesn't
var items = db.Person
.GroupBy(x => x.Name).Select(x => new
{
x.Key,
Count = x.Sum(s => s.Count - s.Count2)
}).ToList();
The later query might be possible to rewrite as this though
var items = db.Person
.GroupBy(x => x.Name).Select(x => new
{
x.Key,
Count = x.Sum(s => s.Count)
Count2 = x.Sum(s => s.Count2)
})
.ToList()
Select(x => new {
x.Key,
Count = x.Count - x.Count2
})
.ToList();
Less nice, but certainly doable. We had way more complex GroupBy queries that we had to totally rewrite though. Another option is run these queries trough something like Dapper.
Nested owned entities doesn't update properly in EF Core (2.2.4)
For example this test will fail.
[TestMethod]
public void Change_WithNestedOwnedEntity()
{
var db = new Context();
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
db.Person.Add(new Person { Name = "A", Approved = new AuditInfo(), Requested = new AuditInfo(), WorkDetails = new WorkDetails { Address = new Address { City = "Dummy" } } });
db.SaveChanges();
db = new Context();
var p = db.Person.Single();
p.WorkDetails = new WorkDetails { Address = new Address { City = "Dummy2" } };
db.SaveChanges();
db = new Context();
var person = db.Person.Single();
person.WorkDetails.Address.City.ShouldBe("Dummy2");
}
The value will still be Dummy. Why? Well, The ChangeTracker cannot follow this properly, it will think that Person is modified, WorkDetails is added and Address unchanged.
We tried multiple solution for this, including helping the change tracker by telling it how it should be. In the end we figured out the most stable and easy way to fix it on the current version is to simple apply values to the originals instead of replacing them.
To make this easier we create a CopyValuesFrom method on all our owned entities. We make sure to have tests for these. To make the testing easier we use this helper file https://github.com/bokio/bokiohelpers/blob/master/c%23/CopyValuesFromAsserter.cs. This will fail a test if you add a new property that you forget to copy. Which is what we want because that would break your code.
So our code changes from p.WorkDetails = new WorkDetails { Address = new Address { City = "Dummy2" } }; to p.WorkDetails.CopyValuesFrom(new WorkDetails { Address = new Address { City = "Dummy2" } });.
Nested owned entities are not included when projecting a query
EF Core has a bug around querying nested owned entities. The following test will fail in EF Core 2.2.4
//Fails
[TestMethod]
public void Project_DoesIncludeNested_NoHack()
{
var db = new Context();
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
db.Person.Add(new Person { Name = "A", Approved = new AuditInfo(), Requested = new AuditInfo(), WorkDetails = new WorkDetails { Address = new Address { City = "Dummy" } } });
db.SaveChanges();
db = new Context();
var projected = db.Person.Select(x => new ViewModel
{
Name = x.Name,
WorkDetails = x.WorkDetails,
}).First();
projected.WorkDetails.Address.City.ShouldBe("Dummy"); //This is null here
}
Somehow EF Core loses track of the fact that it should include those columns. The workaround is annoying but not super difficult.
//Passes
[TestMethod]
public void Project_DoesIncludeNested()
{
var db = new Context();
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
db.Person.Add(new Person { Name = "A", Approved = new AuditInfo(), Requested = new AuditInfo(), WorkDetails = new WorkDetails { WorkName = "WN", Address = new Address { City = "Dummy" } } });
db.SaveChanges();
db = new Context();
var projected = db.Person.Select(x => new
{
vm = new ViewModel
{
Name = x.Name,
WorkDetails = x.WorkDetails,
},
x.WorkDetails.Address
})
.First();
projected.vm.WorkDetails.Address.City.ShouldBe("Dummy");
projected.vm.WorkDetails.WorkName.ShouldBe("WN");
}
You might wonder that SQL this will generate? Nothing bad, in fact it looks like I expect it to do without the hack.
SELECT TOP(1) [x].[Id], [x].[WorkDetails_WorkName], [x].[Id], [x].[WorkDetails_Address_City], [x].[WorkDetails_Address_PostalCode], [x].[WorkDetails_Address_Street1], [x].[Name]
FROM [Person] AS [x]
This bug is tracked here, I'm hoping to convince the team that a patch release instead of waiting for 3.0 is the right thing to do. But we will see. https://github.com/aspnet/EntityFrameworkCore/issues/13546
Can't share instances of Owned Entities between objects.
This code:
[TestMethod]
public void Modify_SharedOwned()
{
var db = new Context();
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
var audit = new AuditInfo();
db.Person.Add(new Person { Name = "A", Approved = audit, Requested = new AuditInfo() });
db.Person.Add(new Person { Name = "A", Approved = audit, Requested = new AuditInfo() });
db.SaveChanges();
}
Will give this error
Message: Test method EFCorePlayground.Tests.OwnedEntityUpdateTests.Modify_SharedOwned threw exception:
System.InvalidOperationException: The entity of type 'Person' is sharing the table 'Person' with entities of type 'Person.Approved#AuditInfo', but there is no entity of this type with the same key value that has been marked as 'Added'. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see the key values.
Copying the shared Owned Entity each time works much better.
[TestMethod]
public void Modify_SharedOwnedWithCopy_OK()
{
var db = new Context();
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
var audit = new AuditInfo();
db.Person.Add(new Person { Name = "A", Approved = audit.Copy(), Requested = new AuditInfo() });
db.Person.Add(new Person { Name = "A", Approved = audit.Copy(), Requested = new AuditInfo() });
db.SaveChanges();
}
I don't know exactly why this happens. But I think it has to do with the fact that Owned Entities are tracked with an Id by EF Core and EF Core expect it to be tried strictly to one Entity.
Null values are not inserted with EF Core
When we add a column in a migration we often add a default value instead of making it nullable. When we did this our plan was that default values was for old values. Not new ones. In EF6 this was the behavior we got because trying to insert null into a column with a default value still throws an exception. EF Core on the other hand by default omit the null values from the insert statement. Compare these two statements generated by the same code.
EF Core (modified for comparability):
exec sp_executesql INSERT INTO [RecipeBundles] ([Id], [Description], [IsStandard], [Name])
VALUES (@p0, @p1, @p2, @p3);
EF6:
exec sp_executesql N'INSERT [dbo].[RecipeBundles]([Id], [Name], [Description], [IsStandard], [Country], [VatRegistrationType])
VALUES (@0, @1, @2, @3, NULL, @4)
You can see that EF Core simply didn't include some of the columns. To get this working like in EF6 we added .ValueGeneratedNever() whenever we set a default value on a column.
Step 10 and 11, generate a script to create the migration table and communicate to the team.
Here are the instructions we sent out to the team.
1. You will want to update your database `bokio-dev` to the latest version before EF Core. So checkout development2 (pull all changes) and then do
git checkout 563e3577
Simply run Bokio now to make sure you have the lastest version. (Last entry in __MigrationHistory should be 201905280514173_NameOfSomeChange)
2. Now checkout head again. "git checkout development2"
3. You can now run the application with EF Core 😎
4. Unfortunately for you nothing is THAT easy. The only reason the application runs fine is because we are no longer checking DB and model compatibility at startup (A good thing for release flows, but worse for dev). It will work until there either someone commits a new migration or you want to create one yourself. To make it work even after that you have 2 options:
4a. Add the new migration table to your database by running the command I post below. (recommended way because we want this flow tested as much as possible)
4b. Delete the database bokio-dev and rebuild it by running "Update-Database" in Package Manager Console inside VS and for the project Bokio.Core
5. Now you should be up and running properly and hopefully future proof.
Here is the script we have. You will want to change the details on the row you insert in your script.
CREATE TABLE [dbo].[__EFMigrationsHistory](
[MigrationId] [nvarchar](150) NOT NULL,
[ProductVersion] [nvarchar](32) NOT NULL,
CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY CLUSTERED
(
[MigrationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
INSERT INTO [dbo].[__EFMigrationsHistory]
([MigrationId]
,[ProductVersion])
VALUES
('20190529105158_initial-core'
,'2.2.4-servicing-10062')
Summary
We have now worked through our flow in this 37k character long post 🙈. You will run into different challenges to deal with but I hope this helps some people and give you a better view on what an upgrade would mean. If we would restart the project I would research the support for certain things better. For example by testing out Owned Entities better + looking at the logged issues in GitHub. If you have a lot of nested Owned Entities You might want to hold off a bit. Remember that we don't use things like TPH, Lazy Loading etc etc so there might be some pitfalls there we never came across.
The same goes for GroupBy, if you use that a lot today EF Core 2.2.4 will not be a nice upgrade for you. Hopefully 3.0 will be better.