(EFCore Best Practices) Set a SQL Server Project

Create an ASPNET Core Project

I shall be using Visual Studio. First of all create an empty ASPNET Core project. No folders are created for us. So we have to add a folder called Models to hold our model classes.

You will also have to install the nuget package for sql server and ef core.

PM> Install - Package Microsoft.EntityFrameworkCore.SqlServer

Get SQL Server Connection String

Open the SQL Server object explorer by using the menu View > SQL Server Object Explorer. Locate a running instance. In my case it is ProjectModels.

Right click to open the properties. Then locate connection string in the properties window. Copy this connection string. We will use it in the next steps.

Video Explanation (see it happen!)

Please watch the following youtube video:

Add Model classes

Right click the Models folder and add a class called Item. It adds a file called Item.cs. Double-click and open this file so that we can view the source code.

// Item.cs file in Models folder 

// PM> Install - Package Microsoft.EntityFrameworkCore.SqlServer 

using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;
using Microsoft.EntityFrameworkCore;

namespace ASPNETCoreApp.Models
{
  public class Item
  {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    // column for name property  
    public String Name { get; set; } = default!;

    public int Price { get; set; }
  }

  public class Invoice
  {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    [ForeignKey("ItemFKey")]
    [Required]
    public int ItemFK { get; set; }

    public Item ItemFKey { get; set; } = default!;

    public DateTime InvoiceDate { get; set; }
  }

  // dbcontext  
  public class MyProjectContext : DbContext
  {
    public MyProjectContext(DbContextOptions options) : base(options)
    {
      // IMPORTANT: you will get exception here  
      // if your SqlServer connection fails  
      Database.EnsureCreated();
    }

    // MUST be PLURAL  
    public DbSet<Item> Items { get; set; } = default!;

    public DbSet<Invoice> Invoices { get; set; } = default!;
  }
}

First we have the namespaces. Then we have added a model class called Item. This class contains Name and Price as properties.

We have also added an Invoice model to the same file to hold invoices. This model has been added so that we have a foreign key from the Item model. The primary key of the Item table is the foreign key here.

Lastly, we have the DbContext.

The constructor ensures that the database is created. If you get a runtime exception at this point, then you should check the connection string. We shall be adding this connection string in the program.cs file.

Lastly, we have added the Items and Invoices properties.

Program.cs File

Next let us complete the program.cs file.

First of all we have the namespaces. Then get the WebApplicationBuilder as a builder object.

using ASPNETCoreApp.Models;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// remove all logging to keep 
// things clear remove excess logs 
builder.Logging.ClearProviders();

builder.Services.AddRazorPages();

builder.Services.AddDbContext<MyProjectContext>(

    opt => opt.UseSqlServer(
                "Data Source=(localdb)\\ProjectModels;" +
                "Initial Catalog=mydb;Integrated Security=True;"
                )
             

  );

var app = builder.Build();

app.MapRazorPages();

app.Run();
  

Logging is important when we study and measure performance. But logging can become too heavy. So we can either set log level to none, or we can remove all log providers. I will take the second option.

So let's clear all loggin providers. We shall add them on need basis whenever required.

Add a service for razor pages - builder.Services.AddRazorPages();.

After this add the dbcontext service, and set the connection string. We could have stored this in the appsettings configuration file also, and used the GetConnectionString function to access it.

{
  "Logging": {
    "LogLevel": {
      "Default": "None",
      "Microsoft.AspNetCore": "None"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "sqlconnstr": "... your connstring ... "
  }
}

// access using builder.Configuration.GetConnectionString("sqlconnstr")) 

Finally, builder.Build gives us the web application.

MapRazorPages is called and the application is then run.

Seed the Database

Next we have to add some data to our database.

Open the solution explorer and add a folder called Pages and then a razor page called Index as you are seeing here.

Double click and open the index cshtml file. Let's examine the markup. First we have the three directives page, tag helpers and model. Then we shall show a feedback message in a mark tag so that we it appears colored. After this we have an anchor link connected to a handler called Seed.

// Pages -> Index.cshtml 

@page "{handler?}"

@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers

@model ASPNETCoreApp.Pages.IndexModel

<h1>Welcome!</h1>

<mark>
  @Model.Message
</mark>

<p>
  <a asp-page="Index" asp-page-handler="Seed">Click to Seed</a>
</p>

Next let us have a look at the backing class. Double-click and open it so that we can see the source code.

// Pages -> Index.cshtml.cs 

using ASPNETCoreApp.Models;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;

namespace ASPNETCoreApp.Pages
{
  public class IndexModel : PageModel
  {
    private readonly MyProjectContext _ctx;

    public IndexModel(MyProjectContext ctx)
    {
      _ctx = ctx;
    }

    public String? Message { get; private set; }

    public async Task OnGetSeedAsync()
    {
      if (await _ctx.Set<Item>().AnyAsync())
      {
        Message = "Already seeded.";

        return;
      }

      // add 10 items 
      for (int i = 0; i < 10; i++)
      {
        var item = new Item() { Name = $"Item_{i}", Price = i * 10 };

        _ctx.Items.Add(item);

      }

      await _ctx.SaveChangesAsync();

      // add 10 invoices for each item 
      foreach (Item item in _ctx.Items)
      {
        for (int i = 0; i < 10; i++)
        {
          _ctx.Invoices.Add(new Invoice()
          {
            InvoiceDate = DateTime.Today.AddDays(-i),
            ItemFK = item.ID
          });
        }
      }

      await _ctx.SaveChangesAsync();

      Message = "Done seeding!";

    }
  }
}

First we have the namespaces.

Then we have the backing class IndexModel. The constructor obtains the ProjectContext through dependency injection.

The property public String? Message is used to display a feedback message on the razor page.

The code for seeding is in the SeedAsync function that is connected to the anchor on the cshtml file that we saw just now.

An if condition checks if the Items table contains any records. If so, the message "Already seeded" is shown.

Next a loop is used to add 10 item records, and saves the changes to the database await _ctx.SaveChangesAsync();

After that a for-each loop runs and adds 10 invoices for each of the items added above. So it contains 100 invoices.

Finally, the changes are saved, and a message set as "Done seeding!".

Run the Project and Seed the Database

Now run the project and allow the home page to open.

First check the console window - it should be blank because we removed all loggers in the program.cs file.

Next click the "click to seed" button and allow the seeding to take place. If everything goes fine, you should see the message "Done Seeding!"

Next open the sql server object explorer and verify that the invoices and items tables contain their data.

In our next tutorial we shall start with the performance steps. Thanks!