using System;

namespace EFCoreDbFunctionsSample.Entities
{
    public class Person
    {
        public int Id { get; set; }

        public string Name { get; set; }

        public DateTime AddDate { get; set; }
    }
}

var usersInfo = context.People.Where(person => (DateTime.Now - person.AddDate).Days <= 10).ToList();

'The LINQ expression 'DbSet<Person>.Where(p => (DateTime.Now - p.AddDate).Days <= 10)'
could not be translated. Either rewrite the query in a form that can be translated,
or switch to client evaluation explicitly by inserting a call to either
AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().
See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

SELECT [p].[Id], [p].[AddDate], [p].[Name]
FROM [People] AS [p]
WHERE DATEDIFF(Day, [p].[AddDate], GETDATE()) <= 10

namespace EFCoreDbFunctionsSample.DataLayer
{
    public enum SqlDateDiff
    {
        Year,
        Quarter,
        Month,
        DayOfYear,
        Day,
        Week,
        Hour,
        Minute,
        Second,
        MilliSecond,
        MicroSecond,
        NanoSecond
    }

    public static class SqlDbFunctionsExtensions
    {
        public static int SqlDateDiff(SqlDateDiff interval, DateTime initial, DateTime end)
            => throw new InvalidOperationException($"{nameof(SqlDateDiff)} method cannot be called from the client side.");
        public static readonly MethodInfo SqlDateDiffMethodInfo = typeof(SqlDbFunctionsExtensions)
            .GetRuntimeMethod(
                nameof(SqlDbFunctionsExtensions.SqlDateDiff),
                new[] { typeof(SqlDateDiff), typeof(DateTime), typeof(DateTime) }
            );
    }
}

   [DataContract]
    public class Book 
    {
        [DataMember]
        public int Code { get; set; }

        [DataMember]
        public string Name { get; set; }             
    }

  [ServiceContract]
    public interface ISampleService
    {
        [OperationContract]
        IEnumerable<Book> GetAll();

        [OperationContract]
        void Save( Book book );
    }

public class SampleService : ISampleService
    {
        public List<Book> ListOfBook 
        {
            get; 
            private set; 
        }

        public SampleService()
        {
            ListOfBook = new List<Book>();
        }
        public IEnumerable<Book> GetAll()
        {
            ListOfBook.AddRange( new Book[] 
            {
                new Book(){Code=1 , Name="Book1"},
                new Book(){Code=2 , Name="Book2"},
            } );
            return ListOfBook;
        }

        public void Save( Book book )
        {
            ListOfBook.Add( book );
        }
    }

 [DataContract]
    public class Book 
    {
        [DataMember]
        public int Code { get; set; }
        [DataMember]
        public string Name { get; set; }

        [DataMember]
        public string Author { get; set; }
    }

[DataMember( IsRequired = true )]
public string Author { get; set; }

  [DataContract]
    public class Book
    {
        [DataMember]
        public int Code { get; set; }
        [DataMember]
        public string Name { get; set; }

        [DataMember( IsRequired = true )]
        public string Author { get; set; }

        [OnDeserializing]
        private void OnDeserializing( StreamingContext context )
        {
            if ( string.IsNullOrEmpty( Author ) )
            {
                Author = "Masoud Pakdel";
            }
        }
    }

[DataContract]
    public class Book : IExtensibleDataObject
    {
        [DataMember]
        public int Code { get; set; }
        [DataMember]
        public string Name { get; set; }

        [DataMember]
        public string Author { get; set; }
    
        public virtual ExtensionDataObject ExtensionData
        {
            get { return _extensionData; }
            set
            {
                _extensionData = value;
            }
        }
        private ExtensionDataObject _extensionData;
    }

public IEnumerable<Book> GetAll()
        {
            ListOfBook.AddRange( new Book[] 
            {
                new Book(){Code=1 , Name="Book1", Author="Masoud Pakdel"},
                new Book(){Code=2 , Name="Book2" },
            } );
            return ListOfBook;
        }

 [ServiceBehavior( IgnoreExtensionDataObject = true )]
  public class SampleService : ISampleService

namespace Loans.Entities
{
    public class Applicant
    {
        public int Id { set; get; }

        public string Name { set; get; }

