Creating a Multi-Project .Net Core Database Solution

Richard Beggs
Jul 8, 2020 · 12 min read

When creating a solution you may want to seperate out your areas of concern to keep them seperate. This is done by creating different projects within the solution. This helps with seperating the data layer and the business layer from the presentation layer. Most of the guides for settting up .net core solutions have all the code in a single project. We are going to set up a multi project solution that will highlight the issues and show how to over come them. First of all we need to create a solution.

Image for post
Image for post

We will create a new ASP .NET Web application in C#. I have called the solution “PubReviews” and the MVC project “PubReviews.Web” but you can call it what you like. Make sure that the “Place solution and project in the same directory” is unchecked.

Image for post
Image for post

We are creating a Web Application MVC with no authentication and are using ASP.Net Core 3.1 as it is the latest version at this time.

Image for post
Image for post

If we look at the “Solution Explorer” we should have a single .net core project called PubReviews.Web within our PubReviews solution.

Image for post
Image for post

What we want to do now is add another project for the data layer. We could add a service layer too but to keep it simple we will just add the one project. The process will be the same to add a service layer. Right click on the solution and select “Add” -> “New Project ”

Image for post
Image for post

We will add a .Net Core Class Library as we already have a .net core web application.

Image for post
Image for post

We will name the new project “PubReviews.Data”. We should now have a solution with 2 projects like this.

Image for post
Image for post

The PubReviews.Data project is going to be responsible for all database actions. We now need to create a relationship between the Web project and the Data project. Right click on the PubReviews.Web project and select “Add” -> “Project Reference”

Image for post
Image for post

Select the PubReviews.Web project and press “OK”. Now there will be a link from the web project to the data project.

We need to create some models that can be mapped to the database. We will create a new folder “Data” and inside that create another new folder “Models”. Inside the “Models” folder we will create our classes, “Pub.cs” and “Review.cs”.

Image for post
Image for post

The “Pub.cs” class will be pretty simple. Just basic details Id, Name etc. and a list of Reviews. Because a pub can have more than one review but a review can only be for one pub. Make sure that you make the Review.cs public so it can be seen by the Pubs.cs. The Review.cs only contains the Id and the Number of Stars for the review.

Image for post
Image for post
Image for post
Image for post

Install Entity Framework NuGets

To create the database tables from the datamodels we created in code we need to create a migration. To do this we need to add the following NuGet package to the PubReview.Web project.
- Microsoft.EntityFrameworkCore.Tools

Image for post
Image for post

Select “Browse” and search for “Microsoft.EntityFrameworkCore.Tools” and the click “Install”

We also need to add the following NuGets for the PubReviews.Data.

  • Microsoft.EntityFrameworkCore
  • Microsoft.EntityFrameworkCore.Design
  • Microsoft.EntityFrameworkCore.Tools
  • Microsoft.EntityFrameworkCore.SqlServer (we are using SqlServer)

Right click on the PubReview.Data project and select “Manage Nuget packages” Search for “Microsoft.EntityFrameworkCore” and install it.

Create DB Context

Before we can create a database we need to setup a dbcontext so the code can connect to the database. To do this we need to create a new folder “Context” in the PubReview.Data project. In the Context folder we will create a class “PubReviewsContext.cs”

Image for post
Image for post

We need to declare the 2 DataSets we want to create Pubs and Reviews. This is done at the class level. These map to the DataModels we setup earlier in the Data -> Models folder.

public DbSet<Pub> Pubs { get; set; }
public DbSet<Review> Reviews { get; set; }

The “PubReviewsContext.cs” class will extend the DbContext. We need to create a constructor that takes the options. This is going to pass the “options” to the base DbContext. Now we need to setup the connection string so the database can connect to the DBContext.

Image for post
Image for post

Dependency Injection

In previous versions of .net you would have had to use a third party tool such as ninject to acheive dependency injection. However .net core has now got dependency injection built in. There are a lot of guides out there on dependency injection however they don’t deal with dependency injection across multiple projects. We want to inject the “options” into the PubReviewsContext.cs class which is in the PubReviews.Data project. We could do this directly from the PubReviews.Web project but that would get messy having dependency injection setups for all the different projects in one place. It would make more sense to split the different setups to their own project. Fortunatly there is a way we can do this. We are going to create an extension that contains the dependency injection setups.

In the PubsReviews.Data project add a class “ServiceCollectionExtensions.cs”

Image for post
Image for post

This will be a static class contain a static method that “RegisterDataServices” This is where we will setup our dependency injections.

Image for post
Image for post

We need to add the dbContext to the services and provide a connection string.

Image for post
Image for post

To do this we add

services.AddDbContext<PubReviewsContext>(o=>o.UseSqlServer(“”)));

This will add the dbcontext to the service with the hard coded connection string. It would be better to have the connection string stored in the appSettings.json file rather than hardcoded in the depths of the data layer. To do this we need to pass in the configuration and get the connection string from there.

Image for post
Image for post

configuration.GetConnectionString(“Name of connection string in appsetting.json”) In this case we have called it “DefaultConnection”. We have had to add the following usings to the ServiceCollectionExtension.cs

  • Microsoft.EntityFrameworkCore;
  • Microsoft.Extensions.Configuration;
  • Microsoft.Extensions.DependencyInjection;
  • PubReviews.Data.Context;

We need to add a connection string to our appSettings.json before we forget. In the PubReviews.Web project open the appsettings.json file and add the connections string.

Image for post
Image for post

"ConnectionStrings”: { DefaultConnection”: “”Server=.;Database=PubReview;Integrated Security=True;”}

