USE [master]
GO
ALTER DATABASE [testdb2] 
      ADD FILEGROUP [InMemory_InMemory] CONTAINS MEMORY_OPTIMIZED_DATA 
GO
ALTER DATABASE [testdb2] 
      ADD FILE ( NAME = N'InMemory_InMemory', FILENAME = N'D:\SQL_Data\MSSQL11.MSSQLSERVER\MSSQL\DATA\InMemory_InMemory' ) 
      TO FILEGROUP [InMemory_InMemory]
GO

 Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables.

 bit, tinyint, smallint, int, bigint, money, smallmoney, float, real, datetime, smalldatetime, datetime2,
date, time, numberic, decimal, char(n),  varchar(n) ,nchar(n),  nvarchar(n), sysname, binary(n),
varbinary(n), and Uniqueidentifier

set statistics time off
SET STATISTICS IO Off
set nocount on
go
-----------------------------

Print 'insert into tblNormal'

DECLARE @start datetime = getdate()
declare @insertCount int = 100000
declare @startId int = 1
declare @customerID int = @startId

while @customerID < @startId + @insertCount
begin
    insert into tblNormal values (@customerID, 'Test', '2013-01-01T00:00:00')
    set @customerID +=1
end

Print DATEDIFF(ms,@start,getdate());
go
-----------------------------

Print 'insert into tblMemoryOptimized_Schema_And_Data'

DECLARE @start datetime = getdate()
declare @insertCount int = 100000
declare @startId int = 1
declare @customerID int = @startId

while @customerID < @startId + @insertCount
begin
    insert into tblMemoryOptimized_Schema_And_Data values (@customerID, 'Test', '2013-01-01T00:00:00')
    set @customerID +=1
end
Print DATEDIFF(ms,@start,getdate());
Go
-----------------------------

Print 'insert into tblMemoryOptimized_Schema_Only'

DECLARE @start datetime = getdate()
declare @insertCount int = 100000
declare @startId int = 1
declare @customerID int = @startId

while @customerID < @startId + @insertCount
begin
    insert into tblMemoryOptimized_Schema_Only values (@customerID, 'Test', '2013-01-01T00:00:00')
    set @customerID +=1
end
Print DATEDIFF(ms,@start,getdate());

Go

 insert into tblNormal
36423

insert into tblMemoryOptimized_Schema_And_Data
30516

insert into tblMemoryOptimized_Schema_Only
3176

 set nocount on
print 'tblNormal'
set statistics time on
select count(CustomerID) from tblNormal
set statistics time off
go
print 'tblMemoryOptimized_Schema_And_Data'
set statistics time on
select count(CustomerID) from tblMemoryOptimized_Schema_And_Data
set statistics time off
go
print 'tblMemoryOptimized_Schema_Only'
set statistics time on
select count(CustomerID) from tblMemoryOptimized_Schema_Only
set statistics time off
go

 tblNormal
 SQL Server Execution Times:
CPU time = 46 ms,  elapsed time = 52 ms.

tblMemoryOptimized_Schema_And_Data
 SQL Server Execution Times:
CPU time = 32 ms,  elapsed time = 33 ms.

tblMemoryOptimized_Schema_Only
 SQL Server Execution Times:
CPU time = 31 ms,  elapsed time = 30 ms.

• لازم است در آرگومان اول حداقل یک % وجود داشته باشد.
  
• حداکثر تعداد کاراکترهایی را که می‌توان در آرگومان اول قرار داد 8000 میباشد.

USE tempdb
GO
CREATE TABLE UsingPATINDEX (ID INT, Words VARCHAR (100))
GO
INSERT INTO UsingPATINDEX (ID, Words)
SELECT 1, '1one'
UNION ALL
SELECT 2, '11eleven'
UNION ALL
SELECT 3, '2two'
UNION ALL
SELECT 4, '22twentytwo'
UNION ALL
SELECT 5, '111oneeleven'
GO

SELECT PATINDEX('%[a-z]%',Words) 'مکان اولین کاراکتر غیر عددی',
SUBSTRING(Words,PATINDEX('%[a-z]%',Words),1) 'نام اولین کاراکتر غیر عددی بعد از عدد',
Words 'متن اصلی'
FROM  UsingPATINDEX

