[WebApi Tutorial] Library Check – Audit And Join All The Things

March 2, 2017Jamie Taylor2 comments

WebApi Tutorial – Audit and Join All the Things

Today’s header image was created by Lakerain Snake, the original source for the image is available here

Stop!

In the name of love?

This is the forth in a series of blog posts related to building a WebApi application.

If you haven’t read the previous parts, I’d recommend starting there.

Let’s Go

If you remember, previously we built a basic WebApi application, added Entity Framework Core, added a DbContext, and some services. In this post we’ll build on that application.

What We’ll Be Doing

In this post we’ll:

Investigate Shadow Properties
Create a Join Table
Create methods to seed the joined data (which will involve a little refactoring)
Alter the Book and Character Services to get the Join Table data from the database

As with the previous parts in this series, I’ve already uploaded all of the code to a GitHub repository. You can head over there if you want to take a sneak peak at what we’re going to create in this tutorial.

Also, as with the previous part in the series, I’m not going to be including Visual Studio specific steps for this tutorial.

You can read about why, here.

Running the Service

The first thing I would recommend that you do is make sure that the code from last time still runs. So cd into the src directory and issue the run command:

dotnet run

Then head over to ‘http://localhost:5000/Characters/Get/1’ in your browser:

webApiTutorial Response — The JSON response for our Character record

If something doesn’t go quite right, then take a quick look at the the previous part of this series, or clone the code from the GitHub repository for this part and read along.

Shadow Properties

I’ve written about Shadow Properties before.

You can read my write up on them here

The short version of what we’re going to do is have all of our Data Models implement an IAuditable interface. That way, we can target all models which implement that interface and add the properties to the database entires as we write them.

Interfacing

Let’s start with the IAuditable Interface. Create a file within the Models directory called “IAuditable.cs” and paste the following code into it:

	namespace webApiTutorial.Models
	{
	public interface IAuditable
	{
	}
	}

Then we need to edit our Book and Character database models to implement the new interfaces.

