public class User
    {
        public User()
        {
            UserRoles = new HashSet<UserRole>();
        }

        public int Id { get; set; }

        public string Username { get; set; }

        public string Password { get; set; }

        public string DisplayName { get; set; }

        public bool IsActive { get; set; }

        public DateTimeOffset? LastLoggedIn { get; set; }

        /// <summary>
        /// every time the user changes his Password,
        /// or an admin changes his Roles or stat/IsActive,
        /// create a new `SerialNumber` GUID and store it in the DB.
        /// </summary>
        public string SerialNumber { get; set; }

        public virtual ICollection<UserRole> UserRoles { get; set; }
    }

    public class Role
    {
        public Role()
        {
            UserRoles = new HashSet<UserRole>();
        }

        public int Id { get; set; }
        public string Name { get; set; }

        public virtual ICollection<UserRole> UserRoles { get; set; }
    }

    public static class CustomRoles
    {
        public const string Admin = nameof(Admin);
        public const string User = nameof(User);
    }

    public class UserRole
    {
        public int UserId { get; set; }
        public int RoleId { get; set; }

        public virtual User User { get; set; }
        public virtual Role Role { get; set; }
    }

    public interface IUnitOfWork : IDisposable
    {
        DbSet<TEntity> Set<TEntity>() where TEntity : class;

        int SaveChanges(bool acceptAllChangesOnSuccess);
        int SaveChanges();
        Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = new CancellationToken());
        Task<int> SaveChangesAsync(CancellationToken cancellationToken = new CancellationToken());
    }

    public class ApplicationDbContext : DbContext, IUnitOfWork
    {
        public ApplicationDbContext(DbContextOptions options) : base(options)
        { }

        public virtual DbSet<User> Users { set; get; }
        public virtual DbSet<Role> Roles { set; get; }
        public virtual DbSet<UserRole> UserRoles { get; set; }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            // it should be placed here, otherwise it will rewrite the following settings!
            base.OnModelCreating(builder);

            // Custom application mappings
            builder.Entity<User>(entity =>
            {
                entity.Property(e => e.Username).HasMaxLength(450).IsRequired();
                entity.HasIndex(e => e.Username).IsUnique();
                entity.Property(e => e.Password).IsRequired();
                entity.Property(e => e.SerialNumber).HasMaxLength(450);
            });

            builder.Entity<Role>(entity =>
            {
                entity.Property(e => e.Name).HasMaxLength(450).IsRequired();
                entity.HasIndex(e => e.Name).IsUnique();
            });

            builder.Entity<UserRole>(entity =>
            {
                entity.HasKey(e => new { e.UserId, e.RoleId });
                entity.HasIndex(e => e.UserId);
                entity.HasIndex(e => e.RoleId);
                entity.Property(e => e.UserId);
                entity.Property(e => e.RoleId);
                entity.HasOne(d => d.Role).WithMany(p => p.UserRoles).HasForeignKey(d => d.RoleId);
                entity.HasOne(d => d.User).WithMany(p => p.UserRoles).HasForeignKey(d => d.UserId);
            });
        }
    }

    /// <summary>
    /// Only used by EF Tooling
    /// </summary>
    public class ApplicationDbContextFactory : IDesignTimeDbContextFactory<ApplicationDbContext>
    {
        public ApplicationDbContext CreateDbContext(string[] args)
        {
            var basePath = Directory.GetCurrentDirectory();
            Console.WriteLine($"Using `{basePath}` as the BasePath");
            var configuration = new ConfigurationBuilder()
                                    .SetBasePath(basePath)
                                    .AddJsonFile("appsettings.json")
                                    .Build();
            var builder = new DbContextOptionsBuilder<ApplicationDbContext>();
            var connectionString = configuration.GetConnectionString("DefaultConnection");
            builder.UseSqlServer(connectionString);
            return new ApplicationDbContext(builder.Options);
        }
    }

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=(LocalDB)\\MSSQLLocalDB;Initial Catalog=ASPNETCore2CookieAuthenticationDB;Integrated Security=True;MultipleActiveResultSets=True;"
  },
  "LoginCookieExpirationDays": 30
}

    public interface IUsersService
    {
        Task<string> GetSerialNumberAsync(int userId);
        Task<User> FindUserAsync(string username, string password);
        Task<User> FindUserAsync(int userId);
        Task UpdateUserLastActivityDateAsync(int userId);
    }

    public interface IRolesService
    {
        Task<List<Role>> FindUserRolesAsync(int userId);
        Task<bool> IsUserInRole(int userId, string roleName);
        Task<List<User>> FindUsersInRoleAsync(string roleName);
    }

    public interface IDbInitializerService
    {
        void Initialize();
        void SeedData();
    }

    public interface ICookieValidatorService
    {
        Task ValidateAsync(CookieValidatePrincipalContext context);
    }

    public class CookieValidatorService : ICookieValidatorService
    {
        private readonly IUsersService _usersService;
        public CookieValidatorService(IUsersService usersService)
        {
            _usersService = usersService;
            _usersService.CheckArgumentIsNull(nameof(usersService));
        }

        public async Task ValidateAsync(CookieValidatePrincipalContext context)
        {
            var userPrincipal = context.Principal;

            var claimsIdentity = context.Principal.Identity as ClaimsIdentity;
            if (claimsIdentity?.Claims == null || !claimsIdentity.Claims.Any())
            {
                // this is not our issued cookie
                await handleUnauthorizedRequest(context);
                return;
            }

            var serialNumberClaim = claimsIdentity.FindFirst(ClaimTypes.SerialNumber);
            if (serialNumberClaim == null)
            {
                // this is not our issued cookie
                await handleUnauthorizedRequest(context);
                return;
            }

            var userIdString = claimsIdentity.FindFirst(ClaimTypes.UserData).Value;
            if (!int.TryParse(userIdString, out int userId))
            {
                // this is not our issued cookie
                await handleUnauthorizedRequest(context);
                return;
            }

            var user = await _usersService.FindUserAsync(userId).ConfigureAwait(false);
            if (user == null || user.SerialNumber != serialNumberClaim.Value || !user.IsActive)
            {
                // user has changed his/her password/roles/stat/IsActive
                await handleUnauthorizedRequest(context);
            }

            await _usersService.UpdateUserLastActivityDateAsync(userId).ConfigureAwait(false);
        }

        private Task handleUnauthorizedRequest(CookieValidatePrincipalContext context)
        {
            context.RejectPrincipal();
            return context.HttpContext.SignOutAsync(CookieAuthenticationDefaults.AuthenticationScheme);
        }
    }

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddScoped<IUnitOfWork, ApplicationDbContext>();
            services.AddScoped<IUsersService, UsersService>();
            services.AddScoped<IRolesService, RolesService>();
            services.AddScoped<ISecurityService, SecurityService>();
            services.AddScoped<ICookieValidatorService, CookieValidatorService>();
            services.AddScoped<IDbInitializerService, DbInitializerService>();

            services.AddDbContext<ApplicationDbContext>(options =>
            {
                options.UseSqlServer(
                    Configuration.GetConnectionString("DefaultConnection"),
                    serverDbContextOptionsBuilder =>
                        {
                            var minutes = (int)TimeSpan.FromMinutes(3).TotalSeconds;
                            serverDbContextOptionsBuilder.CommandTimeout(minutes);
                            serverDbContextOptionsBuilder.EnableRetryOnFailure();
                        });
            });

            // Only needed for custom roles.
            services.AddAuthorization(options =>
                    {
                        options.AddPolicy(CustomRoles.Admin, policy => policy.RequireRole(CustomRoles.Admin));
                        options.AddPolicy(CustomRoles.User, policy => policy.RequireRole(CustomRoles.User));
                    });

            // Needed for cookie auth.
            services
                .AddAuthentication(options =>
                {
                    options.DefaultChallengeScheme = CookieAuthenticationDefaults.AuthenticationScheme;
                    options.DefaultSignInScheme = CookieAuthenticationDefaults.AuthenticationScheme;
                    options.DefaultAuthenticateScheme = CookieAuthenticationDefaults.AuthenticationScheme;
                })
                .AddCookie(options =>
                {
                    options.SlidingExpiration = false;
                    options.LoginPath = "/api/account/login";
                    options.LogoutPath = "/api/account/logout";
                    //options.AccessDeniedPath = new PathString("/Home/Forbidden/");
                    options.Cookie.Name = ".my.app1.cookie";
                    options.Cookie.HttpOnly = true;
                    options.Cookie.SecurePolicy = CookieSecurePolicy.SameAsRequest;
                    options.Cookie.SameSite = SameSiteMode.Lax;
                    options.Events = new CookieAuthenticationEvents
                    {
                        OnValidatePrincipal = context =>
                        {
                            var cookieValidatorService = context.HttpContext.RequestServices.GetRequiredService<ICookieValidatorService>();
                            return cookieValidatorService.ValidateAsync(context);
                        }
                    };
                });