PATINDEX('%[a-z]%',Words) 'مکان اولین کاراکتر غیر عددی'

SUBSTRING(Words,PATINDEX('%[a-z]%',Words),1) 'نام اولین کاراکتر غیر عددی بعد از عدد'

• در تابع PATINDEX می‌توانید براساس Collation دلخواه عملیات جستجو را انجام دهید، برای روش‌تر شدن مطلب شکل زیر را مشاهده نمایید:

• باید متذکر شوم، با دیدن دو مثال آخر،این تصور ایجاد نشود که تابع PATINDEX شبیه به تابع LIKE می‌باشد، چرا که تابع PATINDEX موقعیت کاراکتر را بر می‌گرداند، نه خود کاراکتر را،عملکرد تابع PATINDEX شبیه به عملکرد تابع CHARINDEX می‌باشد.
  

    /// <summary>
    /// حساب بانکی
    /// </summary>
    public class Account
    {
        /// <summary>
        /// مشتری
        /// </summary>
        public string Customer { get; set; }
        /// <summary>
        /// موجودی حساب
        /// </summary>
        public float Balance { get; set; }
        /// <summary>
        /// وضعیت
        /// </summary>
        public bool Active { get; set; }

        public Account(string customer, float balance)
        {
            Customer = customer;
            Balance = balance;
            Active = true;
        }
        /// <summary>
        /// افزایش موجودی / واریز به حساب
        /// </summary>
        /// <param name="amount">مبلغ واریز</param>
        public void Credit(float amount)
        {
            if (!Active)
                throw new Exception("این حساب مسدود است.");
            if (amount < 0)
                throw new ArgumentOutOfRangeException("amount");
            Balance += amount;
        }
        /// <summary>
        /// کاهش موجودی / برداشت از حساب
        /// </summary>
        /// <param name="amount">مبلغ برداشت</param>
        public void Debit(float amount)
        {
            if (!Active)
                throw new Exception("این حساب مسدود است.");
            if (amount < 0)
                throw new ArgumentOutOfRangeException("amount");
            if (Balance < amount)
                throw new ArgumentOutOfRangeException("amount");
            Balance -= amount;
        }
        /// <summary>
        /// انسداد / رفع انسداد
        /// </summary>
        public void ChangeStateAccount()
        {
            Active = !Active;
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var account = new Account("Ali",1000);

            account.Credit(4000);
            account.Debit(2000);
            Console.WriteLine("Current balance is ${0}", account.Balance);
            Console.ReadKey();
        }
    }

    [TestClass]
    public class UnitTest
    {
        /// <summary>
        /// تعریف حساب جدید و بررسی تمامی فرآیند‌های معمول روی حساب
        /// </summary>
        [TestMethod]
        public void Create_New_Account_And_Check_The_Process()
        {
            //Arrange
            var account = new Account("Hassan", 4000);
            var account2 = new Account("Ali", 10000);
            //Act
            account.Credit(5000);
            account2.Debit(3000);
            account.ChangeStateAccount();
            account2.Active = false;
            account2.ChangeStateAccount();
            //Assert
            Assert.AreEqual(account.Balance,9000);
            Assert.AreEqual(account2.Balance,7000);
            Assert.IsTrue(account2.Active);
            Assert.AreEqual(account.Active,false);
        }

        /// <summary>
        /// زمانی که کاربر بخواهد به یک حساب مسدود واریز کند باید جلوی آن گرفته شود.
        /// </summary>
        [TestMethod]
        [ExpectedException(typeof (Exception))]
        public void When_Deactive_Account_Wants_To_add_Credit_Should_Throw_Exception()
        {
            //Arrange
            var account = new Account("Hassan", 4000) {Active = false};
            //Act
            account.Credit(4000);
            //Assert
            //Assert is handled with ExpectedException
        }

        [TestMethod]
        [ExpectedException(typeof (ArgumentOutOfRangeException))]
        public void When_Customer_Wants_To_Debit_More_Than_Balance_Should_Throw_ArgumentOutOfRangeException()
        {
            //Arrange
            var account = new Account("Hassan", 4000);
            //Act
            account.Debit(5000);
            //Assert
            //Assert is handled with ArgumentOutOfRangeException
        }

  public class OrganizationalService : ICommandHandler<CreateUnitTypeCommand>,
                                         ICommandHandler<DeleteUnitTypeCommand>,                                    
    {
        private readonly IUnitOfWork _unitOfWork;
        private readonly IUnitTypeRepository _unitTypeRepository;
        private readonly IOrganizationUnitRepository _organizationUnitRepository;
        private readonly IOrganizationUnitDomainService _organizationUnitDomainService;

        public OrganizationalService(IUnitOfWork unitOfWork, IUnitTypeRepository unitTypeRepository, IOrganizationUnitRepository organizationUnitRepository, IOrganizationUnitDomainService organizationUnitDomainService)
        {
            _unitOfWork = unitOfWork;
            _unitTypeRepository = unitTypeRepository;
            _organizationUnitRepository = organizationUnitRepository;
            _organizationUnitDomainService = organizationUnitDomainService;
        }

    [TestClass]
    public class OrganizationServiceTest
    {
        private static OrganizationalService _organizationalService;
        private static Mock<IUnitTypeRepository> _mockUnitTypeRepository;
        private static Mock<IUnitOfWork> _mockUnitOfWork;
        private static Mock<IOrganizationUnitRepository> _mockOrganizationUnitRepository;
        private static Mock<IOrganizationUnitDomainService> _mockOrganizationUnitDomainService;

        [ClassInitialize]
        public static void ClassInit(TestContext context)
        {
            TestBootstrapper.ConfigureDependencies();
            _mockUnitOfWork = new Mock<IUnitOfWork>();
            _mockUnitTypeRepository = new Mock<IUnitTypeRepository>();
            _mockOrganizationUnitRepository = new Mock<IOrganizationUnitRepository>();
            _mockOrganizationUnitDomainService=new Mock<IOrganizationUnitDomainService>();
            _organizationalService = new OrganizationalService(_mockUnitOfWork.Object, _mockUnitTypeRepository.Object,  _mockOrganizationUnitRepository.Object,_mockOrganizationUnitDomainService.Object);
        }

        /// <summary>
        /// یک نوع واحد سازمانی را حذف مینماید
        /// </summary>
        /// <param name="command"></param>
        public void Handle(DeleteUnitTypeCommand command)
        {
            var unitType = _unitTypeRepository.FindBy(command.UnitTypeId);
            if (unitType == null)
                throw new DeleteEntityNotFoundException();

            ICanDeleteUnitTypeSpecification canDeleteUnitType = new CanDeleteUnitTypeSpecification(_organizationUnitRepository);
            if (canDeleteUnitType.IsSatisfiedBy(unitType))
                throw new UnitTypeIsUnderUsingException(unitType.Title);
            _unitTypeRepository.Remove(unitType);
        }

        /// <summary>
        /// کامند حذف نوع واحد سازمانی باید به درستی حذف کند.
        /// </summary>
        [TestMethod]
        public void DeleteUnitTypeCommand_Should_Delete_UnitType()
        {
            //Arrange
            var unitTypeId=new Guid();
            var deleteUnitTypeCommand = new DeleteUnitTypeCommand { UnitTypeId = unitTypeId };
            var unitType = new UnitType("خوشه");
            var org = new List<OrganizationUnit>();
            _mockUnitTypeRepository.Setup(d => d.FindBy(deleteUnitTypeCommand.UnitTypeId)).Returns(unitType);
            _mockUnitTypeRepository.Setup(x => x.Remove(unitType));
            _mockOrganizationUnitRepository.Setup(z => z.FindBy(unitType)).Returns(org);
            try
            {
                //Act
                _organizationalService.Handle(deleteUnitTypeCommand);
            }
            catch (Exception ex)
            {
                //Assert
                Assert.Fail(ex.Message);
            }
        }

        /// <summary>
        /// کامند حذف یک نوع واحد سازمانی باید پیش از حذف بررسی کند که این شناسه داده شده برای حذف موجود باشد.
        /// </summary>
        [TestMethod]
        [ExpectedException(typeof(DeleteEntityNotFoundException))]
        public void DeleteUnitTypeCommand_ShouldNot_Delete_When_UnitTypeId_NotExist()
        {
            //Arrange
            var unitTypeId = new Guid();
            var deleteUnitTypeCommand = new DeleteUnitTypeCommand();
            var unitType = new UnitType("خوشه");
            var org = new List<OrganizationUnit>();
            _mockUnitTypeRepository.Setup(d => d.FindBy(unitTypeId)).Returns(unitType);
            _mockUnitTypeRepository.Setup(x => x.Remove(unitType));
            _mockOrganizationUnitRepository.Setup(z => z.FindBy(unitType)).Returns(org);

            //Act
            _organizationalService.Handle(deleteUnitTypeCommand);
        }

        /// <summary>
        /// کامند حذف یک نوع واحد سازمانی نباید اجرا شود وقتی که نوع واحد برای تعریف واحد‌های سازمان استفاده شده است.
        /// </summary>
        [TestMethod]
        [ExpectedException(typeof(UnitTypeIsUnderUsingException))]
        public void DeleteUnitTypeCommand_ShouldNot_Delete_When_UnitType_Exist_but_UsedForDefineOrganizationUnit()
        {
            //Arrange
            var unitTypeId = new Guid();
            var deleteUnitTypeCommand = new DeleteUnitTypeCommand { UnitTypeId = unitTypeId };
            var unitType = new UnitType("خوشه");
            var org = new List<OrganizationUnit>()
            {
                new OrganizationUnit("مدیریت یک", unitType, null),
                new OrganizationUnit("مدیریت دو", unitType, null)
            };
            _mockUnitTypeRepository.Setup(d => d.FindBy(deleteUnitTypeCommand.UnitTypeId)).Returns(unitType);
            _mockUnitTypeRepository.Setup(x => x.Remove(unitType));
            _mockOrganizationUnitRepository.Setup(z => z.FindBy(unitType)).Returns(org);

            //Act
            _organizationalService.Handle(deleteUnitTypeCommand);
        }

 SELECT sum([Rating_TotalRating]), sum([Rating_TotalRaters]), sum([Rating_AverageRating]) FROM [BlogPosts]

                context.BlogPost.Select(r =>
                                        new
                                        {
                                            Sum1 = r.Sum(x => x.RatingTotalRating),
                                            Sum2 = r.Sum(x => x.RatingTotalRaters),
                                            Sum3 = r.Sum(x => x.RatingAverageRating)
                                        }).FirstOrDefault();

 var sum1 = context.BlogPost.Sum(x => x.RatingTotalRating);
var sum2 = context.BlogPost.Sum(x => x.RatingTotalRaters);
var sum2 = context.BlogPost.Sum(x => x.RatingAverageRating);

                context.BlogPost
                       .GroupBy(dummyNumber => 0)
                       .Select(r =>
                                        new
                                        {
                                            Sum1 = r.Sum(x => x.RatingTotalRating),
                                            Sum2 = r.Sum(x => x.RatingTotalRaters),
                                            Sum3 = r.Sum(x => x.RatingAverageRating)
                                        }).FirstOrDefault();

SELECT TOP (1) 
                        [Extent1].[K1] AS [K1], 
                        Sum([Extent1].[A1]) AS [A1], 
                        Sum([Extent1].[A2]) AS [A2],
                        Sum([Extent1].[A3]) AS [A3]
                        FROM ( SELECT 
                            0 AS [K1], 
                            [Extent1].[RatingTotalRating] AS [A1], 
                            [Extent1].[RatingTotalRaters] AS [A2],
       [Extent1].[RatingAverageRating] AS [A3]
                            FROM [dbo].[BlogPosts] AS [Extent1]
                        )  AS [Extent1]
                        GROUP BY [K1]

public class Order
{
    public string CustomerId { get; set; }
    public LineItem[] LineItems { get; set; }
    public double TotalPrice { get; set; }
}
 
public class Customer
{
    public string Name { get; set; }
    public string Address { get; set; }
    public short Age { get; set; }
    public string HashedPassword { get; set; }
}

var order = session.Include<Order>(x => x.CustomerId)
                   .Load("orders/1234");
 
// این کوئری از کش سشن خوانده می‌شود و کاری به سرور ندارد
var cust = session.Load<Customer>(order.CustomerId);

var orders = session.Include<Order>(x => x.CustomerId)
    .Load("orders/1234", "orders/4321");
 
foreach (var order in orders)
{
    // این کوئری‌ها سمت کلاینت هستند و به سرور ارسال نمی‌شوند
    var cust = session.Load<Customer>(order.CustomerId);
}

var orders = session.Query<Order>()
    .Customize(x => x.Include<Order>(o => o.CustomerId))
    .Where(x => x.TotalPrice > 100)
    .ToList();
 
foreach (var order in orders)
{
    // این کوئری‌ها سمت کلاینت اجرا می‌شوند
    var cust = session.Load<Customer>(order.CustomerId);
}

public class Order
{
    public string CustomerId { get; set; }
    public string[] SupplierIds { get; set; }
    public LineItem[] LineItems { get; set; }
    public double TotalPrice { get; set; }
}

var orders = session.Include<Order>(x => x.SupplierIds)
    .Load("orders/1234", "orders/4321");
 
foreach (var order in orders)
{
    foreach (var supplierId in order.SupplierIds)
    {
        // از کش سشن خوانده می‌شود
        var supp = session.Load<Supplier>(supplierId);
    }
}

public class Order
{
    public string CustomerId { get; set; }
    public string[] SupplierIds { get; set; }
    public Referral Refferal { get; set; }
    public LineItem[] LineItems { get; set; }
    public double TotalPrice { get; set; }
}

public class Referral
{
    public string CustomerId { get; set; }
    public double CommissionPercentage { get; set; }
}

var order = session.Include<Order>(x => x.Refferal.CustomerId)
    .Load("orders/1234");
 
// از کش سشن خوانده می‌شود
var referrer = session.Load<Customer>(order.Refferal.CustomerId);

public class LineItem
{
    public string ProductId { get; set; }
    public string Name { get; set; }
    public int Quantity { get; set; }
    public double Price { get; set; }
}

var order = session.Include<Order>(x => x.LineItems.Select(li => li.ProductId))
    .Load("orders/1234");
 
foreach (var lineItem in order.LineItems)
{
    // از کش سمت کلاینت خوانده می‌شود
    var product = session.Load<Product>(lineItem.ProductId);
}

var order = session.Load<Order>("orders/1");
var customer = session.Load<Customer>(order.CustomerId);

 <div>
                              <div>
                                  <div>
                                      <p><span>First Name </span>: Jonathan</p>
                                  </div>
   </div>
                          </div>

@model System.Web.UI.WebControls.ListItem


<div>
    <p><span>@Model.Text </span>: @Model.Value</p>
</div>

@using System.Web.UI.WebControls
@using ZekrWepApp.Filters
@model ZekrModel.Admin

    <div>
        <h1>Bio Graph</h1>
        <div>
            
            @{
                ListItemCollection collection = GetCustomProperties.Get(Model,exclude:new string[]{"Poems","Id"});
                foreach (var item in collection)
                {
                    Html.RenderPartial(MVC.Shared.Views._BioRow, item);
                }
            }

                    </div>
    </div>

  public class GetCustomProperties
    {
        private static PropertyInfo[] getObjectsInfos(object obj,string[] inclue,string[] exclude )
        {
            var list = obj.GetType().GetProperties();

            PropertyInfo[] outputPropertyInfos = null;

            if (inclue != null)
            {           
                return list.Where(propertyInfo => inclue.Contains(propertyInfo.Name)).ToArray();
            }
            if (exclude != null)
            {
                return list.Where(propertyInfo => !exclude.Contains(propertyInfo.Name)).ToArray();
            }
            return list;
        }
    }

 public static ListItemCollection Get(object obj,string[] inclue=null,string[] exclude=null)
        {
            var propertyInfos = getObjectsInfos(obj, inclue, exclude);
            if (propertyInfos == null) throw new ArgumentNullException("propertyInfos is null");

            var collection = new ListItemCollection();

            foreach (PropertyInfo propertyInfo in propertyInfos)
            {


                string name = propertyInfo.Name;

                foreach (Attribute attribute in propertyInfo.GetCustomAttributes(true))
                {
                    DisplayAttribute displayAttribute = attribute as DisplayAttribute;

                    if (displayAttribute != null)
                    {
                        name = displayAttribute.Name;
                        break;
                    }                  
                }

                string value = "";
                object objvalue = propertyInfo.GetValue(obj);
                if (objvalue != null) value = objvalue.ToString();

                collection.Add(new ListItem(name,value));
            }
            return collection;
        }

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Web;
using System.Web.Mvc.Html;
using System.Web.UI.WebControls;
using Links;

namespace ZekrWepApp.Filters
{
    
    public class GetCustomProperties
    {
        public static ListItemCollection Get(object obj,string[] inclue=null,string[] exclude=null)
        {
            var propertyInfos = getObjectsInfos(obj, inclue, exclude);
            if (propertyInfos == null) throw new ArgumentNullException("propertyInfos is null");

            var collection = new ListItemCollection();

            foreach (PropertyInfo propertyInfo in propertyInfos)
            {
                string name = propertyInfo.Name;
                foreach (Attribute attribute in propertyInfo.GetCustomAttributes(true))
                {
                    DisplayAttribute displayAttribute = attribute as DisplayAttribute;

                    if (displayAttribute != null)
                    {
                        name = displayAttribute.Name;
                        break;
                    }                  
                }

                string value = "";
                object objvalue = propertyInfo.GetValue(obj);
                if (objvalue != null) value = objvalue.ToString();

                collection.Add(new ListItem(name,value));
            }
            return collection;
        }
        private static PropertyInfo[] getObjectsInfos(object obj,string[] include,string[] exclude )
        {
            var list = obj.GetType().GetProperties();

            PropertyInfo[] outputPropertyInfos = null;

            if (include != null)
            {           
                return list.Where(propertyInfo => include.Contains(propertyInfo.Name)).ToArray();
            }
            if (exclude != null)
            {
                return list.Where(propertyInfo => !exclude.Contains(propertyInfo.Name)).ToArray();
            }
            return list;
        }  
    }   
}

amount=1000&orderId=452&Pid=xxx&....

using System;
using System.Collections.Generic;
using System.Linq;

namespace Utils
{
    public class QueryStringParametersList
    {
        private string Symbol = "&";
        private List<KeyValuePair<string, string>> list { get; set; }

        public QueryStringParametersList()
        {
            list = new List<KeyValuePair<string, string>>();
        }
        public QueryStringParametersList(string symbol)
        {
            Symbol = symbol;
           list = new List<KeyValuePair<string, string>>(); 
        }

        public int Size
        {
            get { return list.Count; }
        }
        public void Add(string key, string value)
        {
            list.Add(new KeyValuePair<string, string>(key, value));
        }

        public string GetQueryStringPostfix()
        {
            return string.Join(Symbol, list.Select(p => Uri.EscapeDataString(p.Key) + "=" + Uri.EscapeDataString(p.Value)));
        }
    }
}

QueryStringParametersList queryparamsList = new QueryStringParametersList();

            ueryparamsList.Add("consumer_key", requestPayment.Consumer_Key);
            queryparamsList.Add("amount", requestPayment.Amount.ToString());
            queryparamsList.Add("callback", requestPayment.Callback);
            queryparamsList.Add("description", requestPayment.Description);
            queryparamsList.Add("email", requestPayment.Email);
            queryparamsList.Add("mobile", requestPayment.Mobile);
            queryparamsList.Add("name", requestPayment.Name);
            queryparamsList.Add("irderid", requestPayment.OrderId.ToString());

   private QueryStringParametersList ReadParams(object obj)
        {
            PropertyInfo[] propertyInfos = obj.GetType().GetProperties();

            QueryStringParametersList queryparamsList = new QueryStringParametersList();
            for (int i = 0; i < propertyInfos.Count(); i++)
            {
                queryparamsList.Add(propertyInfos[i].Name.ToLower(),propertyInfos[i].GetValue(obj).ToString() );              
            }
            return queryparamsList;
        }

• آشنایی با Code first migrations : قسمت اول و دوم
• وادار کردن EF Code first به ساخت بانک اطلاعاتی پیش از شروع به کار برنامه
• بررسی خطاهای متداول عملیات Migration در حین به روز رسانی پروژه‌های EF Code First
• بازسازی جدول MigrationHistory با کد نویسی در EF Code first