Notes from building a first ASP.Net Core App (part 9)

I now want to connect my application to the database. The Entity Framework has been completely re-written for Dot Net Core. Larger ORMs such as Entity Framework and nHibernate are great ideas, especially with their integration with LINQ, because they allows you to write SQL-like syntax in your middle-tier, instead of manipulating SQL strings. This means that they essentially shift any syntax errors that would normally be found at runtime back to compile time, and anyone that has studied the benefits of discovering bugs earlier in the development life-cycle knows that the earlier you find an error in the process, the cheaper it is to correct.

That’s all well and good, except that the performance price for doing this has sometimes been a factor of 10x. That is, queries that would take 500ms in Entity Framework have taken only 50ms in Dapper, a streamlined, cut down ORM (known as a Micro-ORM), and marginally less with a straight Sql Data Reader (perhaps around 48ms).

So many people, especially people that code for high performance sites, have moved away from Entity Framework and are now using Micro-ORMs, such as Dapper.

And as I’ve suggested above, the downside is that Micro-ORMs such as Dapper force us to return to using strings, which returns us to the position of potentially only discovering errors in SQL at runtime. To counteract this, developers must absolutely write a lot of tests to ensure this situation does not occur.

But for now, I want to see how to get the new Entity Framework to work, so I will set up the application to perform some work, then later on perhaps I will run a few tests comparing the new Entity Framework to Dapper.

