How to connect ASP.NET Core 2.0 project with MSSql database using Entity Framework Core

What Will I Learn?

In this tutorial I will show You:

• how to create domain models in ASP.NET Core 2.0 application
• how to install Entity Framework Core and connect application with MSSQL Database
• how to create own database using code first migration and Fluent API
• how to add connection string to our Data base.

Requirements

• C#
• ASP.NET Core 2.0
• Visual Studio 2015 +/Visual Studio Code
• MSSQL Server

Difficulty

• Intermediate

Tutorial Contents

Let's start:
At the beginning we will create new project, it will be ASP.NET Core 2.0 Web API project.

If we want to create database and connect it with our application we have to create our Domain model. I will add new Folder to our application, named Domain, in this folder i will put 3 new classes:

• User
• Animal
• Role

This classes will be describing our objects, they look like this:

User:

public class User
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public string Password { get; set; }
        public Role Role { get; set; }
        public ICollection<Animal> Animals { get; set; }

        public User()
        {
            Animals = new List<Animal>();
        }
    }

Animal

public class Animal
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public string Kind { get; set; }
        public int Age { get; set; }
    }

Role

    public enum Role
    {
        Admin = 0,
        Animal = 1
    }

Ok, we have created our domain model. Main class is „User”, User has some properties witch describe his name, email, password. What is more User has Id. This id is unique in whole application, it wil be useful during save user to database, because each user must be unique in database. User has also collection of animals, witch it is one to many connection, and Role as enum.
We want to store in our database all data from our application, so we have to first download and add dependecies to Entity Framework Core. I recommend you to use Nuget package, it is very simple and fast. Let me show you, how to do it:

We have to add 2 package

• Microsoft.EntityFrameworkCore
• Microsoft.EntityFrameworkCore.SqlServer

After installing two EF package, we have to create new folder with two classes:

• Context.cs – in my case i will create just ApplicationContext.cs
• DbInitializer.cs

In the first class we will create DbSet of our domain models, this will be write in our database, what is more we will use FluentApi to manage our models, add maximum lenght, define primary Key, add foreign Key. Fluent API is very powerfull, you can use it alternatively with DataAnnotation, I prefer FluentAPI because my domain model have only properties and some domain functions, domain models are cleaner than using data annotation.
DbInitializer is class where we can define what data should be put into our database after create, it is great because after each creating new database we will have sample data for test.

ApplicationContext:

public class ApplicationContext: DbContext
    {
        public DbSet<User> Users { get; set; }
        public DbSet<Animal> Animals { get; set; }

        public AppContext(DbContextOptions< ApplicationContext > options) : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            SetupUser(modelBuilder);
            SetupAnimal(modelBuilder);
        }

        private void SetupUser(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>()
                .HasKey(x => x.Id);

            modelBuilder.Entity<User>()
                .HasIndex(u => u.Email)
                .IsUnique(true);

            modelBuilder.Entity<User>()
                .Property(u => u.Email)
                .IsRequired()
                .HasMaxLength(255);

            modelBuilder.Entity<User>()
                .Property(u => u.Password)
                .IsRequired()
                .HasMaxLength(255);

            modelBuilder.Entity<User>()
                .Property(u => u.Name)
                .IsRequired()
                .HasMaxLength(50);

            modelBuilder.Entity<User>()
                .HasMany(u => u.Animals)
                .WithOne(a => a.UserId);
        }

        private void SetupAnimal(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Animal>()
                .HasKey(x => x.Id);

            modelBuilder.Entity<Animal>()
                .Property(u => u.Age)
                .IsRequired();

            modelBuilder.Entity<Animal>()
                .Property(u => u.Kind)
                .IsRequired()
                .HasMaxLength(255);

            modelBuilder.Entity<Animal>()
                .Property(u => u.Name)
                .IsRequired()
                .HasMaxLength(50);
        }
    }

In DbInitializer we just add one user with one animal to create with new database

public static void Initialize(ApplicationContext context)
        {
            context.Database.EnsureCreated();

            if (context.Users.Any())
            {
                return;
            }

            var user = new User
            {
                Id = Guid.NewGuid(),
                Email = "[email protected]",
                Name = "User1",
                Password = "password",
                Role = Role.Admin,
                Animals = new List<Animal>
                {
                    new Animal
                    {
                        Id = Guid.NewGuid(),
                        Age = 5,
                        Name = "animal",
                        Kind = "dog"
                    }
                }
            };

            context.Users.Add(user);
            context.SaveChanges();
        }

Now we have to add connection string to appsetting.json

{
  "Logging": {
    "IncludeScopes": false,
    "Debug": {
      "LogLevel": {
        "Default": "Warning"
      }
    },
    "Console": {
      "LogLevel": {
        "Default": "Warning"
      }
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "Server=user;Database=AppDataBase;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

In Program.cs we have to add information about our context:

public static void Main(string[] args)
        {
            var host = BuildWebHost(args);

            using (var scope = host.Services.CreateScope())
            {
                var service = scope.ServiceProvider;
                try
                {
                    var context = service.GetRequiredService<ApplicationContext>();
                    DbInitializer.Initialize(context);
                }
                catch (Exception e)
                {
                    var logger = service.GetRequiredService<ILogger<Program>>();
                    logger.LogError(e, "Error while seeding database");
                }
            }

            host.Run();
        }

And finally in startup.cs:

public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<ApplicationContext>(options =>
                options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
            services.AddMvc();
        }

When we have created our domain model and Data folder with classes, changed program.cs and added connection string we have to create migration.
Go to package Manager Console and write enable-migration, and add-migrations
After this in our project will acure new folder with our migration:

At the end we have to create our new database, in Package Manager Console write update-database, thanks this comment in our SqlServer will be created new database with table and sample data

Curriculum

• Custom error handling
• Token authorization
• Email sending

---

Posted on Utopian.io - Rewarding Open Source Contributors

---