Notes on Integrating Postgres Entity Framework with DOT NET Core 2.0

First create a Core 2.0 app say dotnetmvcapp using

dot net new dotnetmvcapp

now we will follow online tutorial

https://www.youtube.com/watch?v=md20lQut9EE

I am just listing my files & its final content & Few important points.

dotnetmvcapp.csproj

<Project Sdk="Microsoft.NET.Sdk.Web">

<PropertyGroup>

<TargetFramework>netcoreapp2.0</TargetFramework>

</PropertyGroup>

<ItemGroup>

<PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.0" />

<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSql" Version="2.0.0" />

<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.0" />

<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.0.0" />

</ItemGroup>

<ItemGroup>

<DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.0" />

<DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" />

</ItemGroup>

</Project>

run restore packages

dotnet restore

Startup.cs

add below using directives first

public void ConfigureServices(IServiceCollection services)

{

services.AddMvc();

services.AddEntityFrameworkNpgsql()

.AddDbContext<dotnetmvcappContext>(opt

=> opt.UseNpgsql(Configuration.GetConnectionString("MyWebAppConnection")));

}

Here You Need to add Below using get read of error message at “ UseNpgsql” &

“dotnetmvcappContext”.

using dotnetmvcapp.Models;

using Microsoft.EntityFrameworkCore;

Create Model:

a) Create Models folder

b) Inside Models folder add

dotnetmvcappContext.cs

Users.cs

dotnetmvcappContext.cs

using Microsoft.EntityFrameworkCore;

namespace dotnetmvcapp.Models

{

public class dotnetmvcappContext : DbContext

{

public dotnetmvcappContext(DbContextOptions<dotnetmvcappContext> options) : base(options)

{

}

public DbSet<User> Users {get;set;}

}

}

Users.cs

namespace dotnetmvcapp.Models

{

public class User

{

public int Id{get;set;}

public string Name{get;set;}

public string Email{get;set;}

}

}

appsettings.json

{

"ConnectionStrings":{

"MyWebAppConnection":"User ID= xdba;Password=sangram;Server=localhost;Port=5432;Database=xplay;Integrated Security=true;Pooling = true"

},

"Logging": {

"IncludeScopes": false,

"LogLevel": {

"Default": "Warning"

}

}

}

Run Migration:

dotnet ef migrations add InitialMigration

It initializes our 'dotnetmvcappContext'

this Migration can be undone using

ef migrations remove

Apply Migration to Db:

dotnet ef database update

this will create a Users Table inside postgres which conforms to

CREATE TABLE public."Users"

(

"Id" integer NOT NULL DEFAULT nextval('"Users_Id_seq"'::regclass),

"Email" text COLLATE pg_catalog."default",

"Name" text COLLATE pg_catalog."default",

CONSTRAINT "PK_Users" PRIMARY KEY ("Id")

)

you can check postgre sql and confirm the User Table has been created.

Running direct SQL query from Entity Framework

First we will create a test table in our database

CREATE TABLE [dbo].[Address](

                [Id] [int] IDENTITY(1,1) NOT NUL primary keyL,

                [AddressLine1] [varchar](100) NULL,

                [AddressLine2] [varchar](100) NULL,

                [City] [varchar](100) NULL,

                [ZipCode] [varchar](15) NULL,

                [Phone] [varchar](15) NULL,

                [Mobile] [varchar](15) NULL

)

Create a console application ,Add Ado.Net entity model for database in which we have just created test table,while selecting tables make sure Address table is selected.

In my case database was Playground & context class generated is  PlaygroundEntities.

Inside your main we will add our code that will do native query on top of context class using ExecuteStoreQuery

Here is my code inside main

static void Main(string[] args)

        {

            PlaygroundEntities db = new PlaygroundEntities();

            //var query = from m in db.Addresses where m.City == "kankavali" select m;

           

            object[] parameters = { "1"};

            ObjectResult<ShortAddress> ads = db.ExecuteStoreQuery<ShortAddress>("select Id,AddressLine1 from Address where id= {0}", parameters);

            foreach (ShortAddress i in ads)

            {

                Console.WriteLine(i.AddressLine1);

                Console.WriteLine();

            }

           

            Console.ReadKey();

        }

As in select I am taking only 2 columns and address entity has more column we need to have one more class of whose type our result is.

That’s why we will add a simple class as follows

class ShortAddress

    {

        public int Id { get; set; }

        public string AddressLine1 { get; set; }

    }

time to insert some test records in Address table. Now we are ready to run console application and check output