        public int Age { set; get; }

        public string Address { set; get; }

        public decimal Salary { set; get; }
    }
}

namespace Loans.Entities
{
    public class LoanProduct
    {
        public int Id { set; get; }

        public string ProductName { set; get; }

        public decimal InterestRate { set; get; }
    }
}

namespace Loans.Entities
{
    public class LoanApplication
    {
        public int Id { set; get; }

        public LoanProduct Product { set; get; }

        public LoanAmount Amount { set; get; }

        public Applicant Applicant { set; get; }

        public bool IsAccepted { set; get; }
    }

    public class LoanAmount
    {
        public string CurrencyCode { get; set; }

        public decimal Principal { get; set; }
    }
}

namespace Loans.Models
{
    public class IdentityVerificationStatus
    {
        public bool Passed { get; set; }
    }
}

using Loans.Models;

namespace Loans.Services.Contracts
{
    public interface IIdentityVerifier
    {
        void Initialize();

        bool Validate(string applicantName, int applicantAge, string applicantAddress);

        void Validate(string applicantName, int applicantAge, string applicantAddress, out bool isValid);

        void Validate(string applicantName, int applicantAge, string applicantAddress,
            ref IdentityVerificationStatus status);
    }
}

namespace Loans.Services.Contracts
{
    public interface ICreditScorer
    {
        int Score { get; }

        void CalculateScore(string applicantName, string applicantAddress);
    }
}

using System;
using Loans.Entities;
using Loans.Services.Contracts;

namespace Loans.Services
{
    public class LoanApplicationProcessor
    {
        private const decimal MinimumSalary = 1_500_000_0;
        private const int MinimumAge = 18;
        private const int MinimumCreditScore = 100_000;

        private readonly IIdentityVerifier _identityVerifier;
        private readonly ICreditScorer _creditScorer;

        public LoanApplicationProcessor(
            IIdentityVerifier identityVerifier,
            ICreditScorer creditScorer)
        {
            _identityVerifier = identityVerifier ?? throw new ArgumentNullException(nameof(identityVerifier));
            _creditScorer = creditScorer ?? throw new ArgumentNullException(nameof(creditScorer));
        }

        public bool Process(LoanApplication application)
        {
            application.IsAccepted = false;

            if (application.Applicant.Salary < MinimumSalary)
            {
                return application.IsAccepted;
            }

            if (application.Applicant.Age < MinimumAge)
            {
                return application.IsAccepted;
            }

            _identityVerifier.Initialize();

            var isValidIdentity = _identityVerifier.Validate(
                application.Applicant.Name, application.Applicant.Age, application.Applicant.Address);

            if (!isValidIdentity)
            {
                return application.IsAccepted;
            }

            _creditScorer.CalculateScore(application.Applicant.Name, application.Applicant.Address);
            if (_creditScorer.Score < MinimumCreditScore)
            {
                return application.IsAccepted;
            }

            application.IsAccepted = true;
            return application.IsAccepted;
        }
    }
}

using System;
using Loans.Models;
using Loans.Services.Contracts;

namespace Loans.Services
{
    public class IdentityVerifierServiceGateway : IIdentityVerifier
    {
        public DateTime LastCheckTime { get; private set; }

        public void Initialize()
        {
            // Initialize connection to external service
        }

        public bool Validate(string applicantName, int applicantAge, string applicantAddress)
        {
            Connect();
            var isValidIdentity = CallService(applicantName, applicantAge, applicantAddress);
            LastCheckTime = DateTime.Now;
            Disconnect();

            return isValidIdentity;
        }

        private void Connect()
        {
            // Open connection to external service
        }

        private bool CallService(string applicantName, int applicantAge, string applicantAddress)
        {
            // Make call to external service, interpret the response, and return result

            return false; // Simulate result for demo purposes
        }

        private void Disconnect()
        {
            // Close connection to external service
        }

        public void Validate(string applicantName, int applicantAge, string applicantAddress, out bool isValid)
        {
            throw new NotImplementedException();
        }

