ASP.NET Core Razor Pages: Simple Login using Entity Framework Database First Approach

Microsoft .NET Core framework is more suited for distributed environment rather than single tier machine. Due to this distributed tier nature of the .NET core framework, two major concepts are implanted into the heart of .NET core framework and are utilized quite heavily i.e.

1. Service Oriented Architecture as .NET Microservices.
2. Dependency Injection design pattern.

I will not go into the details of these two concepts. You can consult the provided links for further study and understanding of these two concepts.

In today's tutorial I will demonstrate the creation of a razor pages base simple asp.net core login application using entity framework database first approach.

Prerequisites:

Following are some prerequisites before you proceed any further in this tutorial:

1. Basic understanding of ASP.NET Core framework.  
2. Upgrade Windows Power Shell to latest version.
   
3. Knowledge about entity framework
4. Knowledge about Claim Base Identity Model
5. Knowledge about Bootstrap.
6. Knowledge about C# programming.  
7. Knowledge about C# LINQ.

The example code is developed in Microsoft Visual Studio 2017 Professional & SQL Server 2014. The example code is in .NET Core 2.1, .NET 5 solution is available at [.NET 5 Upgrade] ASP.NET Core Razor Pages: Simple Login using Database First Entity Framework

Download Now! OR .NET 5 Upgrade

Let's begin now.

1) First create your existing SQL server database. Example database scripts named "db_corelogin" is provided with the downloadable solution which will be utilized in asp.net core web application.
Make sure to update SQL server connection string in the provided example solution.
2) Now, create a new .Net core web application project and name it "CoreLoginEfDbFirst" as shown below i.e.

3) Build the solution and ensure that the build is successful then restart visual studio.

4) Now, in order to import existing database context object using entity framework to my core web application. I need to install following library packages via "Tools->NuGet Package Manager->Manage NuGet Packages for Solution" in below mention order i.e.

1. Microsoft.EntityFrameworkCore.SqlServer
2. Microsoft.EntityFrameworkCore.Tools
3. Microsoft.EntityFrameworkCore.SqlServer.Design

5) Click "Tools->NuGet Package Manager->Package Manager Console" as shown below i.e.

6) Type the following command inside the console as shown below. Do not forget to update your SQL server connection string configuration in this command i.e.

Scaffold-DbContext "Server=SQL SERVER (e.g localhost);Database=DATABASE (e.g db_corelogin);Trusted_Connection=True;user id=SQL USERNAME;password=SQL PASSWORD;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models/DB

The above command will create following folders and files i.e.

Remember that when we use database first approach in asp.net mvc framework, we have a cool graphical designer UI through which we select the tables, store procedures and other database objects to be imported into the web application via entity framework. The database context file also get imported through which we communicate to SQL database engine. In .net core however, there is no cool graphical user interface to import the SQL database context, we have to import the database context via above command and then inside the created .cs database context file, we need to write appropriate business logic methods to access SQL database tables, store procedures or queries to access the data. Login.cs class is the object class of our SQL database table Login.

7) Now, create "Models\DB\LoginByUsernamePassword.cs" object class file to access your store procedure results.

8) Then, create "Models\LoginViewModel.cs" ViewModel class file and replace the following code in it i.e.

//-----------------------------------------------------------------------
// <copyright file="LoginViewModel.cs" company="None">
//     Copyright (c) Allow to distribute this code and utilize this code for personal or commercial purpose.
// </copyright>
// <author>Asma Khalid</author>
//-----------------------------------------------------------------------

namespace CoreLoginEfDbFirst.Models
{
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;

    /// <summary>
    /// Login view model class.
    /// </summary>
    public class LoginViewModel
    {
        #region Properties

        /// <summary>
        /// Gets or sets to username address.
        /// </summary>
        [Required]
        [Display(Name = "Username")]
        public string Username { get; set; }

        /// <summary>
        /// Gets or sets to password address.
        /// </summary>
        [Required]
        [DataType(DataType.Password)]
        [Display(Name = "Password")]
        public string Password { get; set; }

        #endregion
    }
}

The above piece of code is my view model class which will be attached to the target view in order to process user inputs.

9) Now, open "Models\DB\db_coreloginContext.cs"file and add following lines of code in it i.e.

