Hello everyone in my second article in .NET Core on (NO)Windows series. This time I would like to show you how you can in easy way create a connection between PostgreSQL database and your application with using Entity Framework Core.

A project creation and packages adding

First of all, create new solution and add to it new Web API project with using below commands

 
dotnet new sln –n EntityFrameworkCore 
dotnet new webapi –n EntityFrameworkCore.Web 
dotnet sln EntityFrameworkCore.sln add EntityFrameworkCore.Web/EntityFrameworkCore.Web.csproj 

After this operation, it should look like in below way

Now you can open the project in Visual Studio Code editor or in any other text editor (if you have any issues with .NET Core CLI basics, please check my last posts).

The next step is to add references to EntityFramework.Web.csproj file. My file looks like on below listing

 
<Project Sdk="Microsoft.NET.Sdk.Web">
  <PropertyGroup>
    <TargetFramework>netcoreapp1.1</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <Folder Include="wwwroot\" />
  </ItemGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore" Version="1.1.1" />
    <PackageReference Include="Microsoft.AspNetCore.Mvc" Version="1.1.2" />
    <PackageReference Include="Microsoft.Extensions.Logging.Debug" Version="1.1.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="1.1.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="1.1.2" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="1.1.0" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL.Design" Version="1.1.0" />
  </ItemGroup>
  <ItemGroup>
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="1.0.1" />
  </ItemGroup>
</Project>

We added a few packages

• entity framework core packages
• to manage PostgreSQL database
• dotnet ef tool which allows to create migration

And the last important step is installing Nuget packages. In main directory execute command dotnet restore

Model and database context creation

In this example, I will add a few classes. The classes uses the two most popular relations – one to many and many to many.

At the beginning create please create below files in Models directory:
Product.cs, Employee.cs, Shop.cs, Position.cs w katalogu Models
It should look like on the below picture

As you can see on the diagram there was created Shop class which has relations to Product class and Employee class. Between Shop class and Employee class exist relation 1:N (a shop has many employees, and an employee has only one shop).
Between Shop class and Product class exist relation N:N (a shop has many products, and products has many shops).

Let’s move to described relations.

1. One to many relation

   It’s simple relation with using entity framework core. So, we can start from this point.

   First, open Employee.cs file and please add two below properties

    
   public int ShopId { get; set; }
   public Shop Shop { get; set; }
   

   The properties say that there is only one Shop for Employee.

   If you want to create many to many relation just add objects collection in Shop.cs file, like below.

    
   public ICollection<Employee> Employees {get; set; }
   

2. Many to many relation

   It’s the second relation kind which will be described by me. So, there is not easy like in classic Entity Framework because EF Core doesn’t support this type of relation. We need to create a proxy class – ShopProduct.cs (help table).

   It’s a Models directory at this moment

   

   Let’s move to ShopProduct.cs file and fill it like on the below picture

   

   The next step is creating relation between ShopProduct and Employee and Product.

   

   There is necessary DbEntities class if we want to finish creating N:N relation. So, please create DbEntities.cs file and just add db sets.

   

   As you can see I didn’t add DbSet collection because it’s only our help class and I don’t think so it’s required in the simple example. But if you want have option to directly invoke ShopProduct table fell free to add it in this place. Now we need to override OnModelCreating method and tell how generator should create many to many relation.

    
       public class DbEntities : DbContext
       {
           public DbEntities(DbContextOptions<DbEntities> options) : base(options)
           {
   
           }
           protected override void OnModelCreating(ModelBuilder modelBuilder)
           {
               modelBuilder.Entity<ShopProduct>()
                           .HasKey(entity => new { entity.ProductId, entity.ShopId });
   
               modelBuilder.Entity<ShopProduct>()
                           .HasOne(sp => sp.Product)
                           .WithMany(p => p.ProductShops)
                           .HasForeignKey(sp => sp.ProductId);
   
               modelBuilder.Entity<ShopProduct>()
                            .HasOne(sp => sp.Shop)
                            .WithMany(s => s.ShopProducts)
                            .HasForeignKey(sp => sp.ShopId);
           }
   
           public DbSet<Employee> Employees { get; set; }
           public DbSet<Product> Products { get; set; }
           public DbSet<Shop> Shops { get; set; }
          // public DbSet<ShopProduct> ShopProducts { get; set; }
       }
   

Migrations with EF tools

The last step is to create our models structure on relation database side.

First, let’s define connection string in appsetting.json file

Second, assign db context in Startup.cs file

There is everything what we need to create migration. Open terminal in directory where you can find EntityFrameworkCore.Web.csproj file and execute below commands

1. dotnet ef migrations add InitialMigration

   

2. dotnet ef database update

   

Summary

In the article I showed how you can prepare .NET Core project, how you can create context which contains 1:N and N:N relation and the way to create migration and database update using dotnet ef tool.

I hope the article will be helpful for beginners and I’m waiting for your feedback. Have fun with .NET core .

GIT repository: https://github.com/artzie92/dotnetowo_ef_core_postgre