        public void Validate(string applicantName, int applicantAge, string applicantAddress,
            ref IdentityVerificationStatus status)
        {
            throw new NotImplementedException();
        }
    }
}

        public LoanApplicationProcessor(
            IIdentityVerifier identityVerifier,
            ICreditScorer creditScorer)
        {
            _identityVerifier = identityVerifier ?? throw new ArgumentNullException(nameof(identityVerifier));
            _creditScorer = creditScorer ?? throw new ArgumentNullException(nameof(creditScorer));
        }

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>netcoreapp2.2</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <ProjectReference Include="..\Loans\Loans.csproj" />
  </ItemGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.NET.Test.Sdk" Version="16.3.0" />
    <PackageReference Include="MSTest.TestAdapter" Version="2.0.0" />
    <PackageReference Include="MSTest.TestFramework" Version="2.0.0" />    
  </ItemGroup>
</Project>

using Loans.Entities;
using Loans.Services;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace Loans.Tests
{
    [TestClass]
    public class LoanApplicationProcessorShould
    {
        [TestMethod]
        public void DeclineLowSalary()
        {
            var product = new LoanProduct {Id = 99, ProductName = "Loan", InterestRate = 5.25m};
            var amount = new LoanAmount {CurrencyCode = "Rial", Principal = 2_000_000_0};
            var applicant =
                new Applicant {Id = 1, Name = "User 1", Age = 25, Address = "This place", Salary = 1_100_000_0};
            var application = new LoanApplication {Id = 42, Product = product, Amount = amount, Applicant = applicant};
            var processor = new LoanApplicationProcessor(null, null);
            processor.Process(application);

            Assert.IsFalse(application.IsAccepted);
        }
    }
}

public class LoanApplicationProcessor
{
    private const decimal MinimumSalary = 1_500_000_0;

Test method Loans.Tests.LoanApplicationProcessorShould.DeclineLowSalary threw exception:
System.ArgumentNullException: Value cannot be null.
Parameter name: identityVerifier

        public LoanApplicationProcessor(
            IIdentityVerifier identityVerifier,
            ICreditScorer creditScorer)
        {
            _identityVerifier = identityVerifier ?? throw new ArgumentNullException(nameof(identityVerifier));
            _creditScorer = creditScorer ?? throw new ArgumentNullException(nameof(creditScorer));
        }

> dotnet add package Moq
> Install-Package Moq

using Loans.Entities;
using Loans.Services;
using Loans.Services.Contracts;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Moq;

namespace Loans.Tests
{
    [TestClass]
    public class LoanApplicationProcessorShould
    {
        [TestMethod]
        public void DeclineLowSalary()
        {
            var product = new LoanProduct {Id = 99, ProductName = "Loan", InterestRate = 5.25m};
            var amount = new LoanAmount {CurrencyCode = "Rial", Principal = 2_000_000_0};
            var applicant =
                new Applicant {Id = 1, Name = "User 1", Age = 25, Address = "This place", Salary = 1_100_000_0};
            var application = new LoanApplication {Id = 42, Product = product, Amount = amount, Applicant = applicant};

            var mockIdentityVerifier = new Mock<IIdentityVerifier>();
            var mockCreditScorer = new Mock<ICreditScorer>();

            var processor = new LoanApplicationProcessor(mockIdentityVerifier.Object, mockCreditScorer.Object);
            processor.Process(application);

            Assert.IsFalse(application.IsAccepted);
        }
    }
}

using Loans.Entities;
using Loans.Services;
using Loans.Services.Contracts;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Moq;

namespace Loans.Tests
{
    [TestClass]
    public class LoanApplicationProcessorShould
    {        
        [TestMethod]
        public void Accept()
        {
            var product = new LoanProduct {Id = 99, ProductName = "Loan", InterestRate = 5.25m};
            var amount = new LoanAmount {CurrencyCode = "Rial", Principal = 2_000_000_0};
            var applicant =
                new Applicant {Id = 1, Name = "User 1", Age = 25, Address = "This place", Salary = 1_500_000_0};
            var application = new LoanApplication {Id = 42, Product = product, Amount = amount, Applicant = applicant};

            var mockIdentityVerifier = new Mock<IIdentityVerifier>();
            var mockCreditScorer = new Mock<ICreditScorer>();

            var processor = new LoanApplicationProcessor(mockIdentityVerifier.Object, mockCreditScorer.Object);
            processor.Process(application);

            Assert.IsTrue(application.IsAccepted);
        }
    }
}

_identityVerifier.Initialize();
var isValidIdentity = _identityVerifier.Validate(
    application.Applicant.Name, application.Applicant.Age, application.Applicant.Address);

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

