Database First EF Core and Inverse Property

I have a database where many of the tables have a set of audit data rows:

I have a database where many of the tables have a set of audit data rows:

public Guid CreatedByUserId { get; set; }
public DateTime CreatedDate { get; set; }
public Guid? ModifiedByUserId { get; set; }
public DateTime? ModifiedDate { get; set; }

For example, the Area and Citation table both have such a set of rows. The userIds are linked by a foreign key to the User table (as you would expect.)

When I run the EF scaffolding generator (this is a db first project) I run this:

dotnet ef dbcontext scaffold "....connection..." Microsoft.EntityFrameworkCore.SqlServer -o "output" --data-annotations

When I look at the User class I get this:

public class User
{
    public User()
    {
        AreaCreatedByUser = new HashSet<Area>();
        AreaModifiedByUser = new HashSet<Area>();
        CitationCreatedByUser = new HashSet<Citation>();
        CitationModifiedByUser = new HashSet<Citation>();
    }
public Guid Id { get; set; }
[Required]
[StringLength(50)]
public string Name { get; set; }
public Guid CreatedByUserId { get; set; }
public DateTime CreatedDate { get; set; }
public Guid? ModifiedByUserId { get; set; }
public DateTime? ModifiedDate { get; set; }

public virtual ICollection&lt;Area&gt; AreaCreatedByUser { get; set; }
[InverseProperty("ModifiedByUser")]
public virtual ICollection&lt;Area&gt; AreaModifiedByUser { get; set; }
[InverseProperty("CreatedByUser")]
public virtual ICollection&lt;Citation&gt; CitationCreatedByUser { get; set; }
[InverseProperty("ModifiedByUser")]
public virtual ICollection&lt;Citation&gt; CitationModifiedByUser { get; set; }

}

(It is actually used in hundreds of tables, but I have abbreviated the above to make it a bit clearer.)

I really don't want to navigate from a user to all the records that use a user in these audit lines, but I don't know what I can do to strip this out or prevent it from being generated. When I get a user from the database I don't want all these extra fields, even if they are null without an include. I guess if I drop the FK relationship that might do it, but that does not seem a good idea at all.

Any suggestions?

Migrate Entity Framework Core to SQL Database on Startup

Migrate Entity Framework Core to SQL Database on Startup

This ASP.NET Core tutorial explains how to migrate Entity Framework Core to SQL Database on Startup. How to automatically migrate database changes from code in ASP.NET Core using Entity Framework Core from the Startup.cs file. Use the EF Core DB Context Service to automatically migrate database changes.

Example code tested with ASP.NET Core 3.1

This is a super quick example of how to automatically migrate database changes from code in ASP.NET Core using Entity Framework Core from the Startup.cs file.

Solution

Register the EF Core DB Context as an ASP.NET Core Service

The Entity Framework Core DB Context is registered as a service with the ASP.NET Core Dependency Injection (DI) system from the ConfigureServices() method of the Startup.cs file.

// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<DataContext>(x => x.UseSqlite("Data Source=LocalDatabase.db"));

    ...
}

Use the EF Core DB Context Service to automatically migrate database changes

An instance of the EF Core DB Context service is injected as a parameter into the Configure() method of the Startup.cs file, the DB Context instance is then used to apply any pending migrations to the database by calling the Database.Migrate() method.

// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env, DataContext dataContext)
{
    // migrate any database changes on startup (includes initial db creation)
    dataContext.Database.Migrate();

    ...
}
Extra Info

While updating the tutorial from an EF Core InMemory database to SQLite I ran into some difficulties trying to automatically run database migrations from the Startup.cs. At first I was following a tutorial on the MS Docs website that called services.BuildServiceProvider().GetService<MyDatabaseContext>().Database.Migrate(); from within the ConfigureServices() method, but this resulted in the following warning in the console when I ran the application:

Startup.cs(39,13): warning ASP0000: Calling 'BuildServiceProvider' from application code results in an additional copy of singleton services being created.
Consider alternatives such as dependency injecting services as parameters to 'Configure'.
[/Users/jwatmore/Projects/aspnet-core-3-registration-login-api/WebApi.csproj]

Database Design Tutorial - How to Design & Plan Database for Beginners

Database Design Tutorial - How to Design & Plan Database for Beginners

Learn how to design and plan a database for beginners. This database design course will help you understand database concepts and give you a deeper grasp of database design. Database design is the organisation of data according to a database model. The designer determines what data must be stored and how the data elements interrelate. With this information, they can begin to fit the data to the database model.