Here are the first few lines of each file, with the interfaces implemented. First the Book data model:

	namespace webApiTutorial.Models
	{
	public class Book : IAuditable
	{
	public int BookId { get; set; }

Now the Character data model:

	namespace webApiTutorial.Models
	{
	public class Character : IAuditable
	{
	public int CharacterId { get; set; }

view raw Character.cs hosted with ❤ by GitHub

Creating The DB Fields

Now we need to create the shadow properties when we create the model.

We’ll create a migration to set them in a moment.

We’ll need to change the “DwContext.cs” file to ensure that the new fields for the Shadow Properties are created.

The first thing we need to do is alter the OnModelCreating method. The changes we make here will ensure that the fields are created on the relevant records. After this, we’ll need to ensure that the Audit information is set whenever we add entity records to the database.

Here’s how we want our “DwContext.cs” to look:

	using Microsoft.EntityFrameworkCore;
	using System;
	using System.Linq;
	using System.Reflection;
	using webApiTutorial.Models;

	namespace webApiTutorial.DatabaseContexts
	{
	public class DwContext : DbContext
	{
	public DwContext(DbContextOptions<DwContext> options) : base(options) { }
	public DwContext() { }

	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
	// Create our audit fields as shadow properties
	foreach (var entityType in modelBuilder.Model.GetEntityTypes()
	.Where(e => typeof(IAuditable).IsAssignableFrom(e.ClrType)))
	{
	modelBuilder.Entity(entityType.ClrType)
	.Property<DateTime>("Created");

	modelBuilder.Entity(entityType.ClrType)
	.Property<DateTime?>("Modified");

	modelBuilder.Entity(entityType.ClrType)
	.Property<string>("CreatedBy");

	modelBuilder.Entity(entityType.ClrType)
	.Property<string>("ModifiedBy");
	}

	base.OnModelCreating(modelBuilder);
	}

	public override int SaveChanges()
	{
	ApplyAuditInformation();
	return base.SaveChanges();
	}

	private void ApplyAuditInformation()
	{
	var modifiedEntities = ChangeTracker.Entries<IAuditable>()
	.Where(e => e.State == EntityState.Added \|\| e.State == EntityState.Modified);
	foreach (var entity in modifiedEntities)
	{
	entity.Property("Modified").CurrentValue = DateTime.UtcNow;
	entity.Property("ModifiedBy").CurrentValue = String.Empty;
	if (entity.State == EntityState.Added)
	{
	entity.Property("Created").CurrentValue = DateTime.UtcNow;
	entity.Property("CreatedBy").CurrentValue = "Migration";
	}
	}
	}

	public DbSet<Book> Books { get; set; }
	public DbSet<Character> Characters { get; set; }
	}
	}

Let’s look at the changes, first the new using statements:

	using Microsoft.EntityFrameworkCore;
	using System;
	using System.Linq;
	using System.Reflection;
	using webApiTutorial.Models;

We need these using statements in our new OnModelCreating content. Speaking of which:

	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
	// Create our audit fields as shadow properties
	foreach (var entityType in modelBuilder.Model.GetEntityTypes()
	.Where(e => typeof(IAuditable).IsAssignableFrom(e.ClrType)))
	{
	modelBuilder.Entity(entityType.ClrType)
	.Property<DateTime>("Created");

	modelBuilder.Entity(entityType.ClrType)
	.Property<DateTime?>("Modified");

	modelBuilder.Entity(entityType.ClrType)
	.Property<string>("CreatedBy");

	modelBuilder.Entity(entityType.ClrType)
	.Property<string>("ModifiedBy");
	}

	base.OnModelCreating(modelBuilder);
	}

Here we iterate through all of the Entities in the DbSet which implement the IAuditable interface. For each of those entities, we create the following properties:

Created as a DateTime
CreatedBy as a String
Modified as a nullable DateTime

For more on nullable types, see this MSDN article

ModifiedBy as a String

Then we call the base DbContext’s OnModelCreating method

The last thing we need to do is ensure that the relevant properties are updated whenever we persist changes to the DbSets:

	public override int SaveChanges()
	{
	ApplyAuditInformation();
	return base.SaveChanges();
	}

	private void ApplyAuditInformation()
	{
	var modifiedEntities = ChangeTracker.Entries<IAuditable>()
	.Where(e => e.State == EntityState.Added \|\| e.State == EntityState.Modified);
	foreach (var entity in modifiedEntities)
	{
	entity.Property("Modified").CurrentValue = DateTime.UtcNow;
	entity.Property("ModifiedBy").CurrentValue = String.Empty;
	if (entity.State == EntityState.Added)
	{
	entity.Property("Created").CurrentValue = DateTime.UtcNow;
	entity.Property("CreatedBy").CurrentValue = "Migration";
	}
	}
	}

In the ApplyAuditInformation method, we’re iterating through the entires in the Change Tracker which implement the IAuditable interface and setting the values for the audit fields.

A Note On Our API

Our API will be readonly, as such none of the entities should be modifiable. Since we can’t modify the contents of the records, we should never see the modified fields change.

	foreach (var entity in modifiedEntities)
	{
	entity.Property("Modified").CurrentValue = DateTime.UtcNow;
	entity.Property("ModifiedBy").CurrentValue = String.Empty;
	if (entity.State == EntityState.Added)
	{
	entity.Property("Created").CurrentValue = DateTime.UtcNow;
	entity.Property("CreatedBy").CurrentValue = "Migration";
	}

In a real-world API we might have writeable entities, in that case we would need a useful string in the highlighted lines.

In most cases this would be the name of the user who created or modified the entities.

Migrating The Data

In order to apply the Shadow Properties, we’ll need to create a migration. Doing this is easy, but we’ll need to alter the migration slightly once we’ve created it.

To create the migration, stop the application (Ctrl +C will do it), then run the following command:

dotnet ef migrations add AddedShadowProperties

This will re-build the application and build a our migration. The migration that it creates will look a little like this:

It will be in the Migrations directory, with a name that ends in “AddedShadowProperties”

	using System;
	using System.Collections.Generic;
	using Microsoft.EntityFrameworkCore.Migrations;

	namespace webApiTutorial.Migrations
	{
	public partial class AddedShadowProperties : Migration
	{
	protected override void Up(MigrationBuilder migrationBuilder)
	{
	migrationBuilder.AddColumn<DateTime>(
	name: "Created",
	table: "Characters",
	nullable: false,
	defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));

	migrationBuilder.AddColumn<string>(
	name: "CreatedBy",
	table: "Characters",
	nullable: true);

	migrationBuilder.AddColumn<DateTime>(
	name: "Modified",
	table: "Characters",
	nullable: true);

	migrationBuilder.AddColumn<string>(
	name: "ModifiedBy",
	table: "Characters",
	nullable: true);

	migrationBuilder.AddColumn<DateTime>(
	name: "Created",
	table: "Books",
	nullable: false,
	defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));

	migrationBuilder.AddColumn<string>(
	name: "CreatedBy",
	table: "Books",
	nullable: true);

	migrationBuilder.AddColumn<DateTime>(
	name: "Modified",
	table: "Books",
	nullable: true);

	migrationBuilder.AddColumn<string>(
	name: "ModifiedBy",
	table: "Books",
	nullable: true);
	}

	protected override void Down(MigrationBuilder migrationBuilder)
	{
	migrationBuilder.DropColumn(
	name: "Created",
	table: "Characters");

	migrationBuilder.DropColumn(
	name: "CreatedBy",
	table: "Characters");

	migrationBuilder.DropColumn(
	name: "Modified",
	table: "Characters");

	migrationBuilder.DropColumn(
	name: "ModifiedBy",
	table: "Characters");

	migrationBuilder.DropColumn(
	name: "Created",
	table: "Books");

	migrationBuilder.DropColumn(
	name: "CreatedBy",
	table: "Books");

	migrationBuilder.DropColumn(
	name: "Modified",
	table: "Books");

	migrationBuilder.DropColumn(
	name: "ModifiedBy",
	table: "Books");
	}
	}
	}

There are two issues with this migration, and they’re in the Up method:

	protected override void Up(MigrationBuilder migrationBuilder)
	{
	migrationBuilder.AddColumn<DateTime>(
	name: "Created",
	table: "Characters",
	nullable: false,
	defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));

	migrationBuilder.AddColumn<string>(
	name: "CreatedBy",
	table: "Characters",
	nullable: true);

	migrationBuilder.AddColumn<DateTime>(
	name: "Modified",
	table: "Characters",
	nullable: true);

	migrationBuilder.AddColumn<string>(
	name: "ModifiedBy",
	table: "Characters",
	nullable: true);

	migrationBuilder.AddColumn<DateTime>(
	name: "Created",
	table: "Books",
	nullable: false,
	defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));

