Cacher is the code snippet organizer for pro developers

https://garywoodfine.com/using-ef-core-in-a-separate-class-library-project/

Entity Framework Core is an Object Relational Mapper (ORM) for ASP.net Core projects. It’s. really easy to use and you can get up and running with it really quickly.

That being said it does have some quirks and idiosyncracies which may catch you off guard if you’re not aware of them. I have found keeping an eye on Julie Lermans blog really helps, she seems to have a knack for find ing the edge cases and solutions when it comes to Entity Framework in general.

In the particular example I want to highlight in this blog post, is something I uncovered when developing my series of blogs post, regarding developing an ASP.net Core API for my Stop Web Crawlers WordPress Plugin.

I wanted to keep my Database layer was seperate from API logic, in that I didn’t really want to include my Database Enity classes etc within my API code. Potentially I will be developing a further two micro service type API’s which may use the same Database Schema but not necessarily the same database server.

Can’t wait to see it in action ?
Check out the code on GitHub

GET CODE
 

Add Database Class Library Project
We make use of solution and projects files in the source code, so if you are not familiar with working with solution files using .net core you may want to check out my post – Creating and Editing Solutions files with .net core CLI.

I’ll add a new class library project, primary purpose of this project is to create Database Context object which well share across a couple of projects.

1
dotnet new class --name Api.Database
Rename the generated class to ApiContext.cs

We’ll also add a reference to Microsoft.EntityFrameworkCore. In the terminal window you can use dotnet add package Microsoft.EntityFrameworkCore. In our case we are going to use Microsoft SQL Server 2017, as database server. So lets go ahead and add a reference to those packages too. dotnet add package Microsoft.EntityFrameworkCore.SqlServer and we will also need dotnet add package Microsoft.EntityFrameworkCore.Tools.DotNet

We can now create a barebones Database Context.

1
2
3
4
5
6
7
8
9
10
using System;
using Microsoft.EntityFrameworkCore;
 
namespace Api.Database
{
    public class ApiContext : DbContext
    {
        public ApiContext (DbContextOptions<ApiContext> options) : base(options){ }
    }
}
Database Entities
We’ll now create a new project which we’ll use to create a Database Entity objects we’ll call it rather unimaginetively Api.Database.Entity.

The default class created via the project template we’ll rename to BaseEntity.cs and we’ll add some basic entity properties we need.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
 
namespace Api.Database.Entity
{
    public class BaseEntity
    {
       [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Key]
        public int Id { get; set; }
        public DateTime Created { get; set; }
        public DateTime Modified { get; set; }
    }
}
We’ll also add an additional folder to the Entity Project and call it Threats. We’ll also create 3 additional classes Status.cs , Threat.cs, ThreatType.cs. To View the properties of the file check the source code repository



Add Reference to Entity
We now need to add a reference for our Entity Project project to the Database Project.

1
2
:~/code/swcApi/src/Api.Database$ dotnet add reference "../Api.Database.Entity/Api.Database.Entity.csproj"
Reference `..\Api.Database.Entity\Api.Database.Entity.csproj` added to the project.
For the purpose of my project I will create a static class which will be used to store all our constant string declarations, called DBGlobals . We do this in order to try and minimize the use of Magic Strings in our code and we have one consistent string we can use throughout our library.

We’ll declare a constant string SchemaName

1
2
3
4
5
6
7
8
9
using System;
 
namespace Api.Database
{
   public static  class DBGlobals
 {
    public const string SchemaName = "Portal";
 }
}
View Code
Review the Api.Database.csproj to ensure the 2 Nuget Packages have been added.

