In this post I’m going to describe the cleanest way to setup your Entity Framework code in .Net Core. For this example, I’m going to create the database first, then I’ll run a POCO generator to generate the Plain Old C# Objects and their mappings. Finally, I’ll put it together in a clean format.
The Database
Here is the SQL script you’ll need for the sample database. I’m keeping this as simple as possible. There are only two tables with one foreign key constraint between the two. The purpose of the tables becomes obvious when you look at the table names. This sample will be working with stores and the products that are in the stores. Before you start, you’ll need to create a SQL database named DemoData. Then execute this script to create the necessary test data:
delete from Product
delete from Store
go
DBCC CHECKIDENT ('[Store]', RESEED, 0);
DBCC CHECKIDENT ('[Product]', RESEED, 0);
go
insert into store (name) values ('ToysNStuff')
insert into store (name) values ('FoodBasket')
insert into store (name) values ('FurnaturePlus')
go
insert into product (Store,Name,price) values (1,'Doll',5.99)
insert into product (Store,Name,price) values (1,'Tonka Truck',18.99)
insert into product (Store,Name,price) values (1,'Nerf Gun',12.19)
insert into product (Store,Name,price) values (2,'Bread',2.39)
insert into product (Store,Name,price) values (1,'Cake Mix',4.59)
insert into product (Store,Name,price) values (3,'Couch',235.97)
insert into product (Store,Name,price) values (3,'Bed',340.99)
insert into product (Store,Name,price) values (3,'Table',87.99)
insert into product (Store,Name,price) values (3,'Chair',45.99)
POCO Generator
Next, you can follow the instructions at this blog post: Scaffolding ASP.Net Core MVC
There are a few tricks to making this work:
- Make sure you’re using Visual Studio 2015. This example does not work for Visual Studio 2017.
- Create a throw-away project as a .Net Core Web Application. Make sure you use “Web Application” and not “Empty” or “Web API”.
You can navigate to the directory, then open a command window, copy the directory from the top of the explorer window, type “cd ” in the command window and paste the directory there. Then hit enter and you’ll be in the correct directory. You’ll need to be inside the directory that contains the project.json file where you copied the NuGet package text (see article at link). The dotnet ef command will not work outside that directory. You’ll end up with a command line like this:
dotnet ef dbcontext scaffold "Server=YOURSQLNAME;Database=DemoData;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer --output-dir Models
Once you have generated your POCOs, you’ll have a directory named Models inside the .Net Core MVC project:
If you look at Product.cs or Store.cs, you’ll see the POCO objects, like this:
public partial class Product
{
public int Id { get; set; }
public int Store { get; set; }
public string Name { get; set; }
public decimal? Price { get; set; }
public virtual Store StoreNavigation { get; set; }
}
Next, you’ll find a context that is generated for you. That context contains all your mappings:
public partial class DemoDataContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(@"Server=YOURSQLNAME;Database=DemoData;Trusted_Connection=True;");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Product>(entity =>
{
entity.Property(e => e.Name).HasColumnType("varchar(50)");
entity.Property(e => e.Price).HasColumnType("money");
entity.HasOne(d => d.StoreNavigation)
.WithMany(p => p.Product)
.HasForeignKey(d => d.Store)
.OnDelete(DeleteBehavior.Restrict)
.HasConstraintName("FK_store_product");
});
modelBuilder.Entity<Store>(entity =>
{
entity.Property(e => e.Address).HasColumnType("varchar(50)");
entity.Property(e => e.Name).HasColumnType("varchar(50)");
entity.Property(e => e.State).HasColumnType("varchar(50)");
entity.Property(e => e.Zip).HasColumnType("varchar(50)");
});
}
public virtual DbSet<Product> Product { get; set; }
public virtual DbSet<Store> Store { get; set; }
}
At this point, you can copy this code into your desired project and it’ll work as is. It’s OK to leave it in this format for a small project, but this format will become cumbersome if your project grows beyond a few tables. I would recommend manually breaking up your mappings to individual source files. Here is an example of two mapping files for the mappings of each table defined in this sample:
public static class StoreConfig
{
public static void StoreMapping(this ModelBuilder modelBuilder)
{
modelBuilder.Entity<Store>(entity =>
{
entity.ToTable("Store");
entity.Property(e => e.Address).HasColumnType("varchar(50)");
entity.Property(e => e.Name).HasColumnType("varchar(50)");
entity.Property(e => e.State).HasColumnType("varchar(50)");
entity.Property(e => e.Zip).HasColumnType("varchar(50)");
});
}
}
And the product config file:
public static class ProductConfig
{
public static void ProductMapping(this ModelBuilder modelBuilder)
{
modelBuilder.Entity<Product>(entity =>
{
entity.ToTable("Product");
entity.Property(e => e.Name).HasColumnType("varchar(50)");
entity.Property(e => e.Price).HasColumnType("money");
entity.HasOne(d => d.StoreNavigation)
.WithMany(p => p.Product)
.HasForeignKey(d => d.Store)
.OnDelete(DeleteBehavior.Restrict)
.HasConstraintName("FK_store_product");
});
}
}
Then change your context to this:
public partial class DemoDataContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(@"Server=YOURSQLNAME;Database=DemoData;Trusted_Connection=True;");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.StoreMapping();
modelBuilder.ProductMapping();
}
public virtual DbSet<Product> Product { get; set; }
public virtual DbSet<Store> Store { get; set; }
}
Notice how the complexity of the mappings is moved out of the context definition and into individual configuration files. It’s best to create a configuration directory to put those files in and further divide your config files from your POCO source files. I also like to move my context source file out to another directory or project. An example of a directory structure is this:
You can name your directories any name that suits your preferences. I’ve seen DAC used as Data Access Control, Sometimes Repository makes more sense. As you can see in my structure above, I grouped all of my database source files in the DAC directory, then the Configuration sub-directory contains mappings, the Domain directory has all of my POCOs and finally I left the context in the DAC directory itself. Another technique is to split these into different projects and use an IOC container to glue it all together. You can use that technique to break dependencies between dlls that are generated by your project.