Finally, Entity Framework has support for databases other than SQL Server, and as many of us know, MVC 6 is cross-platform. This means we .NET devs can now launch our ASP.NET applications on a Linux server, saving huge on server costs and giving us some freedom. EF7 now supports SQL Server, Postgres, and SQLite, with Azure Table Storage and other non-relational data store support on the way. However, there is a noticeable lack of official MySQL support.
Why Postgres?
Linux
So why Postgresql? Well, for one, MySQL is not available and it doesn’t seem to be in the works (let me know if I’m wrong). So that makes Postgresql the only Linux-based full-featured database provider.
Strengths over SQL Server
A nameless author published an interesting blog comparing SQL Server and Postgres. There are a lot of strong arguments as to why Postgres is stronger, or at least comparable, to SQL Server. Technical reasons aside, Postgresql has zero licensing fees. 2nd Quandrant posted a related blog on this at with some valuable information.
JSON Support
Non-relational data support in your relational database. The best of both worlds! There isn’t direct EF7 LINQ support for this yet, but it is on the way. When it hits, this will solidify NPGSQL as my favourite provider.
Software Requirements
To get started, you’ll need the following:
1. The latest Visual Studio 2015 update and templates.
2. PostgreSQL
3. pgAdmin
Setting up your project
The following short steps will help you set up a Postgres database and update your ASP.NET application to use it as its default database connection.
1. Set up your database
Boot up pgAdmin and log in with the superuser credentials you supplied on installation. To create a database, drill into your localhost server, right click “Databases” and click “New Database”. Enter your database name and create it.
2. Set up your credentials
To create a user for your database, open up the query window for your localhost (the SQL icon in the icon bar). Run the following script:
create user {{username}} with password '{{password}}';
grant all privileges on database {{database name}} to {{username}};This will give your new user all the privileges on your new database.
3. Create your project
When you create your project, select the latest ASP.NET MVC 6 Web Application template. This will scaffold your user authentication models.
4. Add your npgsql dependencies
Here are all of the Entity Framework packages you’ll need (as of the date this blog was posted). So, go to your project.json file and replace the existing references with these:
"EntityFramework.Commands": "7.0.0-rc1-final",
"EntityFramework.Core": "7.0.0-rc1-final",
"EntityFramework.Relational": "7.0.0-rc1-final",
"EntityFramework7.Npgsql": "3.1.0-rc1-3",NuGet should auto-restore these (isn’t MVC6 package management so much nicer?).
5. Delete all files in the Migrations folder
The current templates automatically add a starter migration for your application. However, they are specific to SQL Server, so they have to be deleted. Otherwise, you won’t be able to build.
6. Update the EF7 Startup configuration
In Startup.cs, you should see the following code:
services.AddEntityFramework()
.AddSqlServer()
.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(Configuration["Data:DefaultConnection:ConnectionString"]));This sets up your EF7 context to work with the SQL Server provider. We don’t want that! Swap that code out with the following:
services.AddEntityFramework()
.AddNpgsql()
.AddDbContext<ApplicationDbContext>(options => options.UseNpgsql(Configuration["Data:DefaultConnection:ConnectionString"]));7. Update your DefaultConnection in your appsettings.json file
If you open up appsettings.json, you should see the following:
"Data": {
"DefaultConnection": {
"ConnectionString": "Server=(localdb)\\mssqllocaldb;Database={{dbname}};Trusted_Connection=True;MultipleActiveResultSets=true"
}
},Postgres connection strings are a little different, so replace with the following:
"Data": {
"DefaultConnection": {
"ConnectionString": "Host=localhost;Username={{username}};Password={{password}};Database={{database name}}"
}
},8. Create and run the initialization migration
We will need to jump to command prompt/PowerShell for this, so open it up and CD to the folder with your XPROJ file. Once you’re there, execute the following:
dnx ef migrations add init
dnx ef database updateThis will add your first migration with required ASP.NET Identity modeling and then run the migration against your database. After you’ve run these, refresh your server tree in pgAdmin and navigate to localhost -> Databases -> {{database name}} -> Schemas -> public -> Tables. You’ll see your new AspNet user tables there.
9. Run your application
That’s it! You’ve configured your application to use a Postgres database. You can register and look at your data in pgAdmin.
Have fun!
EF7 has a lot of changes, but for the most part, managing your context models is the same.