Microsoft.EntityFrameworkCore.Design
Microsoft.EntityFrameworkCore.Tools.DotNet
We are now ready to update, ApiContext.cs with additional logic

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
    public class ApiContext : DbContext
    {
        public ApiContext(DbContextOptions options) : base(options) { }
        public DbSet Threats { get; set; }
        public DbSet Type { get; set; }
        public DbSet Status { get; set; }
 
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
           
            modelBuilder.HasDefaultSchema(schema: SchemaName.Portal);
            modelBuilder.Entity();
            modelBuilder.Entity();
            modelBuilder.Entity();
            base.OnModelCreating(modelBuilder);
 
 
        }
 
        public override int SaveChanges()
        {
            AddAuitInfo();
            return base.SaveChanges();
        }
 
        public async Task SaveChangesAsync()
        {
            AddAuitInfo();
            return await base.SaveChangesAsync();
        }
 
        private void AddAuitInfo()
        {
            var entries = ChangeTracker.Entries().Where(x => x.Entity is BaseEntity && (x.State == EntityState.Added || x.State == EntityState.Modified));
            foreach (var entry in entries)
            {
                if (entry.State == EntityState.Added)
                {
                    ((BaseEntity)entry.Entity).Created = DateTime.UtcNow;
                }
            ((BaseEntity)entry.Entity).Modified = DateTime.UtcNow;
            }
        }
    }
Our EF Core work is now complete. We have a basic Schema, and we have developed our Context, for the most part we are done. All we need to do now is create the Migration to get our Database up and running.

I will illustrate how we will make use of SQL Server 2016, running within a Docker Container to enable testing of the Migration Scripts. I won’t discuss how to get started with SQL Server and Docker. Microsoft do a pretty good job of explaining Connect to SQL Server on Linux



Once you have your SQL Server up and running whichever way you choose. We will need to create another DummyUI console project. The reason for this is a little quirk in how EF Core works, is that you are unable run Migrations against Class Library projects. We will create a Dummy Console Poject, that will be used as the Start Up project for the Migration task.

We will need to add a couple of references to the DummyDB

Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.Design
Microsoft.EntityFrameworkCore.Tools.DotNet
We also need to add project references to our Api.Database & Api.Database.Entity

We need to add an Additional class to our Api.Database pjoject

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
 
 
namespace Api.Database
{
public class ApiContextFactory : IDbContextFactory<ApiContext>
    {
        public ApiContext Create(DbContextFactoryOptions options)
        {
           var builder = new DbContextOptionsBuilder<ApiContext>();
            builder.UseSqlServer(
                "Server=(localdb)\\mssqllocaldb;Database=config;Trusted_Connection=True;MultipleActiveResultSets=true");
 
            return new ApiContext(builder.Options);
        }
    }
}
 

The process of creating a Migration is as follows:

Open the Terminal and navigate to the folder containing Api.Database.csproj
so right clicking on the project and Selecting Open Command line

Then using dotnet ef --startup-project ../DummyDB migrations add [Migration Name] will initiate the migrations

When setting up an EF Core project ensure the CSproj file has the following references

1
2
3
4
5
6
7
  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.0.1" />
   </ItemGroup>
     
  <ItemGroup>
     <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0-preview1-final" />
  </ItemGroup>
Add reference the EF Library
In order to make use of our seperate library we will need to edit the StartUp.cs to add our Context to the Services Collection.

In my case I am making use of Microsoft SQL Server, so I will also need to add Nuget Package Microsoft.EntityFrameworkCore.SqlServer and Microsoft.EntityFrameworkCore.SqlServer.Design to the Api Project.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<ApiContext>(options => options.UseSqlServer(Configuration.GetConnectionString("ApiDB")));
            services.AddMvc();
           
    
        }
 
        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
        {
            loggerFactory.AddConsole(Configuration.GetSection("Logging"));
            loggerFactory.AddDebug();
            app.UseMvc();
          
            using (var serviceScope = app.ApplicationServices.GetRequiredService<IServiceScopeFactory>().CreateScope())
            {
                serviceScope.ServiceProvider.GetService<ApiContext>().Database.Migrate();
               
            }
  }
Summary
Using the above technique you can encapsulate all your database logic within a separate class library project that you can share across multiple projects. You will not be dependent on any project to build or execute any database changes, the result being all your database activities are encapsulated and constrained to one library.

To expand further on this example, you may want to read How to seed your EF Core Database, to find out how you can further isolate and manage your database concerns using EF Core.