The highlighted lines show the issues. When a Created date is not supplied, the default .NET epoch date will be used. We need to swap this for the following:

	protected override void Up(MigrationBuilder migrationBuilder)
	{
	migrationBuilder.AddColumn<DateTime>(
	name: "Created",
	table: "Characters",
	nullable: false,
	defaultValue: DateTime.Now);

	migrationBuilder.AddColumn<string>(
	name: "CreatedBy",
	table: "Characters",
	nullable: true);

	migrationBuilder.AddColumn<DateTime>(
	name: "Modified",
	table: "Characters",
	nullable: true);

	migrationBuilder.AddColumn<string>(
	name: "ModifiedBy",
	table: "Characters",
	nullable: true);

	migrationBuilder.AddColumn<DateTime>(
	name: "Created",
	table: "Books",
	nullable: false,
	defaultValue: DateTime.Now);

Here we’ve swapped the default value for the Created Date to be DateTime.Now, meaning that if we don’t have a value for that field, we’ll swap it for whatever DateTime.Now evaluates to.

We can get around this by creating a mapping, but that would add a little too much complexity.

Now we need to apply the migration and run the application:

	dotnet ef database update
	dotnet run

You should see a whole ream of SqLite commands fly by, then you should be able to point your browser at ‘http://localhost:5000/Characters/Get/1’ again and get the following response:

Nothing has changed, right?

Let’s consult the database:

Character Table with Audit data — Here we can see the audit data for our Character records

Voila, our Shadow Properties!

Join Tables

Let’s take a look at our original Entity Relationship Diagram:

from back in the first post in this series

Domain Model — Our domain modal (generated with DbVisualiser)

We have a join table here, which joins a Character entity to a Book entity. This is how we’ll model a Many-to-Many relationship for our entities.

We’ll do a Many-to-Many relationship here, because one Character can be in many Books, and one Book can have many Characters. This relationship is what a Join Table and a Many-to-Many relationship was built for.

Oh yeah!

Creating the Join Table

Create a file within the Models directory called “BookCharacter.cs” and paste the following code into it:

	namespace webApiTutorial.Models
	{
	public class BookCharacter : IAuditable
	{
	public int BookId { get; set; }
	public virtual Book Book { get; set; }
	public int CharacterId {get; set; }
	public virtual Character Character { get; set; }
	}
	}

Each BookCharacter record is going to bridge a Character record and a Book record.

We need to add a DbSet for the BookCharacters in the DwContext class:

	public DbSet<Book> Books { get; set; }
	public DbSet<Character> Characters { get; set; }
	public DbSet<BookCharacter> BookCharacters { get; set; }

Then we need to tell the OnModelCreating that the primary key for the BookCharacter table will be a composite of the Ids for the Book and Character records that it represents.

	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
	// Create our audit fields as shadow properties
	foreach (var entityType in modelBuilder.Model.GetEntityTypes()
	.Where(e => typeof(IAuditable).IsAssignableFrom(e.ClrType)))
	{
	modelBuilder.Entity(entityType.ClrType)
	.Property<DateTime>("Created");

	modelBuilder.Entity(entityType.ClrType)
	.Property<DateTime?>("Modified");

	modelBuilder.Entity(entityType.ClrType)
	.Property<string>("CreatedBy");

	modelBuilder.Entity(entityType.ClrType)
	.Property<string>("ModifiedBy");
	}

	modelBuilder.Entity<BookCharacter>().HasKey(x => new { x.BookId, x.CharacterId });

	base.OnModelCreating(modelBuilder);
	}

