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.7
dotnet add package Npgsql --version 5.0.7
dotnet tool install --global dotnet-ef
updating: dotnet tool update --global dotnet-ef
dotnet 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 build
dotnet 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
English