Implementing Dotnet Core Entity Framework with PostgreSQL

Entity framework is a easy way to manage your DB connections and databases

Creating an app using dotnet core

dotnet new webapi -o entityFramework --no-https

Access the application’s path and install the packages

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL --version 5.0.7dotnet add package Npgsql --version 5.0.7dotnet tool install --global dotnet-efupdating: dotnet tool update --global dotnet-efdotnet add package Microsoft.EntityFrameworkCore.Design

Creating the model

using Microsoft.EntityFrameworkCore;using System;using System.ComponentModel.DataAnnotations;using System.ComponentModel.DataAnnotations.Schema;namespace entityFramework.Models{    [Index(nameof(Id))]    [Table("tb_user")]    public class User    {        [Key]        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]        [Column("id")]        public int Id { get; set; }        [Required]        [Column("email", TypeName = "varchar(256)")]        public string Email { get; set; }        [Required]        [Column("name", TypeName = "varchar(256)")]        public string Name { get; set; }        [Required]        [Column("address", TypeName = "varchar(2056)")]        public string Address { get; set; }        [Column("created")]        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]        public DateTime Created { get; set; }        [Column("updated")]        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]        public DateTime Updated { get; set; }            }}

Creating the DataContext

using Microsoft.EntityFrameworkCore;namespace entityFramework.Data{    public class UserServiceContext : DbContext    {        private readonly string _connectionString;        public UserServiceContext()        {        }        public UserServiceContext(DbContextOptions<UserServiceContext> options) : base(options)        {        }        public UserServiceContext(string connectionString)        {            _connectionString = connectionString;        }        public DbSet<Models.User> User { get; set; }    }}

Creating the class to get our connection string

using Microsoft.EntityFrameworkCore;using Microsoft.Extensions.Configuration;using Npgsql;namespace entityFramework.Data{    public class PostgresConnection    {        public string GetConnectionString { get; set; }        private IConfiguration Configuration { get; set; }                public PostgresConnection(IConfiguration configuration)        {            Configuration = configuration;            this.GetPostgresConnectionString();        }        private void GetPostgresConnectionString()        {            var builder = new NpgsqlConnectionStringBuilder            {                Host = Configuration.GetValue<string>("db_host"),                Database = Configuration.GetValue<string>("db_database"),                Username = Configuration.GetValue<string>("db_username"),                Password = Configuration.GetValue<string>("db_password")            };            GetConnectionString = builder.ConnectionString;        }    }}

Creating your class DataAccess to manipulate our database with options to use two BDs (Read and Write)

using entityFramework.Models;using Microsoft.EntityFrameworkCore;using Microsoft.Extensions.Configuration;using Microsoft.AspNetCore.Mvc;using System.Collections.Generic;using System.Linq;using System.Threading.Tasks;using System;namespace entityFramework.Data{    public class UserDataAccess    {        private DbContextOptions<UserServiceContext> _read;        private DbContextOptions<UserServiceContext> _write;        private IConfiguration Configuration { get; }        public UserDataAccess(IConfiguration configuration)        {            Configuration = configuration;        }        public async Task<ActionResult<IEnumerable<Models.User>>> GetById(int id)        {            var dbContext = new UserServiceContext(this.GetReadContextOptions(false));            if (!dbContext.Database.CanConnect())                dbContext = new UserServiceContext(this.GetReadContextOptions(true));            using (dbContext)            {                return await dbContext.User.Where(p => p.Id == id).ToListAsync();            }        }        public async Task<int> CreateUser(User item)        {            var dbContext = new UserServiceContext(this.GetWriteContextOptions(false));            if (!dbContext.Database.CanConnect())                dbContext = new UserServiceContext(this.GetWriteContextOptions(true));            using (dbContext)            {                dbContext.User.Add(item);                return await dbContext.SaveChangesAsync();            }        }        public async Task<int> UpdateUser(User item)        {            var dbContext = new UserServiceContext(this.GetWriteContextOptions(false));            if (!dbContext.Database.CanConnect())                dbContext = new UserServiceContext(this.GetWriteContextOptions(true));            using (dbContext)            {                var objEdited = await dbContext.User.Where(p => p.Id == item.Id).FirstOrDefaultAsync();                if (objEdited != null)                {                    objEdited.Address = item.Address;                    objEdited.Email = item.Email;                    objEdited.Name= item.Name;                    objEdited.Updated = DateTime.Now;                }                return await dbContext.SaveChangesAsync();            }        }        private DbContextOptions<UserServiceContext> GetReadContextOptions(bool force)        {            if (_read is null || force)                _read = this.GetOptionsBuilder("read").Options;            return _read;        }        private DbContextOptions<UserServiceContext> GetWriteContextOptions(bool force)        {            if (_write is null || force)                _write = this.GetOptionsBuilder("write").Options;            return _write;        }        private DbContextOptionsBuilder<UserServiceContext> GetOptionsBuilder(string strConnectionProfile)        {            return new DbContextOptionsBuilder<UserServiceContext>().UseNpgsql(new PostgresConnection(Configuration).GetConnectionString);        }    }}

Configuring our Startup.cs to use Enitity Framework Migrations and Updates

public void ConfigureServices(IServiceCollection services){    services.AddControllers();    services.AddDbContext<UserServiceContext>(options =>    {                options.UseNpgsql(new PostgresConnection(Configuration).GetConnectionString);            });            services.AddSingleton<UserDataAccess>();    }}

Finally we just need to fill our data to connect the database on the file appsettings.json

"db_host": "","db_username": "","db_database": "","db_password": ""

Voila! We just need to test our app migrations and data update

dotnet builddotnet ef migrations add FirstMigration

If everything is right we gonna have a path with the files created to update our DB

No we just need to push our migrations to our DB

dotnet ef database update

If everything is ok we gonna receive the message

Perfect! our app is ready and connected to our BD!, let’s just check our DB if the tables are created

Download code: https://github.com/rondweb/entityframeworkwithpostgresql