Now we need to create and apply a migration for adding the BookCharacter table:

	dotnet ef migrations add AddedBookCharacterTable
	dotnet ef database update
	dotnet run

Then check the Database to see the changes

Our BookCharacter table, as created by EF Core

I’m whizzing through this part because we’ve performed steps that are very similar to this already in this post.

However, we’ll need to seed the new Join Table data before we can use it – otherwise the new table will be empty.

Seeding The New Join Table Data

What we’re going to do here is refactor the way that we’re seeding the database. We’ll create a class with methods which will read through JSON files which represent Book, Character and BookCharacter records, create those records and persist them to the database.

A lot of what we’re about to do was inspired by this article by Julie Lerman

By the way, Julie Lerman is THE expert on Entity Framework and ADO.NET (which was around before Entity Framework)

Database Seeder

The first thing we need to do is create a class which we can use to seed our database. It will have separate methods for Book, Character and BookCharacter records. Each of those methods will deal with a separate JSON file, reading it and deserialising it’s content to a collection of those records.

In the src directory, you’ll need to create a sub-directory called “DatabaseTools” and add a file called “DatabaseSeeder.cs” within it. Once you’ve created that file, paste the following code into it:

	using Newtonsoft.Json;
	using System.Collections.Generic;
	using System.IO;
	using System.Linq;
	using webApiTutorial.DatabaseContexts;
	using webApiTutorial.Models;

	namespace webApiTutorial.DatabaseTools
	{
	public class DatabaseSeeder
	{
	private DwContext _context;

	public DatabaseSeeder(DwContext context)
	{
	_context = context;
	}

	public int SeedBookEntitiesFromJson()
	{
	var recordsAdded = default(int);

	var filePath = Path.Combine(Directory.GetCurrentDirectory(), "SeedData", "BookSeedData.json");
	if (File.Exists(filePath))
	{
	var dataSet = File.ReadAllText(filePath);
	var seedData = JsonConvert.DeserializeObject<List<Book>>(dataSet);

	// ensure that we only get the distinct books (based on their name)
	var distinctSeedData = seedData.GroupBy(b => b.BookName).Select(b => b.First());

	_context.Books.AddRange(distinctSeedData);
	recordsAdded = _context.SaveChanges();
	}

	return recordsAdded;
	}

	public int SeedCharacterEntitiesFromJson()
	{
	var recordsAdded = default(int);
	var filePath = Path.Combine(Directory.GetCurrentDirectory(), "SeedData", "CharacterSeedData.json");
	if (File.Exists(filePath))
	{
	var dataSet = File.ReadAllText(filePath);
	var seedData = JsonConvert.DeserializeObject<IEnumerable<Character>>(dataSet);

	// ensure that we only get the distinct characters (based on their name)
	var distinctSeedData = seedData.GroupBy(c => c.CharacterName).Select(c => c.First());

	_context.Characters.AddRange(distinctSeedData);
	recordsAdded = _context.SaveChanges();
	}

	return recordsAdded;
	}

	public int SeedBookCharacterEntriesFromJson()
	{
	var recordsAdded = default(int);
	var filePath = Path.Combine(Directory.GetCurrentDirectory(), "SeedData", "BookCharacterSeedData.json");
	if (File.Exists(filePath))
	{
	var dataSet = File.ReadAllText(filePath);
	var seedData = JsonConvert.DeserializeObject<List<BookCharacterSeedData>>(dataSet);

	foreach(var seedBook in seedData)
	{
	var dbBook = _context.Books.Single(b => b.BookName == seedBook.BookName);

	foreach (var seedChar in seedBook.CharacterNames)
	{
	var dbChar = _context.Characters.FirstOrDefault(c => c.CharacterName == seedChar);
	if (dbChar != null)
	{
	_context.BookCharacters.Add(new BookCharacter
	{
	Book = dbBook,
	Character = dbChar
	});
	}
	}
	}
	recordsAdded = _context.SaveChanges();
	}

	return recordsAdded;
	}
	}
	}

