I have one webapi in asp.net core 2.2 and i have huge database for LIKE and COMMENT so we decide to add another database like POSTGERSQL to manage only LIKE and COMMENT so we can reduce the database load on one database. here we are not thinking to microservice
right now. Parallel we are working on but mean time we have to manage current database load.
As Mikes said, we need to know either your are using or not an ORM.
Npgsql has an Entity Framework (EF) Core provider. It behaves like other EF Core providers (e.g. SQL Server), so the
general EF Core docs apply here as well. If you're just getting started with EF Core, those docs are the best place to start.
To use the Npgsql EF Core provider, add a dependency on Npgsql.EntityFrameworkCore.PostgreSQL. You can follow the instructions in the general
EF Core Getting Started docs.
Below is a .csproj file for a console application that uses the Npgsql EF Core provider:
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
namespace ConsoleApp.PostgreSQL
{
public class BloggingContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
public DbSet<Post> Posts { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder.UseNpgsql("Host=my_host;Database=my_db;Username=my_user;Password=my_pw");
}
public class Blog
{
public int BlogId { get; set; }
public string Url { get; set; }
public List<Post> Posts { get; set; }
}
public class Post
{
public int PostId { get; set; }
public string Title { get; set; }
public string Content { get; set; }
public int BlogId { get; set; }
public Blog Blog { get; set; }
}
}
Modify the ConfigureServices method in Startup.cs:
public IServiceProvider ConfigureServices(IServiceCollection services)
=> services.AddEntityFrameworkNpgsql()
.AddDbContext<BloggingContext>()
.BuildServiceProvider();
The Npgsql EF Core provider also supports reverse-engineering a code model from an existing PostgreSQL database ("database-first"). To do so, use dotnet CLI to execute the following:
dotnet ef dbcontext scaffold "Host=my_host;Database=my_db;Username=my_user;Password=my_pw" Npgsql.EntityFrameworkCore.PostgreSQL
Best regards,
Maher
Twitter : @maherjend
Blog : https://maherjendoubi.io
as your postgresql database is a relational, you will create a new dbcontext and repository for it. As you will need to do the joins with your own code, two repositories will be fine.
Dear sir,
Currently in this we are using EF with MSQL so parallel want to use postgre db with same EF. We don't want to completely discontinued mssql. All operations working same only for like and comments we add Postgre. I hope you got me .
Ok so as your code help can I add two or more dbcontext in service?
Like one for Mssql as already we have and also can add another like mongodb we already have same for Postgre
In my application i have sone like and comments feature like facebook so one post haa multiple rows and that have IDs of post and user ID and comments data that why I'm looking saprate database so that can release my Database load and I have another database
for like n comments so that why im thinking.
Plz suggest it's good for now or we have to think about any other solution.
Thanks
Ok so as your code help can I add two or more dbcontext in service?
If you would like to use two databases,you need to implement two dbContexts.
Register two contexts inConfigureServicesmethod using connection string keys from config file, or you can specify connection strings directly inside the method as arguments.
Then you can resolve necessary contexts in constructors.
With Regards,
Xing
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
49 Points
153 Posts
What is best way to add another Database like POSTGRESQL
Aug 30, 2019 10:35 AM|dharmeshsharma|LINK
Dear Friends,
I have one webapi in asp.net core 2.2 and i have huge database for LIKE and COMMENT so we decide to add another database like POSTGERSQL to manage only LIKE and COMMENT so we can reduce the database load on one database. here we are not thinking to microservice right now. Parallel we are working on but mean time we have to manage current database load.
here is my some configuration .
public void ConfigureServices(IServiceCollection services) { services.AddDbContext<DSObjectContext>(options => { options.UseSqlServer( Configuration.GetConnectionString("DefaultConnection")); }); services.AddHangfire(x => x.UseSqlServerStorage(Configuration.GetConnectionString("DefaultConnection"))); services.AddHangfireServer(); services.AddIdentity<IdentityUser, IdentityRole>() .AddEntityFrameworkStores<DSObjectContext>() .AddDefaultTokenProviders(); services.AddApiVersioning(options => { options.ReportApiVersions = true; options.AssumeDefaultVersionWhenUnspecified = true; options.DefaultApiVersion = new ApiVersion(1, 0); }); JwtSecurityTokenHandler.DefaultInboundClaimTypeMap.Clear(); services.AddAuthentication(options => { options.DefaultAuthenticateScheme = JwtBearerDefaults.AuthenticationScheme; options.DefaultScheme = JwtBearerDefaults.AuthenticationScheme; options.DefaultChallengeScheme = JwtBearerDefaults.AuthenticationScheme; }) .AddJwtBearer(options => { options.RequireHttpsMetadata = false; options.SaveToken = true; options.TokenValidationParameters = new TokenValidationParameters { ValidateIssuer = true, ValidateAudience = true, ValidateLifetime = true, ValidateIssuerSigningKey = true, ValidIssuer = Configuration["Jwt:Issuer"], ValidAudience = Configuration["Jwt:Issuer"], IssuerSigningKey = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(Configuration["Jwt:Key"])) }; }); services.AddTransient<IDbContext, DSObjectContext>(); services.AddScoped(typeof(IRepository<>), typeof(Repository<>)); services.Configure<MongoDBDatabaseSetting>(Configuration.GetSection(nameof(MongoDBDatabaseSetting))); services.AddSingleton<IMongoDBDatabaseSetting>(sp => sp.GetRequiredService<IOptions<MongoDBDatabaseSetting>>().Value); services.ConfigureApplicationServices(Configuration); // here add more services //services.AddResponseCompression(); services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2) .AddJsonOptions(options => options.SerializerSettings.ContractResolver = new DefaultContractResolver()); }In this configuration you can see MongoDb we have used. but not getting idea to add postgre so plz give some idea.
Thanks
Dharmesh Sharma
info@dharmeshsharma.com
www.dharmeshsharma.com
All-Star
190392 Points
27642 Posts
Moderator
Re: What is best way to add another Database like POSTGRESQL
Aug 30, 2019 03:40 PM|Mikesdotnetting|LINK
How do you plan to access the database? Plain ADO.NET? Dapper? EF Core?
Participant
1878 Points
1002 Posts
MVP
Re: What is best way to add another Database like POSTGRESQL
Aug 30, 2019 04:05 PM|maherjendoubi|LINK
Hello,
As Mikes said, we need to know either your are using or not an ORM.
Npgsql has an Entity Framework (EF) Core provider. It behaves like other EF Core providers (e.g. SQL Server), so the general EF Core docs apply here as well. If you're just getting started with EF Core, those docs are the best place to start.
Development happens in the Npgsql.EntityFrameworkCore.PostgreSQL repository, all issues should be reported there.
To use the Npgsql EF Core provider, add a dependency on
Npgsql.EntityFrameworkCore.PostgreSQL. You can follow the instructions in the general EF Core Getting Started docs.Below is a
.csprojfile for a console application that uses the Npgsql EF Core provider:<Project Sdk="Microsoft.NET.Sdk"> <PropertyGroup> <TargetFramework>netcoreapp2.2</TargetFramework> </PropertyGroup> <ItemGroup> <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="2.2.0" /> </ItemGroup> </Project>using System.Collections.Generic; using Microsoft.EntityFrameworkCore; namespace ConsoleApp.PostgreSQL { public class BloggingContext : DbContext { public DbSet<Blog> Blogs { get; set; } public DbSet<Post> Posts { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseNpgsql("Host=my_host;Database=my_db;Username=my_user;Password=my_pw"); } public class Blog { public int BlogId { get; set; } public string Url { get; set; } public List<Post> Posts { get; set; } } public class Post { public int PostId { get; set; } public string Title { get; set; } public string Content { get; set; } public int BlogId { get; set; } public Blog Blog { get; set; } } }Modify the
ConfigureServicesmethod inStartup.cs:public IServiceProvider ConfigureServices(IServiceCollection services) => services.AddEntityFrameworkNpgsql() .AddDbContext<BloggingContext>() .BuildServiceProvider();The Npgsql EF Core provider also supports reverse-engineering a code model from an existing PostgreSQL database ("database-first"). To do so, use dotnet CLI to execute the following:
Best regards,
Maher
Blog : https://maherjendoubi.io
All-Star
53594 Points
13328 Posts
Re: What is best way to add another Database like POSTGRESQL
Aug 30, 2019 06:52 PM|bruce (sqlwork.com)|LINK
Member
49 Points
153 Posts
Re: What is best way to add another Database like POSTGRESQL
Sep 01, 2019 05:58 PM|dharmeshsharma|LINK
Currently in this we are using EF with MSQL so parallel want to use postgre db with same EF. We don't want to completely discontinued mssql. All operations working same only for like and comments we add Postgre. I hope you got me .
Thanks
Dharmesh
info@dharmeshsharma.com
www.dharmeshsharma.com
Member
49 Points
153 Posts
Re: What is best way to add another Database like POSTGRESQL
Sep 01, 2019 06:00 PM|dharmeshsharma|LINK
Like one for Mssql as already we have and also can add another like mongodb we already have same for Postgre
info@dharmeshsharma.com
www.dharmeshsharma.com
All-Star
53594 Points
13328 Posts
Re: What is best way to add another Database like POSTGRESQL
Sep 01, 2019 06:07 PM|bruce (sqlwork.com)|LINK
not sure why you would use postgresql and mssql in the same project as they have the same features.
Member
49 Points
153 Posts
Re: What is best way to add another Database like POSTGRESQL
Sep 01, 2019 08:13 PM|dharmeshsharma|LINK
Plz suggest it's good for now or we have to think about any other solution.
Thanks
info@dharmeshsharma.com
www.dharmeshsharma.com
Contributor
2220 Points
732 Posts
Re: What is best way to add another Database like POSTGRESQL
Sep 02, 2019 02:09 AM|Xing Zou|LINK
Hi dharmeshsharma,
If you would like to use two databases,you need to implement two dbContexts.
Register two contexts in
ConfigureServicesmethod using connection string keys from config file, or you can specify connection strings directly inside the method as arguments.Then you can resolve necessary contexts in constructors.
With Regards,
Xing
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.