Implement a Relational Database With ASP.NET Core

Prepare Your Data

In this chapter, you’re going to learn about executing SQL queries on your database using two different methods:

• Raw SQL strings executed from C#.

• Object queries using LINQ.

There’s just one problem. Your database, which you’ve designed and built very nicely, is empty. So there’s nothing to query! Now, we could go crazy with this project and do the full MVC treatment - scaffold views and controllers, add a bootstrap template, really dress it up and make it usable. But that’s not really what this course is about.

Our focus here is the database, not the entire application. So if you have the time and the desire, feel free to add the controllers and views to this project later. Finish it out if you like. As for this course, we’re only going to focus on the work necessary to demonstrate these two querying methods. But first, we need some data!

Our queries are pointless if there are no recipes. So, I’ve prepared some data that you can import into your database. However, I may have added a few things to the model to make it more interesting. Let's look at how to change your model, so this data will import properly, then I’ll show you how to import it into your database.

Update the Model

Let’s look at the original design:

The first thing that jumps out is the three objects on the left. Aside from the name of the objects, they’re all identical. That’s because they’re just lookup tables. Sometimes you can use an enum data type for things like this, but that’s only a good idea if your list of enumerated items is finite. If you might need to add to it in the future, it’s better to use a lookup table for that entity in your database.

With that in mind, you can get rid of some redundancy in the code by adding a new abstract class that you can extend with these other three. How about something like this?

public abstract class Lookup
{
    public int Id { get; set; }
    public string Name { get; set; }
}
 
public class FoodType : Lookup { }
 
public class FoodEthnicity : Lookup { }
 
public class UnitOfMeasure : Lookup { }

This is a nice change that makes it a lot easier to create additional lookup tables if necessary later on. Go ahead and make this change to your own code.

Next, depending on where you live, your recipes might use the imperial system of measurement, or they might use the metric system. It wouldn’t be very sensitive to build an app with only one system of measurement. So we need a way to set the measurement system on a per-recipe basis. Here’s one way you might do this:

1. First, add a couple of  enum  data types to represent the measurement systems and also the temperature types for those systems. You can do this is in any of the code files for your model classes. I put mine just below my  Lookup  class:

public abstract class Lookup
{
    public int Id { get; set; }
    public string Name { get; set; }
}
 
public enum MeasurementType { Imperial, Metric }
public enum TemperatureUnit { Celcius, Fahrenheit }

2. Next, add a  MeasurementType  property to two model classes:  UnitOfMeasure  and  Recipe. For  UnitOfMeasure, add a default constructor as well as a constructor that accepts a  MeasurementType  value to set the type for the object:

public class UnitOfMeasure : Lookup
{
    public UnitOfMeasure() : base() { }
    public UnitOfMeasure(MeasurementType m) : base()
    {
        this.MeasurementType = m;
    }
    public MeasurementType MeasurementType { get; set; }
}

3. For the  Recipe  class, add one property for  MeasurementType:

public class Recipe
{
    public int Id { get; set; }
    public string Name { get; set; }
    public MeasurementType MeasurementType { get; set; }
    public int BakeTemperature { get; set; }
    public int BakeTime { get; set; }
    public string Instructions { get; set; }
    public int FoodTypeId { get; set; }
    public int FoodEthnicityId { get; set; }
 
    public virtual FoodType FoodType { get; set; }
    public virtual FoodEthnicity FoodEthnicity { get; set; }
    public virtual ICollection<RecipeIngredient> RecipeIngredients { get; set;}
}

4. Finally, make a few items nullable, in case some recipes have value for these items. For example, in the data you’re going to import, there is a recipe for some no-bake cookies. If you’re not going to bake them, then there’s not going to be a  BakeTemperature  or a  BakeTime. I believe these are the only two properties that need to be nullable, aside from the  UnitOfMeasureId in the  Ingredient  class, which was already nullable in the initial design. So go ahead and make this change to the  Recipe  class, making  BakeTemperature  and  BakeTime  nullable integers.

public class Recipe
{
    public int Id { get; set; }
    public string Name { get; set; }
    public MeasurementType MeasurementType { get; set; }
    public int? BakeTemperature { get; set; }
    public int? BakeTime { get; set; }
    public string Instructions { get; set; }
    public int FoodTypeId { get; set; }
    public int FoodEthnicityId { get; set; }
 
    public virtual FoodType FoodType { get; set; }
    public virtual FoodEthnicity FoodEthnicity { get; set; }
    public virtual ICollection<RecipeIngredient> RecipeIngredients { get; set;}
}

Update the Database

Whenever you make a change to a model class, it no longer matches the database exactly, so the ORM can’t do its work properly. If you try to access the database before you’ve updated it to match the new model changes, you’ll get an error message telling you that the database context has changed, is no longer valid, and that you must perform a new migration.

Let’s take care of this now:

1. Add a new migration just like you added the initial migration. You can call it anything you like. I called mine ModelUpdate1.