public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
   app.UseAuthentication();

            var scopeFactory = app.ApplicationServices.GetRequiredService<IServiceScopeFactory>();
            using (var scope = scopeFactory.CreateScope())
            {
                var dbInitializer = scope.ServiceProvider.GetService<IDbInitializerService>();
                dbInitializer.Initialize();
                dbInitializer.SeedData();
            }

        [AllowAnonymous]
        [HttpPost("[action]")]
        public async Task<IActionResult> Login([FromBody]  User loginUser)
        {
            if (loginUser == null)
            {
                return BadRequest("user is not set.");
            }

            var user = await _usersService.FindUserAsync(loginUser.Username, loginUser.Password).ConfigureAwait(false);
            if (user == null || !user.IsActive)
            {
                await HttpContext.SignOutAsync(CookieAuthenticationDefaults.AuthenticationScheme);
                return Unauthorized();
            }

            var loginCookieExpirationDays = _configuration.GetValue<int>("LoginCookieExpirationDays", defaultValue: 30);
            var cookieClaims = await createCookieClaimsAsync(user).ConfigureAwait(false);
            await HttpContext.SignInAsync(
                CookieAuthenticationDefaults.AuthenticationScheme,
                cookieClaims,
                new AuthenticationProperties
                {
                    IsPersistent = true, // "Remember Me"
                    IssuedUtc = DateTimeOffset.UtcNow,
                    ExpiresUtc = DateTimeOffset.UtcNow.AddDays(loginCookieExpirationDays)
                });

            await _usersService.UpdateUserLastActivityDateAsync(user.Id).ConfigureAwait(false);

            return Ok();
        }

        private async Task<ClaimsPrincipal> createCookieClaimsAsync(User user)
        {
            var identity = new ClaimsIdentity(CookieAuthenticationDefaults.AuthenticationScheme);
            identity.AddClaim(new Claim(ClaimTypes.NameIdentifier, user.Id.ToString()));
            identity.AddClaim(new Claim(ClaimTypes.Name, user.Username));
            identity.AddClaim(new Claim("DisplayName", user.DisplayName));

            // to invalidate the cookie
            identity.AddClaim(new Claim(ClaimTypes.SerialNumber, user.SerialNumber));

            // custom data
            identity.AddClaim(new Claim(ClaimTypes.UserData, user.Id.ToString()));

            // add roles
            var roles = await _rolesService.FindUserRolesAsync(user.Id).ConfigureAwait(false);
            foreach (var role in roles)
            {
                identity.AddClaim(new Claim(ClaimTypes.Role, role.Name));
            }

            return new ClaimsPrincipal(identity);
        }

[Route("api/[controller]")]
[Authorize(Policy = CustomRoles.Admin)]
public class MyProtectedAdminApiController : Controller

        [AllowAnonymous]
        [HttpGet("[action]"), HttpPost("[action]")]
        public async Task<bool> Logout()
        {
            await HttpContext.SignOutAsync(CookieAuthenticationDefaults.AuthenticationScheme);
            return true;
        }

@import url('open-iconic/font/css/open-iconic-bootstrap.min.css');

