I am learning Entity Framework Core as part of my Azure journey. Database is an important part in an application. In the old days, developers wrote raw SQL queries. Later, we have had ADO.NET. Recently we have ORM. I have had a chance to work (know) the 2 big guys: NHibernate and Entity Framework.

ORM does more than just a mapping between object model and database representation, such as SQL Table, Column. Each ORM framework comes with a plenty of features, supports variety of scenarios. ORM helps you build a better application. Let’s discover some from the latest ORM from Microsoft: Entity Framework Core.

I was amazed by visiting the official document site. Everything you need to learn is there, in well-written, understandable pages. To my learning, I started with courses on Pluralsight, author Julie Lerman. If you happen to have Pluralsight account, go ahead and watch them. I is worth your time. Then I read the EF document on its official site.

It is  easy to say that “Hey I know Entity Framework Core“. Yes, I understand it. But I need the skill, not just a mental understanding. To make sure I build EF skill, I write blog posts and write code. It is also my advice to you, developers.

Journey to Azure

• Getting Started
• Data Access EF Core (Current)

Getting Started Objectives

1. Define a simple domain model and hook up with EF Core in ASP.NET Core + EF Core project
2. Migration: From code to database
3. API testing with Postman or Fiddler (I do not want to spend time on building UI)
4. Unit Testing with In Memory and real databases.
5. Running on Azure with Azure SQL
6. Retry strategy

1 – Domain Model

To get started, I have only these super simple domain model

namespace Aduze.Domain
{
    public abstract class Entity
    {
        public int Id { get; set; }
    }

    public class User : Entity
    {
        public string LoginName { get; set; }
        public string FullName { get; set; }
        public Image Avatar { get; set; }
    }

    public class Image : Entity
    {
        public string Uri { get; set; }
    }
}

A User with an avatar (Image).

Next, I have to setup DbContext

namespace Aduze.Data
{
    public class AduzeContext : DbContext
    {
        public DbSet<User> Users { get; set; }

        public AduzeContext(DbContextOptions options)
        :base(options)
        {
            
        }
        
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }
    }
}

Pretty simple just like the example in the document site. Just a quick note here, I organize domain classes in Domain project, data access layer in Data project. I do not like the term Repository very much.

Wire them up in the ASP.NET Core Web project

       public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();
            services.AddDbContext<AduzeContext>(options =>
            {
                options.UseSqlServer(Configuration.GetConnectionString("AduzeSqlConnection"))
                    .EnableSensitiveDataLogging();
            });
            services.AddLogging(log =>
                log.AddAzureWebAppDiagnostics()
                    .AddConsole());
        }

Just call the extension method: AddDbContext and done. God damn simple!

2 – Migration

The system cannot work unless there is a database. There are 2 possible solutions

1. Use your SQL skill and create database with correct schema.
2. Use what EF offers

I have done the former many years. Let’s explore the later.

Having your VS 2017 opened, access the Package Manager Console window

Add-Migration

1. Default project: Aduze.Data where the DbContext is configured.
2. Add-Migration: A PowerShell command supplied by EF Core. Tips: Type Get-Help Add-Migration to ask for help
3. InitializeUser: The migration name. One can give whatever makes sense.

After executed, The “Migrations” folder is added into the Data project. Visit EF Core document to understand what it does and syntaxes.

Script-Migration

So how does the SQL script look like?

PM> Script-Migration

IF OBJECT_ID(N'__EFMigrationsHistory') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;

GO

CREATE TABLE [Image] (
    [Id] int NOT NULL IDENTITY,
    [Uri] nvarchar(max) NULL,
    CONSTRAINT [PK_Image] PRIMARY KEY ([Id])
);

GO

CREATE TABLE [Users] (
    [Id] int NOT NULL IDENTITY,
    [AvatarId] int NULL,
    [FullName] nvarchar(max) NULL,
    [LoginName] nvarchar(max) NULL,
    CONSTRAINT [PK_Users] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Users_Image_AvatarId] FOREIGN KEY ([AvatarId]) REFERENCES [Image] ([Id]) ON DELETE NO ACTION
);

GO

CREATE INDEX [IX_Users_AvatarId] ON [Users] ([AvatarId]);

GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20180420112151_InitializeUser', N'2.0.2-rtm-10011');

GO

Cool! I can take the script and run in SQL Management Studio. Having scripts ready, I can use them to create Azure SQL database later on.

Update-Database