2. When the migration is ready, update the database.

3. When the update is complete, import the data.

Import the Data

Ensure that you have installed SQL Server Express on your machine. If you have, you will find a folder for Microsoft SQL Server 2017 in your applications list when you click the Windows Start button. If it is not there, install it now from Microsoft.

The data you need is held in the GitHub repository for this course, and you will need to download the following files before you can import the test data into your database: 

• FoodEthnicities.csv

• FoodTypes.csv

• UnitsOfMeasure.csv

• Recipes.csv

• Ingredients.csv

• RecipeIngredients.csv

Each file contains the data for the table of the same name. So for each file in the list, you must complete the following steps to perform the import:

1. Under the Microsoft SQL Server 2017 folder in your applications list, select SQL Server 2017 Import and Export Data (64-bit). (If you have installed the 32-bit version of SQL Server instead of the 64-bit version, select the 32-bit version of this tool instead).

2. At the Welcome screen, click Next. 

3. On the next screen (Choose a Data Source), select Flat File Source from the Data source dropdown, then browse and select the desired file. 

   

4. Click Next.

5. The next screen shows a preview of the data you will be importing.  

   

6. Click Next.

7. Now you need to choose your destination database. From the Destination dropdown, select .NET Framework Data Provider for SQL Server. Then, in the large box below the Destination dropdown, locate the Data heading and the ConnectionString box. 

   

8. Copy the connection string from your application’s appsettings.json file and paste it in this box. My connection string looks like this:  
   Server=(localdb)\\mssqllocaldb;Database=MyRecipes;Trusted_Connection=True;MultipleActiveResultSets=true; 
   If there are any double slashes in your connection string (as you can see in the Server section of mine) after you paste it, remove one of them, then click Next.

9. At the next screen, click Next.

10. Click Finish on the next two screens. The import will now run. Repeat this process for the rest of the downloaded files.

Query a Database With SQL in C#

Now that you have data in your database, it’s time to learn how to query it. The first thing you need to do is get access to your database context object. The object is created when the application starts, in the  ConfigureServices  method of the Startup.cs file.

public void ConfigureServices(IServiceCollection services)
{
    services.Configure<CookiePolicyOptions>(options =>
    {
        options.CheckConsentNeeded = context => true;
        options.MinimumSameSitePolicy = SameSiteMode.None;
    });

    services.AddDbContext<ApplicationDbContext>(options =>
        options.UseSqlServer(
            Configuration.GetConnectionString(
                "DefaultConnection")));

    services.AddDefaultIdentity<IdentityUser>()
        .AddDefaultUI(UIFramework.Bootstrap4)
        .AddEntityFrameworkStores<ApplicationDbContext>();

    services.AddMvc().SetCompatibilityVersion(
        CompatibilityVersion.Version_2_2);
}

Note the reference here to  DefaultConnection. This is the name of the connection string that points to your database in the appsettings.json file. When you set up additional connection strings, such as to an online database on Azure, you can redirect your application to the desired database by changing the name referenced here.

To receive an instance of the database context object in a controller, you can add it as a parameter in the controller’s constructor. If you have already scaffolded your model classes, this is done for you. If you create empty controllers, you may need to add the context injection. The following example shows how the database context is injected into the  HomeController. 

public class HomeController : Controller
{
    private readonly ApplicationDbContext _context;

    public HomeController(ApplicationDbContext context)
    {
        _context = context;
    }

    public IActionResult Index()
    {
        return View();
    }

    ...
}

You can now use  _context  anywhere in the  HomeController  to query and modify the database.

Let’s suppose you want to immediately grab a list of your recipes as soon as the app and home page open. You can do this by executing a raw SQL query on your context object. Access the object and its table references through LINQ, and then execute the SQL query as demonstrated here.

var myRecipes = _context.Recipes.FromSql(
    "SELECT * FROM dbo.Recipes").ToList();

Notice that the  FromSql  method accepts a string containing the query string you wish to execute. You must parameterize the query when passing any user data in a query string. Let’s assume you want to find all the recipes in which the name contains a search string entered by the user: 

// let queryStr = user-provided query string 

var myRecipes = _context.Recipes.FromSql(
    "SELECT * FROM dbo.Recipes WHERE [Name] LIKE ‘%{0}%’", queryStr
).ToList();

The use of curly braces within the SQL string indicates the use of parameterized input. The number inside the braces is the zero-based index indicating the parameter number (i.e., first, second, third) based on the entry order following the string. In this case,  queryStr  is the first parameter in the list and is, therefore, parameter  0.

Parameterizing your query strings in this fashion prevents SQL Injection attacks as a result of the user attempting to insert SQL command into the user input.

You’ve seen examples demonstrating how to execute queries that retrieve the entire list of recipes as well as ones that contain a user-provided string. Now it’s time to get some practice!

Coding Exercise: Execute SQL Queries