dotnet tool update -g Microsoft.Web.LibraryManager.Cli
libman init
libman install bootstrap --provider unpkg --destination wwwroot/lib/bootstrap
libman install open-iconic --provider unpkg --destination wwwroot/lib/open-iconic
libman install jquery --provider unpkg --destination wwwroot/lib/jquery
libman install toastr --provider unpkg --destination wwwroot/lib/toastr

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8" />
    <meta
      name="viewport"
      content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no"
    />
    <title>BlazorWasm.Client</title>
    <base href="/" />

    <link href="lib/toastr/build/toastr.min.css" rel="stylesheet" />
    <link
      href="lib/open-iconic/font/css/open-iconic-bootstrap.min.css"
      rel="stylesheet"
    />
    <link href="lib/bootstrap/dist/css/bootstrap.min.css" rel="stylesheet" />
    <link href="css/app.css" rel="stylesheet" />
    <link href="BlazorWasm.Client.styles.css" rel="stylesheet" />
  </head>

  <body>
    <div id="app">Loading...</div>

    <div id="blazor-error-ui">
      An unhandled error has occurred.
      <a href="" class="reload">Reload</a>
      <a class="dismiss">🗙</a>
    </div>

    <script src="lib/jquery/dist/jquery.min.js"></script>
    <script src="lib/toastr/build/toastr.min.js"></script>
    <script src="js/common.js"></script>
    <script src="_framework/blazor.webassembly.js"></script>
  </body>
</html>

.valid.modified:not([type="checkbox"]) {
  outline: 1px solid #26b050;
}

.invalid {
  outline: 1px solid red;
}

.validation-message {
  color: red;
}

#blazor-error-ui {
  background: lightyellow;
  bottom: 0;
  box-shadow: 0 -1px 2px rgba(0, 0, 0, 0.2);
  display: none;
  left: 0;
  padding: 0.6rem 1.25rem 0.7rem 1.25rem;
  position: fixed;
  width: 100%;
  z-index: 1000;
}

#blazor-error-ui .dismiss {
  cursor: pointer;
  position: absolute;
  right: 0.75rem;
  top: 0.5rem;
}

.spinner {
  border: 16px solid silver !important;
  border-top: 16px solid #337ab7 !important;
  border-radius: 50% !important;
  width: 80px !important;
  height: 80px !important;
  animation: spin 700ms linear infinite !important;
  top: 50% !important;
  left: 50% !important;
  transform: translate(-50%, -50%);
  position: absolute !important;
}

@keyframes spin {
  0% {
    transform: rotate(0deg);
  }

  100% {
    transform: rotate(360deg);
  }
}

.room-image {
  display: block;
  width: 100%;
  height: 150px;
  background-size: cover !important;
  border: 3px solid green;
  position: relative;
}

.room-image-title {
  position: absolute;
  top: 0;
  right: 0;
  background-color: green;
  color: white;
  padding: 0px 6px;
  display: inline-block;
}

window.ShowToastr = (type, message) => {
  if (type === "success") {
    toastr.success(message, "Operation Successful", { timeOut: 10000 });
  }
  if (type === "error") {
    toastr.error(message, "Operation Failed", { timeOut: 10000 });
  }
};

@using BlazorWasm.Client.Utils

@inherits LayoutComponentBase

<NavMenu />
<div>
  @Body
</div>

<nav class="navbar navbar-expand-sm navbar-dark bg-dark p-0">
    <a class="navbar-brand mx-4" href="#">Navbar</a>
    <button class="navbar-toggler" type="button" data-toggle="collapse"
            data-target="#navbarSupportedContent"
            aria-controls="navbarSupportedContent"
            aria-expanded="false"
            aria-label="Toggle navigation">
        <span class="navbar-toggler-icon"></span>
    </button>
    <div class="collapse navbar-collapse pr-2" id="navbarSupportedContent">
        <ul class="navbar-nav mr-auto"></ul>
        <ul class="my-0 navbar-nav">
            <li class="nav-item p-0">
                <NavLink class="nav-link" href="registration">
                    <span class="p-2">
                        Register
                    </span>
                </NavLink>
            </li>
            <li class="nav-item p-0">
                <NavLink class="nav-link" href="login">
                    <span class="p-2">
                        Login
                    </span>
                </NavLink>
            </li>
        </ul>
    </div>
</nav>

@page "/"

<form>
    <div class="row p-0 mx-0 mt-4">
        <div class="col-12 col-md-5  offset-md-1 pl-2  pr-2 pr-md-0">
            <div class="form-group">
                <label>Check In Date</label>
                <input type="text" class="form-control" />
            </div>
        </div>
        <div class="col-8 col-md-3 pl-2 pr-2">
            <div class="form-group">
                <label>No. of nights</label>
                <select class="form-control">
                    @for (var i = 1; i <= 10; i++)
                    {
                        <option value="@i">@i</option>
                    }
                </select>
            </div>
        </div>
        <div class="col-4 col-md-2 p-0 pr-2">
            <div class="form-group">
                <label>&nbsp;</label>
                <input type="submit" value="Go" class="btn btn-success btn-block" />
            </div>
        </div>
    </div>
</form>

using System;

namespace BlazorWasm.Client.Models.ViewModels
{
    public class HomeVM
    {
        public DateTime StartDate { get; set; } = DateTime.Now;

        public DateTime EndDate { get; set; }

        public int NoOfNights { get; set; } = 1;
    }
}

using BlazorWasm.Client.Models.ViewModels

@code{
    HomeVM HomeModel = new HomeVM();
}

<EditForm Model="HomeModel">
 // ...
</EditForm>

<InputDate min="@DateTime.Now.ToString("yyyy-MM-dd")"
           @bind-Value="HomeModel.StartDate"
           type="text"
           class="form-control" />

<select @bind="HomeModel.NoOfNights">

dotnet add package Blazored.LocalStorage

namespace BlazorWasm.Client
{
    public class Program
    {
        public static async Task Main(string[] args)
        {
            var builder = WebAssemblyHostBuilder.CreateDefault(args);
            // ...
            builder.Services.AddBlazoredLocalStorage();
            // ...
        }
    }
}

@using Blazored.LocalStorage

@page "/"