        public virtual ICollection<ProjectIssue> ProjectIssues { set; get; }
    }

    public class ProjectIssue
    {
        public int Id { set; get; }
        public string Body { set; get; }

        [ForeignKey("ProjectStatusId")]
        public virtual ProjectIssueStatus ProjectIssueStatus { set; get; }
        public int ProjectStatusId { set; get; }

        [ForeignKey("ProjectId")]
        public virtual Project Project { set; get; }
        public int ProjectId { set; get; }
    }

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

        public virtual ICollection<ProjectIssue> ProjectIssues { set; get; }
    }

    public class MyContext : DbContext
    {
        public DbSet<ProjectIssueStatus> ProjectStatus { get; set; }
        public DbSet<ProjectIssue> ProjectIssues { get; set; }
        public DbSet<Project> Projects { get; set; }
    }

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

        protected override void Seed(MyContext context)
        {
            var project1 = new Project { Name = "پروژه جدید" };
            context.Projects.Add(project1);

            var stat1 = new ProjectIssueStatus { Name = "درحال انجام" };
            var stat2 = new ProjectIssueStatus { Name = "انجام شد" };
            context.ProjectStatus.Add(stat1);
            context.ProjectStatus.Add(stat2);

            var issue1 = new ProjectIssue
            {
                Body = "تغییر قلم گزارش",
                ProjectIssueStatus = stat1,
                Project = project1
            };
            var issue2 = new ProjectIssue
            {
                Body = "تغییر لوگوی گزارش",
                ProjectIssueStatus = stat1,
                Project = project1
            };
            context.ProjectIssues.Add(issue1);
            context.ProjectIssues.Add(issue2);

            base.Seed(context);
        }
    }

    public static class Test
    {
        public static void RunTests()
        {
            Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyContext, Configuration>());

            using (var ctx = new MyContext())
            {
                var projectId = 1;
                var list = ctx.ProjectStatus.Select(x => new
                                                    {
                                                        Id = x.Id,
                                                        Name = x.Name,
                                                        Count = x.ProjectIssues.Count(p => p.ProjectId == projectId)
                                                    }).ToList();
                foreach (var item in list)
                    Console.WriteLine("{0}:{1}",item.Name, item.Count);                
            }
        }
    }

SELECT [Project1].[Id] AS [Id],
       [Project1].[Name] AS [Name],
       [Project1].[C1] AS [C1]
FROM   (
           SELECT [Extent1].[Id] AS [Id],
                  [Extent1].[Name] AS [Name],
                  (
                      SELECT COUNT(1) AS [A1]
                      FROM   [dbo].[ProjectIssues] AS [Extent2]
                      WHERE  ([Extent1].[Id] = [Extent2].[ProjectStatusId])
                             AND ([Extent2].[ProjectId] = 1 /*@p__linq__0*/)
                  ) AS [C1]
           FROM   [dbo].[ProjectIssueStatus] AS [Extent1]
       ) AS [Project1]

        public DateTime BirthDate { get; set; }
        public DateTime HireDate { get; set; }
        public DateTime? LeaveDate { get; set; }

از این جدول چه می‌فهمید؟ با یک نگاه می‌توان فهمید که ساختار بالای من باید 24 بایت گرفته باشد؛ برای ساختاری که هم تاریخ و هم زمان (ساعت) را پشتیبانی می‌کند. ولی با نگاه دقیق‌تر به نام پراپرتی‌ها این نکته روشن می‌شود که ما یک گپ Gap (فضای بیهوده) داریم چون زمان تولد، استخدام و ترک سازمان اصلا نیازی به ساعت ندارند و همان تاریخ کافی است. یعنی نوع Date با حجم کلی 9 بایت؛ که در نتیجه 15 بایت صرفه جویی در یک رکورد صورت خواهد گرفت.

پس کد بالا را به شکل زیر تغییر می‌دهم:

          [Column(TypeName = "date")]
        public DateTime BirthDate { get; set; }

        [Column(TypeName = "date")]
        public DateTime HireDate { get; set; }

        [Column(TypeName = "date")]
        public DateTime? LeaveDate { get; set; }

