Blogs Hub

by AskGif | Mar 21, 2019 | Category :coding

Learning Postgresql (npgsql), Entity Framework 7 (EF Core), and ASP.NET MVC 6

Learning Postgresql (npgsql), Entity Framework 7 (EF Core), and ASP.NET MVC 6

<p>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.</p> <p>&nbsp;</p> <p>Why Postgres?</p> <p>Linux</p> <p>So why Postgresql? Well, for one, MySQL is not available and it doesn&rsquo;t seem to be in the works (let me know if I&rsquo;m wrong). So that makes Postgresql the only Linux-based full-featured database provider.</p> <p>&nbsp;</p> <p>Strengths over SQL Server</p> <p>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.</p> <p>&nbsp;</p> <p>JSON Support</p> <p>Non-relational data support in your relational database. The best of both worlds! There isn&rsquo;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.</p> <p>&nbsp;</p> <p>Software Requirements</p> <p>To get started, you&rsquo;ll need the following:</p> <p>1. The latest Visual Studio 2015 update and templates.</p> <p>2. PostgreSQL</p> <p>3. pgAdmin</p> <p>&nbsp;</p> <p>Setting up your project</p> <p>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.</p> <p>&nbsp;</p> <p>1. Set up your database</p> <p>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 &ldquo;Databases&rdquo; and click &ldquo;New Database&rdquo;. Enter your database name and create it.</p> <p>&nbsp;</p> <p>2. Set up your credentials</p> <p>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:</p> <pre class="language-markup"><code>create user {{username}} with password '{{password}}'; grant all privileges on database {{database name}} to {{username}};</code></pre> <p>This will give your new user all the privileges on your new database.</p> <p>&nbsp;</p> <p>3. Create your project</p> <p>When you create your project, select the latest ASP.NET MVC 6 Web Application template. This will scaffold your user authentication models.</p> <p>&nbsp;</p> <p>4. Add your npgsql dependencies</p> <p>Here are all of the Entity Framework packages you&rsquo;ll need (as of the date this blog was posted). So, go to your project.json file and replace the existing references with these:</p> <pre class="language-markup"><code>"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",</code></pre> <p>NuGet should auto-restore these (isn&rsquo;t MVC6 package management so much nicer?).</p> <p>&nbsp;</p> <p>5. Delete all files in the Migrations folder</p> <p>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&rsquo;t be able to build.</p> <p>&nbsp;</p> <p>6. Update the EF7 Startup configuration</p> <p>In Startup.cs, you should see the following code:</p> <pre class="language-csharp"><code>services.AddEntityFramework() .AddSqlServer() .AddDbContext&lt;ApplicationDbContext&gt;(options =&gt; options.UseSqlServer(Configuration["Data:DefaultConnection:ConnectionString"]));</code></pre> <p>This sets up your EF7 context to work with the SQL Server provider. We don&rsquo;t want that! Swap that code out with the following:</p> <pre class="language-csharp"><code>services.AddEntityFramework() .AddNpgsql() .AddDbContext&lt;ApplicationDbContext&gt;(options =&gt; options.UseNpgsql(Configuration["Data:DefaultConnection:ConnectionString"]));</code></pre> <p>7. Update your DefaultConnection in your appsettings.json file</p> <p>If you open up appsettings.json, you should see the following:</p> <pre class="language-csharp"><code>"Data": { "DefaultConnection": { "ConnectionString": "Server=(localdb)\\mssqllocaldb;Database={{dbname}};Trusted_Connection=True;MultipleActiveResultSets=true" } },</code></pre> <p>Postgres connection strings are a little different, so replace with the following:</p> <pre class="language-csharp"><code>"Data": { "DefaultConnection": { "ConnectionString": "Host=localhost;Username={{username}};Password={{password}};Database={{database name}}" } },</code></pre> <p>8. Create and run the initialization migration</p> <p>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&rsquo;re there, execute the following:</p> <pre class="language-markup"><code>dnx ef migrations add init dnx ef database update</code></pre> <p>This will add your first migration with required ASP.NET Identity modeling and then run the migration against your database. After you&rsquo;ve run these, refresh your server tree in pgAdmin and navigate to localhost -&gt; Databases -&gt; {{database name}} -&gt; Schemas -&gt; public -&gt; Tables. You&rsquo;ll see your new AspNet user tables there.</p> <p>&nbsp;</p> <p>9. Run your application</p> <p>That&rsquo;s it! You&rsquo;ve configured your application to use a Postgres database. You can register and look at your data in pgAdmin.</p> <p>&nbsp;</p> <p>Have fun!</p> <p>EF7 has a lot of changes, but for the most part, managing your context models is the same.</p> <p>&nbsp;</p>

read more...