@inject ILocalStorageService LocalStorage
@inject IJSRuntime JsRuntime

<EditForm Model="HomeModel" OnValidSubmit="SaveInitialData">

@code{
    HomeVM HomeModel = new HomeVM();

    private async Task SaveInitialData()
    {
        try
        {
            HomeModel.EndDate = HomeModel.StartDate.AddDays(HomeModel.NoOfNights);
            await LocalStorage.SetItemAsync("InitialRoomBookingInfo", HomeModel);
        }
        catch (Exception e)
        {
            await JsRuntime.ToastrError(e.Message);
        }
    }
}

namespace BlazorServer.Common
{
    public static class ConstantKeys
    {
        public const string LocalInitialBooking = "InitialRoomBookingInfo";
    }
}

<Project Sdk="Microsoft.NET.Sdk.BlazorWebAssembly">
  <ItemGroup>
    <ProjectReference Include="..\..\BlazorServer\BlazorServer.Common\BlazorServer.Common.csproj" />
  </ItemGroup>
</Project>

@using BlazorServer.Common

await LocalStorage.SetItemAsync(ConstantKeys.LocalInitialBooking, HomeModel);

@page "/hotel/rooms"

<h3>HotelRooms</h3>

@code {

}

@page "/"

@inject ILocalStorageService LocalStorage
@inject IJSRuntime JsRuntime
@inject NavigationManager NavigationManager


@code{
    HomeVM HomeModel = new HomeVM();

    private async Task SaveInitialData()
    {
        try
        {
            HomeModel.EndDate = HomeModel.StartDate.AddDays(HomeModel.NoOfNights);
            await LocalStorage.SetItemAsync(ConstantKeys.LocalInitialBooking, HomeModel);
            NavigationManager.NavigateTo("hotel/rooms");
        }
        catch (Exception e)
        {
            await JsRuntime.ToastrError(e.Message);
        }
    }
}

CREATE INDEX IX_Contacts_FirstName ON dbo.Contacts(FirstName);

Sql Server این بار به جای اسکن دوباره‌ی ایندکس خوشه دار، با استفاده از Index Seek و بهره بردن از ایندکس ایجاد شده‌ی توسط ما، یک پلن قابل قبول را برای ما ارائه داده است.

Index Intersection

در برخی از سناریوها لازم است یکسری ستون دیگر هم علاوه بر ستونی که ایندکس را بر روی آن تعریف کرده‌ایم، در بخش شرط یا خروجی select استفاده شوند. یکی از راه‌حل‌ها، ایجاد یک ایندکس غیر خوشه‌ای که سایر ستون‌ها را نیز Include می‌کند، می‌باشد. با وجود ایندکس‌هایی که هر کدام از آنها می‌توانند برای ادا کردن بخشی از شروط، نقش ایفا کنند، Sql Server  هم با به کار بردن آنها می‌تواند رکوردهایی که در فصل مشترک حاصل از جسجتوی این ایندکس‌ها بدست آمده را به عنوان خروجی کوئری ما بازگشت دهد. این عملیات Index Intersection نام دارد. به مثال زیر توجه کنید:

SET STATISTICS IO ON;

SELECT ContactID,
       FirstName,
       LastName
FROM   dbo.Contacts
WHERE  FirstName = 'Catherine'
       AND LastName = 'Cox';

SET STATISTICS IO OFF;

در کوئری بالا علاوه بر FirstName که یک ایندکس غیر خوشه دار را بر روی آن ایجاد کرده‌ایم، فیلد LastName را هم در بخش Select و شرط، مطرح کرده‌ایم. حالا اگر آن را اجرا کنیم، به آمار و پلن زیر دست خواهیم یافت:

بله تعداد Page‌های خوانده شده این بار به 68 افزایش یافته است که نسبت به حالت بدون LastName که 2 Page خوانده شده بود، زیاد است. همانطور که در پلن زیر مشخص است، به دلیل ایندکسی که برروی FirstName ایجاد کرده‌ایم، نمی‌تواند تمام داده‌های مورد نیاز کوئری را مهیا کند. عملیات Key Lookup و nested loop هم این بار اضافه شده‌اند. Sql Server همچنان استفاده از ایندکس موجود را در کنار Key Lookup از ایندکس خوشه دار، ارزان‌تر از اسکن ایندکس خوشه دار، تشخیص داده است.

مشکل زمانی گریبان گیر ما خواهد شد که به ازای هر مطابقتی در ایندکس غیر خوشه دار، یک بار به ایندکس خوشه دار برای بررسی شرط بعدی و واکشی دیتا، رجوع خواهد شد. باید دقت کرد که Key Lookup همیشه به عنوان مشکل مطرح نمی‌شود. ولی باعث افزایش غیرضروری هزینه‌های CPU و I/O برای کوئری خواهد شد.

برای استفاده از الگوی Index Intersection، یک ایندکس غیر خوشه دار برروی ستون LastName ایجاد خواهیم کرد:

CREATE INDEX IX_Contacts_LastName ON dbo.Contacts(LastName);

اگر این بار کوئری قبل را اجرا کنیم، به آمار و پلن زیر خواهیم رسید:

بله تعداد Page‌های خوانده شده به 5 کاهش یافته و این بار به جای استفاده از Key Lookup، از دو index seek استفاده کرده است که هزینه‌ای کمتر را نسبت به حالت قبل خواهد داشت. به دلیل اینکه این دو ایندکس تمام دیتای لازم را می‌توانند مهیا کنند، دیگر نیازی به رجوع به ایندکس خوشه دار نخواهد بود. تصویر زیر در درک پلن بالا و این الگو می‌تواند مفید باشد:

Multiple Columns

در دو الگوی قبل، بیشتر به ایجاد ایندکس‌، بر روی یک ستون متمرکز شده بودیم. اگر تعدادی از ستون‌ها در بخش شروط مربوط به کوئری مطرح شوند، بهتر است آنها را در قالب یک ایندکس نگهداری کنیم. برای نشان دادن تأثیر این مورد،  یک ایندکس غیر خوشه دار را بر روی دو ستون ایجاد می‌کنیم: 