As I have already mentioned that .net core heavily utilizes "Service Oriented Architecture as .NET Microservices and Dependency Injection design pattern" concepts. Therefore, we need to make some changes in the above auto generated database context class, otherwise we will not be able to communicate with SQL server database engine. So, in the above class ensure that "OnConfiguring(...)" is either removed or commented out as I have already commented out this method in  "db_coreloginContext.cs" file. Ensure that your database context file above must also have overload constructor as shown below i.e.

        public db_coreloginContext(DbContextOptions<db_coreloginContext> options)
            : base(options)
        {
        }

Rest is your choice i.e what new database logic methods you need to add or remove any existing entities. In order to access data objects from SQL database via custom queries, store procedures or direct query from tables. You need to register your target custom objects with the model builder inside your "OnModelCreating(...)" method of "db_coreloginContext.cs" database context class. As shown below that I will not do any direct table query so, I have commented out my table entity object Login pre-build registration with the model builder and since, I am using the result of my SQL server database store procedure, therefore, I have registered my custom store procedure data returining object with the model builder i.e.

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            ////modelBuilder.Entity<Login>(entity =>
            ////{
            ////    entity.Property(e => e.Id).HasColumnName("id");

            ////    entity.Property(e => e.Password)
            ////        .IsRequired()
            ////        .HasColumnName("password")
            ////        .HasMaxLength(50)
            ////        .IsUnicode(false);

            ////    entity.Property(e => e.Username)
            ////        .IsRequired()
            ////        .HasColumnName("username")
            ////        .HasMaxLength(50)
            ////        .IsUnicode(false);
            ////});

            // [Asma Khalid]: Query for store procedure.
            modelBuilder.Query<LoginByUsernamePassword>();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            ////modelBuilder.Entity<Login>(entity =>
            ////{
            ////    entity.Property(e => e.Id).HasColumnName("id");

            ////    entity.Property(e => e.Password)
            ////        .IsRequired()
            ////        .HasColumnName("password")
            ////        .HasMaxLength(50)
            ////        .IsUnicode(false);

            ////    entity.Property(e => e.Username)
            ////        .IsRequired()
            ////        .HasColumnName("username")
            ////        .HasMaxLength(50)
            ////        .IsUnicode(false);
            ////});

            // [Asma Khalid]: Query for store procedure.
            modelBuilder.Entity<LoginByUsernamePassword>().HasNoKey();
        }

Then, I have created "LoginByUsernamePasswordMethodAsync(...)" method, which is access to my store procedure inside SQL server database i.e.

#region Login by username and password store procedure method.

        /// <summary>
        /// Login by username and password store procedure method.
        /// </summary>
        /// <param name="usernameVal">Username value parameter</param>
        /// <param name="passwordVal">Password value parameter</param>
        /// <returns>Returns - List of logins by username and password</returns>
        public async Task<List<LoginByUsernamePassword>> LoginByUsernamePasswordMethodAsync(string usernameVal, string passwordVal)
        {
            // Initialization.
            List<LoginByUsernamePassword> lst = new List<LoginByUsernamePassword>();

            try
            {
                // Settings.
                SqlParameter usernameParam = new SqlParameter("@username", usernameVal ?? (object)DBNull.Value);
                SqlParameter passwordParam = new SqlParameter("@password", passwordVal ?? (object)DBNull.Value);

                // Processing.
                string sqlQuery = "EXEC [dbo].[LoginByUsernamePassword] " +
                                    "@username, @password";

                lst = await this.Query<LoginByUsernamePassword>().FromSql(sqlQuery, usernameParam, passwordParam).ToListAsync();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // Info.
            return lst;
        }

        #endregion

#region Login by username and password store procedure method.

        /// <summary>
        /// Login by username and password store procedure method.
        /// </summary>
        /// <param name="usernameVal">Username value parameter</param>
        /// <param name="passwordVal">Password value parameter</param>
        /// <returns>Returns - List of logins by username and password</returns>
        public async Task<List<LoginByUsernamePassword>> LoginByUsernamePasswordMethodAsync(string usernameVal, string passwordVal)
        {
            // Initialization.
            List<LoginByUsernamePassword> lst = new List<LoginByUsernamePassword>();

            try
            {
                // Settings.
                SqlParameter usernameParam = new SqlParameter("@username", usernameVal ?? (object)DBNull.Value);
                SqlParameter passwordParam = new SqlParameter("@password", passwordVal ?? (object)DBNull.Value);

                // Processing.
                string sqlQuery = "EXEC [dbo].[LoginByUsernamePassword] " +
                                    "@username, @password";

                lst = await this.Set<LoginByUsernamePassword>().FromSqlRaw(sqlQuery, usernameParam, passwordParam).ToListAsync();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // Info.
            return lst;
        }

        #endregion

10) In order to access SQL server database I need to store my database connection string in "appsettings.json" file which is the recommended way. So, open "appsettings.json" file and replace the following code in it i.e.

{
  "ConnectionStrings": {
    "db_corelogin": "Server=SQL SERVER;Database=DATABASE;Trusted_Connection=True;user id=SQL USERNAME;password=SQL PASSWORD;"
  },
  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Warning"
    }
  }
}

