With Entity Framework we can create relationships between our tables. It is very easy to use navigational properties to create foreign keys and be able to relate one table to another. This is important because it allows us to avoid repeating information. So, if we have a table of invoices and a table of customers, then we can have a relationship between these two tables, so that in the table of invoices we have a field, ClientId, which contains a reference to our client in the customer’s table.

When we create an ASP.NET application with the individual user account system by default, then the AspNetUsers  table will contain our users. We can make the relation of the AspNetUsers table with any other table of our application. Let’s do this.

In this post I will use: Visual Studio 2017 Community Edition, ASP.NET Core 2.0 and Entity Framework Core 2.0.

The first thing we need is an ASP.NET project with an individual user account. If you just created the project, do an add-migration in the Package Manager Console in Visual Studio:

add-migration Initial

Then, do the update-database:

Update-Database

Now what we are going to do is create a new model, invoice, which will contain information about sales of our system, and will also allow us to save a reference to a user of the AspNetUsers table:

public class Invoice
{
  public int Id {get; set; }
  public decimal Total {get; set; }
  public string Concept {get; set; }
  public string ClientId {get; set; }
  public ApplicationUser Client {get; set; }
}

As you can see, in this model we have the ClientId column, which serves as a foreign key for the navigational property “Client”. Notice that we are following the convention  EntityId, where in our case the Entity is represented by the property called Client, which is of the ApplicationUser type. This is enough to tell Entity Framework Core 2.0 that we want a relationship between the Invoices table and the AspNetUsers table. Now, we are going to place the corresponding DbSet in our ApplicationDbContext:

public DbSet<Invoice> Invoices {get; set; }

With this we can add a new migration:

Add-Migration Invoices

And then we can do an  Update-Database. We will then do a test, insert a record in the Invoices table and place our user in the ClientId field. If you have not done so, sign up and log in to your application. Then, prepare a class so you can use the ApplicationDbContext and the UserManager. For this example, I will use the HomeController. Modify the HomeController’s constructor in the following way:

private readonly ApplicationDbContext context;
private readonly UserManager<ApplicationUser>  userManager;

public HomeController (ApplicationDbContext context,
UserManager<ApplicationUser> userManager)
{
this.context = context;
this.userManager = userManager;
}

In this way, we are taking advantage of the dependency injection to use the Context and the UserManager. Now, modify the About action with the following code:

public IActionResult About()
{
  if (User.Identity.IsAuthenticated)
  {
    var invoice = new Invoice ();
    invoice.Concept = "Computer";
    invoice.Total = 500;
    invoice.ClientId = userManager.GetUserId (HttpContext.User);
    context.Add (invoice);
    context.SaveChanges ();
  }

ViewData ["Message"] = "Your application description page.";

return View ();
}

In the code within the If conditional, we can see that we are instantiating an invoice, and we are inserting it into the database. We can see that we are assigning the current user to the ClientId field. Run this code by going to the About page while logged in. With this we have inserted a record in the Invoices table. In order to see if it has worked, we only need to make the following statement with Entity Framework:

var invoices = context.Invoices.Include (x => x.Client) .ToList ();

Which will bring all the invoices with the information of the Clients (users). We can also make a query to check this, in the case of SQL Server, we can do an inner join:

Select AspNetUsers.Email, Concept, Total
from Invoices
inner join AspNetUsers
ON Invoices.ClientId = AspNetUsers.Id

Summary

To relate the AspNetUsers table with any other table of your application, you simply have to place the foreign key in the model of the table with which you want to relate AspNetUsers.