public static void CreateUniqueIndex(this DbContext context, string tableName, string fieldName)
{
      context.Database.ExecuteSqlCommand("CREATE UNIQUE INDEX [IX_Unique_" + tableName 
+ "_" + fieldName + "] ON [" + tableName + "]([" + fieldName + "] ASC);");
}

public class MyDbMigrationsConfiguration : DbMigrationsConfiguration<MyContext>
{
        public BlogDbMigrationsConfiguration()
        {
            AutomaticMigrationsEnabled = true;
            AutomaticMigrationDataLossAllowed = true;
        }

        protected override void Seed(MyContext context)
        {
            CreateUniqueIndex(context, "table1", "field1");
            base.Seed(context);
        }
}

using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Objects;
using System.Linq;
using System.Linq.Expressions;
using System.Text.RegularExpressions;

namespace General
{
    public static class ContextExtensions
    {
        public static string GetTableName<T>(this DbContext context) where T : class
        {
            ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext;
            return objectContext.GetTableName<T>();
        }

        public static string GetTableName<T>(this ObjectContext context) where T : class
        {
            var sql = context.CreateObjectSet<T>().ToTraceString();
            var regex = new Regex("FROM (?<table>.*) AS");
            var match = regex.Match(sql);
            string table = match.Groups["table"].Value;
            return table
                    .Replace("`", string.Empty)
                    .Replace("[", string.Empty)
                    .Replace("]", string.Empty)
                    .Replace("dbo.", string.Empty)
                    .Trim();
        }

        private static bool hasUniqueIndex(this DbContext context, string tableName, string indexName)
        {
            var sql = "SELECT count(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = '" 
                      + tableName + "' and CONSTRAINT_NAME = '" + indexName + "'";
            var result = context.Database.SqlQuery<int>(sql).FirstOrDefault();
            return result > 0;
        }

        private static void createUniqueIndex(this DbContext context, string tableName, string fieldName)
        {
            var indexName = "IX_Unique_" + tableName + "_" + fieldName;
            if (hasUniqueIndex(context, tableName, indexName))
                return;

            var sql = "ALTER TABLE [" + tableName + "] ADD CONSTRAINT [" + indexName + "] UNIQUE ([" + fieldName + "])";
            context.Database.ExecuteSqlCommand(sql);
        }

        public static void CreateUniqueIndex<TEntity>(this DbContext context, Expression<Func<TEntity, object>> fieldName) where TEntity : class
        {
            var field = ((MemberExpression)fieldName.Body).Member.Name;
            createUniqueIndex(context, context.GetTableName<TEntity>(), field);
        }
    }
}

context.CreateUniqueIndex<User>(x=>x.Name);

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public string Category { get; set; }
}

namespace WebForms
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Net;
    using System.Net.Http;
    using System.Web.Http;

    public class ProductsController : ApiController
    {

        Product[] products = new Product[] 
        { 
            new Product { Id = 1, Name = "Tomato Soup", Category = "Groceries", Price = 1 }, 
            new Product { Id = 2, Name = "Yo-yo", Category = "Toys", Price = 3.75M }, 
            new Product { Id = 3, Name = "Hammer", Category = "Hardware", Price = 16.99M } 
        };

        public IEnumerable<Product> GetAllProducts()
        {
            return products;
        }

        public Product GetProductById(int id)
        {
            var product = products.FirstOrDefault((p) => p.Id == id);
            if (product == null)
            {
                throw new HttpResponseException(HttpStatusCode.NotFound);
            }
            return product;
        }

        public IEnumerable<Product> GetProductsByCategory(string category)
        {
            return products.Where(
                (p) => string.Equals(p.Category, category,
                    StringComparison.OrdinalIgnoreCase));
        }
    }
}

using System.Web.Http;

RouteTable.Routes.MapHttpRoute(
    name: "DefaultApi",
    routeTemplate: "api/{controller}/{id}",
    defaults: new { id = System.Web.Http.RouteParameter.Optional }
    );

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.Master" 
    AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebForms._Default" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <h2>Products</h2>
    <table>
    <thead>
        <tr><th>Name</th><th>Price</th></tr>
    </thead>
    <tbody id="products">
    </tbody>
    </table>
</asp:Content>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
    <script src="Scripts/jquery-1.7.1.min.js" type="text/javascript"></script>
</asp:Content>

<script type="text/javascript">
    function getProducts() {
        $.getJSON("api/products",
            function (data) {
                $('#products').empty(); // Clear the table body.

                // Loop through the list of products.
                $.each(data, function (key, val) {
                    // Add a table row for the product.
                    var row = '<td>' + val.Name + '</td><td>' + val.Price + '</td>';
                    $('<tr/>', { text: row })  // Append the name.
                        .appendTo($('#products'));
                });
            });
        }

        $(document).ready(getProducts);
</script>

در لایه سرویس یک کلاس Service Factory داریم که قرار است همه سرویس‌ها، برای برقراری ارتباط با یکدیگر از آن استفاده کنند.این کلاس معمولا در لایه سرویس به اشکال گوناگونی پیاده سازی میشود که کارش وهه سازی از Interface‌های درخواستی است. اما برای یکپارچه کردن آن با Unity من آنرا به شکل زیر پیاده سازی کرده ام

public class ServiceFactory : MarshalByRefObject
    {
        static IUnityContainer uContainer = new UnityContainer();
        public static Type DataContextType { get; set; }

        public static void Initialise(IUnityContainer unityContainer, Type dbContextType)
        {
            uContainer = unityContainer;
            DataContextType = dbContextType;
            uContainer.RegisterType(typeof(BaseDataContext), DataContextType, new HierarchicalLifetimeManager());
        }
        public static T Create<T>()
        {
            return (T)Activator.CreateInstance<T>();
        }
        public static T Create<T>(string fullTypeName)
        {
            return (T)System.Reflection.Assembly.GetExecutingAssembly().CreateInstance(fullTypeName);
        }
        public static T Create<T>(Type entityType)
        {
            return (T)Activator.CreateInstance(entityType);
        }
        public static dynamic Create(Type entityType)
        {
            return Activator.CreateInstance(entityType);
        }

        public static T Get<T>()
        {
            return uContainer.Resolve<T>();
        }
        public static object Get(Type type)
        {
            return uContainer.Resolve(type);
        }
    }

در این کلاس ما بجای ایجاد داینامیک آبجکت‌ها، از Unity استفاده کرده‌ایم. در همان ابتدا که برنامه‌ی وب ما برای اولین بار اجرا میشود و بعد از Register کردن کلاس‌ها، می‌توانیم container را به صورت پارامتر سازنده به کلاس Service Factory ارسال کنیم. به این ترتیب برای استفاده از سرویس‌ها در لایه Business از Unity بهره میبریم.

البته استفاده از Unity برای DataContext خیلی منطقی نیست و بهتر است نوع DataContext را در ابتدا بگیریم و هرجا نیاز داشتیم با استفاده از متد Create از آن وهله سازی بکنیم.

Claim type: sid - Claim value: f3940d6e58cbb576669ee49c90e22cb1
Claim type: http://schemas.xmlsoap.org/ws/2005/05/identity/claims/nameidentifier - Claim value: d860efca-22d9-47fd-8249-791ba61b07c7
Claim type: http://schemas.microsoft.com/identity/claims/identityprovider - Claim value: local
Claim type: http://schemas.microsoft.com/claims/authnmethodsreferences - Claim value: pwd
Claim type: given_name - Claim value: Vahid
Claim type: family_name - Claim value: N

namespace ImageGallery.MvcClient.WebApp
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
            JwtSecurityTokenHandler.DefaultInboundClaimTypeMap.Clear();
        }

• Claim type: sid - Claim value: 91f5a09da5cdbbe18762526da1b996fb
• Claim type: sub - Claim value: d860efca-22d9-47fd-8249-791ba61b07c7
• Claim type: idp - Claim value: local
• Claim type: given_name - Claim value: Vahid
• Claim type: family_name - Claim value: N

namespace ImageGallery.MvcClient.WebApp
{
        public void ConfigureServices(IServiceCollection services)
        {
// ...
              .AddOpenIdConnect("oidc", options =>
              {
  // ...
                  options.ClaimActions.Remove("amr");
                  options.ClaimActions.DeleteClaim("sid");
                  options.ClaimActions.DeleteClaim("idp");
              });
        }

• Claim type: sub - Claim value: d860efca-22d9-47fd-8249-791ba61b07c7
• Claim type: amr - Claim value: pwd
• Claim type: given_name - Claim value: Vahid
• Claim type: family_name - Claim value: N

namespace DNT.IDP
{
    public static class Config
    {
        // identity-related resources (scopes)
        public static IEnumerable<IdentityResource> GetIdentityResources()
        {
            return new List<IdentityResource>
            {
                new IdentityResources.OpenId(),
                new IdentityResources.Profile(),
                new IdentityResources.Address()
            };
        }

AllowedScopes =
{
    IdentityServerConstants.StandardScopes.OpenId,
    IdentityServerConstants.StandardScopes.Profile,
    IdentityServerConstants.StandardScopes.Address
},

namespace DNT.IDP
{
    public static class Config
    {
        public static List<TestUser> GetUsers()
        {
            return new List<TestUser>
            {
                new TestUser
                {
// ...
                    Claims = new List<Claim>
                    {
// ...
                        new Claim("address", "Main Road 1")
                    }
                },
                new TestUser
                {
// ...
                    Claims = new List<Claim>
                    {
// ...
                        new Claim("address", "Big Street 2")
                    }
                }
            };
        }

namespace ImageGallery.MvcClient.WebApp
{
    public class Startup
    {
        public void ConfigureServices(IServiceCollection services)
        {
// ...
              .AddOpenIdConnect("oidc", options =>
              {
   // ...
                  options.Scope.Add("address");
   // …
   options.ClaimActions.DeleteClaim("address");
              });
        }

GET idphostaddress/connect/userinfo
Authorization: Bearer R9aty5OPlk

dotnet add package IdentityModel

namespace ImageGallery.MvcClient.WebApp.Controllers
{
    [Authorize]
    public class GalleryController : Controller
    {
        public async Task<IActionResult> OrderFrame()
        {
            var discoveryClient = new DiscoveryClient(_configuration["IDPBaseAddress"]);
            var metaDataResponse = await discoveryClient.GetAsync();

            var userInfoClient = new UserInfoClient(metaDataResponse.UserInfoEndpoint);

            var accessToken = await HttpContext.GetTokenAsync(OpenIdConnectParameterNames.AccessToken);
            var response = await userInfoClient.GetAsync(accessToken);
            if (response.IsError)
            {
                throw new Exception("Problem accessing the UserInfo endpoint.", response.Exception);
            }

            var address = response.Claims.FirstOrDefault(c => c.Type == "address")?.Value;
            return View(new OrderFrameViewModel(address));
        }

namespace ImageGallery.MvcClient.ViewModels
{
    public class OrderFrameViewModel
    {
        public string Address { get; } = string.Empty;

        public OrderFrameViewModel(string address)
        {
            Address = address;
        }
    }
}

@model ImageGallery.MvcClient.ViewModels.OrderFrameViewModel
<div class="container">
    <div class="h3 bottomMarginDefault">Order a framed version of your favorite picture.</div>
    <div class="text bottomMarginSmall">We've got this address on record for you:</div>
    <div class="text text-info bottomMarginSmall">@Model.Address</div>
    <div class="text">If this isn't correct, please contact us.</div>
</div>

<li><a asp-area="" asp-controller="Gallery" asp-action="OrderFrame">Order a framed picture</a></li>

namespace DNT.IDP
{
    public static class Config
    {
        public static List<TestUser> GetUsers()
        {
            return new List<TestUser>
            {
                new TestUser
                {
//...
                    Claims = new List<Claim>
                    {
//...
                        new Claim("role", "PayingUser")
                    }
                },
                new TestUser
                {
//...
                    Claims = new List<Claim>
                    {
//...
                        new Claim("role", "FreeUser")
                    }
                }
            };
        }

namespace DNT.IDP
{
    public static class Config
    {
        public static IEnumerable<IdentityResource> GetIdentityResources()
        {
            return new List<IdentityResource>
            {
                // ...
                new IdentityResource(
                    name: "roles",
                    displayName: "Your role(s)",
                    claimTypes: new List<string>() { "role" })
            };
        }

namespace DNT.IDP
{
    public static class Config
    {
        public static IEnumerable<Client> GetClients()
        {
            return new List<Client>
            {
                new Client
                {
// ...
                    AllowedScopes =
                    {
// ...
                        "roles"
                    }
// ...
                }
             };
        }

options.Scope.Add("roles");

• Claim type: sub - Claim value: d860efca-22d9-47fd-8249-791ba61b07c7
• Claim type: amr - Claim value: pwd
• Claim type: given_name - Claim value: Vahid
• Claim type: family_name - Claim value: N

options.ClaimActions.MapUniqueJsonKey(claimType: "role", jsonKey: "role");

• Claim type: sub - Claim value: d860efca-22d9-47fd-8249-791ba61b07c7
• Claim type: amr - Claim value: pwd
• Claim type: given_name - Claim value: Vahid
• Claim type: family_name - Claim value: N
• Claim type: role - Claim value: PayingUser

@if(User.IsInRole("PayingUser"))
{
   <li><a asp-area="" asp-controller="Gallery" asp-action="OrderFrame">Order a framed picture</a></li>
}

options.TokenValidationParameters = new TokenValidationParameters
{
    NameClaimType = JwtClaimTypes.GivenName,
    RoleClaimType = JwtClaimTypes.Role,
};

namespace ImageGallery.MvcClient.WebApp.Controllers
{
    [Authorize]
    public class GalleryController : Controller
    {
        [Authorize(Roles = "PayingUser")]
        public async Task<IActionResult> OrderFrame()
        {

using Microsoft.AspNetCore.Mvc;

public class AuthorizationController : Controller
{
    public IActionResult AccessDenied()
    {
        return View();
    }
}

<div class="container">
    <div class="h3">Woops, looks like you're not authorized to view this page.</div>
    <div>Would you prefer to <a asp-controller="Gallery" asp-action="Logout">log in as someone else</a>?</div>
</div>

namespace ImageGallery.MvcClient.WebApp
{
    public class Startup
    {
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddAuthentication(options =>
            {
                // ...
            }).AddCookie("Cookies", options =>
              {
                  options.AccessDeniedPath = "/Authorization/AccessDenied";
              })
// ...

var items = context.Bookings
                    .Where(booking => booking.StartTime.Year == 2012)
                    .Select(booking => new
                    {
                        booking.StartTime.Year,
                        booking.StartTime.Month,
                        DaysInMonth = EF.Functions.DateDiffDay(
                                        booking.StartTime.Date.AddDays(1 - booking.StartTime.Date.Day),
                                        booking.StartTime.Date.AddDays(1 - booking.StartTime.Date.Day).AddMonths(1)
                                        )
                    })
                    .Distinct()
                    .OrderBy(r => r.Year)
                        .ThenBy(r => r.Month)
                    .ToList();

var items = context.Bookings
                    .Select(x => new { x.StartTime, EndTime = x.StartTime.AddMinutes(x.Slots * 30) })
                    .OrderByDescending(x => x.EndTime)
                        .ThenByDescending(x => x.StartTime)
                    .Take(10)
                    .ToList();

var items = context.Bookings
                    .GroupBy(x => new { x.StartTime.Year, x.StartTime.Month })
                    .Select(x => new
                    {
                        x.Key.Year,
                        x.Key.Month,
                        Count = x.Count()
                    })
                    .OrderBy(x => x.Year)
                        .ThenBy(x => x.Month)
                    .ToList();

Round(100 * Sum(Slots) / (decimal)(25 * DaysInMonth), 1)

var items = context.Bookings
                    .Select(booking => new
                    {
                        booking.Facility.Name,
                        booking.StartTime.Year,
                        booking.StartTime.Month,
                        booking.Slots,
                        DaysInMonth = EF.Functions.DateDiffDay(
                                        booking.StartTime.Date.AddDays(1 - booking.StartTime.Date.Day),
                                        booking.StartTime.Date.AddDays(1 - booking.StartTime.Date.Day).AddMonths(1)
                                        )
                    })
                    .GroupBy(b => new { b.Name, b.Year, b.Month, b.DaysInMonth })
                    .Select(g => new
                    {
                        g.Key.Name,
                        g.Key.Year,
                        g.Key.Month,
                        Utilization = SqlDbFunctionsExtensions.SqlRound(
                                100 * g.Sum(b => b.Slots) / (decimal)(25 * g.Key.DaysInMonth),
                                1)
                    })
                    .OrderBy(r => r.Name)
                        .ThenBy(r => r.Year)
                            .ThenBy(r => r.Month)
                    .ToList();

namespace EFCorePgExercises.Utils
{
    public static class SqlDbFunctionsExtensions
    {
        public static decimal SqlRound(decimal value, int precision)
            => throw new InvalidOperationException($"{nameof(SqlRound)} method cannot be called from the client side.");

        private static readonly MethodInfo _sqlRoundMethodInfo = typeof(SqlDbFunctionsExtensions)
            .GetRuntimeMethod(
                nameof(SqlDbFunctionsExtensions.SqlRound),
                new[] { typeof(decimal), typeof(int) }
            );

        public static void AddCustomSqlFunctions(this ModelBuilder modelBuilder)
        {
            modelBuilder.HasDbFunction(_sqlRoundMethodInfo)
                .HasTranslation(args =>
                {
                    return SqlFunctionExpression.Create("ROUND",
                        args,
                        _sqlRoundMethodInfo.ReturnType,
                        typeMapping: null);
                });
        }
    }
}

namespace EFCorePgExercises.DataLayer
{
    public class ApplicationDbContext : DbContext
    {
         // ...

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
         // ...
            modelBuilder.AddCustomSqlFunctions();
         // ...
        }
    }
}