Which allows me to create the database directly from Package Manager Console (which is a PowerShell). Let’s see

PM> Update-Database -Verbose

By turning Verbose on, It logs everything out in the console. The result is my database created

It is very smart. How could It do?

1. Read the startup project Aduze.Web and extract the ConnectionString from appsettings.json
2. Run the migrations created from Add-Migration command.

3 – API Testing

So far nothing has happened yet.

namespace Aduze.Web.Controllers
{
    public class UserController : Controller
    {
        private readonly AduzeContext _context;

        public UserController(AduzeContext context)
        {
            _context = context;
        }
        [HttpPost]
        public async Task<IActionResult> Create([FromBody]User user)
        {
            _context.Add(user);
            await _context.SaveChangesAsync();
            return Json(user);
        }

        [HttpGet]
        public async Task<IActionResult> Index()
        {
            var users = await _context.Users.ToListAsync();
            return Json(users);
        }
    }
}

A typical Web API controller.

1. Create: Will insert a user. There is no validation, mapping between request to domain, … It is not a production code.
2. Index: List all users.

Here is the test using Postman

If I invoke the /user endpoint, the user is on the list.

Hey, what was going on behind the scene?

There are plenty of information you can inspect from the Debug window. When inserting a user, those are queries sent to the database (you should see the one to insert the avatar image).

So far so good. I have gone from domain model and build a full flow endpoint API. How about unit testing?

4 – Unit Test

One of the biggest concern when doing unit test is the database dependency. How could EF Core help? It has In-Memory provider. But first, I have to refactor my code since I do not want to test API controller.

namespace Aduze.Data
{
    public class UserData
    {
        private readonly AduzeContext _context;

        public UserData(AduzeContext context)
        {
            _context = context;
        }

        public async Task<User> Create(User user)
        {
            _context.Add(user);
            await _context.SaveChangesAsync();
            return user;
        }

        public async Task<IEnumerable<User>> GetAll()
        {
            return await _context.Users.ToListAsync();
        }
    }
}

namespace Aduze.Web.Controllers
{
    public class UserController : Controller
    {
        private readonly UserData _userData;

        public UserController(UserData userData)
        {
            _userData = userData;
        }
        [HttpPost]
        public async Task<IActionResult> Create([FromBody]User user)
        {
            return Json(await _userData.Create(user));
        }

        [HttpGet]
        public async Task<IActionResult> Index()
        {
            return Json(await _userData.GetAll());
        }
    }
}

That’s should do the trick. Then just register the new UserData service to IoC

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();
            services.AddDbContext<AduzeContext>(options =>
            {
                options.UseSqlServer(Configuration.GetConnectionString("AduzeSqlConnection"))
                    .EnableSensitiveDataLogging();
            });
            services.AddScoped<UserData>();
        }

Time to create a test project: Aduze.Tests. And then install the Microsoft.EntityFrameworkCore.InMemory package

PM> Install-Package Microsoft.EntityFrameworkCore.InMemory

This is really cool, see below

Because my refactor UserData uses async version. It seems to have a problem with MS Tests runner. But it is the same with testing directly again AduzeDbContext.

1. Use DbContextOptionsBuilder to tell EF Core that the context will use In Memory provider.
2. Pass the options to DbContext constructor.

Having the power to control which provider will be using is a powerful design. One can have a test suite that is independent to the provider. Most of the time we will test with In Memory provider. But when time comes to verify that the database schema is correct, can switch to a real database.

5 – Azure SQL

Time to grow up … to the cloud with these simple steps

1. Publish the web to Azure
2. Create Azure SQL database
3. Update connection string
4. Run the script (remember the Script-Migration command?) to create database schema

Just add the connection string: AduzeSqlConnection (is defined in appsettings.json at the local development).

Test again with Postman. Oh yeah baby. It works like a charm.

6 – Retry Strategy

This topic is not something I want to explore at this stage of my learning journey. But it is important to be aware of, at least note down the reference link to Connection Resiliency.

 

Wrap Up

It is not something new nor complicated if we look at its surface. However, when I get my hands dirty at the code and writing, I learn so much. Knowing how to define a DbContext is easy, understanding why it was designed that way is another complete story.

But is that all about EF Core? No. It is just a beginning. There are many things that developers will look at them when they experience problems in real projects. The document is there, the community is there. Oh, S.O has all answers.

What I will look at next is how EF Core supports developers with DDD (Domain Driven Design).