Do not forget to update your SQL server configurations in the above connection string.

11) Now, I need to register my database context as .NET Microservices with the .net core framework in order to access my database within my application. To do so, open the "Startup.cs" file and add following line of code at the end of "ConfigureServices(...)" method i.e.

            // [Asma Khalid]: Register SQL database configuration context as services.  
            services.AddDbContext<db_coreloginContext>(options => options.UseSqlServer(Configuration.GetConnectionString("db_corelogin")));

I have now registered my database context as .NET Microservices with the .net core framework using my SQL server connection string configuration. So, now I can easily access my database context object using Dependency Injection design pattern. Just for the knowledge dependency injection design pattern enables access to the target global object via overload constructor parameter of the class that requires the access. When I register my database context as the above line of code, the framework will automatically instantiate my database context object at global level and when my any target class requires access to the database context object then that class needs to utilize "db_coreloginContext" database context object as a parameter of it's overload constructor, that's the beauty of dependency injection design pattern.

12) Do a little cleanup of your folder hierarchy i.e.

1. Remove "Pages\_Layout.cshtml" file.
2. Move "Pages\_ValidationScriptsPartial.cshtml" file to Views\Shared folder.
3. Except "Pages\_ViewImports.cshtml" file, "Pages\_ViewStart.cshtml" file and "Pages\Error.cshtml" file, remove all other files inside "Pages" folder.

13) Now, create "Pages\Index.cshtml" page file with view model attached to it.

14) Open, "Pages\Index.cshtml.cs" file and add/replace following lines of code in it i.e.

        #region Private Properties.

        /// <summary>
        /// Database Manager property.
        /// </summary>
        private readonly db_coreloginContext databaseManager;

The above piece of code is basically a read only property to store the reference of my database context object as a part or dependency injection design pattern. Then in the below code I have created an overload constructor with the database context object as a parameter following dependency injection design pattern and within my overload constructor I have stored the reference of the database context object for my class i.e.

#region Default Constructor method.

        /// <summary>
        /// Initializes a new instance of the <see cref="IndexModel"/> class.
        /// </summary>
        /// <param name="databaseManagerContext">Database manager context parameter</param>
        public IndexModel(db_coreloginContext databaseManagerContext)
        {
            try
            {
                // Settings.
                this.databaseManager = databaseManagerContext;
            }
            catch (Exception ex)
            {
                // Info
                Console.Write(ex);
            }
        }

        #endregion

In asp.net core framework unlike asp.net mvc framework, in order to bind the view model class to the UI I need to define "[BindProperty]" data annotation above the view model public property which is defined within the IndexModel class "Pages\Index.cshtml.cs" file.  as shown below i.e.

#region Public Properties

        /// <summary>
        /// Gets or sets login model property.
        /// </summary>
        [BindProperty]
        public LoginViewModel LoginModel { get; set; }

        #endregion

Now, below "OnGet()" method is the default mandatory method which will be executed when the page is called. I have simple verified the user authorization in the "OnGet()" method i.e. if user is login then go to home page otherwise go to login page i.e.

#region On Get method.

        /// <summary>
        /// GET: /Index
        /// </summary>
        /// <returns>Returns - Appropriate page </returns>
        public IActionResult OnGet()
        {
            try
            {
                // Verification.
                if (this.User.Identity.IsAuthenticated)
                {
                    // Home Page.
                    return this.RedirectToPage("/Home/Index");
                }
            }
            catch (Exception ex)
            {
                // Info
                Console.Write(ex);
            }

            // Info.
            return this.Page();
        }

        #endregion