خصوصیت Column از نسخه 4.5دات نت فریم ورک اضافه شده و در فضای نام System.ComponentModel.DataAnnotations.Schema قرار گرفته است.

public class ProductViewModel
{
    Public int ProductId {get; set;}
    public string Name { get; set; }
    public string Rrp { get; set; }
    public string SellingPrice { get; set; }
    public string Discount { get; set; }
    public string Savings { get; set; }
}

using SoCPatterns.Layered.Model;

namespace SoCPatterns.Layered.Service
{
    public class ProductListRequest
    {
        public CustomerType CustomerType { get; set; }
    }
}

public class ProductListResponse
{
    public bool Success { get; set; }
    public string Message { get; set; }
    public IList<ProductViewModel> Products { get; set; }
}

public static class ProductMapperExtensionMethods
{
    public static ProductViewModel ConvertToProductViewModel(this Model.Product product)
    {
        ProductViewModel productViewModel = new ProductViewModel();
        productViewModel.ProductId = product.Id;
        productViewModel.Name = product.Name;
        productViewModel.RRP = String.Format(“{0:C}”, product.Price.RRP);
        productViewModel.SellingPrice = String.Format(“{0:C}”, product.Price.SellingPrice);
        if (product.Price.Discount > 0)
            productViewModel.Discount = String.Format(“{0:C}”, product.Price.Discount);
        if (product.Price.Savings < 1 && product.Price.Savings > 0)
            productViewModel.Savings = product.Price.Savings.ToString(“#%”);
        return productViewModel;
    }
    public static IList<ProductViewModel> ConvertToProductListViewModel(
        this IList<Model.Product> products)
    {
        IList<ProductViewModel> productViewModels = new List<ProductViewModel>();
        foreach(Model.Product p in products)
        {
            productViewModels.Add(p.ConvertToProductViewModel());
        }
        return productViewModels;
    }
}

public class ProductService
{
    private Model.ProductService _productService;
    public ProductService(Model.ProductService ProductService)
    {
        _productService = ProductService;
    }
    public ProductListResponse GetAllProductsFor(
        ProductListRequest productListRequest)
    {
        ProductListResponse productListResponse = new ProductListResponse();
        try
        {
            IList<Model.Product> productEntities =
                _productService.GetAllProductsFor(productListRequest.CustomerType);
            productListResponse.Products = productEntities.ConvertToProductListViewModel();
            productListResponse.Success = true;
        }
        catch (Exception ex)
        {
            // Log the exception…
            productListResponse.Success = false;
            // Return a friendly error message
            productListResponse.Message = ex.Message;
        }
        return productListResponse;
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SoCPatterns.Layered.Model;

namespace SoCPatterns.Layered.Repository
{
    public class ProductRepository : IProductRepository
    {
        public IList<Model.Product> FindAll()
        {
            var products = from p in new ShopDataContext().Products
                                select new Model.Product
                                {
                                    Id = p.ProductId,
                                    Name = p.ProductName,
                                    Price = new Model.Price(p.Rrp, p.SellingPrice)
                                };
            return products.ToList();
        }
    }
}

using SoCPatterns.Layered.Service;

public interface IProductListView
{
    void Display(IList<ProductViewModel> Products);
    Model.CustomerType CustomerType { get; }
    string ErrorMessage { set; }
}

using SoCPatterns.Layered.Service;

namespace SoCPatterns.Layered.Presentation
{
    public class ProductListPresenter
    {
        private IProductListView _productListView;
        private Service.ProductService _productService;
        public ProductListPresenter(IProductListView ProductListView,
            Service.ProductService ProductService)
        {
            _productService = ProductService;
            _productListView = ProductListView;
        }
        public void Display()
        {
            ProductListRequest productListRequest = new ProductListRequest();
            productListRequest.CustomerType = _productListView.CustomerType;
            ProductListResponse productResponse =
                _productService.GetAllProductsFor(productListRequest);
            if (productResponse.Success)
            {
                _productListView.Display(productResponse.Products);
            }
            else
            {
                _productListView.ErrorMessage = productResponse.Message;
            }
        }
    }
}