I have used Server =. to show it is my local machine and called my database “PubReviews”

Now we need to call our extension to inject the services at startup. Fortunatly .net core makes dependency injection easy. In the PubReviews.Web there is a StartUp.cs class with a “ConfigureServices” method.

Image for post
Image for post

This is where we are going to call our extension from. All we need to do is to add: services.RegisterDataServices(Configuration);

Image for post
Image for post

We can see in the startUp.cs that the Configuration is already injected into the class and it is a class level variable so we can pass that into the extension.

Create Database

Now we have our databaseContext all wired up we can create our database. We need to open the “Package Manager Console”. This is located in a tab at the bottom of the screen.

Image for post
Image for post

Type the following command to create the initial migration script. Make sure to select the PubReviews.Data project from the dropdown list. The name of the script is not important, it is only for reference.

Add-Migration CreateInitialDatabase

Image for post
Image for post

After the command has run there will be a folder in the PubReviews.Data project called “Migrations” this will contain the migration scripts that will create the database.

Image for post
Image for post

In the Package Manager Console type “Update-Database”

Image for post
Image for post

This will run the script and create the database and the tables.

Image for post
Image for post

If we log into SQL Server we can see our tables have been created with all the fields and relationships.

Seeding Data

Now we have the database structure in place we need to look at inserting some data into it. This is just base line data to test it is working correctly but it this can be used to insert reference data such as countries or years. We don’t have any of that so we will just add some base line data.

To do this we are going to create a DBInitialiser class. We are goign to create it inside the “Data” folder in the PubReviews.Data project. We need to create a static method “Seed” that take our PubReviewContext as a parameter.

Image for post
Image for post

Now we need to add the data to our PubReviewContext. We need to make sure that the tables are empty as we don’t want to add data every time the project is started.

Image for post
Image for post

To Add “Reviews” we will populate them on the Pub object. We simply need to add a list of reviews that can contain as many reviews as we like.

Reviews = new List<Review> { new Review { NumberOfStars = 2 } } }

Image for post
Image for post

At the end of the process call context.SaveChanges();

Now we need to call the “Seed” method from the Main method in the Program class in the PubReviews.Web project.

Image for post
Image for post

We need to split out the HostBuilder Build and Run commands. This will allow us to insert our seed command.

Image for post
Image for post

Now we want to create a scope for the Services. We will have to add the following using.

-using Microsoft.Extensions.DependencyInjection;

We can now get the PubReviewContext and pass it to our static method “Seed”

Image for post
Image for post

If we hit F5 and run the application we can check the database in MS SQL managment studio to see the records have been created.

Image for post
Image for post
Pubs table
Image for post
Image for post
Pub Review table

Creating Services

Now we have our populated database we want to be able to interact with it via the PubReviews.Web project. To do this we will create a service we will use to interact with the database. In the PubReviews.Data project we will create a new folder “Services” and a class called PubService.cs and an interface called IPubService.cs

Image for post
Image for post

In the interface we are going to define a couple of methods to return pubs from the database. We will need to add the following using
-using PubReviews.Data.Data.Models

The Methods will be:

List<Pub> Get();

List<Pub> GetByRating();

Image for post
Image for post

We now implement theIPubService interface in the PubService.cs class.

Image for post
Image for post

Here we can start to write our logic. We should really use a list of data transfer objects as the return type of the methods but for simplicity we will just return the data object.

To access the PubReviewContext we need to inject it into the service. As we have already added the context to the services we only need to inject it into the PubService.cs. This is done in the constructor were the context is passed in as a parameter and set to a class level property the same way all other dependency injection is done.

Image for post
Image for post

We can now use the _context to query the database. We have added .Include(“Reviews”) because we want to bring back the reviews as well as the Pubs.

Image for post
Image for post

Now we need to call these service methods from the PubReview.Web. We will need to create a model, controller and a view to display the results in the same way you would with any MVC application. We will create a PubController.cs which is just and empty controller in the “Controllers” folder.

Image for post
Image for post

We now need to create a viewModel called PubViewModel in the “Models” folder.

Image for post
Image for post

We will add the fields that we are going to display on the page to the viewModel. We are adding a field “AverageRating” which will calculate the average rating of a Pub.

Image for post
Image for post

We now need to create a View to display the results. We will create a folder “Pub” in the “View” folder. On the “Pub” folder right click and select “Add” -> “View…”

Image for post
Image for post

Select “Razor View” this will scaffold the new view.

Image for post
Image for post

The view name is “Index” and the Template is “List” and the model class is the viewmodel we just created. There is no Data context class selected because we are interacting with the database via the services not directly.

Image for post
Image for post

This will create an index.cshtml page that is already formated for displaying a list of PubViewModels.

In the Pub controller we need to call the PubService.cs to get the data from the database. To do this we need to inject the service the same way we have done before.

Image for post
Image for post

We need to set up the service so that it is available on the PubController.cs. in the StartUp.cs class with add the following code to the “ConfigureServices” method. services.AddTransient<IPubService,PubService>();

Image for post
Image for post

Back in the controller we can now use the _pubService to get the Data. We will need to convert the data from a list of DataModels into a List of ViewModels and return that list to the Index view.

Image for post
Image for post

We have added some logic so if a pub has no reviews it gets an average rating of 0. Now when we launch the application add navigate to /Pub we get get.

Image for post
Image for post

To implement the Edit, Details and Delete methods will be very straight forward since all the set up has been done the methods just need added to the pubService and the called by the controller.

The Startup

Medium's largest active publication, followed by +752K people. Follow to join our community.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store