CREATE INDEX IX_Contacts_FirstNameLastName
    ON dbo.Contacts(FirstName, LastName);

SET STATISTICS IO ON;

SELECT ContactID,
       FirstName,
       LastName
FROM   dbo.Contacts
WHERE  FirstName = 'Catherine'
       AND LastName = 'Cox';

SET STATISTICS IO OFF;

با اجرای کوئری بالا به آمار و پلن زیر خواهیم رسید:

باید توجه داشت هر زمان که نیاز است یکسری فیلد، در قسمت شرطی خیلی از کوئری‌ها تکرار شوند، ایجاد کردن یک ایندکس برروی آنها به صورت یکجا، ایده‌ی خوبی خواهد بود.

الگوی Multiple Columns هم به مانند الگوی Search Columns باید هنگام ایندکس گذاری دیتابیس در نظر گرفته شود و از اهمیت بالایی برخوردار است. باید توجه داشت اگر فیلدهایی که در قسمت شرطی کوئری مطرح می‌شوند، متغییر باشد، استفاده از الگوی Index Intersection مفید خواهد. ولی برای مواقعی که نیاز است یکسری فیلد به صورت یکجا در بخش شرطی کوئری مطرح شوند، الگوی Multiple Columns کارآیی بهتری خواهد داشت. از این دو الگوی مطرح شده که در تناقض باهم قرار دارند، می‌توان به نحوی استفاده برد تا هزینه‌ی کلی را کاهش داد.

Covering Index

الگوی بعدی، ایندکس پوشش دهنده نام گرفته است. همانند نامی که دارد، هدف آن نگهداری یکسری ستون در ستون‌های ایندکس تولیدی که اتفاقا این ستون‌ها در قسمت شرطی کوئری قرار ندارند، ولی قرار است به عنوان خروجی Select برگردانده شوند، می‌باشد.
این الگو به عنوان یک روش استاندارد ایندکس گذاری در Sql Server مطرح بوده است. البته در ادامه و با بروز شدن روش‌هایی که می‌توان ایندکس‌ها را ایجاد کرد، این الگو نسبت به قبل کمتر مفید است! از آن جهت که یک روش شناخته شده می‌باشد، در این قسمت این مورد را هم مطرح کردیم. به مثال زیر توجه کنید:

SET STATISTICS IO ON;

SELECT ContactID,
       FirstName,
       LastName,
       IsActive
FROM   dbo.Contacts
WHERE  FirstName = 'Catherine'
       AND LastName = 'Cox';

SET STATISTICS IO OFF;

در کوئری بالا این بار قصد داریم خصوصیت IsActive را که در ایندکس IX_Contacts_FirstNameLastName نگهداری نمی‌شود و همچنین در قسمت شرطی هم مطرح نشده و نیازی به آن نبوده، هم واکشی کنیم. با توجه به نتایج بدست آمده که در آمار و پلن زیر مشخص است، باز هم تعداد Page‌های خوانده شده به 5 افزایش یافته و بار دیگر، Key Lookup و Nested Loop را در کنار یک Index Seek، برروی ایندکسی که با الگوی Multiple Columns ایجاد کرده‌ایم، خواهیم داشت.

الگوی index covering پیشنهاد می‌کند ستونی را هم که در قسمت شرطی مطرح نمی‌شود، به عنوان ستونی اصلی در ایندکس، نگهداری کنیم؛ به شکل زیر:

CREATE INDEX IX_Contacts_FirstNameLastNameIsActive ON dbo.Contacts(FirstName, LastName,IsActive)

ایندکس غیر خوشه دار بالا، 3 فیلدی را که قرار است در بخش شرطی مطرح شوند، یا به عنوان خروجی Select برگردانده شوند، در بر می‌گیرد. سپس کوئری قبلی را دوباره اجرا میکنیم. به نتایج زیر خواهیم رسید:

باز هم هزینه‌ی Key Lookup حذف شده و این بار از ایندکس جدید ما استفاده شده و تعداد Page‌های خوانده شده هم به 2 کاهش یافته است.
این الگو در بیشتر سناریو‌ها کاملا مفید بوده و پتانسیل افزایش کارآیی را در بیشتر سناریو‌ها دارد. اما در سال‌های اخیر از زمانیکه امکانات جدیدی در Sql Server 2005 به بعد ایجاد شد، از استفاده‌ی آن کاسته شده است. با وجود این امکانات جدید که در الگوی بعد به آن خواهیم پرداخت، می‌توان ستون‌های اضافی را در ایندکس‌ها، Include کنیم و نیازی نیست که جزء ستون‌های اصلی ایندکس باشند. 

Included Columns

الگوی Included Columns درواقعا پسر عموی الگوی Covering Index می‌باشد. در این الگو از عبارت INCLUDE در ایجاد یا تغییر ایندکس استفاده می‌شود و از این طریق امکان این را مهیا می‌کند تا یکسری ستون که جز ستون‌های اصلی ایندکس نیستند هم در ایندکس غیر خوشه دار ما افزوده شوند و حتی در قسمت شرطی هم مطرح شوند. این عمل خیلی شبیه به نگهداری دیتا‌های غیر کلیدی در یک ایندکس خوشه دار می‌باشد و این همان تفاوت اصلی بین دو الگو مطرح شده است.

اگر کوئری زیر را اجرا کنیم:

SET STATISTICS IO ON;

SELECT ContactID,
       FirstName,
       LastName,
       EmailAddress
FROM   dbo.Contacts
WHERE  FirstName = 'Catherine';

SET STATISTICS IO OFF;

68 Page خوانده شده خواهیم داشت که حاصل یک Index Seek بر روی ایندکس IX_Contacts_FirstName می‌باشد و برای واکشی بقیه ستون‌ها هم یک Key Lookup بر روی ایندکس خوشه دار در پلن مشخص خواهد بود.

علاوه بر ایندکس‌های ایجاد شده‌ی در مراحل قبل، حال یک ایندکس غیر خوشه‌ای را با استفاده از الگوی INC ایجاد می‌کنیم:

CREATE INDEX IX_Contacts_FirstNameINC ON dbo.Contacts(FirstName)
INCLUDE (LastName, IsActive, EmailAddress);

دوباره کوئری قبلی را اگر اجرا کنیم، نتایج به دست آمده، به شرح زیر خواهد بود:

این بار از ایندکس جدید ایجاد شده استفاده شده و تعداد Page‌های خوانده شده، به 3 کاهش یافته است. با توجه به انعطاف پذیری این الگو می‌توان از اندک افزایشی که در تعداد Page‌های خوانده شده نسبت به الگوی ایندکس پوشش دهنده وجود دارد، چشم پوشی کرد.
در مثال‌های قبل چندین ایندکس بر روی جدول Contacts ایجاد کرده‌ایم که 4 مورد از آنها به صورت اختصاصی بر روی فیلد FirstName بوده است. باید توجه کرد این ایندکس‌ها نیاز به فضا و نگهداری در مواقع ویرایش رکورد‌های جدول خواهند داشت. لذا این هزینه‌ها اثر منفی برروی تمام عملیاتی خواهند داشت که روی جدول انجام می‌شود.
الگوی INC می‌تواند این مشکل را برطرف کند. برای مثال با استفاده از آن می‌توان ایندکس‌های تولید شده‌ی در مراحل قبل را بر روی FirstName، توسط یک ایندکس نیز پوشش داد. لذا ایندکس‌های قبلی را حذف کرده و با یکسری کوئری، مشخص خواهیم کرد که گفته‌ی ما صحت دارد:

IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.Contacts')
AND name = 'IX_Contacts_FirstNameLastName')
DROP INDEX IX_Contacts_FirstNameLastName ON dbo.Contacts
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.Contacts')
AND name = 'IX_Contacts_FirstNameLastNameIsActive')
DROP INDEX IX_Contacts_FirstNameLastNameIsActive ON dbo.Contacts
GO
IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.Contacts')
AND name = 'IX_Contacts_FirstName')
DROP INDEX IX_Contacts_FirstName ON dbo.Contacts
GO

با کدهای بالا ایندکس‌هایی را که بر روی FirstName ایجاد شده بودند، حذف کرده و این بار تمام کوئری‌های مطرح شده‌ی در مراحل قبل را یکبار دیگر اجرا می‌کنیم:

SET STATISTICS IO ON;

SELECT ContactID,
       FirstName
FROM   dbo.Contacts
WHERE  FirstName = 'Catherine';

SELECT ContactID,
       FirstName,
       LastName
FROM   dbo.Contacts
WHERE  FirstName = 'Catherine'
       AND LastName = 'Cox';

SELECT ContactID,
       FirstName,
       LastName,
       IsActive
FROM   dbo.Contacts
WHERE  FirstName = 'Catherine'
       AND LastName = 'Cox';

SET STATISTICS IO OFF;

دو نکته‌ای که باید به آنها توجه کرد:

1. کوئری‌ها بالا در مقایسه با الگوهای قبلی به چه شکلی اجرا خواهند شد؟
2. توجه کردن به تعداد Page‌های خوانده شده

در جواب مورد دوم، با اینکه حدود 50% افزایش در تعداد Page‌های خوانده شده نسبت به حالتی که به صورت جدا از هم برای هر کوئری خاص یک ایندکس در نظر گرفته بودیم، داشته‌ایم ولی این تغییر کارآیی نمی‌تواند ساخت 4 ایندکس را به جای 1 ایندکس که تمام آنها را پوشش می‌دهد، توجیه کند! در حالیکه ما به کارآیی مورد نظر خود دست یافته‌ایم.

در نتیجه الگوی INC هنگام ساخت ایندکس‌های غیر خوشه دار خیلی مهم است و باید به آن توجه زیادی کرد. بیشتر در مواقعی‌که نیاز است عملیات Lookup را حذف کنید و سرعت خواندن و کارآیی اجرای کوئری را افزایش دهید، این الگو مناسب خواهد بود. همچنین با کاهش تعداد ایندکس‌ها برای پوشش دادن ایندکس‌های لازم برای کوئری‌ها مشابه، باید توجه کرد که باز هم نسبت به حالتی که هیچ ایندکس غیر خوشه داری ایجاد نشده، کارآیی افزایش می‌یابد.

Filtered Indexes

ممکن است در برخی از جداول دیتابیس، یکسری رکوردهایی با مقدار‌هایی که به ندرت یا هرگز از آنها در یک برنامه‌ی کاربردی استفاده نخواهد شد، ذخیره شده باشند. در این مواقع، حذف آنها از نتیجه‌ی خروجی کوئری‌ها می‌تواند خیلی مفید باشد. یا در مواقعی می‌توان از این مورد برای مشخص کردن یک زیر مجموعه‌ی از داده‌های جدول، برای ایجاد ایندکس استفاده کرد. همچنین می‌توان به جای کوئری زدن بر روی میلیون‌ها رکورد موجود در جدول، ایندکس‌ها را طوری ایجاد کرد که پوشش دهنده‌ی بخشی از دیتای چند میلیونی باشند.

SET STATISTICS IO ON;

SELECT   ContactID,
         FirstName,
         LastName,
         CertificationDate
FROM     dbo.Contacts
WHERE    CertificationDate IS NOT NULL
ORDER BY CertificationDate;

SELECT   ContactID,
         FirstName,
         LastName,
         CertificationDate
FROM     dbo.Contacts
WHERE    CertificationDate BETWEEN '20050101' AND '20050201'
ORDER BY CertificationDate;

SET STATISTICS IO OFF;

CREATE INDEX IX_Contacts_CertificationDate ON dbo.Contacts(CertificationDate)
INCLUDE (FirstName, LastName)
WHERE CertificationDate IS NOT NULL;

USE AdventureWorks2012;


GO
CREATE TABLE dbo.Customer (
    CustomerID  INT        ,
    FillterData CHAR (1000),
    CONSTRAINT PK_Customer_CustomerID PRIMARY KEY CLUSTERED (CustomerID)
);

CREATE TABLE dbo.SalesOrderHeader (
    SalesOrderID INT        ,
    OrderDate    DATETIME   ,
    DueDate      DATETIME   ,
    CustomerID   INT        ,
    FillterData  CHAR (1000),
    CONSTRAINT PK_SalesOrderHeader_SalesOrderID PRIMARY KEY CLUSTERED (SalesOrderID),
    CONSTRAINT GK_SalesOrderHeader_CustomerID_FROM_Customer FOREIGN KEY (CustomerID) REFERENCES dbo.Customer (CustomerID)
);

INSERT INTO dbo.Customer (CustomerID)
SELECT CustomerID
FROM   Sales.Customer;

INSERT INTO dbo.SalesOrderHeader (SalesOrderID, OrderDate, DueDate, CustomerID)
SELECT SalesOrderID,
       OrderDate,
       DueDate,
       CustomerID
FROM   Sales.SalesOrderHeader;

SET STATISTICS IO ON;

DELETE dbo.Customer
WHERE  CustomerID = 701;

SET STATISTICS IO OFF;

CREATE INDEX IS_SalesOrderHeader_CustomerID ON dbo.SalesOrderHeader(CustomerID)

public partial class AddIndexes : DbMigration
    {
        private const string IndexName = "IX_LogSamples";

        public override void Up()
        {
            Sql(String.Format(@"CREATE NONCLUSTERED INDEX [{0}]
                               ON [dbo].[Logs] ([SampleId],[Date])
                               INCLUDE ([Value])", IndexName));

        }

        public override void Down()
        {
            DropIndex("dbo.Logs", IndexName);
        }
    }

using System;
using System.Reflection.Emit;

namespace FastReflectionTests
{
    class Program
    {
        static int Calculate(int a, int b, int c)
        {
            var result = a * b;
            return result - c;
        }

        static void Main(string[] args)
        {
            //روش متداول
            Console.WriteLine(Calculate(10, 2, 3));

            //تعریف امضای متد
            var myMethod = new DynamicMethod(
                                        name: "CalculateMethod",
                                        returnType: typeof(int),
                                        parameterTypes: new[] { typeof(int), typeof(int), typeof(int) },
                                        m: typeof(Program).Module);
            //تعریف بدنه متد
            var il = myMethod.GetILGenerator();

            il.Emit(opcode: OpCodes.Ldarg_0); // بارگذاری اولین آرگومان بر روی پشته ارزیابی 
            il.Emit(opcode: OpCodes.Ldarg_1); // بارگذاری دومین آرگومان بر روی پشته ارزیابی 
            il.Emit(opcode: OpCodes.Mul); // انجام عملیات ضرب
            il.Emit(opcode: OpCodes.Stloc_0); // ذخیره سازی نتیجه عملیات ضرب در یک متغیر محلی
            il.Emit(opcode: OpCodes.Ldloc_0); // متغیر محلی را بر روی پشته ارزیابی قرار می‌دهد تا در عملیات بعدی قابل استفاده باشد
            il.Emit(opcode: OpCodes.Ldarg_2); // آرگومان سوم را بر روی پشته ارزیابی قرار می‌دهد
            il.Emit(opcode: OpCodes.Sub); // انجام عملیات تفریق
            il.Emit(opcode: OpCodes.Ret); // بازگشت نتیجه

            //فراخوانی متد پویا
            var method = (Func<int, int, int, int>)myMethod.CreateDelegate(typeof(Func<int, int, int, int>));
            Console.WriteLine(method(10, 2, 3));

        }
    }
}

 System.InvalidProgramException was unhandled
Message=Common Language Runtime detected an invalid program.

 il.DeclareLocal(typeof(int));

        static int Calculate(int x)
        {
            int result = 0;
            for (int i = 0; i < 10; i++)
            {
                result += i * x;
            }
            return result;
        }

using System;
using System.Reflection.Emit;

namespace FastReflectionTests
{
    class Program
    {
        static int Calculate(int x)
        {
            int result = 0;
            for (int i = 0; i < 10; i++)
            {
                result += i * x;
            }
            return result;
        }

        static void Main(string[] args)
        {
            //روش متداول
            Console.WriteLine(Calculate(10));

            //تعریف امضای متد
            var myMethod = new DynamicMethod(
                                        name: "CalculateMethod",
                                        returnType: typeof(int), // خروجی متد عدد صحیح است
                                        parameterTypes: new[] { typeof(int) }, // یک پارامتر عدد صحیح دارد
                                        m: typeof(Program).Module);
            //تعریف بدنه متد
            var il = myMethod.GetILGenerator();

            // از برچسب‌ها برای انتقال کنترل استفاده می‌شود
            // در اینجا به دو برچسب برای تعریف ابتدای حلقه
            // و همچنین برای پرش به جایی که متد خاتمه می‌یابد نیاز داریم
            var loopStart = il.DefineLabel();
            var methodEnd = il.DefineLabel();

            // variable 0; result = 0
            il.DeclareLocal(typeof(int)); //  برای تعریف متغیر محلی نتیجه عملیات
            il.Emit(OpCodes.Ldc_I4_0); // عدد ثابت صفر را بر روی پشته ارزیابی قرار می‌دهد
            il.Emit(OpCodes.Stloc_0); // و نهایتا این عدد ثابت به متغیر محلی انتساب داده خواهد شد

            // variable 1; i = 0
            il.DeclareLocal(typeof(int)); // در اینجا کار تعریف و مقدار دهی متغیر حلقه انجام می‌شود
            il.Emit(OpCodes.Ldc_I4_0); // عدد ثابت صفر را بر روی پشته ارزیابی قرار می‌دهد
            il.Emit(OpCodes.Stloc_1); // و نهایتا این عدد ثابت به متغیر حلقه در ایندکس یک انتساب داده خواهد شد

            // در اینجا کار تعریف بدنه حلقه شروع می‌شود
            il.MarkLabel(loopStart); // شروع حلقه را علامتگذاری می‌کنیم تا بعدا بتوانیم به این نقطه پرش نمائیم
            il.Emit(OpCodes.Ldloc_1); // در ادامه می‌خواهیم بررسی کنیم که آیا مقدار متغیر حلقه از عدد 10 کوچکتر است یا خیر
            il.Emit(OpCodes.Ldc_I4, 10); // عدد ثابت ده را بر روی پشته ارزیابی قرار می‌دهد
            // برای انجام بررسی‌های تساوی یا کوچکتر یا بزرگتر نیاز است ابتدا دو متغیر مدنظر بر روی پشته قرار گیرند
            il.Emit(OpCodes.Bge, methodEnd);  // اگر اینطور نیست و مقدار متغیر از 10 کمتر نیست، کنترل برنامه را به انتهای متد هدایت خواهیم کرد

            // i * x
            il.Emit(OpCodes.Ldloc_1); // مقدار متغیر حلقه را بر روی پشته قرار می‌دهد
            il.Emit(OpCodes.Ldarg_0); // مقدار اولین آرگومان متد را بر روی پشته قرار می‌دهد
            il.Emit(OpCodes.Mul); // انجام عملیات ضرب
            // نتیجه این عملیات اکنون بر روی پشته قرار گرفته است

            // result += 
            il.Emit(OpCodes.Ldloc_0); // متغیر نتیجه را بر روی پشته قرار می‌دهد
            il.Emit(OpCodes.Add); // اکنون عملیات جمع بر روی نتیجه ضرب قسمت قبل که بر روی پشته قرار دارد و همچنین متغیر نتیجه انجام می‌شود
            il.Emit(OpCodes.Stloc_0); // ذخیره سازی نتیجه در متغیر محلی

            // i++
            // در اینجا کار افزایش متغیر حلقه انجام می‌شود
            il.Emit(OpCodes.Ldloc_1); // مقدار متغیر حلقه بر روی پشته قرار می‌گیرد
            il.Emit(OpCodes.Ldc_I4_1); // عدد ثابت یک بر روی پشته قرار می‌گیرد
            il.Emit(OpCodes.Add); // سپس این دو عدد بارگذاری شده با هم جمع خواهند شد
            il.Emit(OpCodes.Stloc_1); // نتیجه در متغیر حلقه ذخیره خواهد شد

            // مرحله بعد شبیه سازی حلقه با پرش به ابتدای برچسب آن است
            il.Emit(OpCodes.Br, loopStart);

            //در اینجا انتهای متد علامتگذاری شده است
            il.MarkLabel(methodEnd);
            il.Emit(OpCodes.Ldloc_0); // مقدار نتیجه بر روی پشته قرار داده شده
            il.Emit(OpCodes.Ret); // و بازگشت داده می‌شود

            //فراخوانی متد پویا
            var method = (Func<int, int>)myMethod.CreateDelegate(typeof(Func<int, int>));
            Console.WriteLine(method(10));
        }
    }
}

using System;
using System.Reflection.Emit;

namespace FastReflectionTests
{
    class Program
    {
        public static void print(int i)
        {
            Console.WriteLine("i: {0}", i);
        }

        static void Main(string[] args)
        {
            //روش متداول
            print(10);

            //تعریف امضای متد
            var myMethod = new DynamicMethod(
                                        name: "myMethod",
                                        returnType: typeof(void),
                                        parameterTypes: null, // پارامتری ندارد
                                        m: typeof(Program).Module);
            //تعریف بدنه متد
            var il = myMethod.GetILGenerator();
            il.Emit(OpCodes.Ldc_I4, 10); // عدد ثابت 10 را بر روی پشته قرار می‌دهد
            // اکنون این مقدار بر روی پشته است و از آن می‌توان برای فراخوانی متد پرینت استفاده کرد
            il.Emit(OpCodes.Call, typeof(Program).GetMethod("print"));
            il.Emit(OpCodes.Ret);


            //فراخوانی متد پویا
            var method = (Action)myMethod.CreateDelegate(typeof(Action));
            method();
        }
    }
}

using System;
using System.Reflection.Emit;

namespace FastReflectionTests
{
    class Program
    {
        static void Main(string[] args)
        {
            //تعریف امضای متد
            var myMethod = new DynamicMethod(
                                        name: "mulMethod",
                                        returnType: typeof(int),
                                        parameterTypes: new[] { typeof(int) },
                                        m: typeof(Program).Module);
            //تعریف بدنه متد
            var il = myMethod.GetILGenerator();
            il.Emit(OpCodes.Ldarg_0); // اولین آرگومان متد را بر روی پشته قرار می‌دهد
            il.Emit(OpCodes.Ldc_I4, 42); // عدد ثابت 42 را بر روی پشته قرار می‌دهد
            il.Emit(OpCodes.Mul); // ضرب این دو در هم
            il.Emit(OpCodes.Ret); // بازگشت نتیجه

            //فراخوانی متد پویا
            var method = (Func<int, int>)myMethod.CreateDelegate(typeof(Func<int, int>));
            Console.WriteLine(method(10));

            // فراخوانی متد پویای فوق در یک متد پویای دیگر
            var callerMethod = new DynamicMethod(
                                        name: "callerMethod",
                                        returnType: typeof(int),
                                        parameterTypes: new[] { typeof(int), typeof(int) },
                                        m: typeof(Program).Module);
            //تعریف بدنه متد
            var callerMethodIL = callerMethod.GetILGenerator();
            callerMethodIL.Emit(OpCodes.Ldarg_0); // پارامتر اول متد را بر روی پشته قرار می‌دهد
            callerMethodIL.Emit(OpCodes.Ldarg_1); // پارامتر دوم متد را بر روی پشته قرار می‌دهد
            callerMethodIL.Emit(OpCodes.Mul); // ضرب این دو در هم
            //حاصل ضرب اکنون بر روی پشته است که در فراخوانی بعدی استفاده می‌شود
            callerMethodIL.Emit(OpCodes.Call, myMethod); // فراخوانی یک متد پویای دیگر
            callerMethodIL.Emit(OpCodes.Ret);

            //فراخوانی متد پویای جدید
            var method2 = (Func<int, int, int>)callerMethod.CreateDelegate(typeof(Func<int, int, int>));
            Console.WriteLine(method2(10, 2));
        }
    }
}