How to use Postgres SQL, NSql and Entity Framework Core

Hello. I'll show you step by step how to work with relational and non-relational database at the same time with Postgres and support for EFCore.

Postgres

Postgres is an object-relational database owned by Oracle company, however it is an open source database with a strong community backing it. Postgres is incredibly robust and reliable, given its 30-year history in the market.

Data Types

Primitives: Integer, Numeric, String, Boolean
Structured: Date/Time, Array, Range / Multirange, UUID
Document: JSON/JSONB, XML, Key-value (Hstore)
Geometry: Point, Line, Circle, Polygon
Customizations: Composite, Custom Types

JSON/JSONB - Working with NSql

One of the wonders of the Postgres database is the possibility of working in a relational and non-relational way in the same database, in the same object, in the same table. YEA ! It is possible, through the Document data type that receives Json and JsonBinary (JsonB) data.

Entity FrameWork Core ORM

In order to be able to use EFcore in our methods using NSql, we need to install a nuget package called npgsql.entityframeworkcore.postgresql which will allow us to make the CRUD methods more similar to the EFCore ORM we are used to.

Prática - Config

Pacotes

npgsql.entityframeworkcore.postgresql

Microsoft.EntityFrameworkCore

Microsoft.EntityFrameworkCore.Design

Microsoft.EntityFrameworkCore.Toll

EFCore.NamingConventions

StartUp

ConfigureServices Method

services.AddDbContext<PostgresNsql.MyContext>(option => option.UseNpgsql(Configuration.GetConnectionString("Nsql")));

DBContext

public class MyContext : DbContext
    {
        public MyContext(DbContextOptions options) :base(options)
        {

        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder
            .UseNpgsql()
            .UseSnakeCaseNamingConvention();

        protected override void OnModelCreating(ModelBuilder modelBuilder)
            => modelBuilder
            .Entity<DataModel>()
            .Property(e => e.Id)
            .UseIdentityAlwaysColumn();

        public DbSet<DataModel> DataModels { get; set; }
    }

UseSnakeCaseNamingConvention() Snake Case may be needed to handle JSONs that separate 2 words with _ underscore. That way you won't have problems with serialization.

Setting auto-increment for ID column

.Property(e => e.Id)

 .UseIdentityAlwaysColumn();

Identity always column is one of the types of value assignment you can give, in this mode you define that the values will be generated directly in the database, making it impossible for you to set the value at the time of insertion into the database.

Model

In creating the model is the big leap in using SQL and NSql together. If you have unstable data yet and it's not 100% mappable and structured, you can use DataType Json to get the NSql part of your data.

For that, you need to add the System.Text.Json library and type its property with the JsonDocument class, as it will already identify that data will be received there in JSON and will facilitate de-cerealization.

The model needs to be IDisposable as the JsonDocument class is IDisposable too, improving memory management.

public class DataModel : IDisposable
    {
        public int Id { get; set; }
        public string Nome { get; set; }
        public int idade { get; set; }

        public JsonDocument Data { get; set; }

        public void Dispose() => Data?.Dispose();
    }

Estructured Data

To identify the NSql part of our model, we need to add the Data Notation [Column(TypeName = "jsonb")] to identify the Json object and map the properties.

public class DataModel
    {
        public int Id { get; set; }
        public string Nome { get; set; }
        public int idade { get; set; }

        [Column(TypeName = "jsonb")]
        public Endereco Address { get; set; }
    }

public class Endereco // Mapping the JSON Data received
{
    public string Rua { get; set; }
    public int Numero { get; set; }
    public Casa[] Casas { get; set; }
}

public class Casa 
{
    public decimal Preco { get; set; }
    public string EnderecoCompleto { get; set; }
}

CRUD

I didn't use a repository or some more complex architecture to make the article easier to understand.

But I will now show in Controller how we can work with JSON type data.

GetAll()

Note that nothing changes in the listing of all columns.

public List<DataModel> GetAll()
        {
            return _myContext.DataModels.ToList();
        }

GetCustom

A question I had when starting to work with Postgres SQL and NSql together is whether it could be done in a query, joining the SQL and NSql data. Below is an example of a Get where I parameterize that I only want results from 12 year olds AND with the last name equal to "Paixao". Age is an SQL value from the age column and "lastname" is an item of the data we receive in JSON.

public IEnumerable<string> Get()
        {
            var resultado = _myContext.DataModels;

            return resultado.Where(x => x.idade == 12 && x.Data.RootElement.GetProperty("lastName").GetString() == "Paixao").ToList();           
        }

Another example I brought is if you want to display only 1 of the data items in JSON.

public IEnumerable<string> Get()
        {
            var resultado = _myContext.DataModels;

return resultado.Select(x=>x.Data.RootElement.GetProperty("lastName").GetString()).ToList();
        }

Elements of the JsonElement class from the above code

RootElement is a class that will get the JSON data from the Data property allowing you to then name with the GetProperty("property") method which JSON property you will work with and finally you must put the GetString method () to convert the JSON data you filtered to string.