Next, I have created the "OnPostLogIn(..)" method which will verify the username and password via SQL database access and then by using claim base identity model mechanism sign in the user into my web application. Here, notice that in order to tell the framework that the method is post, I need to mandatory write the prefix "OnPost" before writing the handler name of my choice i.e. LogIn . So, it will become "OnPostLogin()" method, this naming convention is important. A helper "SignInUser(...)" method is also written for signing in the user via claim base identity model mechanism i.e.

        #region On Post Login method.

        /// <summary>
        /// POST: /Index/LogIn
        /// </summary>
        /// <returns>Returns - Appropriate page </returns>
        public async Task<IActionResult> OnPostLogIn()
        {
            try
            {
                // Verification.
                if (ModelState.IsValid)
                {
                    // Initialization.
                    var loginInfo = await this.databaseManager.LoginByUsernamePasswordMethodAsync(this.LoginModel.Username, this.LoginModel.Password);

                    // Verification.
                    if (loginInfo != null && loginInfo.Count() > 0)
                    {
                        // Initialization.
                        var logindetails = loginInfo.First();

                        // Login In.
                        await this.SignInUser(logindetails.Username, false);

                        // Info.
                        return this.RedirectToPage("/Home/Index");
                    }
                    else
                    {
                        // Setting.
                        ModelState.AddModelError(string.Empty, "Invalid username or password.");
                    }
                }
            }
            catch (Exception ex)
            {
                // Info
                Console.Write(ex);
            }

            // Info.
            return this.Page();
        }

        #endregion

To access the "OnPostLogIn(...)" method in the HTML, you do not need to write the "OnPost" prefix, but, only "LogIn" as shown in the below line of code from "Pages\Index.cshtml" file which will post request to the "OnPostLogIn(...)" method i.e.

                <div class="form-group">
                    <div class="col-md-offset-2 col-md-10">
                        <button asp-page-handler="LogIn" class="btn btn-default">Log in</button>
                    </div>
                </div>

15) Open, "Startup.cs" file to register the authorization services with the .net core framework in order to utilize the authentication services of the claim base identity model mechanism. Go to, "ConfigureServices(...)" method and add following lines of code before the database context registration i.e.

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            // [Asma Khalid]: Authorization settings.
            services.AddAuthentication(options =>
            {
                options.DefaultSignInScheme = CookieAuthenticationDefaults.AuthenticationScheme;
                options.DefaultAuthenticateScheme = CookieAuthenticationDefaults.AuthenticationScheme;
                options.DefaultChallengeScheme = CookieAuthenticationDefaults.AuthenticationScheme;
            }).AddCookie(options =>
            {
                options.LoginPath = new PathString("/Index");
                options.ExpireTimeSpan = TimeSpan.FromMinutes(5.0);
            });

            // [Asma Khalid]: Authorization settings.
            services.AddMvc().AddRazorPagesOptions(options =>
            {
                options.Conventions.AuthorizeFolder("/");
                options.Conventions.AllowAnonymousToPage("/Index");
            });

            // [Asma Khalid]: Register SQL database configuration context as services.  
            services.AddDbContext<db_coreloginContext>(options => options.UseSqlServer(Configuration.GetConnectionString("db_corelogin")));
        }

16) In "Startup.cs" file for authenticaon purpose add following lines of code at the end of "Configure(...)" method i.e.

            // [Asma Khalid]: Register simple authorization.
            app.UseAuthentication();

17) Create "Pages\Home\Index.cshtml" file and replace the following code in it i.e.

@page 


@model CoreLoginEfDbFirst.Pages.Home.IndexModel

@{
    ViewBag.Title = "ASP.NET Core Razor Pages - Simple Login Database First Approach";
}

<h2>@ViewBag.Title.</h2>

<div class="jumbotron">
    <h1>Welcome</h1>
    <p class="lead">Login from "@User.Identity.Name" Account.</p>
</div>

<div class="row">
    <div class="col-md-8">
        <form method="post" role="form" class="form-horizontal">
            <div class="form-group">
                <div class="col-md-offset-2 col-md-10">
                    <button asp-page-handler="LogOff" class="btn btn-default">Log Off</button>
                </div>
            </div>
        </form>
    </div>
</div>

In the above code, I have created a simple home page which is accessible to only authorize users of this application.

18) Execute the project and you will be able to see the following i.e.

Conclusion

In this article, you will learn to create razor pages base simple asp.net core login application using entity framework. You will learn to scaffold existing database context into your web application via NuGet Package Console command. You will also learn about dependency injection design pattern. You will also learn about registration of database context .net microservices with the .net core framework in order to utilize the SQL server database access. You will also learn about claim base identity model authorization with .net core web application. You will also learn about razor pages development and you will also learn about accessing store procedures with .net core entity framework web application.