Database Design Tutorial - How to Design & Plan Database for Beginners

This database design course will help you understand database concepts and give you a deeper grasp of database design.

Database design is the organisation of data according to a database model. The designer determines what data must be stored and how the data elements interrelate. With this information, they can begin to fit the data to the database model.

⭐️ Contents ⭐
⌨️ (0:00:00) Introduction
⌨️ (0:03:12) What is a Database?
⌨️ (0:11:04) What is a Relational Database?
⌨️ (0:23:42) RDBMS
⌨️ (0:37:32) Introduction to SQL
⌨️ (0:44:01) Naming Conventions
⌨️ (0:47:16) What is Database Design?
⌨️ (1:00:26) Data Integrity
⌨️ (1:13:28) Database Terms
⌨️ (1:28:28) More Database Terms
⌨️ (1:38:46) Atomic Values
⌨️ (1:44:25) Relationships
⌨️ (1:50:35) One-to-One Relationships
⌨️ (1:53:45) One-to-Many Relationships
⌨️ (1:57:50) Many-to-Many Relationships
⌨️ (2:02:24) Designing One-to-One Relationships
⌨️ (2:13:40) Designing One-to-Many Relationships
⌨️ (2:23:50) Parent Tables and Child Tables
⌨️ (2:30:42) Designing Many-to-Many Relationships
⌨️ (2:46:23) Summary of Relationships
⌨️ (2:54:42) Introduction to Keys
⌨️ (3:07:24) Primary Key Index
⌨️ (3:13:42) Look up Table
⌨️ (3:30:19) Superkey and Candidate Key
⌨️ (3:48:59) Primary Key and Alternate Key
⌨️ (3:56:34) Surrogate Key and Natural Key
⌨️ (4:03:43) Should I use Surrogate Keys or Natural Keys?
⌨️ (4:13:07) Foreign Key
⌨️ (4:25:15) NOT NULL Foreign Key
⌨️ (4:38:17) Foreign Key Constraints
⌨️ (4:49:50) Simple Key, Composite Key, Compound Key
⌨️ (5:01:54) Review and Key Points....HA GET IT? KEY points!
⌨️ (5:10:28) Introduction to Entity Relationship Modeling
⌨️ (5:17:34) Cardinality
⌨️ (5:24:41) Modality
⌨️ (5:35:14) Introduction to Database Normalization
⌨️ (5:39:48) 1NF (First Normal Form of Database Normalization)
⌨️ (5:46:34) 2NF (Second Normal Form of Database Normalization)
⌨️ (5:55:00) 3NF (Third Normal Form of Database Normalization)
⌨️ (6:01:12) Indexes (Clustered, Nonclustered, Composite Index)
⌨️ (6:14:36) Data Types
⌨️ (6:25:55) Introduction to Joins
⌨️ (6:39:23) Inner Join
⌨️ (6:54:48) Inner Join on 3 Tables
⌨️ (7:07:41) Inner Join on 3 Tables (Example)
⌨️ (7:23:53) Introduction to Outer Joins
⌨️ (7:29:46) Right Outer Join
⌨️ (7:35:33) JOIN with NOT NULL Columns
⌨️ (7:42:40) Outer Join Across 3 Tables
⌨️ (7:48:24) Alias
⌨️ (7:52:13) Self Join

Build a CRUD App with ASP.NET MVC and Entity Framework

Build a CRUD App with ASP.NET MVC and Entity Framework

Let’s walk through creating a basic application that allows the creation, reading, updating, and deletion of data (CRUD) with Entity Framework by your users while managing them easily with Okta.

Let’s walk through creating a basic application that allows the creation, reading, updating, and deletion of data (CRUD) with Entity Framework by your users while managing them easily with Okta.

Interested in building a secure ASP.NET MVC website that allows users to handle their own data with ease?

For a fun example, we will create a web application that lists upcoming rocket launches for space enthusiasts! You will have everything you need to get up and running by the end of this post.

What Does Your ASP.NET MVC + Entity Framework App Need to Do?

For the purposes of this tutorial, here are the requirements you’ll need to fulfill:

  • Users must sign in to see a specific page (i.e. a gated flow)
  • Users must be able to create and read data
  • The app should display user-specific data, i.e. name

Be sure to have Visual Studio and the latest ASP.NET Framework (4.x) version installed on your development machine before you continue. We will walk through installing the other dependencies together. Let’s get started!

Create Sample Users for Your ASP.NET MVC App

For this tutorial, you’ll use Okta for user creation and authentication.