Before you get started with queries, add the context injection code to your  HomeController  (if you haven’t done it yet), and then add the following using statements as well: 

using MyRecipes.Models;
using MyRecipes.Data;
using Microsoft.EntityFrameworkCore;

Now let’s see what you remember about SQL. Using the following example as a reference, write individual methods in your  HomeController  for executing each of the queries listed in this exercise.

Example

[HttpGet]
public JsonResult GetAllRecipes()
{
    var recipes = _context.Recipes.FromSql(
        "SELECT * FROM dbo.Recipes").ToList();
    return Json(recipes);
}

Queries

1. All recipes whose bake time is less than 30 minutes.

2. All recipes whose name includes “BBQ.”

3. All recipes whose bake temperature is greater than or equal to 350 degrees Fahrenheit.

4. All recipes whose instructions contain the words “whip” and “fold.”

5. All recipes whose ethnic origin is Italian.

6. All dinner recipes.

When you’ve completed these, test them by running your application and entering the URL for each method in your browser (i.e., http:\\your_domain\Home\GetAllRecipes).

In the next section, we’ll look at the LINQ equivalents of these queries to show the similarities and differences in the query structure.

Query a Database With LINQ in C#

LINQ, an acronym for Language-Integrated Query, is an object-based means of querying a database through the database context object. 

Let’s jump right into this. Remember those two SQL queries I showed you in the last section? Let’s look at how these might be constructed using LINQ. Then we’ll break down the execution, so you can better understand what’s taking place.

var myRecipesLINQ = _context.Recipes.ToList();

In this example, begin the same way as before, but this time you don’t need to define the query. Since you are requesting the contents of the Recipes table, indicate the table name followed by  .ToList(). This converts the result of the query to an easily managed List object containing all of the recipes.

If you want to accept user input and find the recipes  containing the search terms provided (as in the previous example), the LINQ query would look like this:

// let queryStr = user-provided query string

var myRecipesLINQ2 = _context.Recipes.Where(x =>
    x.Name.Contains(queryStr)).ToList();

Notice the  Where  method we’re calling within the  Recipes  object. This is the equivalent of the  WHERE  keyword in SQL. As you become familiar with LINQ, you’ll find that many of the methods available through the database context correspond almost directly to their SQL counterparts. One difference is noted above. Did you catch it?

The object  x.Name,  which indicates the  Name  attribute of the  Recipe  object, uses a  Contains  method to determine whether the  queryStr  exists in the recipe’s name.  Contains  is the equivalent of the  LIKE  keyword in SQL.

If the syntax is confusing, you’re probably not familiar with lambda expressions. A lambda expression is an anonymous function that lets you write local functions that can be passed as arguments. In the example above,  x => x.Name.Contains(queryStr)  is the lambda expression. In the context of the Where statement, it says:

Give me all Recipes (and let x represent a Recipe instance) such that the Name attribute of the Recipe object (x.Name) contains the string found in queryStr. 

If that’s still confusing, don’t worry. You’ll get the hang of it. Your best teacher, in this case, is not me or this course, but IntelliSense in Visual Studio. You’ve seen it at work before. As you type an expression, IntelliSense brings up lists and suggestions to help you write code faster. Consider this example as I’m typing out the above query: 

IntelliSense understands that  x  in this case represents a  Recipe  instance, so it gives me everything associated with the  Recipe  object. Likewise, when I select Name and then type a period, I get a list of all the things I can do with a string object: 

You can use LINQ to navigate through the database context. A period after the context variable brings up all the tables in your database. Selecting a table and entering another period brings up all the query functions you can perform on that table. With that in mind, it’s time for your next task.

Coding Exercise: Execute LINQ Queries

It’s time for more practice. This time, you’re going to see what you can learn about LINQ using IntelliSense as a guide. You wrote six queries in SQL in the last section. Now you’ll write those same queries using LINQ. Follow the logic you used in the SQL versions to help you navigate through your LINQ queries. You’ll find that it's pretty intuitive. Continue doing this in your  HomeController. Just add a second version of each of the methods you wrote, identifying them as LINQ methods, for example:

public JsonResult GetAllRecipesLINQ() { }

When you’ve completed the code for this exercise, test your LINQ queries the same way you did the SQL queries. The results should be the same.

Let's Recap!

We covered a lot of information in this chapter.

• First, you made some changes to your model classes and then performed another code-first migration to prep the database to receive some sample data.

• Next, you learned how to use the SQL Server 2017 Import and Export Data tool to import data from the CSV files you downloaded into the database tables.

• After loading up your database with test data, you learned how to execute SQL query strings using Entity Framework and the database context object.

• You also learned about the importance of parameterizing all user-provided data in your queries to protect your database from SQL injection attacks.

• Finally, you learned how to execute queries on your database through the context object using LINQ instead of raw SQL.

Now let’s take a quick look back at the rest of what you’ve learned here in the first half of the course.