There’s a lot going on here, so let’s take a look at each method in turn. First the SeedBookEntriesFromJson method:

	public int SeedBookEntitiesFromJson()
	{
	var recordsAdded = default(int);

	var filePath = Path.Combine(Directory.GetCurrentDirectory(), "SeedData", "BookSeedData.json");
	if (File.Exists(filePath))
	{
	var dataSet = File.ReadAllText(filePath);
	var seedData = JsonConvert.DeserializeObject<List<Book>>(dataSet);

	// ensure that we only get the distinct books (based on their name)
	var distinctSeedData = seedData.GroupBy(b => b.BookName).Select(b => b.First());

	_context.Books.AddRange(distinctSeedData);
	recordsAdded = _context.SaveChanges();
	}

Here we deserialise the contents of a JSON file (called “BookSeedData.json” from the “SeedData” directory), get the distinct values (based on the name field) and add them to the context.

	public int SeedCharacterEntitiesFromJson()
	{
	var recordsAdded = default(int);
	var filePath = Path.Combine(Directory.GetCurrentDirectory(), "SeedData", "CharacterSeedData.json");
	if (File.Exists(filePath))
	{
	var dataSet = File.ReadAllText(filePath);
	var seedData = JsonConvert.DeserializeObject<IEnumerable<Character>>(dataSet);

	// ensure that we only get the distinct characters (based on their name)
	var distinctSeedData = seedData.GroupBy(c => c.CharacterName).Select(c => c.First());

	_context.Characters.AddRange(distinctSeedData);
	recordsAdded = _context.SaveChanges();
	}

	return recordsAdded;
	}

Here we do a pretty similar thing, except that we use a file called “CharacterSeedData.json” and for Character entities.

	public int SeedBookCharacterEntriesFromJson()
	{
	var recordsAdded = default(int);
	var filePath = Path.Combine(Directory.GetCurrentDirectory(), "SeedData", "BookCharacterSeedData.json");
	if (File.Exists(filePath))
	{
	var dataSet = File.ReadAllText(filePath);
	var seedData = JsonConvert.DeserializeObject<List<BookCharacterSeedData>>(dataSet);

	foreach(var seedBook in seedData)
	{
	var dbBook = _context.Books.Single(b => b.BookName == seedBook.BookName);

	foreach (var seedChar in seedBook.CharacterNames)
	{
	var dbChar = _context.Characters.FirstOrDefault(c => c.CharacterName == seedChar);
	if (dbChar != null)
	{
	_context.BookCharacters.Add(new BookCharacter
	{
	Book = dbBook,
	Character = dbChar
	});
	}
	}
	}
	recordsAdded = _context.SaveChanges();
	}

This method is the most complex of the three, it takes the entires in a BookCharacterSeedData.json file and deserialises them to instances of the BookCharacterSeedData class

We’ll create this class in a moment

We then iterate through the list of deserialised data, get the matching Book database model, then all of the Character database models that are referenced in the BookCharacterSeedData instance. Once we’ve found all of that data, we add a new BookCharacter record.

BookCharacterSeedData

In the DatabaseTools directory, create a file called “BookCharacterSeedData.cs” and paste the following code into it:

	using System.Collections.Generic;

	namespace webApiTutorial.DatabaseTools
	{
	public class BookCharacterSeedData
	{
	public string BookName {get; set;}
	public List<string> CharacterNames {get; set;}
	}
	}

Seed Data JSON Files

In the DatabaseSeeder class we had lines like the following:

      var filePath = Path.Combine(Directory.GetCurrentDirectory(), "SeedData", "CharacterSeedData.json");

where we used the GetCurrentDirectory, which returns the src directory, since that’s where the application is being run from.

VS Code webApiTutorial4 — Our project directory layout

So we’ll need a directory there called “SeedData” to house our Seed Data JSON files.

VS Code webApiTutorial4 with SeedData directory — Our project directory layout with the SeedData directory selected

Within this directory, we create a file called “BookSeedData.json” and paste the following code into it:

	[
	{
	"BookName": "The Colour of Magic",
	"BookOrdinal": "1",
	"BookIsbn10": "086140324X",
	"BookIsbn13": "9780552138932",
	"BookDescription": "On a world supported on the back of a giant turtle (sex unknown), a gleeful, explosive, wickedly eccentric expedition sets out. There's an avaricious but inept wizard, a naive tourist whose luggage moves on hundreds of dear little legs, dragons who only exist if you believe in them, and of course THE EDGE of the planet ...",
	"BookCoverImageUrl": "http://wiki.lspace.org/mediawiki/images/c/c9/Cover_The_Colour_Of_Magic.jpg"
	}
	]

Now create a file called “CharacterSeedData.json” and paste the following code into it:

	[
	{
	"CharacterName": "Rincewind",
	"CharacterOrdinal": "0"
	},
	{
	"CharacterName": "Twoflower",
	"CharacterOrdinal": "1"
	},
	{
	"CharacterName": "The Luggage",
	"CharacterOrdinal": "2"
	},
	{
	"CharacterName": "Liessa Wyrmbidder",
	"CharacterOrdinal": "3"
	},
	{
	"CharacterName" : "Hrun, the Barbarian",
	"CharacterOrdinal": "4"
	},
	{
	"CharacterName" : "Bel-Shamharoth",
	"CharacterOrdinal": "5"
	}
	]

And, last but not least, create a file called “BookCharacterSeedData.json” and paste the following code into it:

	[
	{
	"BookName": "The Colour of Magic",
	"CharacterNames": [
	"Bel-Shamharoth",
	"Hrun, the Barbarian",
	"Liessa Wyrmbidder",
	"The Luggage",
	"Rincewind",
	"Twoflower"
	]
	}
	]

Hopefully you can see how this is more easily manageable than adding hundreds of lines (possibly even thousands) of code just for the seed data.

Plus, if we want to make changes to the database, we don’t need to recompile the application.

Well, technically we do. But there’s a way around that (and I’ll put that in a future post)

Calling the Database Seeder

In the DwExtensions.cs file, paste the following code into the EnsureSeedData method (replacing what is already there):

	public static void EnsureSeedData(this DwContext context)
	{
	if (context.AllMigrationsApplied())
	{
	var dbSeeder = new DatabaseSeeder(context);
	if (!context.Books.Any())
	{
	dbSeeder.SeedBookEntitiesFromJson();
	}
	if (!context.Characters.Any())
	{
	dbSeeder.SeedCharacterEntitiesFromJson();
	}
	if (!context.BookCharacters.Any())
	{
	dbSeeder.SeedBookCharacterEntriesFromJson();
	}

	context.SaveChanges();
	}
	}

Note: this method contains an extra call to SaveChanges, but we’ll leave it for now.

A Final Note on The Seeder

We’ll need to delete the database file from disk before we can seed the database correctly. So head over to the following directory (buried within the bin subdirectory):

webApiTutorial4 Database Location — I’ve even selected the database file for you 😛

Now delete the dwDatabase.db file. This leaves our application without a database, but it will be recreated (and seeded) the next time we start it. Speaking of, let’s do that now:

dotnet run

This time around, our DatabaseSeeder class will take over and add the Book, Character and BookCharacter records for us.

You’ll need to issue a request to get the seeder to fire, so head over to ‘http://localhost:5000/Characters/Get/1’ in your browser:

Nothing in the returned data will have changed, but if you take a look at the tables within the database, you’ll see the new data.

First, the Characters table:

Characters Table Post Seed — Our newly seeded Characters table

Now the Books table:

Books Table Post Seed — Our newly seeded Books table

This should look, pretty much, the same as it did before we started working. Except for the audit fields, obviously.

And finally, the BookCharacters table:

BookCharacters Table Post Seed — Our newly seeded BookCharacters table

Querying For The Joined Data

Having the Join Table in place is pretty useless unless we include it in our queries. Let’s do that now.

Books with Characters

We need to inform the Book data model (the Book.cs file in the Models directory) that it should have the Join Table data, so paste the following code into that file (overwriting the content of the file):

	using System.Collections.Generic;
	using System.Collections.ObjectModel;

	namespace webApiTutorial.Models
	{
	public class Book : IAuditable
	{
	public int BookId { get; set; }
	public int BookOrdinal { get; set; }
	public string BookName { get; set; }
	public string BookIsbn10 { get; set; }
	public string BookIsbn13 { get; set; }
	public string BookDescription { get; set; }
	public virtual ICollection<BookCharacter> BookCharacter { get; set; } = new Collection<BookCharacter>();
	}
	}

Then we need to alter the BaseQuery in the BookService, replacing the entire method with the following:

	private IEnumerable<Book> BaseQuery()
	{
	return _dwContext.Books
	.AsNoTracking()
	.Include(book => book.BookCharacter)
	.ThenInclude(BookCharacter => BookCharacter.Character);
	}

We’ll come back to what’s happening here, in a moment.

Then we need to alter the Book View Model to include any Character data returned in the Join Table. Replace the contents of the BookViewModel.cs file with the following:

	using System.Collections.Generic;

	namespace webApiTutorial.ViewModels
	{
	public class BookViewModel : BaseViewModel
	{
	public BookViewModel()
	{
	Characters = new List<string>();
	}

	public int BookOrdinal { get; set; }
	public string BookName { get; set; }
	public string BookIsbn10 { get; set; }
	public string BookIsbn13 { get; set; }
	public string BookDescription { get; set; }
	public byte[] BookCoverImage { get; set; }
	public string BookCoverImageUrl { get; set; }

	public List<string> Characters { get; set; }
	}
	}

And finally, replace the contents of the ConvertToViewModel method in the BookHelper.cs file with the following:

	public static BookViewModel ConvertToViewModel (Book dbModel)
	{
	var viewModel = new BookViewModel
	{
	BookOrdinal = dbModel.BookOrdinal,
	BookName = dbModel.BookName,
	BookIsbn10 = dbModel.BookIsbn10,
	BookIsbn13 = dbModel.BookIsbn13,
	BookDescription = dbModel.BookDescription,
	};

	foreach(var bc in dbModel.BookCharacter)
	{
	viewModel.Characters.Add(bc.Character.CharacterName ?? string.Empty);
	}

	return viewModel;
	}

If you now restart the application and head over to ‘http://localhost:5000/Books/Get/1’ in your browser, you should receive a response similar to this:

Book with Characters — Note the Characters object in the returned JSON

Character Wth Books

For completeness sake, let’s do the other side too: Adding Books to a Character.

First, let’s replace the contents of the Character.cs file (found in the Models directory):

	using System.Collections.Generic;
	using System.Collections.ObjectModel;

	namespace webApiTutorial.Models
	{
	public class Character : IAuditable
	{
	public int CharacterId { get; set; }

	public string CharacterName { get; set; }
	public int CharacterOrdinal { get;set; }

	public virtual ICollection<BookCharacter> BookCharacter { get; set; } = new Collection<BookCharacter>();
	}
	}

Now we need to replace the BaseQuery method in the Character Service with this one:

	private IEnumerable<Character> BaseQuery()
	{
	return _dwContext.Characters
	.AsNoTracking()
	.Include(character => character.BookCharacter)
	.ThenInclude(bookCharacter => bookCharacter.Book);
	}

As with the BookService, we’ll come back to what’s happening here in a moment.

Now replace the contents of the CharacterViewModel.cs file with these:

	namespace webApiTutorial.ViewModels
	{
	public class CharacterViewModel : BaseViewModel
	{
	public CharacterViewModel()
	{
	Books = new List<string>();
	}

	public string CharacterName { get; set; }
	public List<string> Books { get; set; }
	}
	}

And finally, replace the contents of the ConvertToviewModel method in the CharacterHelper.cs with the following:

	public static CharacterViewModel ConvertToviewModel (Character dbModel)
	{
	var viewModel = new CharacterViewModel
	{
	CharacterName = dbModel.CharacterName
	};

	foreach (var book in dbModel.BookCharacter)
	{
	viewModel.Books.Add(book.Book.BookName ?? string.Empty);
	}

	return viewModel;
	}

If you now restart the application and head over to ‘http://localhost:5000/Characters/Get/1’ in your browser, you should receive a response similar to this:

Character with Books — Note the Book object in the returned JSON

How The Join Works

Cast your mind back to the Book service.

all those few lines ago

In the BaseQuery method, we have:

	private IEnumerable<Book> BaseQuery()
	{
	return _dwContext.Books
	.AsNoTracking()
	.Include(book => book.BookCharacter)
	.ThenInclude(BookCharacter => BookCharacter.Character);
	}

Entity Framework Core does not perform Lazy Loading on navigating properties as it is not yet supported.

This is correct at the time of writing this blog post.

However, Entity Framework Core supports both Eager and Explicit Loading techniques.

You can read more about all three of these loading techniques here.

Because we want to load the navigation property data as part of the queries as we build them, we’re using Eager Loading. This has the benefit of only loading the navigation property records when we explicitly build it into the queries.

Explicit Loading is the opposite of this. Enabling Explicit Loading will inform .NET Core that we expect to load the navigation property data at a later time, but that we do indeed want it to be loaded with our initial query. Which leads to slowers executing SQL (especially when you have many table that you need to join, in order to get all of the navigation property data).

The decision of whether to use Eager or Explicit Loading should be an application design decision, We’re using Eager Loading here, because it will produce SQL with the navigation property records only when we want those records.

Conclusion

We’ve added Shadow Properties, added a Join Table, completely re-written our seed methods to make them data driven, and altered our services to use Eager Loading to populate the navigation properties.

That’s about it for this tutorial series. You should now have a web api project which you can query to get Book and Character details. Try altering the database seed data and see what kind of database you can create; then let me know in the comments.

It’s been a real learning curve producing this first tutorial series, and I’m looking forward to creating the next one. Who knows what it will be?

Well, I do but I’m not about to give the game away, am I?

If this post has helped you out, please consider