Why use Okta for authentication? Okta makes identity management easier, more secure, and more scalable than what you’re used to. Okta is an API service that allows you to create, edit, and securely store user accounts and user account data, and connect them with one or more applications.

If you haven’t already, go to developer.okta.com and create an account to get started, then continue with the following steps.

Visit the Users tab and click Add Person. Fill out the form and be sure to set the Password drop down to “Set by Admin”, and fill in a temporary password for the user. Click Save and Add Another and continue adding a couple more users.

Once you have the users added, you’ll notice the status for your newly created users is set to “Password expired”. This is expected for admin-created users and will guide them through their reset password flow during the first login to your site - without any additional work on your part.

Set Up Your ASP.NET MVC Application in Okta

Now that you have your users set up in your database, set up this specific application within Okta. On the Dashboard, click Applications in the main menu and on the Application screen, click Add Application. Select Web and then click Next.

Name the application “Okta MVC CRUD App”. Select Implicit (Hybrid) in addition to the pre-selected Authorization Code, and click Done.

Your application has been created, but you still need to add the logout redirect now that the field is available. Select Edit, add the URI [http://localhost:8080/Account/PostLogout](http://localhost:8080/Account/PostLogout "http://localhost:8080/Account/PostLogout"), and click Save.

Scroll down and you’ll have access to the ClientID and Client Secret. Keep these on hand to add to your application’s Web.config file later on. Now your app is good to go for Okta auth!

Create an ASP.NET MVC Application with Entity Framework using a LocalDB Connection

Click File > New > Project. Select Visual C# then select ASP.NET** Application**. Choose MVC and name it “CrudMVCCodeFirst”.

Right-click on the project and select Properties. Go to the Web tab and set the project URL to reflect the application settings in Okta’s portal [https://localhost:8080](https://localhost:8080 "https://localhost:8080").

Right-click on the project and select Manage NuGet Packages. Tap on Browse and search for Entity Framework. Install version 6.2.0.

Open the Web.config and add your connection string inside the configuration element to use LocalDB using the code below.

<connectionStrings>
    <add name="LaunchContext" 
	connectionString="Data Source=(LocalDb)\MSSQLLocalDB;Initial 
        Catalog=RocketLaunch1;Integrated Security=SSPI;" 
	providerName="System.Data.SqlClient" />
  </connectionStrings>


You will be using Entity Framework to create database entities. This code first approach eliminates the need for you to define a database. Instead, the first time you access your data by running the app, Entity Framework will create your database for you using LocalDB as your database engine.
Create a class in the Models folder called LaunchEntry.cs and add the properties below.

public class LaunchEntry
{
    public int Id { get; set; }
    public string LaunchInfo { get; set; }
    public string PostedByUserName { get; set; }
}


Create a folder at the project level called Data and add a class inside of it called LaunchContext.cs Add System.Data.Entity, CrudMVCCodeFirst.Models and System.Data.Entity.ModelConfiguration.Conventions to the usings at the top of the file and extend the class with DBContext. Now you can add your DbSets. The name of the connection string in your Web.config is passed into the constructor. Add the code below.

public class LaunchContext : DbContext
{
    public LaunchContext() : base("LaunchContext")
    {
    }

    public DbSet<LaunchEntry> Launches { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    }
}


You will be using Entity Framework to create database entities. This code first approach eliminates the need for you to define a database. Instead, the first time you access your data by running the app, Entity Framework will create your database for you using LocalDB as your database engine.
Right-click on the Controller folder and add a new scaffolded item.

Select MVC 5 Controller with views, using Entity Framework and click Add.

Select “LaunchEntry” as the Model class and “LaunchContext” as the Data context class. Set the controller name to “LaunchController”, leave the other default values and click Add.

You will be using Entity Framework to create database entities. This code first approach eliminates the need for you to define a database. Instead, the first time you access your data by running the app, Entity Framework will create your database for you using LocalDB as your database engine.
Because this is a claims-aware application, we need to indicate where to get user identity information from. Open your Global.asax.cs file. Add System.Web.Helpers to the usings group. Replace the Application_Start() method with the code below to match the name of claim type - “name” in this case - from the JsonWebToken (JWT) you are receiving from Okta to set your user’s identifier.

protected void Application_Start()
{
    AreaRegistration.RegisterAllAreas();
    FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
    RouteConfig.RegisterRoutes(RouteTable.Routes);
    BundleConfig.RegisterBundles(BundleTable.Bundles);

    AntiForgeryConfig.UniqueClaimTypeIdentifier = "name";
}


Go into Views/Shared and open _Layout.cshtml. Add a link to your Launch controller’s newly generated Index page by replacing the unordered list with the class nav navbar-nav with the code below. This will allow you to navigate to the launch controller from the home page of the site.

<ul class="nav navbar-nav">
    <li>@Html.ActionLink("Launches", "Index", "Launch")</li>
    <li>@Html.ActionLink("Home", "Index", "Home")</li>
    <li>@Html.ActionLink("About", "About", "Home")</li>
    <li>@Html.ActionLink("Contact", "Contact", "Home")</li>
</ul>


Run the project in the browser. You should see your Launches link in the navigation bar. Select it to access the Index page, and add a launch or two, adding your name and editing a bit.

Add the Okta .NET SDK to your ASP.NET MVC App

Congrats, you have a working CRUD application that lets you keep track of rocket launches! However - right now anyone can access the Launch page. Let’s add functionality to authenticate users to give only specific people access to that and hide it from public view.

Right-click on the project and select Manage NuGet Packages. Tap on Browse and search for Okta.AspNet. This is the .NET 4.x framework version of the SDK. Find version 1.1.4 and click Install.

You will be using Entity Framework to create database entities. This code first approach eliminates the need for you to define a database. Instead, the first time you access your data by running the app, Entity Framework will create your database for you using LocalDB as your database engine.
Add the Okta account access to your Web.config file under the appSettings section. Use the code below, replacing the Okta client ID and secret with your specific app’s Okta credentials you generated in the portal earlier.

<!-- 1\. Replace these values with your Okta configuration -->
    <add key="okta:ClientId" value="{clientId}" />
    <add key="okta:ClientSecret" value="{clientSecret}" />
    <add key="okta:OktaDomain" value="https://{yourOktaDomain}" />

    <add key="okta:RedirectUri" value="http://localhost:8080/authorization-code/callback" />
    <add key="okta:PostLogoutRedirectUri" value="http://localhost:8080/Account/PostLogout" />


In order to handle OWIN, we need to do that from a Startup class. Right-click on the project and select Add OWIN Startup class. Call it “Startup” and click OK.

Add the following to your usings section.

using Microsoft.Owin.Security;
using Microsoft.Owin.Security.Cookies;
using Okta.AspNet;
using System.Collections.Generic;
using System.Configuration;


Replace the Configuration() method with the code below.

public void Configuration(IAppBuilder app)
{
    app.SetDefaultSignInAsAuthenticationType(CookieAuthenticationDefaults.AuthenticationType);

    app.UseCookieAuthentication(new CookieAuthenticationOptions());

    app.UseOktaMvc(new OktaMvcOptions()
    {
        OktaDomain = ConfigurationManager.AppSettings["okta:OktaDomain"],
        ClientId = ConfigurationManager.AppSettings["okta:ClientId"],
        ClientSecret = ConfigurationManager.AppSettings["okta:ClientSecret"],
        RedirectUri = ConfigurationManager.AppSettings["okta:RedirectUri"],
        PostLogoutRedirectUri = ConfigurationManager.AppSettings["okta:PostLogoutRedirectUri"],
        GetClaimsFromUserInfoEndpoint = true,
        Scope = new List<string> { "openid", "profile", "email" },
    });
}


Now you need to handle the login and logout functionality. Right-click on the Controllers folder and add a new MVC 5 empty controller called AccountController. Add Okta.AspNet and Microsoft.Owin.Security.Cookies to the usings section. Add the code below inside of the AccountController class to handle the Action Results for this controller.

public ActionResult Login()
{
    if (!HttpContext.User.Identity.IsAuthenticated)
    {
        HttpContext.GetOwinContext().Authentication.Challenge(
            OktaDefaults.MvcAuthenticationType);
        return new HttpUnauthorizedResult();
    }

    return RedirectToAction("Index", "Home");
}

[HttpPost]
public ActionResult Logout()
{
    if (HttpContext.User.Identity.IsAuthenticated)
    {
        HttpContext.GetOwinContext().Authentication.SignOut(
            CookieAuthenticationDefaults.AuthenticationType,
            OktaDefaults.MvcAuthenticationType);
    }

    return RedirectToAction("Index", "Home");
}

public ActionResult PostLogout()
{
    return RedirectToAction("Index", "Home");
}


Open the LaunchController and add the Authorize attribute at the top of any actions you don’t want public access to. Leaving the attribute off of the Index action will allow anyone to view the list of upcoming launches - just not edit them. Add Authorize to the top of GET actions for Create, Edit, Delete, and their corresponding POST endpoints. This will ensure those function will be accessed by authenticated users only and will redirect to the Okta login process if they are not authenticated.

//// GET: Launch/Create
[Authorize]
public ActionResult Create()

// POST: Launch/Create
// To protect from overposting attacks, please enable the specific properties you want to bind to, for 
// more details see https://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
[Authorize]
public ActionResult Create([Bind(Include = "Id,LaunchInfo,PostedByUserName")] LaunchEntry launchEntry)


Run the project in a browser and use a different one then what you are logged into the Okta portal with (or log out of your admin account first). Use one of your newly created user credentials and try it out. Click on Launches and you should be taken through the Okta generated authentication flow before landing on the Index page.

Track Users In Your Entity Framework CRUD Actions

You’ve successfully gated the Launches section! However, you might want to know which specific user would be doing the CRUD operations from here - and perhaps display the name of the currently logged in user. Let’s add that functionality now.

Go to Views/Shared and open _Layout.cshtml. Beneath the <ul> list of ActionLinks add the code below to display the user’s name and toggle the Login/Logout link button.

@if (Context.User.Identity.IsAuthenticated)
{
    <ul class="nav navbar-nav navbar-right">
        <li>
            <p class="navbar-text">Hello, <b>@Context.User.Identity.Name</b></p>
        </li>
        <li>
            <a onclick="document.getElementById('logout_form').submit();" 
                style="cursor: pointer;">Log out</a>
        </li>
    </ul>
    <form action="/Account/Logout" method="post" id="logout_form"></form>
}
else
{
    <ul class="nav navbar-nav navbar-right">
        <li>@Html.ActionLink("Log in", "Login", "Account")</li>
    </ul>
}


Go to Views/Launch and open Create.cshtml. Locate the form-group div that specifies the PostedByUserName field and delete it. Locate and remove in the Edit.cshtml as well.

Open the LaunchController. Replace your Create() method with the code below to automatically assign the logged in user to the PostedByUserName property which is called upon saving the form in each case.

// POST: Launch/Create
// To protect from overposting attacks, please enable the specific properties you want to bind to, for 
// more details see https://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
[Authorize]
public ActionResult Create([Bind(Include = "Id,LaunchInfo,PostedByUserName")] LaunchEntry launchEntry)
{
    launchEntry.PostedByUserName = this.User.Identity.Name;

    if (ModelState.IsValid)
    {
        db.Launches.Add(launchEntry);
        db.SaveChanges();
        return RedirectToAction("Index");
    }

    return View(launchEntry);
}


Now replace the Edit() method as well with the code below.

// POST: Launch/Edit/5
// To protect from overposting attacks, please enable the specific properties you want to bind to, for 
// more details see https://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
[Authorize]
public ActionResult Edit([Bind(Include = "Id,LaunchInfo,PostedByUserName")] LaunchEntry launchEntry)
{
    launchEntry.PostedByUserName = this.User.Identity.Name;

    if (ModelState.IsValid)
    {
        db.Entry(launchEntry).State = EntityState.Modified;
        db.SaveChanges();
        return RedirectToAction("Index");
    }
    return View(launchEntry);
}


Launch the application in the browser and log in as one of your created users. Play around with creating new launches, editing a few that already exist and even delete one. You will notice the Posted By field is gone since we automatically assign it to the logged in user now.

That’s it! Your users can now create, edit and delete the launch entries after being signed in, and without typing their name for the changes they produce. You’ve taken a journey from the basics of a CRUD app and transformed it into something that can be done by the users on your site in a secure fashion. Pretty slick!

How have you used this approach in your projects? Leave a comment below and tell me what you’ve used this for.

In the meantime… I have a couple of space rockets to check out.

Learn More

Build a Basic CRUD App with Node and React

Build a Simple CRUD App with Python, Flask, and React

Build a Basic CRUD App with Laravel and Vue

Build a Simple CRUD App with Spring Boot and Vue.js

Build a Basic CRUD App with Laravel and Angular

Build a Basic CRUD App with Laravel and React

How to build RESTful APIs with ASP.NET Core

Build a CRUD App with Angular and Firebase

Building Web APIs with ASP.NET Core

Angular 7 + Spring Boot CRUD Example

The Complete ASP.NET MVC 5 Course

Build a Real-world App with ASP.NET Core and Angular 2 (4+)

ASP NET Core (ASP.NET 5),MVC 6,C#,Angular2 & EF Crash Course