So next, I need to install Entity Framework Core edition. And I want to configure it for Database First Migrations.

  1. Open up Package Manager Console, and run the following commands:
    Install-Package Microsoft.EntityFrameworkCore.SqlServer
    Install-Package Install-Package Microsoft.EntityFrameworkCore.Tools –Pre
    

    Note the Pre in the second command. That is because the Tools for the Entity Framework Core actually haven’t made it to RTM yet. They are still working on them. But in the meanwhile, we can still use the pre-release version. The Tools part is what gives us Migrations in the Package Manager Console.

  2. Some modifications are needed to the project.json file. Make the following additions to the project.json file, found in the root folder.
    {
      "dependencies": {
        "Microsoft.NETCore.App": {
          "version": "1.0.0",
          "type": "platform"
        },
        "Microsoft.AspNetCore.Diagnostics": "1.0.0",
        "Microsoft.AspNetCore.Server.IISIntegration": "1.0.0",
        "Microsoft.AspNetCore.Server.Kestrel": "1.0.0",
        "Microsoft.Extensions.Logging.Console": "1.0.0",
        "Microsoft.Extensions.Configuration.FileExtensions": "1.0.0",
        "Microsoft.Extensions.Configuration.Json": "1.0.0",
        "Microsoft.AspNetCore.StaticFiles": "1.0.0",
        "Microsoft.AspNetCore.Mvc": "1.0.0",
        "Microsoft.EntityFrameworkCore.SqlServer": "1.0.0",
        "Microsoft.EntityFrameworkCore.Design": {
          "type": "build",
          "version": "1.0.0-preview2-final"
        },
        "Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final"
      },
    
      "tools": {
        "Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final",
        "Microsoft.AspNetCore.Server.IISIntegration.Tools": "1.0.0-preview2-final"
      },
    
  3. Now we need a Database Context object. I created new Folder called DBML in the root of the project, and added a new class called HotelDbContext.
    my-first-asp-net-core-app-folder-structure-4
  4. Inside the HotelDbContext, I have inherited from the Entity Framework’s DbContext class, set up a constructor to pass in options such as the connection string, and made a reference to the Hotels DbSet. This is how EntityFramework knows that the Hotel class is what it wants to generate database objects for.
    namespace MyFirstAspNetCoreApp.DBML
    {
        public class HotelDbContext : DbContext
        {
            public HotelDbContext(DbContextOptions<HotelDbContext> options) : base(options)
            {
            }
            public DbSet<Hotel> Hotels { get; set; }
        }
    }
    
  5. The next task is to create the new Sql Hotel Data Service. We will switch the Hotel Data Service from the Dummy Hotel Data Service to the Sql Hotel Data Service.
    In the services folder, I have created a new IHotelDataService.cs file. I have taken the original IHotelDataService.cs interface and have now put that into the IHotelDataService.cs file, removing it from DummyHotelData.cs. Then I added a new class, which also inherits from IHotelDataService, as SqlHotelData. The Services folder now looks like this:
    my-first-asp-net-core-app-folder-structure-5
  6. Here is the new code for SqlHotelData:
    using System.Collections.Generic;
    using MyFirstAspNetCoreApp.Models;
    using MyFirstAspNetCoreApp.DBML;
    
    namespace MyFirstAspNetCoreApp.Services
    {
        public class SqlHotelData : IHotelDataService
        {
            private HotelDbContext context;
    
            public SqlHotelData(HotelDbContext context)
            {
                this.context = context;
            }
            public IEnumerable<Hotel> GetAll()
            {
                return this.context.Hotels;
            }
        }
    }
    

    Note that the SqlHotelData constructor takes a HotelDbContext object as an argument. This is passed in when the class is instantiated. I have then used that context object to obtain the list of Hotels in the GetAll() method.

  7. Back to configuration, and I have discovered that application settings are now meant to be stored in a file called appsettings.json. It says so in a comment the web.config file. So I have renamed my custom configuration file to appsettings.json.my-first-asp-net-core-app-folder-structure-6
  8. This requires a change in the Startup.cs file. Modify the Startup constructor to point to the correct settings json file:
    public Startup()
    {
        Configuration = new ConfigurationBuilder()
                       .SetBasePath(Directory.GetCurrentDirectory())
                       .AddJsonFile("appsettings.json")
                       .Build();
    }
    
  9. While we’re here, we may as well set the data context and redirect from the Dummy data source to the Sql data source. In the Configure Services method, comment out the old Dummy line and introduce the new Sql line:
    public void ConfigureServices(IServiceCollection services)
    {
       services.AddMvc();
       services.AddSingleton<IMyCustomConfiguration, MyCustomConfiguration>();
       services.AddSingleton(implementationFactory => Configuration);
    
       //services.AddScoped<IHotelDataService, DummyHotelData>();
       services.AddScoped<IHotelDataService, SqlHotelData>();
    }
    
  10. Now the reason I needed to fix up the appsettings file was that this is where I want to add in the database connection string. Go to the appsettings.json file and add a new connection string for the Hotel Database.
    {
      "my-custom-message": "The quick brown fox!",
      "ConnectionStrings": {
        "HotelDatabaseConnection" :  "Data Source=(localdb)\\mssqllocaldb;Initial Catalog=MyHotel"
      }
    }
    
  11. To wire this up and add the DbContext in, go back to the ConfigureServices method and add a services.AddDbContext line, as follows:
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddMvc();
        services.AddSingleton<IMyCustomConfiguration, MyCustomConfiguration>();
        services.AddSingleton(implementationFactory => Configuration);
    
        services.AddDbContext<HotelDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("HotelDatabaseConnection")));
        //services.AddScoped<IHotelDataService, DummyHotelData>();
        services.AddScoped<IHotelDataService, SqlHotelData>();
    }
    
  12. Now for the fun bit: Migrations. Because we’ve installed the Entity Framework Core tools and added the “Microsoft.EntityFrameworkCore.Design” dependency in the project.json file, we should now be able to run the migrations from the Package Manager Console window. Open up the Package Manager Console and type:
    Add-Migration MyFirstMigration
    

    If successful, it should show the line “To undo this action, use Remove-Migration.” If you look in you project, there should now be some generated migration code under a new folder called Migrations. I won’t go into it, but feel free to take a look at this.

  13. Finally, to get he migration to execute, go to the Package Manager Console and enter:
    Update-Database
    

    After a bit, it should respond with “Done.”

  14. Go to the View menu in Visual Studio 2015, and select “SQL Server Object Explorer”
    my-first-asp-net-core-app-sql-server-structure
  15. You should see the results above. But we’re not ready to execute it just yet – there is no data. Right click on the dbo.Hotels table and click View Data. We will insert the data there.
    my-first-asp-net-core-app-sql-server-data-entry
  16. Now run the application (F5), and you should see the new data appear in the browser:
    my-first-asp-net-core-app-updated-data
  17. And that’s it! Hopefully I haven’t missed anything. Post a comment if I have and I’ll try to be responsive and fix it pronto.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: