VsNorthwind.cs

// ------------------------------------------------------------------------------------------------
// This code was generated by EntityFramework Reverse POCO Generator (http://www.reversepoco.co.uk/).
// Created by Simon Hughes (https://about.me/simon.hughes).
//
// Registered to: Simon Hughes
// Company      : Reverse POCO
// Licence Type : Commercial
// Licences     : 1
// Valid until  : 03 NOV 2020
//
// Do not make changes directly to this file - edit the template instead.
//
// The following connection settings were used to generate this file:
//     Connection String Name: "MyDbContext"
//     Connection String:      "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True"
// ------------------------------------------------------------------------------------------------
// Database Edition       : Developer Edition (64-bit)
// Database Engine Edition: Enterprise
// Database Version       : 14.0.2027.2

// <auto-generated>
// ReSharper disable CheckNamespace
// ReSharper disable ConvertPropertyToExpressionBody
// ReSharper disable DoNotCallOverridableMethodsInConstructor
// ReSharper disable EmptyNamespace
// ReSharper disable InconsistentNaming
// ReSharper disable NotAccessedVariable
// ReSharper disable PartialMethodWithSinglePart
// ReSharper disable PartialTypeWithSinglePart
// ReSharper disable RedundantCast
// ReSharper disable RedundantNameQualifier
// ReSharper disable RedundantOverridenMember
// ReSharper disable UseNameofExpression
// ReSharper disable UsePatternMatching
#pragma warning disable 1591    //  Ignore "Missing XML Comment" warning

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using Microsoft.EntityFrameworkCore.Query.Internal;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Data;
using System.Data.SqlTypes;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Threading;
using System.Threading.Tasks;

namespace EfrpgVisualStudio
{
    #region Database context interface

    public interface IMyDbContext : IDisposable
    {
        DbSet<AlphabeticalListOfProduct> AlphabeticalListOfProducts { get; set; } // Alphabetical list of products
        DbSet<Category> Categories { get; set; } // Categories
        DbSet<CategorySalesFor1997> CategorySalesFor1997 { get; set; } // Category Sales for 1997
        DbSet<CurrentProductList> CurrentProductLists { get; set; } // Current Product List
        DbSet<Customer> Customers { get; set; } // Customers
        DbSet<CustomerAndSuppliersByCity> CustomerAndSuppliersByCities { get; set; } // Customer and Suppliers by City
        DbSet<CustomerCustomerDemo> CustomerCustomerDemoes { get; set; } // CustomerCustomerDemo
        DbSet<CustomerDemographic> CustomerDemographics { get; set; } // CustomerDemographics
        DbSet<Employee> Employees { get; set; } // Employees
        DbSet<EmployeeTerritory> EmployeeTerritories { get; set; } // EmployeeTerritories
        DbSet<Invoice> Invoices { get; set; } // Invoices
        DbSet<Order> Orders { get; set; } // Orders
        DbSet<OrderDetail> OrderDetails { get; set; } // Order Details
        DbSet<OrderDetailsExtended> OrderDetailsExtendeds { get; set; } // Order Details Extended
        DbSet<OrdersQry> OrdersQries { get; set; } // Orders Qry
        DbSet<OrderSubtotal> OrderSubtotals { get; set; } // Order Subtotals
        DbSet<Product> Products { get; set; } // Products
        DbSet<ProductsAboveAveragePrice> ProductsAboveAveragePrices { get; set; } // Products Above Average Price
        DbSet<ProductSalesFor1997> ProductSalesFor1997 { get; set; } // Product Sales for 1997
        DbSet<ProductsByCategory> ProductsByCategories { get; set; } // Products by Category
        DbSet<QuarterlyOrder> QuarterlyOrders { get; set; } // Quarterly Orders
        DbSet<Region> Regions { get; set; } // Region
        DbSet<SalesByCategory> SalesByCategories { get; set; } // Sales by Category
        DbSet<SalesTotalsByAmount> SalesTotalsByAmounts { get; set; } // Sales Totals by Amount
        DbSet<Shipper> Shippers { get; set; } // Shippers
        DbSet<SummaryOfSalesByQuarter> SummaryOfSalesByQuarters { get; set; } // Summary of Sales by Quarter
        DbSet<SummaryOfSalesByYear> SummaryOfSalesByYears { get; set; } // Summary of Sales by Year
        DbSet<Supplier> Suppliers { get; set; } // Suppliers
        DbSet<Territory> Territories { get; set; } // Territories

        int SaveChanges();
        int SaveChanges(bool acceptAllChangesOnSuccess);
        Task<int> SaveChangesAsync(CancellationToken cancellationToken = default(CancellationToken));
        Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default(CancellationToken));
        DatabaseFacade Database { get; }
        DbSet<TEntity> Set<TEntity>() where TEntity : class;
        string ToString();

        // Stored Procedures
        List<CustOrderHistReturnModel> CustOrderHist(string customerId);
        List<CustOrderHistReturnModel> CustOrderHist(string customerId, out int procResult);
        Task<List<CustOrderHistReturnModel>> CustOrderHistAsync(string customerId);

        List<CustOrdersDetailReturnModel> CustOrdersDetail(int? orderId);
        List<CustOrdersDetailReturnModel> CustOrdersDetail(int? orderId, out int procResult);
        Task<List<CustOrdersDetailReturnModel>> CustOrdersDetailAsync(int? orderId);

        List<CustOrdersOrdersReturnModel> CustOrdersOrders(string customerId);
        List<CustOrdersOrdersReturnModel> CustOrdersOrders(string customerId, out int procResult);
        Task<List<CustOrdersOrdersReturnModel>> CustOrdersOrdersAsync(string customerId);

        List<EmployeeSalesByCountryReturnModel> EmployeeSalesByCountry(DateTime? beginningDate, DateTime? endingDate);
        List<EmployeeSalesByCountryReturnModel> EmployeeSalesByCountry(DateTime? beginningDate, DateTime? endingDate, out int procResult);
        Task<List<EmployeeSalesByCountryReturnModel>> EmployeeSalesByCountryAsync(DateTime? beginningDate, DateTime? endingDate);

        List<SalesByCategoryReturnModel> SalesByCategory(string categoryName, string ordYear);
        List<SalesByCategoryReturnModel> SalesByCategory(string categoryName, string ordYear, out int procResult);
        Task<List<SalesByCategoryReturnModel>> SalesByCategoryAsync(string categoryName, string ordYear);

        List<SalesByYearReturnModel> SalesByYear(DateTime? beginningDate, DateTime? endingDate);
        List<SalesByYearReturnModel> SalesByYear(DateTime? beginningDate, DateTime? endingDate, out int procResult);
        Task<List<SalesByYearReturnModel>> SalesByYearAsync(DateTime? beginningDate, DateTime? endingDate);

        List<TenMostExpensiveProductsReturnModel> TenMostExpensiveProducts();
        List<TenMostExpensiveProductsReturnModel> TenMostExpensiveProducts(out int procResult);
        Task<List<TenMostExpensiveProductsReturnModel>> TenMostExpensiveProductsAsync();

    }

    #endregion

    #region Database context

    public class MyDbContext : DbContext, IMyDbContext
    {
        public MyDbContext()
        {
        }

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

        public DbSet<AlphabeticalListOfProduct> AlphabeticalListOfProducts { get; set; } // Alphabetical list of products
        public DbSet<Category> Categories { get; set; } // Categories
        public DbSet<CategorySalesFor1997> CategorySalesFor1997 { get; set; } // Category Sales for 1997
        public DbSet<CurrentProductList> CurrentProductLists { get; set; } // Current Product List
        public DbSet<Customer> Customers { get; set; } // Customers
        public DbSet<CustomerAndSuppliersByCity> CustomerAndSuppliersByCities { get; set; } // Customer and Suppliers by City
        public DbSet<CustomerCustomerDemo> CustomerCustomerDemoes { get; set; } // CustomerCustomerDemo
        public DbSet<CustomerDemographic> CustomerDemographics { get; set; } // CustomerDemographics
        public DbSet<Employee> Employees { get; set; } // Employees
        public DbSet<EmployeeTerritory> EmployeeTerritories { get; set; } // EmployeeTerritories
        public DbSet<Invoice> Invoices { get; set; } // Invoices
        public DbSet<Order> Orders { get; set; } // Orders
        public DbSet<OrderDetail> OrderDetails { get; set; } // Order Details
        public DbSet<OrderDetailsExtended> OrderDetailsExtendeds { get; set; } // Order Details Extended
        public DbSet<OrdersQry> OrdersQries { get; set; } // Orders Qry
        public DbSet<OrderSubtotal> OrderSubtotals { get; set; } // Order Subtotals
        public DbSet<Product> Products { get; set; } // Products
        public DbSet<ProductsAboveAveragePrice> ProductsAboveAveragePrices { get; set; } // Products Above Average Price
        public DbSet<ProductSalesFor1997> ProductSalesFor1997 { get; set; } // Product Sales for 1997
        public DbSet<ProductsByCategory> ProductsByCategories { get; set; } // Products by Category
        public DbSet<QuarterlyOrder> QuarterlyOrders { get; set; } // Quarterly Orders
        public DbSet<Region> Regions { get; set; } // Region
        public DbSet<SalesByCategory> SalesByCategories { get; set; } // Sales by Category
        public DbSet<SalesTotalsByAmount> SalesTotalsByAmounts { get; set; } // Sales Totals by Amount
        public DbSet<Shipper> Shippers { get; set; } // Shippers
        public DbSet<SummaryOfSalesByQuarter> SummaryOfSalesByQuarters { get; set; } // Summary of Sales by Quarter
        public DbSet<SummaryOfSalesByYear> SummaryOfSalesByYears { get; set; } // Summary of Sales by Year
        public DbSet<Supplier> Suppliers { get; set; } // Suppliers
        public DbSet<Territory> Territories { get; set; } // Territories

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer(@"Data Source=(local);Initial Catalog=Northwind;Integrated Security=True");
            }
        }

        public bool IsSqlParameterNull(SqlParameter param)
        {
            var sqlValue = param.SqlValue;
            var nullableValue = sqlValue as INullable;
            if (nullableValue != null)
                return nullableValue.IsNull;
            return (sqlValue == null || sqlValue == DBNull.Value);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.ApplyConfiguration(new AlphabeticalListOfProductConfiguration());
            modelBuilder.ApplyConfiguration(new CategoryConfiguration());
            modelBuilder.ApplyConfiguration(new CategorySalesFor1997Configuration());
            modelBuilder.ApplyConfiguration(new CurrentProductListConfiguration());
            modelBuilder.ApplyConfiguration(new CustomerConfiguration());
            modelBuilder.ApplyConfiguration(new CustomerAndSuppliersByCityConfiguration());
            modelBuilder.ApplyConfiguration(new CustomerCustomerDemoConfiguration());
            modelBuilder.ApplyConfiguration(new CustomerDemographicConfiguration());
            modelBuilder.ApplyConfiguration(new EmployeeConfiguration());
            modelBuilder.ApplyConfiguration(new EmployeeTerritoryConfiguration());
            modelBuilder.ApplyConfiguration(new InvoiceConfiguration());
            modelBuilder.ApplyConfiguration(new OrderConfiguration());
            modelBuilder.ApplyConfiguration(new OrderDetailConfiguration());
            modelBuilder.ApplyConfiguration(new OrderDetailsExtendedConfiguration());
            modelBuilder.ApplyConfiguration(new OrdersQryConfiguration());
            modelBuilder.ApplyConfiguration(new OrderSubtotalConfiguration());
            modelBuilder.ApplyConfiguration(new ProductConfiguration());
            modelBuilder.ApplyConfiguration(new ProductsAboveAveragePriceConfiguration());
            modelBuilder.ApplyConfiguration(new ProductSalesFor1997Configuration());
            modelBuilder.ApplyConfiguration(new ProductsByCategoryConfiguration());
            modelBuilder.ApplyConfiguration(new QuarterlyOrderConfiguration());
            modelBuilder.ApplyConfiguration(new RegionConfiguration());
            modelBuilder.ApplyConfiguration(new SalesByCategoryConfiguration());
            modelBuilder.ApplyConfiguration(new SalesTotalsByAmountConfiguration());
            modelBuilder.ApplyConfiguration(new ShipperConfiguration());
            modelBuilder.ApplyConfiguration(new SummaryOfSalesByQuarterConfiguration());
            modelBuilder.ApplyConfiguration(new SummaryOfSalesByYearConfiguration());
            modelBuilder.ApplyConfiguration(new SupplierConfiguration());
            modelBuilder.ApplyConfiguration(new TerritoryConfiguration());

            modelBuilder.Entity<CustOrderHistReturnModel>().HasNoKey();
            modelBuilder.Entity<CustOrdersDetailReturnModel>().HasNoKey();
            modelBuilder.Entity<CustOrdersOrdersReturnModel>().HasNoKey();
            modelBuilder.Entity<EmployeeSalesByCountryReturnModel>().HasNoKey();
            modelBuilder.Entity<SalesByCategoryReturnModel>().HasNoKey();
            modelBuilder.Entity<SalesByYearReturnModel>().HasNoKey();
            modelBuilder.Entity<TenMostExpensiveProductsReturnModel>().HasNoKey();
        }


        // Stored Procedures
        public List<CustOrderHistReturnModel> CustOrderHist(string customerId)
        {
            int procResult;
            return CustOrderHist(customerId, out procResult);
        }

        public List<CustOrderHistReturnModel> CustOrderHist(string customerId, out int procResult)
        {
            var customerIdParam = new SqlParameter { ParameterName = "@CustomerID", SqlDbType = SqlDbType.NChar, Direction = ParameterDirection.Input, Value = customerId, Size = 5 };
            if (customerIdParam.Value == null)
                customerIdParam.Value = DBNull.Value;

            var procResultParam = new SqlParameter { ParameterName = "@procResult", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output };
            const string sqlCommand = "EXEC @procResult = [dbo].[CustOrderHist] @CustomerID";
            var procResultData = Set<CustOrderHistReturnModel>()
                .FromSqlRaw(sqlCommand, customerIdParam, procResultParam)
                .ToList();

            procResult = (int) procResultParam.Value;
            return procResultData;
        }

        public async Task<List<CustOrderHistReturnModel>> CustOrderHistAsync(string customerId)
        {
            var customerIdParam = new SqlParameter { ParameterName = "@CustomerID", SqlDbType = SqlDbType.NChar, Direction = ParameterDirection.Input, Value = customerId, Size = 5 };
            if (customerIdParam.Value == null)
                customerIdParam.Value = DBNull.Value;

            const string sqlCommand = "EXEC [dbo].[CustOrderHist] @CustomerID";
            var procResultData = await Set<CustOrderHistReturnModel>()
                .FromSqlRaw(sqlCommand, customerIdParam)
                .ToListAsync();

            return procResultData;
        }

        public List<CustOrdersDetailReturnModel> CustOrdersDetail(int? orderId)
        {
            int procResult;
            return CustOrdersDetail(orderId, out procResult);
        }

        public List<CustOrdersDetailReturnModel> CustOrdersDetail(int? orderId, out int procResult)
        {
            var orderIdParam = new SqlParameter { ParameterName = "@OrderID", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input, Value = orderId.GetValueOrDefault(), Precision = 10, Scale = 0 };
            if (!orderId.HasValue)
                orderIdParam.Value = DBNull.Value;

            var procResultParam = new SqlParameter { ParameterName = "@procResult", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output };
            const string sqlCommand = "EXEC @procResult = [dbo].[CustOrdersDetail] @OrderID";
            var procResultData = Set<CustOrdersDetailReturnModel>()
                .FromSqlRaw(sqlCommand, orderIdParam, procResultParam)
                .ToList();

            procResult = (int) procResultParam.Value;
            return procResultData;
        }

        public async Task<List<CustOrdersDetailReturnModel>> CustOrdersDetailAsync(int? orderId)
        {
            var orderIdParam = new SqlParameter { ParameterName = "@OrderID", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input, Value = orderId.GetValueOrDefault(), Precision = 10, Scale = 0 };
            if (!orderId.HasValue)
                orderIdParam.Value = DBNull.Value;

            const string sqlCommand = "EXEC [dbo].[CustOrdersDetail] @OrderID";
            var procResultData = await Set<CustOrdersDetailReturnModel>()
                .FromSqlRaw(sqlCommand, orderIdParam)
                .ToListAsync();

            return procResultData;
        }

        public List<CustOrdersOrdersReturnModel> CustOrdersOrders(string customerId)
        {
            int procResult;
            return CustOrdersOrders(customerId, out procResult);
        }

        public List<CustOrdersOrdersReturnModel> CustOrdersOrders(string customerId, out int procResult)
        {
            var customerIdParam = new SqlParameter { ParameterName = "@CustomerID", SqlDbType = SqlDbType.NChar, Direction = ParameterDirection.Input, Value = customerId, Size = 5 };
            if (customerIdParam.Value == null)
                customerIdParam.Value = DBNull.Value;

            var procResultParam = new SqlParameter { ParameterName = "@procResult", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output };
            const string sqlCommand = "EXEC @procResult = [dbo].[CustOrdersOrders] @CustomerID";
            var procResultData = Set<CustOrdersOrdersReturnModel>()
                .FromSqlRaw(sqlCommand, customerIdParam, procResultParam)
                .ToList();

            procResult = (int) procResultParam.Value;
            return procResultData;
        }

        public async Task<List<CustOrdersOrdersReturnModel>> CustOrdersOrdersAsync(string customerId)
        {
            var customerIdParam = new SqlParameter { ParameterName = "@CustomerID", SqlDbType = SqlDbType.NChar, Direction = ParameterDirection.Input, Value = customerId, Size = 5 };
            if (customerIdParam.Value == null)
                customerIdParam.Value = DBNull.Value;

            const string sqlCommand = "EXEC [dbo].[CustOrdersOrders] @CustomerID";
            var procResultData = await Set<CustOrdersOrdersReturnModel>()
                .FromSqlRaw(sqlCommand, customerIdParam)
                .ToListAsync();

            return procResultData;
        }

        public List<EmployeeSalesByCountryReturnModel> EmployeeSalesByCountry(DateTime? beginningDate, DateTime? endingDate)
        {
            int procResult;
            return EmployeeSalesByCountry(beginningDate, endingDate, out procResult);
        }

        public List<EmployeeSalesByCountryReturnModel> EmployeeSalesByCountry(DateTime? beginningDate, DateTime? endingDate, out int procResult)
        {
            var beginningDateParam = new SqlParameter { ParameterName = "@Beginning_Date", SqlDbType = SqlDbType.DateTime, Direction = ParameterDirection.Input, Value = beginningDate.GetValueOrDefault() };
            if (!beginningDate.HasValue)
                beginningDateParam.Value = DBNull.Value;

            var endingDateParam = new SqlParameter { ParameterName = "@Ending_Date", SqlDbType = SqlDbType.DateTime, Direction = ParameterDirection.Input, Value = endingDate.GetValueOrDefault() };
            if (!endingDate.HasValue)
                endingDateParam.Value = DBNull.Value;

            var procResultParam = new SqlParameter { ParameterName = "@procResult", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output };
            const string sqlCommand = "EXEC @procResult = [dbo].[Employee Sales by Country] @Beginning_Date, @Ending_Date";
            var procResultData = Set<EmployeeSalesByCountryReturnModel>()
                .FromSqlRaw(sqlCommand, beginningDateParam, endingDateParam, procResultParam)
                .ToList();

            procResult = (int) procResultParam.Value;
            return procResultData;
        }

        public async Task<List<EmployeeSalesByCountryReturnModel>> EmployeeSalesByCountryAsync(DateTime? beginningDate, DateTime? endingDate)
        {
            var beginningDateParam = new SqlParameter { ParameterName = "@Beginning_Date", SqlDbType = SqlDbType.DateTime, Direction = ParameterDirection.Input, Value = beginningDate.GetValueOrDefault() };
            if (!beginningDate.HasValue)
                beginningDateParam.Value = DBNull.Value;

            var endingDateParam = new SqlParameter { ParameterName = "@Ending_Date", SqlDbType = SqlDbType.DateTime, Direction = ParameterDirection.Input, Value = endingDate.GetValueOrDefault() };
            if (!endingDate.HasValue)
                endingDateParam.Value = DBNull.Value;

            const string sqlCommand = "EXEC [dbo].[Employee Sales by Country] @Beginning_Date, @Ending_Date";
            var procResultData = await Set<EmployeeSalesByCountryReturnModel>()
                .FromSqlRaw(sqlCommand, beginningDateParam, endingDateParam)
                .ToListAsync();

            return procResultData;
        }

        public List<SalesByCategoryReturnModel> SalesByCategory(string categoryName, string ordYear)
        {
            int procResult;
            return SalesByCategory(categoryName, ordYear, out procResult);
        }

        public List<SalesByCategoryReturnModel> SalesByCategory(string categoryName, string ordYear, out int procResult)
        {
            var categoryNameParam = new SqlParameter { ParameterName = "@CategoryName", SqlDbType = SqlDbType.NVarChar, Direction = ParameterDirection.Input, Value = categoryName, Size = 15 };
            if (categoryNameParam.Value == null)
                categoryNameParam.Value = DBNull.Value;

            var ordYearParam = new SqlParameter { ParameterName = "@OrdYear", SqlDbType = SqlDbType.NVarChar, Direction = ParameterDirection.Input, Value = ordYear, Size = 4 };
            if (ordYearParam.Value == null)
                ordYearParam.Value = DBNull.Value;

            var procResultParam = new SqlParameter { ParameterName = "@procResult", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output };
            const string sqlCommand = "EXEC @procResult = [dbo].[SalesByCategory] @CategoryName, @OrdYear";
            var procResultData = Set<SalesByCategoryReturnModel>()
                .FromSqlRaw(sqlCommand, categoryNameParam, ordYearParam, procResultParam)
                .ToList();

            procResult = (int) procResultParam.Value;
            return procResultData;
        }

        public async Task<List<SalesByCategoryReturnModel>> SalesByCategoryAsync(string categoryName, string ordYear)
        {
            var categoryNameParam = new SqlParameter { ParameterName = "@CategoryName", SqlDbType = SqlDbType.NVarChar, Direction = ParameterDirection.Input, Value = categoryName, Size = 15 };
            if (categoryNameParam.Value == null)
                categoryNameParam.Value = DBNull.Value;

            var ordYearParam = new SqlParameter { ParameterName = "@OrdYear", SqlDbType = SqlDbType.NVarChar, Direction = ParameterDirection.Input, Value = ordYear, Size = 4 };
            if (ordYearParam.Value == null)
                ordYearParam.Value = DBNull.Value;

            const string sqlCommand = "EXEC [dbo].[SalesByCategory] @CategoryName, @OrdYear";
            var procResultData = await Set<SalesByCategoryReturnModel>()
                .FromSqlRaw(sqlCommand, categoryNameParam, ordYearParam)
                .ToListAsync();

            return procResultData;
        }

        public List<SalesByYearReturnModel> SalesByYear(DateTime? beginningDate, DateTime? endingDate)
        {
            int procResult;
            return SalesByYear(beginningDate, endingDate, out procResult);
        }

        public List<SalesByYearReturnModel> SalesByYear(DateTime? beginningDate, DateTime? endingDate, out int procResult)
        {
            var beginningDateParam = new SqlParameter { ParameterName = "@Beginning_Date", SqlDbType = SqlDbType.DateTime, Direction = ParameterDirection.Input, Value = beginningDate.GetValueOrDefault() };
            if (!beginningDate.HasValue)
                beginningDateParam.Value = DBNull.Value;

            var endingDateParam = new SqlParameter { ParameterName = "@Ending_Date", SqlDbType = SqlDbType.DateTime, Direction = ParameterDirection.Input, Value = endingDate.GetValueOrDefault() };
            if (!endingDate.HasValue)
                endingDateParam.Value = DBNull.Value;

            var procResultParam = new SqlParameter { ParameterName = "@procResult", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output };
            const string sqlCommand = "EXEC @procResult = [dbo].[Sales by Year] @Beginning_Date, @Ending_Date";
            var procResultData = Set<SalesByYearReturnModel>()
                .FromSqlRaw(sqlCommand, beginningDateParam, endingDateParam, procResultParam)
                .ToList();

            procResult = (int) procResultParam.Value;
            return procResultData;
        }

        public async Task<List<SalesByYearReturnModel>> SalesByYearAsync(DateTime? beginningDate, DateTime? endingDate)
        {
            var beginningDateParam = new SqlParameter { ParameterName = "@Beginning_Date", SqlDbType = SqlDbType.DateTime, Direction = ParameterDirection.Input, Value = beginningDate.GetValueOrDefault() };
            if (!beginningDate.HasValue)
                beginningDateParam.Value = DBNull.Value;

            var endingDateParam = new SqlParameter { ParameterName = "@Ending_Date", SqlDbType = SqlDbType.DateTime, Direction = ParameterDirection.Input, Value = endingDate.GetValueOrDefault() };
            if (!endingDate.HasValue)
                endingDateParam.Value = DBNull.Value;

            const string sqlCommand = "EXEC [dbo].[Sales by Year] @Beginning_Date, @Ending_Date";
            var procResultData = await Set<SalesByYearReturnModel>()
                .FromSqlRaw(sqlCommand, beginningDateParam, endingDateParam)
                .ToListAsync();

            return procResultData;
        }

        public List<TenMostExpensiveProductsReturnModel> TenMostExpensiveProducts()
        {
            int procResult;
            return TenMostExpensiveProducts(out procResult);
        }

        public List<TenMostExpensiveProductsReturnModel> TenMostExpensiveProducts(out int procResult)
        {
            var procResultParam = new SqlParameter { ParameterName = "@procResult", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output };
            const string sqlCommand = "EXEC @procResult = [dbo].[Ten Most Expensive Products]";
            var procResultData = Set<TenMostExpensiveProductsReturnModel>()
                .FromSqlRaw(sqlCommand, procResultParam)
                .ToList();

            procResult = (int) procResultParam.Value;
            return procResultData;
        }

        public async Task<List<TenMostExpensiveProductsReturnModel>> TenMostExpensiveProductsAsync()
        {
            const string sqlCommand = "EXEC [dbo].[Ten Most Expensive Products]";
            var procResultData = await Set<TenMostExpensiveProductsReturnModel>()
                .FromSqlRaw(sqlCommand)
                .ToListAsync();

            return procResultData;
        }

    }

    #endregion

    #region Database context factory

    public class MyDbContextFactory : IDesignTimeDbContextFactory<MyDbContext>
    {
        public MyDbContext CreateDbContext(string[] args)
        {
            return new MyDbContext();
        }
    }

    #endregion

    #region Fake Database context

    public class FakeMyDbContext : IMyDbContext
    {
        public DbSet<AlphabeticalListOfProduct> AlphabeticalListOfProducts { get; set; } // Alphabetical list of products
        public DbSet<Category> Categories { get; set; } // Categories
        public DbSet<CategorySalesFor1997> CategorySalesFor1997 { get; set; } // Category Sales for 1997
        public DbSet<CurrentProductList> CurrentProductLists { get; set; } // Current Product List
        public DbSet<Customer> Customers { get; set; } // Customers
        public DbSet<CustomerAndSuppliersByCity> CustomerAndSuppliersByCities { get; set; } // Customer and Suppliers by City
        public DbSet<CustomerCustomerDemo> CustomerCustomerDemoes { get; set; } // CustomerCustomerDemo
        public DbSet<CustomerDemographic> CustomerDemographics { get; set; } // CustomerDemographics
        public DbSet<Employee> Employees { get; set; } // Employees
        public DbSet<EmployeeTerritory> EmployeeTerritories { get; set; } // EmployeeTerritories
        public DbSet<Invoice> Invoices { get; set; } // Invoices
        public DbSet<Order> Orders { get; set; } // Orders
        public DbSet<OrderDetail> OrderDetails { get; set; } // Order Details
        public DbSet<OrderDetailsExtended> OrderDetailsExtendeds { get; set; } // Order Details Extended
        public DbSet<OrdersQry> OrdersQries { get; set; } // Orders Qry
        public DbSet<OrderSubtotal> OrderSubtotals { get; set; } // Order Subtotals
        public DbSet<Product> Products { get; set; } // Products
        public DbSet<ProductsAboveAveragePrice> ProductsAboveAveragePrices { get; set; } // Products Above Average Price
        public DbSet<ProductSalesFor1997> ProductSalesFor1997 { get; set; } // Product Sales for 1997
        public DbSet<ProductsByCategory> ProductsByCategories { get; set; } // Products by Category
        public DbSet<QuarterlyOrder> QuarterlyOrders { get; set; } // Quarterly Orders
        public DbSet<Region> Regions { get; set; } // Region
        public DbSet<SalesByCategory> SalesByCategories { get; set; } // Sales by Category
        public DbSet<SalesTotalsByAmount> SalesTotalsByAmounts { get; set; } // Sales Totals by Amount
        public DbSet<Shipper> Shippers { get; set; } // Shippers
        public DbSet<SummaryOfSalesByQuarter> SummaryOfSalesByQuarters { get; set; } // Summary of Sales by Quarter
        public DbSet<SummaryOfSalesByYear> SummaryOfSalesByYears { get; set; } // Summary of Sales by Year
        public DbSet<Supplier> Suppliers { get; set; } // Suppliers
        public DbSet<Territory> Territories { get; set; } // Territories

        public FakeMyDbContext()
        {
            _database = null;

            AlphabeticalListOfProducts = new FakeDbSet<AlphabeticalListOfProduct>();
            Categories = new FakeDbSet<Category>("CategoryId");
            CategorySalesFor1997 = new FakeDbSet<CategorySalesFor1997>();
            CurrentProductLists = new FakeDbSet<CurrentProductList>();
            Customers = new FakeDbSet<Customer>("CustomerId");
            CustomerAndSuppliersByCities = new FakeDbSet<CustomerAndSuppliersByCity>();
            CustomerCustomerDemoes = new FakeDbSet<CustomerCustomerDemo>("CustomerId", "CustomerTypeId");
            CustomerDemographics = new FakeDbSet<CustomerDemographic>("CustomerTypeId");
            Employees = new FakeDbSet<Employee>("EmployeeId");
            EmployeeTerritories = new FakeDbSet<EmployeeTerritory>("EmployeeId", "TerritoryId");
            Invoices = new FakeDbSet<Invoice>();
            Orders = new FakeDbSet<Order>("OrderId");
            OrderDetails = new FakeDbSet<OrderDetail>("OrderId", "ProductId");
            OrderDetailsExtendeds = new FakeDbSet<OrderDetailsExtended>();
            OrdersQries = new FakeDbSet<OrdersQry>();
            OrderSubtotals = new FakeDbSet<OrderSubtotal>();
            Products = new FakeDbSet<Product>("ProductId");
            ProductsAboveAveragePrices = new FakeDbSet<ProductsAboveAveragePrice>();
            ProductSalesFor1997 = new FakeDbSet<ProductSalesFor1997>();
            ProductsByCategories = new FakeDbSet<ProductsByCategory>();
            QuarterlyOrders = new FakeDbSet<QuarterlyOrder>();
            Regions = new FakeDbSet<Region>("RegionId");
            SalesByCategories = new FakeDbSet<SalesByCategory>();
            SalesTotalsByAmounts = new FakeDbSet<SalesTotalsByAmount>();
            Shippers = new FakeDbSet<Shipper>("ShipperId");
            SummaryOfSalesByQuarters = new FakeDbSet<SummaryOfSalesByQuarter>();
            SummaryOfSalesByYears = new FakeDbSet<SummaryOfSalesByYear>();
            Suppliers = new FakeDbSet<Supplier>("SupplierId");
            Territories = new FakeDbSet<Territory>("TerritoryId");

        }

        public int SaveChangesCount { get; private set; }
        public virtual int SaveChanges()
        {
            ++SaveChangesCount;
            return 1;
        }

        public virtual int SaveChanges(bool acceptAllChangesOnSuccess)
        {
            return SaveChanges();
        }

        public virtual Task<int> SaveChangesAsync(CancellationToken cancellationToken)
        {
            ++SaveChangesCount;
            return Task<int>.Factory.StartNew(() => 1, cancellationToken);
        }
        public virtual Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken)
        {
            ++SaveChangesCount;
            return Task<int>.Factory.StartNew(x => 1, acceptAllChangesOnSuccess, cancellationToken);
        }

        protected virtual void Dispose(bool disposing)
        {
        }

        public void Dispose()
        {
            Dispose(true);
        }

        private DatabaseFacade _database;
        public DatabaseFacade Database { get { return _database; } }

        public DbSet<TEntity> Set<TEntity>() where TEntity : class
        {
            throw new NotImplementedException();
        }

        public override string ToString()
        {
            throw new NotImplementedException();
        }

        // Stored Procedures

        public DbSet<CustOrderHistReturnModel> CustOrderHistReturnModel { get; set; }
        public List<CustOrderHistReturnModel> CustOrderHist(string customerId)
        {
            int procResult;
            return CustOrderHist(customerId, out procResult);
        }

        public List<CustOrderHistReturnModel> CustOrderHist(string customerId, out int procResult)
        {
            procResult = 0;
            return new List<CustOrderHistReturnModel>();
        }

        public Task<List<CustOrderHistReturnModel>> CustOrderHistAsync(string customerId)
        {
            int procResult;
            return Task.FromResult(CustOrderHist(customerId, out procResult));
        }

        public DbSet<CustOrdersDetailReturnModel> CustOrdersDetailReturnModel { get; set; }
        public List<CustOrdersDetailReturnModel> CustOrdersDetail(int? orderId)
        {
            int procResult;
            return CustOrdersDetail(orderId, out procResult);
        }

        public List<CustOrdersDetailReturnModel> CustOrdersDetail(int? orderId, out int procResult)
        {
            procResult = 0;
            return new List<CustOrdersDetailReturnModel>();
        }

        public Task<List<CustOrdersDetailReturnModel>> CustOrdersDetailAsync(int? orderId)
        {
            int procResult;
            return Task.FromResult(CustOrdersDetail(orderId, out procResult));
        }

        public DbSet<CustOrdersOrdersReturnModel> CustOrdersOrdersReturnModel { get; set; }
        public List<CustOrdersOrdersReturnModel> CustOrdersOrders(string customerId)
        {
            int procResult;
            return CustOrdersOrders(customerId, out procResult);
        }

        public List<CustOrdersOrdersReturnModel> CustOrdersOrders(string customerId, out int procResult)
        {
            procResult = 0;
            return new List<CustOrdersOrdersReturnModel>();
        }

        public Task<List<CustOrdersOrdersReturnModel>> CustOrdersOrdersAsync(string customerId)
        {
            int procResult;
            return Task.FromResult(CustOrdersOrders(customerId, out procResult));
        }

        public DbSet<EmployeeSalesByCountryReturnModel> EmployeeSalesByCountryReturnModel { get; set; }
        public List<EmployeeSalesByCountryReturnModel> EmployeeSalesByCountry(DateTime? beginningDate, DateTime? endingDate)
        {
            int procResult;
            return EmployeeSalesByCountry(beginningDate, endingDate, out procResult);
        }

        public List<EmployeeSalesByCountryReturnModel> EmployeeSalesByCountry(DateTime? beginningDate, DateTime? endingDate, out int procResult)
        {
            procResult = 0;
            return new List<EmployeeSalesByCountryReturnModel>();
        }

        public Task<List<EmployeeSalesByCountryReturnModel>> EmployeeSalesByCountryAsync(DateTime? beginningDate, DateTime? endingDate)
        {
            int procResult;
            return Task.FromResult(EmployeeSalesByCountry(beginningDate, endingDate, out procResult));
        }

        public DbSet<SalesByCategoryReturnModel> SalesByCategoryReturnModel { get; set; }
        public List<SalesByCategoryReturnModel> SalesByCategory(string categoryName, string ordYear)
        {
            int procResult;
            return SalesByCategory(categoryName, ordYear, out procResult);
        }

        public List<SalesByCategoryReturnModel> SalesByCategory(string categoryName, string ordYear, out int procResult)
        {
            procResult = 0;
            return new List<SalesByCategoryReturnModel>();
        }

        public Task<List<SalesByCategoryReturnModel>> SalesByCategoryAsync(string categoryName, string ordYear)
        {
            int procResult;
            return Task.FromResult(SalesByCategory(categoryName, ordYear, out procResult));
        }

        public DbSet<SalesByYearReturnModel> SalesByYearReturnModel { get; set; }
        public List<SalesByYearReturnModel> SalesByYear(DateTime? beginningDate, DateTime? endingDate)
        {
            int procResult;
            return SalesByYear(beginningDate, endingDate, out procResult);
        }

        public List<SalesByYearReturnModel> SalesByYear(DateTime? beginningDate, DateTime? endingDate, out int procResult)
        {
            procResult = 0;
            return new List<SalesByYearReturnModel>();
        }

        public Task<List<SalesByYearReturnModel>> SalesByYearAsync(DateTime? beginningDate, DateTime? endingDate)
        {
            int procResult;
            return Task.FromResult(SalesByYear(beginningDate, endingDate, out procResult));
        }

        public DbSet<TenMostExpensiveProductsReturnModel> TenMostExpensiveProductsReturnModel { get; set; }
        public List<TenMostExpensiveProductsReturnModel> TenMostExpensiveProducts()
        {
            int procResult;
            return TenMostExpensiveProducts(out procResult);
        }

        public List<TenMostExpensiveProductsReturnModel> TenMostExpensiveProducts(out int procResult)
        {
            procResult = 0;
            return new List<TenMostExpensiveProductsReturnModel>();
        }

        public Task<List<TenMostExpensiveProductsReturnModel>> TenMostExpensiveProductsAsync()
        {
            int procResult;
            return Task.FromResult(TenMostExpensiveProducts(out procResult));
        }
    }

    #endregion

    #region Fake DbSet

    // ************************************************************************
    // Fake DbSet
    // Implementing Find:
    //      The Find method is difficult to implement in a generic fashion. If
    //      you need to test code that makes use of the Find method it is
    //      easiest to create a test DbSet for each of the entity types that
    //      need to support find. You can then write logic to find that
    //      particular type of entity, as shown below:
    //      public class FakeBlogDbSet : FakeDbSet<Blog>
    //      {
    //          public override Blog Find(params object[] keyValues)
    //          {
    //              var id = (int) keyValues.Single();
    //              return this.SingleOrDefault(b => b.BlogId == id);
    //          }
    //      }
    //      Read more about it here: https://msdn.microsoft.com/en-us/data/dn314431.aspx
    public class FakeDbSet<TEntity> : DbSet<TEntity>, IQueryable<TEntity>, IAsyncEnumerable<TEntity>, IListSource where TEntity : class
    {
        private readonly PropertyInfo[] _primaryKeys;
        private readonly ObservableCollection<TEntity> _data;
        private readonly IQueryable _query;

        public FakeDbSet()
        {
            _primaryKeys = null;
            _data        = new ObservableCollection<TEntity>();
            _query       = _data.AsQueryable();
        }

        public FakeDbSet(params string[] primaryKeys)
        {
            _primaryKeys = typeof(TEntity).GetProperties().Where(x => primaryKeys.Contains(x.Name)).ToArray();
            _data        = new ObservableCollection<TEntity>();
            _query       = _data.AsQueryable();
        }

        public override TEntity Find(params object[] keyValues)
        {
            if (_primaryKeys == null)
                throw new ArgumentException("No primary keys defined");
            if (keyValues.Length != _primaryKeys.Length)
                throw new ArgumentException("Incorrect number of keys passed to Find method");

            var keyQuery = this.AsQueryable();
            keyQuery = keyValues
                .Select((t, i) => i)
                .Aggregate(keyQuery,
                    (current, x) =>
                        current.Where(entity => _primaryKeys[x].GetValue(entity, null).Equals(keyValues[x])));

            return keyQuery.SingleOrDefault();
        }

        public override ValueTask<TEntity> FindAsync(object[] keyValues, CancellationToken cancellationToken)
        {
            return new ValueTask<TEntity>(Task<TEntity>.Factory.StartNew(() => Find(keyValues), cancellationToken));
        }

        public override ValueTask<TEntity> FindAsync(params object[] keyValues)
        {
            return new ValueTask<TEntity>(Task<TEntity>.Factory.StartNew(() => Find(keyValues)));
        }

        IAsyncEnumerator<TEntity> IAsyncEnumerable<TEntity>.GetAsyncEnumerator(CancellationToken cancellationToken)
        {
            return GetAsyncEnumerator(cancellationToken);
        }

        public override EntityEntry<TEntity> Add(TEntity entity)
        {
            _data.Add(entity);
            return null;
        }

        public override void AddRange(params TEntity[] entities)
        {
            if (entities == null) throw new ArgumentNullException("entities");
            foreach (var entity in entities.ToList())
                _data.Add(entity);
        }

        public override void AddRange(IEnumerable<TEntity> entities)
        {
            AddRange(entities.ToArray());
        }

        public override Task AddRangeAsync(params TEntity[] entities)
        {
            if (entities == null) throw new ArgumentNullException("entities");
            return Task.Factory.StartNew(() => AddRange(entities));
        }

        public override void AttachRange(params TEntity[] entities)
        {
            if (entities == null) throw new ArgumentNullException("entities");
            AddRange(entities);
        }

        public override void RemoveRange(params TEntity[] entities)
        {
            if (entities == null) throw new ArgumentNullException("entities");
            foreach (var entity in entities.ToList())
                _data.Remove(entity);
        }

        public override void RemoveRange(IEnumerable<TEntity> entities)
        {
            RemoveRange(entities.ToArray());
        }

        public override void UpdateRange(params TEntity[] entities)
        {
            if (entities == null) throw new ArgumentNullException("entities");
            RemoveRange(entities);
            AddRange(entities);
        }

        public IList GetList()
        {
            return _data;
        }

        IList IListSource.GetList()
        {
            return _data;
        }

        Type IQueryable.ElementType
        {
            get { return _query.ElementType; }
        }

        Expression IQueryable.Expression
        {
            get { return _query.Expression; }
        }

        IQueryProvider IQueryable.Provider
        {
            get { return new FakeDbAsyncQueryProvider<TEntity>(_query.Provider); }
        }

        IEnumerator IEnumerable.GetEnumerator()
        {
            return _data.GetEnumerator();
        }

        IEnumerator<TEntity> IEnumerable<TEntity>.GetEnumerator()
        {
            return _data.GetEnumerator();
        }

        IAsyncEnumerator<TEntity> GetAsyncEnumerator(CancellationToken cancellationToken = default(CancellationToken))
        {
            return new FakeDbAsyncEnumerator<TEntity>(this.AsEnumerable().GetEnumerator());
        }

    }

    public class FakeDbAsyncQueryProvider<TEntity> : IAsyncQueryProvider
    {
        private readonly IQueryProvider _inner;

        public FakeDbAsyncQueryProvider(IQueryProvider inner)
        {
            _inner = inner;
        }

        public IQueryable CreateQuery(Expression expression)
        {
            var m = expression as MethodCallExpression;
            if (m != null)
            {
                var resultType = m.Method.ReturnType; // it should be IQueryable<T>
                var tElement = resultType.GetGenericArguments()[0];
                var queryType = typeof(FakeDbAsyncEnumerable<>).MakeGenericType(tElement);
                return (IQueryable) Activator.CreateInstance(queryType, expression);
            }
            return new FakeDbAsyncEnumerable<TEntity>(expression);
        }

        public IQueryable<TElement> CreateQuery<TElement>(Expression expression)
        {
            var queryType = typeof(FakeDbAsyncEnumerable<>).MakeGenericType(typeof(TElement));
            return (IQueryable<TElement>) Activator.CreateInstance(queryType, expression);
        }

        public object Execute(Expression expression)
        {
            return _inner.Execute(expression);
        }

        public TResult Execute<TResult>(Expression expression)
        {
            return _inner.Execute<TResult>(expression);
        }

        public TResult ExecuteAsync<TResult>(Expression expression, CancellationToken cancellationToken = new CancellationToken())
        {
            return _inner.Execute<TResult>(expression);
        }
    }

    public class FakeDbAsyncEnumerable<T> : EnumerableQuery<T>, IAsyncEnumerable<T>, IQueryable<T>
    {
        public FakeDbAsyncEnumerable(IEnumerable<T> enumerable)
            : base(enumerable)
        {
        }

        public FakeDbAsyncEnumerable(Expression expression)
            : base(expression)
        {
        }

        public IAsyncEnumerator<T> GetAsyncEnumerator(CancellationToken cancellationToken = new CancellationToken())
        {
            return new FakeDbAsyncEnumerator<T>(this.AsEnumerable().GetEnumerator());
        }

        IAsyncEnumerator<T> IAsyncEnumerable<T>.GetAsyncEnumerator(CancellationToken cancellationToken)
        {
            return GetAsyncEnumerator(cancellationToken);
        }

        IEnumerator IEnumerable.GetEnumerator()
        {
            return this.AsEnumerable().GetEnumerator();
        }
    }

    public class FakeDbAsyncEnumerator<T> : IAsyncEnumerator<T>
    {
        private readonly IEnumerator<T> _inner;

        public FakeDbAsyncEnumerator(IEnumerator<T> inner)
        {
            _inner = inner;
        }

        public T Current
        {
            get { return _inner.Current; }
        }
        public ValueTask<bool> MoveNextAsync()
        {
            return new ValueTask<bool>(_inner.MoveNext());
        }

        public ValueTask DisposeAsync()
        {
            _inner.Dispose();
            return new ValueTask(Task.CompletedTask);
        }
    }

    #endregion

    #region POCO classes

    // Alphabetical list of products
    public class AlphabeticalListOfProduct
    {
        public int ProductId { get; set; } // ProductID
        public string ProductName { get; set; } // ProductName (length: 40)
        public int? SupplierId { get; set; } // SupplierID
        public int? CategoryId { get; set; } // CategoryID
        public string QuantityPerUnit { get; set; } // QuantityPerUnit (length: 20)
        public decimal? UnitPrice { get; set; } // UnitPrice
        public short? UnitsInStock { get; set; } // UnitsInStock
        public short? UnitsOnOrder { get; set; } // UnitsOnOrder
        public short? ReorderLevel { get; set; } // ReorderLevel
        public bool Discontinued { get; set; } // Discontinued
        public string CategoryName { get; set; } // CategoryName (length: 15)
    }

    // Categories
    public class Category
    {
        public int CategoryId { get; set; } // CategoryID (Primary key)
        public string CategoryName { get; set; } // CategoryName (length: 15)
        public string Description { get; set; } // Description (length: 1073741823)
        public byte[] Picture { get; set; } // Picture (length: 2147483647)

        // Reverse navigation

        /// <summary>
        /// Child Products where [Products].[CategoryID] point to this entity (FK_Products_Categories)
        /// </summary>
        public virtual ICollection<Product> Products { get; set; } // Products.FK_Products_Categories

        public Category()
        {
            Products = new List<Product>();
        }
    }

    // Category Sales for 1997
    public class CategorySalesFor1997
    {
        public string CategoryName { get; set; } // CategoryName (length: 15)
        public decimal? CategorySales { get; set; } // CategorySales
    }

    // Current Product List
    public class CurrentProductList
    {
        public int ProductId { get; set; } // ProductID
        public string ProductName { get; set; } // ProductName (length: 40)
    }

    // Customers
    public class Customer
    {
        public string CustomerId { get; set; } // CustomerID (Primary key) (length: 5)
        public string CompanyName { get; set; } // CompanyName (length: 40)
        public string ContactName { get; set; } // ContactName (length: 30)
        public string ContactTitle { get; set; } // ContactTitle (length: 30)
        public string Address { get; set; } // Address (length: 60)
        public string City { get; set; } // City (length: 15)
        public string Region { get; set; } // Region (length: 15)
        public string PostalCode { get; set; } // PostalCode (length: 10)
        public string Country { get; set; } // Country (length: 15)
        public string Phone { get; set; } // Phone (length: 24)
        public string Fax { get; set; } // Fax (length: 24)

        // Reverse navigation

        /// <summary>
        /// Child CustomerCustomerDemoes where [CustomerCustomerDemo].[CustomerID] point to this entity (FK_CustomerCustomerDemo_Customers)
        /// </summary>
        public virtual ICollection<CustomerCustomerDemo> CustomerCustomerDemoes { get; set; } // CustomerCustomerDemo.FK_CustomerCustomerDemo_Customers

        /// <summary>
        /// Child Orders where [Orders].[CustomerID] point to this entity (FK_Orders_Customers)
        /// </summary>
        public virtual ICollection<Order> Orders { get; set; } // Orders.FK_Orders_Customers

        public Customer()
        {
            CustomerCustomerDemoes = new List<CustomerCustomerDemo>();
            Orders = new List<Order>();
        }
    }

    // Customer and Suppliers by City
    public class CustomerAndSuppliersByCity
    {
        public string City { get; set; } // City (length: 15)
        public string CompanyName { get; set; } // CompanyName (length: 40)
        public string ContactName { get; set; } // ContactName (length: 30)
        public string Relationship { get; set; } // Relationship (length: 9)
    }

    // CustomerCustomerDemo
    public class CustomerCustomerDemo
    {
        public string CustomerId { get; set; } // CustomerID (Primary key) (length: 5)
        public string CustomerTypeId { get; set; } // CustomerTypeID (Primary key) (length: 10)

        // Foreign keys

        /// <summary>
        /// Parent Customer pointed by [CustomerCustomerDemo].([CustomerId]) (FK_CustomerCustomerDemo_Customers)
        /// </summary>
        public virtual Customer Customer { get; set; } // FK_CustomerCustomerDemo_Customers

        /// <summary>
        /// Parent CustomerDemographic pointed by [CustomerCustomerDemo].([CustomerTypeId]) (FK_CustomerCustomerDemo)
        /// </summary>
        public virtual CustomerDemographic CustomerDemographic { get; set; } // FK_CustomerCustomerDemo
    }

    // CustomerDemographics
    public class CustomerDemographic
    {
        public string CustomerTypeId { get; set; } // CustomerTypeID (Primary key) (length: 10)
        public string CustomerDesc { get; set; } // CustomerDesc (length: 1073741823)

        // Reverse navigation

        /// <summary>
        /// Child CustomerCustomerDemoes where [CustomerCustomerDemo].[CustomerTypeID] point to this entity (FK_CustomerCustomerDemo)
        /// </summary>
        public virtual ICollection<CustomerCustomerDemo> CustomerCustomerDemoes { get; set; } // CustomerCustomerDemo.FK_CustomerCustomerDemo

        public CustomerDemographic()
        {
            CustomerCustomerDemoes = new List<CustomerCustomerDemo>();
        }
    }

    // Employees
    public class Employee
    {
        public int EmployeeId { get; set; } // EmployeeID (Primary key)
        public string LastName { get; set; } // LastName (length: 20)
        public string FirstName { get; set; } // FirstName (length: 10)
        public string Title { get; set; } // Title (length: 30)
        public string TitleOfCourtesy { get; set; } // TitleOfCourtesy (length: 25)
        public DateTime? BirthDate { get; set; } // BirthDate
        public DateTime? HireDate { get; set; } // HireDate
        public string Address { get; set; } // Address (length: 60)
        public string City { get; set; } // City (length: 15)
        public string Region { get; set; } // Region (length: 15)
        public string PostalCode { get; set; } // PostalCode (length: 10)
        public string Country { get; set; } // Country (length: 15)
        public string HomePhone { get; set; } // HomePhone (length: 24)
        public string Extension { get; set; } // Extension (length: 4)
        public byte[] Photo { get; set; } // Photo (length: 2147483647)
        public string Notes { get; set; } // Notes (length: 1073741823)
        public int? ReportsTo { get; set; } // ReportsTo
        public string PhotoPath { get; set; } // PhotoPath (length: 255)

        // Reverse navigation

        /// <summary>
        /// Child Employees where [Employees].[ReportsTo] point to this entity (FK_Employees_Employees)
        /// </summary>
        public virtual ICollection<Employee> Employees { get; set; } // Employees.FK_Employees_Employees

        /// <summary>
        /// Child EmployeeTerritories where [EmployeeTerritories].[EmployeeID] point to this entity (FK_EmployeeTerritories_Employees)
        /// </summary>
        public virtual ICollection<EmployeeTerritory> EmployeeTerritories { get; set; } // EmployeeTerritories.FK_EmployeeTerritories_Employees

        /// <summary>
        /// Child Orders where [Orders].[EmployeeID] point to this entity (FK_Orders_Employees)
        /// </summary>
        public virtual ICollection<Order> Orders { get; set; } // Orders.FK_Orders_Employees

        // Foreign keys

        /// <summary>
        /// Parent Employee pointed by [Employees].([ReportsTo]) (FK_Employees_Employees)
        /// </summary>
        public virtual Employee Employee_ReportsTo { get; set; } // FK_Employees_Employees

        public Employee()
        {
            Employees = new List<Employee>();
            EmployeeTerritories = new List<EmployeeTerritory>();
            Orders = new List<Order>();
        }
    }

    // EmployeeTerritories
    public class EmployeeTerritory
    {
        public int EmployeeId { get; set; } // EmployeeID (Primary key)
        public string TerritoryId { get; set; } // TerritoryID (Primary key) (length: 20)

        // Foreign keys

        /// <summary>
        /// Parent Employee pointed by [EmployeeTerritories].([EmployeeId]) (FK_EmployeeTerritories_Employees)
        /// </summary>
        public virtual Employee Employee { get; set; } // FK_EmployeeTerritories_Employees

        /// <summary>
        /// Parent Territory pointed by [EmployeeTerritories].([TerritoryId]) (FK_EmployeeTerritories_Territories)
        /// </summary>
        public virtual Territory Territory { get; set; } // FK_EmployeeTerritories_Territories
    }

    // Invoices
    public class Invoice
    {
        public string ShipName { get; set; } // ShipName (length: 40)
        public string ShipAddress { get; set; } // ShipAddress (length: 60)
        public string ShipCity { get; set; } // ShipCity (length: 15)
        public string ShipRegion { get; set; } // ShipRegion (length: 15)
        public string ShipPostalCode { get; set; } // ShipPostalCode (length: 10)
        public string ShipCountry { get; set; } // ShipCountry (length: 15)
        public string CustomerId { get; set; } // CustomerID (length: 5)
        public string CustomerName { get; set; } // CustomerName (length: 40)
        public string Address { get; set; } // Address (length: 60)
        public string City { get; set; } // City (length: 15)
        public string Region { get; set; } // Region (length: 15)
        public string PostalCode { get; set; } // PostalCode (length: 10)
        public string Country { get; set; } // Country (length: 15)
        public string Salesperson { get; set; } // Salesperson (length: 31)
        public int OrderId { get; set; } // OrderID
        public DateTime? OrderDate { get; set; } // OrderDate
        public DateTime? RequiredDate { get; set; } // RequiredDate
        public DateTime? ShippedDate { get; set; } // ShippedDate
        public string ShipperName { get; set; } // ShipperName (length: 40)
        public int ProductId { get; set; } // ProductID
        public string ProductName { get; set; } // ProductName (length: 40)
        public decimal UnitPrice { get; set; } // UnitPrice
        public short Quantity { get; set; } // Quantity
        public float Discount { get; set; } // Discount
        public decimal? ExtendedPrice { get; set; } // ExtendedPrice
        public decimal? Freight { get; set; } // Freight
    }

    // Orders
    public class Order
    {
        public int OrderId { get; set; } // OrderID (Primary key)
        public string CustomerId { get; set; } // CustomerID (length: 5)
        public int? EmployeeId { get; set; } // EmployeeID
        public DateTime? OrderDate { get; set; } // OrderDate
        public DateTime? RequiredDate { get; set; } // RequiredDate
        public DateTime? ShippedDate { get; set; } // ShippedDate
        public int? ShipVia { get; set; } // ShipVia
        public decimal? Freight { get; set; } // Freight
        public string ShipName { get; set; } // ShipName (length: 40)
        public string ShipAddress { get; set; } // ShipAddress (length: 60)
        public string ShipCity { get; set; } // ShipCity (length: 15)
        public string ShipRegion { get; set; } // ShipRegion (length: 15)
        public string ShipPostalCode { get; set; } // ShipPostalCode (length: 10)
        public string ShipCountry { get; set; } // ShipCountry (length: 15)

        // Reverse navigation

        /// <summary>
        /// Child OrderDetails where [Order Details].[OrderID] point to this entity (FK_Order_Details_Orders)
        /// </summary>
        public virtual ICollection<OrderDetail> OrderDetails { get; set; } // Order Details.FK_Order_Details_Orders

        // Foreign keys

        /// <summary>
        /// Parent Customer pointed by [Orders].([CustomerId]) (FK_Orders_Customers)
        /// </summary>
        public virtual Customer Customer { get; set; } // FK_Orders_Customers

        /// <summary>
        /// Parent Employee pointed by [Orders].([EmployeeId]) (FK_Orders_Employees)
        /// </summary>
        public virtual Employee Employee { get; set; } // FK_Orders_Employees

        /// <summary>
        /// Parent Shipper pointed by [Orders].([ShipVia]) (FK_Orders_Shippers)
        /// </summary>
        public virtual Shipper Shipper { get; set; } // FK_Orders_Shippers

        public Order()
        {
            Freight = 0m;
            OrderDetails = new List<OrderDetail>();
        }
    }

    // Order Details
    public class OrderDetail
    {
        public int OrderId { get; set; } // OrderID (Primary key)
        public int ProductId { get; set; } // ProductID (Primary key)
        public decimal UnitPrice { get; set; } // UnitPrice
        public short Quantity { get; set; } // Quantity
        public float Discount { get; set; } // Discount

        // Foreign keys

        /// <summary>
        /// Parent Order pointed by [Order Details].([OrderId]) (FK_Order_Details_Orders)
        /// </summary>
        public virtual Order Order { get; set; } // FK_Order_Details_Orders

        /// <summary>
        /// Parent Product pointed by [Order Details].([ProductId]) (FK_Order_Details_Products)
        /// </summary>
        public virtual Product Product { get; set; } // FK_Order_Details_Products

        public OrderDetail()
        {
            UnitPrice = 0m;
            Quantity = 1;
            Discount = 0f;
        }
    }

    // Order Details Extended
    public class OrderDetailsExtended
    {
        public int OrderId { get; set; } // OrderID
        public int ProductId { get; set; } // ProductID
        public string ProductName { get; set; } // ProductName (length: 40)
        public decimal UnitPrice { get; set; } // UnitPrice
        public short Quantity { get; set; } // Quantity
        public float Discount { get; set; } // Discount
        public decimal? ExtendedPrice { get; set; } // ExtendedPrice
    }

    // Orders Qry
    public class OrdersQry
    {
        public int OrderId { get; set; } // OrderID
        public string CustomerId { get; set; } // CustomerID (length: 5)
        public int? EmployeeId { get; set; } // EmployeeID
        public DateTime? OrderDate { get; set; } // OrderDate
        public DateTime? RequiredDate { get; set; } // RequiredDate
        public DateTime? ShippedDate { get; set; } // ShippedDate
        public int? ShipVia { get; set; } // ShipVia
        public decimal? Freight { get; set; } // Freight
        public string ShipName { get; set; } // ShipName (length: 40)
        public string ShipAddress { get; set; } // ShipAddress (length: 60)
        public string ShipCity { get; set; } // ShipCity (length: 15)
        public string ShipRegion { get; set; } // ShipRegion (length: 15)
        public string ShipPostalCode { get; set; } // ShipPostalCode (length: 10)
        public string ShipCountry { get; set; } // ShipCountry (length: 15)
        public string CompanyName { get; set; } // CompanyName (length: 40)
        public string Address { get; set; } // Address (length: 60)
        public string City { get; set; } // City (length: 15)
        public string Region { get; set; } // Region (length: 15)
        public string PostalCode { get; set; } // PostalCode (length: 10)
        public string Country { get; set; } // Country (length: 15)
    }

    // Order Subtotals
    public class OrderSubtotal
    {
        public int OrderId { get; set; } // OrderID
        public decimal? Subtotal { get; set; } // Subtotal
    }

    // Products
    public class Product
    {
        public int ProductId { get; set; } // ProductID (Primary key)
        public string ProductName { get; set; } // ProductName (length: 40)
        public int? SupplierId { get; set; } // SupplierID
        public int? CategoryId { get; set; } // CategoryID
        public string QuantityPerUnit { get; set; } // QuantityPerUnit (length: 20)
        public decimal? UnitPrice { get; set; } // UnitPrice
        public short? UnitsInStock { get; set; } // UnitsInStock
        public short? UnitsOnOrder { get; set; } // UnitsOnOrder
        public short? ReorderLevel { get; set; } // ReorderLevel
        public bool Discontinued { get; set; } // Discontinued

        // Reverse navigation

        /// <summary>
        /// Child OrderDetails where [Order Details].[ProductID] point to this entity (FK_Order_Details_Products)
        /// </summary>
        public virtual ICollection<OrderDetail> OrderDetails { get; set; } // Order Details.FK_Order_Details_Products

        // Foreign keys

        /// <summary>
        /// Parent Category pointed by [Products].([CategoryId]) (FK_Products_Categories)
        /// </summary>
        public virtual Category Category { get; set; } // FK_Products_Categories

        /// <summary>
        /// Parent Supplier pointed by [Products].([SupplierId]) (FK_Products_Suppliers)
        /// </summary>
        public virtual Supplier Supplier { get; set; } // FK_Products_Suppliers

        public Product()
        {
            UnitPrice = 0m;
            UnitsInStock = 0;
            UnitsOnOrder = 0;
            ReorderLevel = 0;
            Discontinued = false;
            OrderDetails = new List<OrderDetail>();
        }
    }

    // Products Above Average Price
    public class ProductsAboveAveragePrice
    {
        public string ProductName { get; set; } // ProductName (length: 40)
        public decimal? UnitPrice { get; set; } // UnitPrice
    }

    // Product Sales for 1997
    public class ProductSalesFor1997
    {
        public string CategoryName { get; set; } // CategoryName (length: 15)
        public string ProductName { get; set; } // ProductName (length: 40)
        public decimal? ProductSales { get; set; } // ProductSales
    }

    // Products by Category
    public class ProductsByCategory
    {
        public string CategoryName { get; set; } // CategoryName (length: 15)
        public string ProductName { get; set; } // ProductName (length: 40)
        public string QuantityPerUnit { get; set; } // QuantityPerUnit (length: 20)
        public short? UnitsInStock { get; set; } // UnitsInStock
        public bool Discontinued { get; set; } // Discontinued
    }

    // Quarterly Orders
    public class QuarterlyOrder
    {
        public string CustomerId { get; set; } // CustomerID (length: 5)
        public string CompanyName { get; set; } // CompanyName (length: 40)
        public string City { get; set; } // City (length: 15)
        public string Country { get; set; } // Country (length: 15)
    }

    // Region
    public class Region
    {
        public int RegionId { get; set; } // RegionID (Primary key)
        public string RegionDescription { get; set; } // RegionDescription (length: 50)

        // Reverse navigation

        /// <summary>
        /// Child Territories where [Territories].[RegionID] point to this entity (FK_Territories_Region)
        /// </summary>
        public virtual ICollection<Territory> Territories { get; set; } // Territories.FK_Territories_Region

        public Region()
        {
            Territories = new List<Territory>();
        }
    }

    // Sales by Category
    public class SalesByCategory
    {
        public int CategoryId { get; set; } // CategoryID
        public string CategoryName { get; set; } // CategoryName (length: 15)
        public string ProductName { get; set; } // ProductName (length: 40)
        public decimal? ProductSales { get; set; } // ProductSales
    }

    // Sales Totals by Amount
    public class SalesTotalsByAmount
    {
        public decimal? SaleAmount { get; set; } // SaleAmount
        public int OrderId { get; set; } // OrderID
        public string CompanyName { get; set; } // CompanyName (length: 40)
        public DateTime? ShippedDate { get; set; } // ShippedDate
    }

    // Shippers
    public class Shipper
    {
        public int ShipperId { get; set; } // ShipperID (Primary key)
        public string CompanyName { get; set; } // CompanyName (length: 40)
        public string Phone { get; set; } // Phone (length: 24)

        // Reverse navigation

        /// <summary>
        /// Child Orders where [Orders].[ShipVia] point to this entity (FK_Orders_Shippers)
        /// </summary>
        public virtual ICollection<Order> Orders { get; set; } // Orders.FK_Orders_Shippers

        public Shipper()
        {
            Orders = new List<Order>();
        }
    }

    // Summary of Sales by Quarter
    public class SummaryOfSalesByQuarter
    {
        public DateTime? ShippedDate { get; set; } // ShippedDate
        public int OrderId { get; set; } // OrderID
        public decimal? Subtotal { get; set; } // Subtotal
    }

    // Summary of Sales by Year
    public class SummaryOfSalesByYear
    {
        public DateTime? ShippedDate { get; set; } // ShippedDate
        public int OrderId { get; set; } // OrderID
        public decimal? Subtotal { get; set; } // Subtotal
    }

    // Suppliers
    public class Supplier
    {
        public int SupplierId { get; set; } // SupplierID (Primary key)
        public string CompanyName { get; set; } // CompanyName (length: 40)
        public string ContactName { get; set; } // ContactName (length: 30)
        public string ContactTitle { get; set; } // ContactTitle (length: 30)
        public string Address { get; set; } // Address (length: 60)
        public string City { get; set; } // City (length: 15)
        public string Region { get; set; } // Region (length: 15)
        public string PostalCode { get; set; } // PostalCode (length: 10)
        public string Country { get; set; } // Country (length: 15)
        public string Phone { get; set; } // Phone (length: 24)
        public string Fax { get; set; } // Fax (length: 24)
        public string HomePage { get; set; } // HomePage (length: 1073741823)

        // Reverse navigation

        /// <summary>
        /// Child Products where [Products].[SupplierID] point to this entity (FK_Products_Suppliers)
        /// </summary>
        public virtual ICollection<Product> Products { get; set; } // Products.FK_Products_Suppliers

        public Supplier()
        {
            Products = new List<Product>();
        }
    }

    // Territories
    public class Territory
    {
        public string TerritoryId { get; set; } // TerritoryID (Primary key) (length: 20)
        public string TerritoryDescription { get; set; } // TerritoryDescription (length: 50)
        public int RegionId { get; set; } // RegionID

        // Reverse navigation

        /// <summary>
        /// Child EmployeeTerritories where [EmployeeTerritories].[TerritoryID] point to this entity (FK_EmployeeTerritories_Territories)
        /// </summary>
        public virtual ICollection<EmployeeTerritory> EmployeeTerritories { get; set; } // EmployeeTerritories.FK_EmployeeTerritories_Territories

        // Foreign keys

        /// <summary>
        /// Parent Region pointed by [Territories].([RegionId]) (FK_Territories_Region)
        /// </summary>
        public virtual Region Region { get; set; } // FK_Territories_Region

        public Territory()
        {
            EmployeeTerritories = new List<EmployeeTerritory>();
        }
    }


    #endregion

    #region POCO Configuration

    // Alphabetical list of products
    public class AlphabeticalListOfProductConfiguration : IEntityTypeConfiguration<AlphabeticalListOfProduct>
    {
        public void Configure(EntityTypeBuilder<AlphabeticalListOfProduct> builder)
        {
            builder.ToView("Alphabetical list of products", "dbo");
            builder.HasNoKey();

            builder.Property(x => x.ProductId).HasColumnName(@"ProductID").HasColumnType("int").IsRequired();
            builder.Property(x => x.ProductName).HasColumnName(@"ProductName").HasColumnType("nvarchar(40)").IsRequired().HasMaxLength(40);
            builder.Property(x => x.SupplierId).HasColumnName(@"SupplierID").HasColumnType("int").IsRequired(false);
            builder.Property(x => x.CategoryId).HasColumnName(@"CategoryID").HasColumnType("int").IsRequired(false);
            builder.Property(x => x.QuantityPerUnit).HasColumnName(@"QuantityPerUnit").HasColumnType("nvarchar(20)").IsRequired(false).HasMaxLength(20);
            builder.Property(x => x.UnitPrice).HasColumnName(@"UnitPrice").HasColumnType("money").IsRequired(false);
            builder.Property(x => x.UnitsInStock).HasColumnName(@"UnitsInStock").HasColumnType("smallint").IsRequired(false);
            builder.Property(x => x.UnitsOnOrder).HasColumnName(@"UnitsOnOrder").HasColumnType("smallint").IsRequired(false);
            builder.Property(x => x.ReorderLevel).HasColumnName(@"ReorderLevel").HasColumnType("smallint").IsRequired(false);
            builder.Property(x => x.Discontinued).HasColumnName(@"Discontinued").HasColumnType("bit").IsRequired();
            builder.Property(x => x.CategoryName).HasColumnName(@"CategoryName").HasColumnType("nvarchar(15)").IsRequired().HasMaxLength(15);
        }
    }

    // Categories
    public class CategoryConfiguration : IEntityTypeConfiguration<Category>
    {
        public void Configure(EntityTypeBuilder<Category> builder)
        {
            builder.ToTable("Categories", "dbo");
            builder.HasKey(x => x.CategoryId).HasName("PK_Categories").IsClustered();

            builder.Property(x => x.CategoryId).HasColumnName(@"CategoryID").HasColumnType("int").IsRequired().ValueGeneratedOnAdd().UseIdentityColumn();
            builder.Property(x => x.CategoryName).HasColumnName(@"CategoryName").HasColumnType("nvarchar(15)").IsRequired().HasMaxLength(15);
            builder.Property(x => x.Description).HasColumnName(@"Description").HasColumnType("ntext").IsRequired(false);
            builder.Property(x => x.Picture).HasColumnName(@"Picture").HasColumnType("image(2147483647)").IsRequired(false).HasMaxLength(2147483647);

            builder.HasIndex(x => x.CategoryName).HasName("CategoryName");
        }
    }

    // Category Sales for 1997
    public class CategorySalesFor1997Configuration : IEntityTypeConfiguration<CategorySalesFor1997>
    {
        public void Configure(EntityTypeBuilder<CategorySalesFor1997> builder)
        {
            builder.ToView("Category Sales for 1997", "dbo");
            builder.HasNoKey();

            builder.Property(x => x.CategoryName).HasColumnName(@"CategoryName").HasColumnType("nvarchar(15)").IsRequired().HasMaxLength(15);
            builder.Property(x => x.CategorySales).HasColumnName(@"CategorySales").HasColumnType("money").IsRequired(false);
        }
    }

    // Current Product List
    public class CurrentProductListConfiguration : IEntityTypeConfiguration<CurrentProductList>
    {
        public void Configure(EntityTypeBuilder<CurrentProductList> builder)
        {
            builder.ToView("Current Product List", "dbo");
            builder.HasNoKey();

            builder.Property(x => x.ProductId).HasColumnName(@"ProductID").HasColumnType("int").IsRequired().ValueGeneratedOnAdd().UseIdentityColumn();
            builder.Property(x => x.ProductName).HasColumnName(@"ProductName").HasColumnType("nvarchar(40)").IsRequired().HasMaxLength(40);
        }
    }

    // Customers
    public class CustomerConfiguration : IEntityTypeConfiguration<Customer>
    {
        public void Configure(EntityTypeBuilder<Customer> builder)
        {
            builder.ToTable("Customers", "dbo");
            builder.HasKey(x => x.CustomerId).HasName("PK_Customers").IsClustered();

            builder.Property(x => x.CustomerId).HasColumnName(@"CustomerID").HasColumnType("nchar(5)").IsRequired().IsFixedLength().HasMaxLength(5).ValueGeneratedNever();
            builder.Property(x => x.CompanyName).HasColumnName(@"CompanyName").HasColumnType("nvarchar(40)").IsRequired().HasMaxLength(40);
            builder.Property(x => x.ContactName).HasColumnName(@"ContactName").HasColumnType("nvarchar(30)").IsRequired(false).HasMaxLength(30);
            builder.Property(x => x.ContactTitle).HasColumnName(@"ContactTitle").HasColumnType("nvarchar(30)").IsRequired(false).HasMaxLength(30);
            builder.Property(x => x.Address).HasColumnName(@"Address").HasColumnType("nvarchar(60)").IsRequired(false).HasMaxLength(60);
            builder.Property(x => x.City).HasColumnName(@"City").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.Region).HasColumnName(@"Region").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.PostalCode).HasColumnName(@"PostalCode").HasColumnType("nvarchar(10)").IsRequired(false).HasMaxLength(10);
            builder.Property(x => x.Country).HasColumnName(@"Country").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.Phone).HasColumnName(@"Phone").HasColumnType("nvarchar(24)").IsRequired(false).HasMaxLength(24);
            builder.Property(x => x.Fax).HasColumnName(@"Fax").HasColumnType("nvarchar(24)").IsRequired(false).HasMaxLength(24);

            builder.HasIndex(x => x.City).HasName("City");
            builder.HasIndex(x => x.CompanyName).HasName("CompanyName");
            builder.HasIndex(x => x.PostalCode).HasName("PostalCode");
            builder.HasIndex(x => x.Region).HasName("Region");
        }
    }

    // Customer and Suppliers by City
    public class CustomerAndSuppliersByCityConfiguration : IEntityTypeConfiguration<CustomerAndSuppliersByCity>
    {
        public void Configure(EntityTypeBuilder<CustomerAndSuppliersByCity> builder)
        {
            builder.ToView("Customer and Suppliers by City", "dbo");
            builder.HasNoKey();

            builder.Property(x => x.City).HasColumnName(@"City").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.CompanyName).HasColumnName(@"CompanyName").HasColumnType("nvarchar(40)").IsRequired().HasMaxLength(40);
            builder.Property(x => x.ContactName).HasColumnName(@"ContactName").HasColumnType("nvarchar(30)").IsRequired(false).HasMaxLength(30);
            builder.Property(x => x.Relationship).HasColumnName(@"Relationship").HasColumnType("varchar(9)").IsRequired().IsUnicode(false).HasMaxLength(9);
        }
    }

    // CustomerCustomerDemo
    public class CustomerCustomerDemoConfiguration : IEntityTypeConfiguration<CustomerCustomerDemo>
    {
        public void Configure(EntityTypeBuilder<CustomerCustomerDemo> builder)
        {
            builder.ToTable("CustomerCustomerDemo", "dbo");
            builder.HasKey(x => new { x.CustomerId, x.CustomerTypeId }).HasName("PK_CustomerCustomerDemo");

            builder.Property(x => x.CustomerId).HasColumnName(@"CustomerID").HasColumnType("nchar(5)").IsRequired().IsFixedLength().HasMaxLength(5).ValueGeneratedNever();
            builder.Property(x => x.CustomerTypeId).HasColumnName(@"CustomerTypeID").HasColumnType("nchar(10)").IsRequired().IsFixedLength().HasMaxLength(10).ValueGeneratedNever();

            // Foreign keys
            builder.HasOne(a => a.Customer).WithMany(b => b.CustomerCustomerDemoes).HasForeignKey(c => c.CustomerId).OnDelete(DeleteBehavior.ClientSetNull).HasConstraintName("FK_CustomerCustomerDemo_Customers");
            builder.HasOne(a => a.CustomerDemographic).WithMany(b => b.CustomerCustomerDemoes).HasForeignKey(c => c.CustomerTypeId).OnDelete(DeleteBehavior.ClientSetNull).HasConstraintName("FK_CustomerCustomerDemo");
        }
    }

    // CustomerDemographics
    public class CustomerDemographicConfiguration : IEntityTypeConfiguration<CustomerDemographic>
    {
        public void Configure(EntityTypeBuilder<CustomerDemographic> builder)
        {
            builder.ToTable("CustomerDemographics", "dbo");
            builder.HasKey(x => x.CustomerTypeId).HasName("PK_CustomerDemographics");

            builder.Property(x => x.CustomerTypeId).HasColumnName(@"CustomerTypeID").HasColumnType("nchar(10)").IsRequired().IsFixedLength().HasMaxLength(10).ValueGeneratedNever();
            builder.Property(x => x.CustomerDesc).HasColumnName(@"CustomerDesc").HasColumnType("ntext").IsRequired(false);
        }
    }

    // Employees
    public class EmployeeConfiguration : IEntityTypeConfiguration<Employee>
    {
        public void Configure(EntityTypeBuilder<Employee> builder)
        {
            builder.ToTable("Employees", "dbo");
            builder.HasKey(x => x.EmployeeId).HasName("PK_Employees").IsClustered();

            builder.Property(x => x.EmployeeId).HasColumnName(@"EmployeeID").HasColumnType("int").IsRequired().ValueGeneratedOnAdd().UseIdentityColumn();
            builder.Property(x => x.LastName).HasColumnName(@"LastName").HasColumnType("nvarchar(20)").IsRequired().HasMaxLength(20);
            builder.Property(x => x.FirstName).HasColumnName(@"FirstName").HasColumnType("nvarchar(10)").IsRequired().HasMaxLength(10);
            builder.Property(x => x.Title).HasColumnName(@"Title").HasColumnType("nvarchar(30)").IsRequired(false).HasMaxLength(30);
            builder.Property(x => x.TitleOfCourtesy).HasColumnName(@"TitleOfCourtesy").HasColumnType("nvarchar(25)").IsRequired(false).HasMaxLength(25);
            builder.Property(x => x.BirthDate).HasColumnName(@"BirthDate").HasColumnType("datetime").IsRequired(false);
            builder.Property(x => x.HireDate).HasColumnName(@"HireDate").HasColumnType("datetime").IsRequired(false);
            builder.Property(x => x.Address).HasColumnName(@"Address").HasColumnType("nvarchar(60)").IsRequired(false).HasMaxLength(60);
            builder.Property(x => x.City).HasColumnName(@"City").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.Region).HasColumnName(@"Region").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.PostalCode).HasColumnName(@"PostalCode").HasColumnType("nvarchar(10)").IsRequired(false).HasMaxLength(10);
            builder.Property(x => x.Country).HasColumnName(@"Country").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.HomePhone).HasColumnName(@"HomePhone").HasColumnType("nvarchar(24)").IsRequired(false).HasMaxLength(24);
            builder.Property(x => x.Extension).HasColumnName(@"Extension").HasColumnType("nvarchar(4)").IsRequired(false).HasMaxLength(4);
            builder.Property(x => x.Photo).HasColumnName(@"Photo").HasColumnType("image(2147483647)").IsRequired(false).HasMaxLength(2147483647);
            builder.Property(x => x.Notes).HasColumnName(@"Notes").HasColumnType("ntext").IsRequired(false);
            builder.Property(x => x.ReportsTo).HasColumnName(@"ReportsTo").HasColumnType("int").IsRequired(false);
            builder.Property(x => x.PhotoPath).HasColumnName(@"PhotoPath").HasColumnType("nvarchar(255)").IsRequired(false).HasMaxLength(255);

            // Foreign keys
            builder.HasOne(a => a.Employee_ReportsTo).WithMany(b => b.Employees).HasForeignKey(c => c.ReportsTo).OnDelete(DeleteBehavior.ClientSetNull).HasConstraintName("FK_Employees_Employees");

            builder.HasIndex(x => x.LastName).HasName("LastName");
            builder.HasIndex(x => x.PostalCode).HasName("PostalCode");
        }
    }

    // EmployeeTerritories
    public class EmployeeTerritoryConfiguration : IEntityTypeConfiguration<EmployeeTerritory>
    {
        public void Configure(EntityTypeBuilder<EmployeeTerritory> builder)
        {
            builder.ToTable("EmployeeTerritories", "dbo");
            builder.HasKey(x => new { x.EmployeeId, x.TerritoryId }).HasName("PK_EmployeeTerritories");

            builder.Property(x => x.EmployeeId).HasColumnName(@"EmployeeID").HasColumnType("int").IsRequired().ValueGeneratedNever();
            builder.Property(x => x.TerritoryId).HasColumnName(@"TerritoryID").HasColumnType("nvarchar(20)").IsRequired().HasMaxLength(20).ValueGeneratedNever();

            // Foreign keys
            builder.HasOne(a => a.Employee).WithMany(b => b.EmployeeTerritories).HasForeignKey(c => c.EmployeeId).OnDelete(DeleteBehavior.ClientSetNull).HasConstraintName("FK_EmployeeTerritories_Employees");
            builder.HasOne(a => a.Territory).WithMany(b => b.EmployeeTerritories).HasForeignKey(c => c.TerritoryId).OnDelete(DeleteBehavior.ClientSetNull).HasConstraintName("FK_EmployeeTerritories_Territories");
        }
    }

    // Invoices
    public class InvoiceConfiguration : IEntityTypeConfiguration<Invoice>
    {
        public void Configure(EntityTypeBuilder<Invoice> builder)
        {
            builder.ToView("Invoices", "dbo");
            builder.HasNoKey();

            builder.Property(x => x.ShipName).HasColumnName(@"ShipName").HasColumnType("nvarchar(40)").IsRequired(false).HasMaxLength(40);
            builder.Property(x => x.ShipAddress).HasColumnName(@"ShipAddress").HasColumnType("nvarchar(60)").IsRequired(false).HasMaxLength(60);
            builder.Property(x => x.ShipCity).HasColumnName(@"ShipCity").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.ShipRegion).HasColumnName(@"ShipRegion").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.ShipPostalCode).HasColumnName(@"ShipPostalCode").HasColumnType("nvarchar(10)").IsRequired(false).HasMaxLength(10);
            builder.Property(x => x.ShipCountry).HasColumnName(@"ShipCountry").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.CustomerId).HasColumnName(@"CustomerID").HasColumnType("nchar(5)").IsRequired(false).IsFixedLength().HasMaxLength(5);
            builder.Property(x => x.CustomerName).HasColumnName(@"CustomerName").HasColumnType("nvarchar(40)").IsRequired().HasMaxLength(40);
            builder.Property(x => x.Address).HasColumnName(@"Address").HasColumnType("nvarchar(60)").IsRequired(false).HasMaxLength(60);
            builder.Property(x => x.City).HasColumnName(@"City").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.Region).HasColumnName(@"Region").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.PostalCode).HasColumnName(@"PostalCode").HasColumnType("nvarchar(10)").IsRequired(false).HasMaxLength(10);
            builder.Property(x => x.Country).HasColumnName(@"Country").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.Salesperson).HasColumnName(@"Salesperson").HasColumnType("nvarchar(31)").IsRequired().HasMaxLength(31);
            builder.Property(x => x.OrderId).HasColumnName(@"OrderID").HasColumnType("int").IsRequired();
            builder.Property(x => x.OrderDate).HasColumnName(@"OrderDate").HasColumnType("datetime").IsRequired(false);
            builder.Property(x => x.RequiredDate).HasColumnName(@"RequiredDate").HasColumnType("datetime").IsRequired(false);
            builder.Property(x => x.ShippedDate).HasColumnName(@"ShippedDate").HasColumnType("datetime").IsRequired(false);
            builder.Property(x => x.ShipperName).HasColumnName(@"ShipperName").HasColumnType("nvarchar(40)").IsRequired().HasMaxLength(40);
            builder.Property(x => x.ProductId).HasColumnName(@"ProductID").HasColumnType("int").IsRequired();
            builder.Property(x => x.ProductName).HasColumnName(@"ProductName").HasColumnType("nvarchar(40)").IsRequired().HasMaxLength(40);
            builder.Property(x => x.UnitPrice).HasColumnName(@"UnitPrice").HasColumnType("money").IsRequired();
            builder.Property(x => x.Quantity).HasColumnName(@"Quantity").HasColumnType("smallint").IsRequired();
            builder.Property(x => x.Discount).HasColumnName(@"Discount").HasColumnType("real").IsRequired();
            builder.Property(x => x.ExtendedPrice).HasColumnName(@"ExtendedPrice").HasColumnType("money").IsRequired(false);
            builder.Property(x => x.Freight).HasColumnName(@"Freight").HasColumnType("money").IsRequired(false);
        }
    }

    // Orders
    public class OrderConfiguration : IEntityTypeConfiguration<Order>
    {
        public void Configure(EntityTypeBuilder<Order> builder)
        {
            builder.ToTable("Orders", "dbo");
            builder.HasKey(x => x.OrderId).HasName("PK_Orders").IsClustered();

            builder.Property(x => x.OrderId).HasColumnName(@"OrderID").HasColumnType("int").IsRequired().ValueGeneratedOnAdd().UseIdentityColumn();
            builder.Property(x => x.CustomerId).HasColumnName(@"CustomerID").HasColumnType("nchar(5)").IsRequired(false).IsFixedLength().HasMaxLength(5);
            builder.Property(x => x.EmployeeId).HasColumnName(@"EmployeeID").HasColumnType("int").IsRequired(false);
            builder.Property(x => x.OrderDate).HasColumnName(@"OrderDate").HasColumnType("datetime").IsRequired(false);
            builder.Property(x => x.RequiredDate).HasColumnName(@"RequiredDate").HasColumnType("datetime").IsRequired(false);
            builder.Property(x => x.ShippedDate).HasColumnName(@"ShippedDate").HasColumnType("datetime").IsRequired(false);
            builder.Property(x => x.ShipVia).HasColumnName(@"ShipVia").HasColumnType("int").IsRequired(false);
            builder.Property(x => x.Freight).HasColumnName(@"Freight").HasColumnType("money").IsRequired(false);
            builder.Property(x => x.ShipName).HasColumnName(@"ShipName").HasColumnType("nvarchar(40)").IsRequired(false).HasMaxLength(40);
            builder.Property(x => x.ShipAddress).HasColumnName(@"ShipAddress").HasColumnType("nvarchar(60)").IsRequired(false).HasMaxLength(60);
            builder.Property(x => x.ShipCity).HasColumnName(@"ShipCity").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.ShipRegion).HasColumnName(@"ShipRegion").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.ShipPostalCode).HasColumnName(@"ShipPostalCode").HasColumnType("nvarchar(10)").IsRequired(false).HasMaxLength(10);
            builder.Property(x => x.ShipCountry).HasColumnName(@"ShipCountry").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);

            // Foreign keys
            builder.HasOne(a => a.Customer).WithMany(b => b.Orders).HasForeignKey(c => c.CustomerId).OnDelete(DeleteBehavior.ClientSetNull).HasConstraintName("FK_Orders_Customers");
            builder.HasOne(a => a.Employee).WithMany(b => b.Orders).HasForeignKey(c => c.EmployeeId).OnDelete(DeleteBehavior.ClientSetNull).HasConstraintName("FK_Orders_Employees");
            builder.HasOne(a => a.Shipper).WithMany(b => b.Orders).HasForeignKey(c => c.ShipVia).OnDelete(DeleteBehavior.ClientSetNull).HasConstraintName("FK_Orders_Shippers");

            builder.HasIndex(x => x.CustomerId).HasName("CustomerID");
            builder.HasIndex(x => x.CustomerId).HasName("CustomersOrders");
            builder.HasIndex(x => x.EmployeeId).HasName("EmployeeID");
            builder.HasIndex(x => x.EmployeeId).HasName("EmployeesOrders");
            builder.HasIndex(x => x.OrderDate).HasName("OrderDate");
            builder.HasIndex(x => x.ShippedDate).HasName("ShippedDate");
            builder.HasIndex(x => x.ShipVia).HasName("ShippersOrders");
            builder.HasIndex(x => x.ShipPostalCode).HasName("ShipPostalCode");
        }
    }

    // Order Details
    public class OrderDetailConfiguration : IEntityTypeConfiguration<OrderDetail>
    {
        public void Configure(EntityTypeBuilder<OrderDetail> builder)
        {
            builder.ToTable("Order Details", "dbo");
            builder.HasKey(x => new { x.OrderId, x.ProductId }).HasName("PK_Order_Details").IsClustered();

            builder.Property(x => x.OrderId).HasColumnName(@"OrderID").HasColumnType("int").IsRequired().ValueGeneratedNever();
            builder.Property(x => x.ProductId).HasColumnName(@"ProductID").HasColumnType("int").IsRequired().ValueGeneratedNever();
            builder.Property(x => x.UnitPrice).HasColumnName(@"UnitPrice").HasColumnType("money").IsRequired();
            builder.Property(x => x.Quantity).HasColumnName(@"Quantity").HasColumnType("smallint").IsRequired();
            builder.Property(x => x.Discount).HasColumnName(@"Discount").HasColumnType("real").IsRequired();

            // Foreign keys
            builder.HasOne(a => a.Order).WithMany(b => b.OrderDetails).HasForeignKey(c => c.OrderId).OnDelete(DeleteBehavior.ClientSetNull).HasConstraintName("FK_Order_Details_Orders");
            builder.HasOne(a => a.Product).WithMany(b => b.OrderDetails).HasForeignKey(c => c.ProductId).OnDelete(DeleteBehavior.ClientSetNull).HasConstraintName("FK_Order_Details_Products");

            builder.HasIndex(x => x.OrderId).HasName("OrderID");
            builder.HasIndex(x => x.OrderId).HasName("OrdersOrder_Details");
            builder.HasIndex(x => x.ProductId).HasName("ProductID");
            builder.HasIndex(x => x.ProductId).HasName("ProductsOrder_Details");
        }
    }

    // Order Details Extended
    public class OrderDetailsExtendedConfiguration : IEntityTypeConfiguration<OrderDetailsExtended>
    {
        public void Configure(EntityTypeBuilder<OrderDetailsExtended> builder)
        {
            builder.ToView("Order Details Extended", "dbo");
            builder.HasNoKey();

            builder.Property(x => x.OrderId).HasColumnName(@"OrderID").HasColumnType("int").IsRequired();
            builder.Property(x => x.ProductId).HasColumnName(@"ProductID").HasColumnType("int").IsRequired();
            builder.Property(x => x.ProductName).HasColumnName(@"ProductName").HasColumnType("nvarchar(40)").IsRequired().HasMaxLength(40);
            builder.Property(x => x.UnitPrice).HasColumnName(@"UnitPrice").HasColumnType("money").IsRequired();
            builder.Property(x => x.Quantity).HasColumnName(@"Quantity").HasColumnType("smallint").IsRequired();
            builder.Property(x => x.Discount).HasColumnName(@"Discount").HasColumnType("real").IsRequired();
            builder.Property(x => x.ExtendedPrice).HasColumnName(@"ExtendedPrice").HasColumnType("money").IsRequired(false);
        }
    }

    // Orders Qry
    public class OrdersQryConfiguration : IEntityTypeConfiguration<OrdersQry>
    {
        public void Configure(EntityTypeBuilder<OrdersQry> builder)
        {
            builder.ToView("Orders Qry", "dbo");
            builder.HasNoKey();

            builder.Property(x => x.OrderId).HasColumnName(@"OrderID").HasColumnType("int").IsRequired();
            builder.Property(x => x.CustomerId).HasColumnName(@"CustomerID").HasColumnType("nchar(5)").IsRequired(false).IsFixedLength().HasMaxLength(5);
            builder.Property(x => x.EmployeeId).HasColumnName(@"EmployeeID").HasColumnType("int").IsRequired(false);
            builder.Property(x => x.OrderDate).HasColumnName(@"OrderDate").HasColumnType("datetime").IsRequired(false);
            builder.Property(x => x.RequiredDate).HasColumnName(@"RequiredDate").HasColumnType("datetime").IsRequired(false);
            builder.Property(x => x.ShippedDate).HasColumnName(@"ShippedDate").HasColumnType("datetime").IsRequired(false);
            builder.Property(x => x.ShipVia).HasColumnName(@"ShipVia").HasColumnType("int").IsRequired(false);
            builder.Property(x => x.Freight).HasColumnName(@"Freight").HasColumnType("money").IsRequired(false);
            builder.Property(x => x.ShipName).HasColumnName(@"ShipName").HasColumnType("nvarchar(40)").IsRequired(false).HasMaxLength(40);
            builder.Property(x => x.ShipAddress).HasColumnName(@"ShipAddress").HasColumnType("nvarchar(60)").IsRequired(false).HasMaxLength(60);
            builder.Property(x => x.ShipCity).HasColumnName(@"ShipCity").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.ShipRegion).HasColumnName(@"ShipRegion").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.ShipPostalCode).HasColumnName(@"ShipPostalCode").HasColumnType("nvarchar(10)").IsRequired(false).HasMaxLength(10);
            builder.Property(x => x.ShipCountry).HasColumnName(@"ShipCountry").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.CompanyName).HasColumnName(@"CompanyName").HasColumnType("nvarchar(40)").IsRequired().HasMaxLength(40);
            builder.Property(x => x.Address).HasColumnName(@"Address").HasColumnType("nvarchar(60)").IsRequired(false).HasMaxLength(60);
            builder.Property(x => x.City).HasColumnName(@"City").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.Region).HasColumnName(@"Region").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.PostalCode).HasColumnName(@"PostalCode").HasColumnType("nvarchar(10)").IsRequired(false).HasMaxLength(10);
            builder.Property(x => x.Country).HasColumnName(@"Country").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
        }
    }

    // Order Subtotals
    public class OrderSubtotalConfiguration : IEntityTypeConfiguration<OrderSubtotal>
    {
        public void Configure(EntityTypeBuilder<OrderSubtotal> builder)
        {
            builder.ToView("Order Subtotals", "dbo");
            builder.HasNoKey();

            builder.Property(x => x.OrderId).HasColumnName(@"OrderID").HasColumnType("int").IsRequired();
            builder.Property(x => x.Subtotal).HasColumnName(@"Subtotal").HasColumnType("money").IsRequired(false);
        }
    }

    // Products
    public class ProductConfiguration : IEntityTypeConfiguration<Product>
    {
        public void Configure(EntityTypeBuilder<Product> builder)
        {
            builder.ToTable("Products", "dbo");
            builder.HasKey(x => x.ProductId).HasName("PK_Products").IsClustered();

            builder.Property(x => x.ProductId).HasColumnName(@"ProductID").HasColumnType("int").IsRequired().ValueGeneratedOnAdd().UseIdentityColumn();
            builder.Property(x => x.ProductName).HasColumnName(@"ProductName").HasColumnType("nvarchar(40)").IsRequired().HasMaxLength(40);
            builder.Property(x => x.SupplierId).HasColumnName(@"SupplierID").HasColumnType("int").IsRequired(false);
            builder.Property(x => x.CategoryId).HasColumnName(@"CategoryID").HasColumnType("int").IsRequired(false);
            builder.Property(x => x.QuantityPerUnit).HasColumnName(@"QuantityPerUnit").HasColumnType("nvarchar(20)").IsRequired(false).HasMaxLength(20);
            builder.Property(x => x.UnitPrice).HasColumnName(@"UnitPrice").HasColumnType("money").IsRequired(false);
            builder.Property(x => x.UnitsInStock).HasColumnName(@"UnitsInStock").HasColumnType("smallint").IsRequired(false);
            builder.Property(x => x.UnitsOnOrder).HasColumnName(@"UnitsOnOrder").HasColumnType("smallint").IsRequired(false);
            builder.Property(x => x.ReorderLevel).HasColumnName(@"ReorderLevel").HasColumnType("smallint").IsRequired(false);
            builder.Property(x => x.Discontinued).HasColumnName(@"Discontinued").HasColumnType("bit").IsRequired();

            // Foreign keys
            builder.HasOne(a => a.Category).WithMany(b => b.Products).HasForeignKey(c => c.CategoryId).OnDelete(DeleteBehavior.ClientSetNull).HasConstraintName("FK_Products_Categories");
            builder.HasOne(a => a.Supplier).WithMany(b => b.Products).HasForeignKey(c => c.SupplierId).OnDelete(DeleteBehavior.ClientSetNull).HasConstraintName("FK_Products_Suppliers");

            builder.HasIndex(x => x.CategoryId).HasName("CategoriesProducts");
            builder.HasIndex(x => x.CategoryId).HasName("CategoryID");
            builder.HasIndex(x => x.ProductName).HasName("ProductName");
            builder.HasIndex(x => x.SupplierId).HasName("SupplierID");
            builder.HasIndex(x => x.SupplierId).HasName("SuppliersProducts");
        }
    }

    // Products Above Average Price
    public class ProductsAboveAveragePriceConfiguration : IEntityTypeConfiguration<ProductsAboveAveragePrice>
    {
        public void Configure(EntityTypeBuilder<ProductsAboveAveragePrice> builder)
        {
            builder.ToView("Products Above Average Price", "dbo");
            builder.HasNoKey();

            builder.Property(x => x.ProductName).HasColumnName(@"ProductName").HasColumnType("nvarchar(40)").IsRequired().HasMaxLength(40);
            builder.Property(x => x.UnitPrice).HasColumnName(@"UnitPrice").HasColumnType("money").IsRequired(false);
        }
    }

    // Product Sales for 1997
    public class ProductSalesFor1997Configuration : IEntityTypeConfiguration<ProductSalesFor1997>
    {
        public void Configure(EntityTypeBuilder<ProductSalesFor1997> builder)
        {
            builder.ToView("Product Sales for 1997", "dbo");
            builder.HasNoKey();

            builder.Property(x => x.CategoryName).HasColumnName(@"CategoryName").HasColumnType("nvarchar(15)").IsRequired().HasMaxLength(15);
            builder.Property(x => x.ProductName).HasColumnName(@"ProductName").HasColumnType("nvarchar(40)").IsRequired().HasMaxLength(40);
            builder.Property(x => x.ProductSales).HasColumnName(@"ProductSales").HasColumnType("money").IsRequired(false);
        }
    }

    // Products by Category
    public class ProductsByCategoryConfiguration : IEntityTypeConfiguration<ProductsByCategory>
    {
        public void Configure(EntityTypeBuilder<ProductsByCategory> builder)
        {
            builder.ToView("Products by Category", "dbo");
            builder.HasNoKey();

            builder.Property(x => x.CategoryName).HasColumnName(@"CategoryName").HasColumnType("nvarchar(15)").IsRequired().HasMaxLength(15);
            builder.Property(x => x.ProductName).HasColumnName(@"ProductName").HasColumnType("nvarchar(40)").IsRequired().HasMaxLength(40);
            builder.Property(x => x.QuantityPerUnit).HasColumnName(@"QuantityPerUnit").HasColumnType("nvarchar(20)").IsRequired(false).HasMaxLength(20);
            builder.Property(x => x.UnitsInStock).HasColumnName(@"UnitsInStock").HasColumnType("smallint").IsRequired(false);
            builder.Property(x => x.Discontinued).HasColumnName(@"Discontinued").HasColumnType("bit").IsRequired();
        }
    }

    // Quarterly Orders
    public class QuarterlyOrderConfiguration : IEntityTypeConfiguration<QuarterlyOrder>
    {
        public void Configure(EntityTypeBuilder<QuarterlyOrder> builder)
        {
            builder.ToView("Quarterly Orders", "dbo");
            builder.HasNoKey();

            builder.Property(x => x.CustomerId).HasColumnName(@"CustomerID").HasColumnType("nchar(5)").IsRequired(false).IsFixedLength().HasMaxLength(5);
            builder.Property(x => x.CompanyName).HasColumnName(@"CompanyName").HasColumnType("nvarchar(40)").IsRequired(false).HasMaxLength(40);
            builder.Property(x => x.City).HasColumnName(@"City").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.Country).HasColumnName(@"Country").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
        }
    }

    // Region
    public class RegionConfiguration : IEntityTypeConfiguration<Region>
    {
        public void Configure(EntityTypeBuilder<Region> builder)
        {
            builder.ToTable("Region", "dbo");
            builder.HasKey(x => x.RegionId).HasName("PK_Region");

            builder.Property(x => x.RegionId).HasColumnName(@"RegionID").HasColumnType("int").IsRequired().ValueGeneratedNever();
            builder.Property(x => x.RegionDescription).HasColumnName(@"RegionDescription").HasColumnType("nchar(50)").IsRequired().IsFixedLength().HasMaxLength(50);
        }
    }

    // Sales by Category
    public class SalesByCategoryConfiguration : IEntityTypeConfiguration<SalesByCategory>
    {
        public void Configure(EntityTypeBuilder<SalesByCategory> builder)
        {
            builder.ToView("Sales by Category", "dbo");
            builder.HasNoKey();

            builder.Property(x => x.CategoryId).HasColumnName(@"CategoryID").HasColumnType("int").IsRequired();
            builder.Property(x => x.CategoryName).HasColumnName(@"CategoryName").HasColumnType("nvarchar(15)").IsRequired().HasMaxLength(15);
            builder.Property(x => x.ProductName).HasColumnName(@"ProductName").HasColumnType("nvarchar(40)").IsRequired().HasMaxLength(40);
            builder.Property(x => x.ProductSales).HasColumnName(@"ProductSales").HasColumnType("money").IsRequired(false);
        }
    }

    // Sales Totals by Amount
    public class SalesTotalsByAmountConfiguration : IEntityTypeConfiguration<SalesTotalsByAmount>
    {
        public void Configure(EntityTypeBuilder<SalesTotalsByAmount> builder)
        {
            builder.ToView("Sales Totals by Amount", "dbo");
            builder.HasNoKey();

            builder.Property(x => x.SaleAmount).HasColumnName(@"SaleAmount").HasColumnType("money").IsRequired(false);
            builder.Property(x => x.OrderId).HasColumnName(@"OrderID").HasColumnType("int").IsRequired();
            builder.Property(x => x.CompanyName).HasColumnName(@"CompanyName").HasColumnType("nvarchar(40)").IsRequired().HasMaxLength(40);
            builder.Property(x => x.ShippedDate).HasColumnName(@"ShippedDate").HasColumnType("datetime").IsRequired(false);
        }
    }

    // Shippers
    public class ShipperConfiguration : IEntityTypeConfiguration<Shipper>
    {
        public void Configure(EntityTypeBuilder<Shipper> builder)
        {
            builder.ToTable("Shippers", "dbo");
            builder.HasKey(x => x.ShipperId).HasName("PK_Shippers").IsClustered();

            builder.Property(x => x.ShipperId).HasColumnName(@"ShipperID").HasColumnType("int").IsRequired().ValueGeneratedOnAdd().UseIdentityColumn();
            builder.Property(x => x.CompanyName).HasColumnName(@"CompanyName").HasColumnType("nvarchar(40)").IsRequired().HasMaxLength(40);
            builder.Property(x => x.Phone).HasColumnName(@"Phone").HasColumnType("nvarchar(24)").IsRequired(false).HasMaxLength(24);
        }
    }

    // Summary of Sales by Quarter
    public class SummaryOfSalesByQuarterConfiguration : IEntityTypeConfiguration<SummaryOfSalesByQuarter>
    {
        public void Configure(EntityTypeBuilder<SummaryOfSalesByQuarter> builder)
        {
            builder.ToView("Summary of Sales by Quarter", "dbo");
            builder.HasNoKey();

            builder.Property(x => x.ShippedDate).HasColumnName(@"ShippedDate").HasColumnType("datetime").IsRequired(false);
            builder.Property(x => x.OrderId).HasColumnName(@"OrderID").HasColumnType("int").IsRequired();
            builder.Property(x => x.Subtotal).HasColumnName(@"Subtotal").HasColumnType("money").IsRequired(false);
        }
    }

    // Summary of Sales by Year
    public class SummaryOfSalesByYearConfiguration : IEntityTypeConfiguration<SummaryOfSalesByYear>
    {
        public void Configure(EntityTypeBuilder<SummaryOfSalesByYear> builder)
        {
            builder.ToView("Summary of Sales by Year", "dbo");
            builder.HasNoKey();

            builder.Property(x => x.ShippedDate).HasColumnName(@"ShippedDate").HasColumnType("datetime").IsRequired(false);
            builder.Property(x => x.OrderId).HasColumnName(@"OrderID").HasColumnType("int").IsRequired();
            builder.Property(x => x.Subtotal).HasColumnName(@"Subtotal").HasColumnType("money").IsRequired(false);
        }
    }

    // Suppliers
    public class SupplierConfiguration : IEntityTypeConfiguration<Supplier>
    {
        public void Configure(EntityTypeBuilder<Supplier> builder)
        {
            builder.ToTable("Suppliers", "dbo");
            builder.HasKey(x => x.SupplierId).HasName("PK_Suppliers").IsClustered();

            builder.Property(x => x.SupplierId).HasColumnName(@"SupplierID").HasColumnType("int").IsRequired().ValueGeneratedOnAdd().UseIdentityColumn();
            builder.Property(x => x.CompanyName).HasColumnName(@"CompanyName").HasColumnType("nvarchar(40)").IsRequired().HasMaxLength(40);
            builder.Property(x => x.ContactName).HasColumnName(@"ContactName").HasColumnType("nvarchar(30)").IsRequired(false).HasMaxLength(30);
            builder.Property(x => x.ContactTitle).HasColumnName(@"ContactTitle").HasColumnType("nvarchar(30)").IsRequired(false).HasMaxLength(30);
            builder.Property(x => x.Address).HasColumnName(@"Address").HasColumnType("nvarchar(60)").IsRequired(false).HasMaxLength(60);
            builder.Property(x => x.City).HasColumnName(@"City").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.Region).HasColumnName(@"Region").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.PostalCode).HasColumnName(@"PostalCode").HasColumnType("nvarchar(10)").IsRequired(false).HasMaxLength(10);
            builder.Property(x => x.Country).HasColumnName(@"Country").HasColumnType("nvarchar(15)").IsRequired(false).HasMaxLength(15);
            builder.Property(x => x.Phone).HasColumnName(@"Phone").HasColumnType("nvarchar(24)").IsRequired(false).HasMaxLength(24);
            builder.Property(x => x.Fax).HasColumnName(@"Fax").HasColumnType("nvarchar(24)").IsRequired(false).HasMaxLength(24);
            builder.Property(x => x.HomePage).HasColumnName(@"HomePage").HasColumnType("ntext").IsRequired(false);

            builder.HasIndex(x => x.CompanyName).HasName("CompanyName");
            builder.HasIndex(x => x.PostalCode).HasName("PostalCode");
        }
    }

    // Territories
    public class TerritoryConfiguration : IEntityTypeConfiguration<Territory>
    {
        public void Configure(EntityTypeBuilder<Territory> builder)
        {
            builder.ToTable("Territories", "dbo");
            builder.HasKey(x => x.TerritoryId).HasName("PK_Territories");

            builder.Property(x => x.TerritoryId).HasColumnName(@"TerritoryID").HasColumnType("nvarchar(20)").IsRequired().HasMaxLength(20).ValueGeneratedNever();
            builder.Property(x => x.TerritoryDescription).HasColumnName(@"TerritoryDescription").HasColumnType("nchar(50)").IsRequired().IsFixedLength().HasMaxLength(50);
            builder.Property(x => x.RegionId).HasColumnName(@"RegionID").HasColumnType("int").IsRequired();

            // Foreign keys
            builder.HasOne(a => a.Region).WithMany(b => b.Territories).HasForeignKey(c => c.RegionId).OnDelete(DeleteBehavior.ClientSetNull).HasConstraintName("FK_Territories_Region");
        }
    }


    #endregion

    #region Stored procedure return models

    public class CustOrderHistReturnModel
    {
        public string ProductName { get; set; }
        public int? Total { get; set; }
    }

    public class CustOrdersDetailReturnModel
    {
        public string ProductName { get; set; }
        public decimal? UnitPrice { get; set; }
        public short Quantity { get; set; }
        public int? Discount { get; set; }
        public decimal? ExtendedPrice { get; set; }
    }

    public class CustOrdersOrdersReturnModel
    {
        public int OrderID { get; set; }
        public DateTime? OrderDate { get; set; }
        public DateTime? RequiredDate { get; set; }
        public DateTime? ShippedDate { get; set; }
    }

    public class EmployeeSalesByCountryReturnModel
    {
        public string Country { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public DateTime? ShippedDate { get; set; }
        public int OrderID { get; set; }
        public decimal? SaleAmount { get; set; }
    }

    public class SalesByCategoryReturnModel
    {
        public string ProductName { get; set; }
        public decimal? TotalPurchase { get; set; }
    }

    public class SalesByYearReturnModel
    {
        public DateTime? ShippedDate { get; set; }
        public int OrderID { get; set; }
        public decimal? Subtotal { get; set; }
        public string Year { get; set; }
    }

    public class TenMostExpensiveProductsReturnModel
    {
        public string TenMostExpensiveProducts { get; set; }
        public decimal? UnitPrice { get; set; }
    }


    #endregion

}
// </auto-generated>