internal class Program
{
    private HttpClient _client;
    private Customer _bush, _obama;
    private Phone _whiteHousePhone, _bushMobilePhone, _obamaMobilePhone;
    private HttpResponseMessage _response;

    private static void Main()
    {
        Task t = Run();
        t.Wait();
        Console.WriteLine("\nPress <enter> to continue...");
        Console.ReadLine();
    }

    private static async Task Run()
    {
        var program = new Program();
        program.ServiceSetup();
        // do not proceed until clean-up completes
        await program.CleanupAsync();
        program.CreateFirstCustomer();
        // do not proceed until customer is added
        await program.AddCustomerAsync();
        program.CreateSecondCustomer();
        // do not proceed until customer is added
        await program.AddSecondCustomerAsync();
        // do not proceed until customer is removed
        await program.RemoveFirstCustomerAsync();
        // do not proceed until customers are fetched
        await program.FetchCustomersAsync();
    }

    private void ServiceSetup()
    {
        // set up infrastructure for Web API call
        _client = new HttpClient { BaseAddress = new Uri("http://localhost:62799/") };
        // add Accept Header to request Web API content negotiation to return resource in JSON format
        _client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue
        ("application/json"));
    }
    private async Task CleanupAsync()
    {
        // call the cleanup method from the service
        _response = await _client.DeleteAsync("api/customer/cleanup/");
    }

    private void CreateFirstCustomer()
    {
        // create customer #1 and two phone numbers
        _bush = new Customer
        {
            Name = "George Bush",
            Company = "Ex President",
            // set tracking state to 'Add' to generate a SQL Insert statement
            TrackingState = TrackingState.Add,
        };
        _whiteHousePhone = new Phone
        {
            Number = "212 222-2222",
            PhoneType = "White House Red Phone",
            // set tracking state to 'Add' to generate a SQL Insert statement
            TrackingState = TrackingState.Add,
        };
        _bushMobilePhone = new Phone
        {
            Number = "212 333-3333",
            PhoneType = "Bush Mobile Phone",
            // set tracking state to 'Add' to generate a SQL Insert statement
            TrackingState = TrackingState.Add,
        };
        _bush.Phones.Add(_whiteHousePhone);
        _bush.Phones.Add(_bushMobilePhone);
    }

    private async Task AddCustomerAsync()
    {
        // construct call to invoke UpdateCustomer action method in Web API service
        _response = await _client.PostAsync("api/customer/updatecustomer/", _bush, new JsonMediaTypeFormatter());
        if (_response.IsSuccessStatusCode)
        {
            // capture newly created customer entity from service, which will include
            // database-generated Ids for all entities
            _bush = await _response.Content.ReadAsAsync<Customer>();
            _whiteHousePhone = _bush.Phones.FirstOrDefault(x => x.CustomerId == _bush.CustomerId);
            _bushMobilePhone = _bush.Phones.FirstOrDefault(x => x.CustomerId == _bush.CustomerId);
            Console.WriteLine("Successfully created Customer {0} and {1} Phone Numbers(s)",
            _bush.Name, _bush.Phones.Count);
            foreach (var phoneType in _bush.Phones)
            {
                Console.WriteLine("Added Phone Type: {0}", phoneType.PhoneType);
            }
        }
        else
            Console.WriteLine("{0} ({1})", (int)_response.StatusCode, _response.ReasonPhrase);
    }

    private void CreateSecondCustomer()
    {
        // create customer #2 and phone numbers
        _obama = new Customer
        {
            Name = "Barack Obama",
            Company = "President",
            // set tracking state to 'Add' to generate a SQL Insert statement
            TrackingState = TrackingState.Add,
        };
        _obamaMobilePhone = new Phone
        {
            Number = "212 444-4444",
            PhoneType = "Obama Mobile Phone",
            // set tracking state to 'Add' to generate a SQL Insert statement
            TrackingState = TrackingState.Add,
        };
        // set tracking state to 'Modifed' to generate a SQL Update statement
        _whiteHousePhone.TrackingState = TrackingState.Update;
        _obama.Phones.Add(_obamaMobilePhone);
        _obama.Phones.Add(_whiteHousePhone);
    }

    private async Task AddSecondCustomerAsync()
    {
        // construct call to invoke UpdateCustomer action method in Web API service
        _response = await _client.PostAsync("api/customer/updatecustomer/", _obama, new JsonMediaTypeFormatter());
        if (_response.IsSuccessStatusCode)
        {
            // capture newly created customer entity from service, which will include
            // database-generated Ids for all entities
            _obama = await _response.Content.ReadAsAsync<Customer>();
            _whiteHousePhone = _bush.Phones.FirstOrDefault(x => x.CustomerId == _obama.CustomerId);
            _bushMobilePhone = _bush.Phones.FirstOrDefault(x => x.CustomerId == _obama.CustomerId);
            Console.WriteLine("Successfully created Customer {0} and {1} Phone Numbers(s)",
            _obama.Name, _obama.Phones.Count);
            foreach (var phoneType in _obama.Phones)
            {
                Console.WriteLine("Added Phone Type: {0}", phoneType.PhoneType);
            }
        }
        else
            Console.WriteLine("{0} ({1})", (int)_response.StatusCode, _response.ReasonPhrase);
    }

    private async Task RemoveFirstCustomerAsync()
    {
        // remove George Bush from underlying data store.
        // first, fetch George Bush entity, demonstrating a call to the
        // get action method on the service while passing a parameter
        var query = "api/customer/" + _bush.CustomerId;
        _response = _client.GetAsync(query).Result;

        if (_response.IsSuccessStatusCode)
        {
            _bush = await _response.Content.ReadAsAsync<Customer>();
            // set tracking state to 'Remove' to generate a SQL Delete statement
            _bush.TrackingState = TrackingState.Remove;
            // must also remove bush's mobile number -- must delete child before removing parent
            foreach (var phoneType in _bush.Phones)
            {
                // set tracking state to 'Remove' to generate a SQL Delete statement
                phoneType.TrackingState = TrackingState.Remove;
            }
            // construct call to remove Bush from underlying database table
            _response = await _client.PostAsync("api/customer/updatecustomer/", _bush, new JsonMediaTypeFormatter());
            if (_response.IsSuccessStatusCode)
            {
                Console.WriteLine("Removed {0} from database", _bush.Name);
                foreach (var phoneType in _bush.Phones)
                {
                    Console.WriteLine("Remove {0} from data store", phoneType.PhoneType);
                }
            }
            else
                Console.WriteLine("{0} ({1})", (int)_response.StatusCode, _response.ReasonPhrase);
        }
        else
        {
            Console.WriteLine("{0} ({1})", (int)_response.StatusCode, _response.ReasonPhrase);
        }
    }

    private async Task FetchCustomersAsync()
    {
        // finally, return remaining customers from underlying data store
        _response = await _client.GetAsync("api/customer/");
        if (_response.IsSuccessStatusCode)
        {
            var customers = await _response.Content.ReadAsAsync<IEnumerable<Customer>>();
            foreach (var customer in customers)
            {
                Console.WriteLine("Customer {0} has {1} Phone Numbers(s)",
                customer.Name, customer.Phones.Count());
                foreach (var phoneType in customer.Phones)
                {
                    Console.WriteLine("Phone Type: {0}", phoneType.PhoneType);
                }
            }
        }
        else
        {
            Console.WriteLine("{0} ({1})", (int)_response.StatusCode, _response.ReasonPhrase);
        }
    }
}

    public class ProductModel
    {
        public int Id { get; set; }

        [Display(Name = "نام کالا")]
        [Required(ErrorMessage = "{0} یک فیلد اجباری است و باید آن را وارد کنید.")]
        [StringLength(50, ErrorMessage = "طول {0} باید کمتر از {1} کاراکتر باشد.")]
        public string Name { get; set; }

        [Display(Name = "قیمت")]
        [Required(ErrorMessage = "{0} یک فیلد اجباری است و باید آن را وارد کنید.")]
        [DataType(DataType.Currency)]
        public double Price { get; set; }

        [Display(Name = "بارکد")]
        [Required(ErrorMessage = "{0} یک فیلد اجباری است و باید آن را وارد کنید.")]
        [StringLength(50, ErrorMessage = "طول {0} باید کمتر از {1} کاراکتر باشد.")]
        [Remote("IsProductExist", "Product", HttpMethod = "POST", ErrorMessage = "این بارکد از قبل در سیستم وجود دارد.")]
        public string Barcode { get; set; }
    }

        [HttpPost]
        [OutputCache(Location = OutputCacheLocation.None, NoStore = true)]
        public ActionResult IsProductExist(string barcode)
        {
            if (barcode == "123456789") return Json(false); // اگر محصول وجود داشت
            return Json(true);
        }

@Html.AntiForgeryToken()

//Post in Ajax
services.AddAntiforgery(o => o.HeaderName = "XSRF-TOKEN");

function initDataTables() {
        table.destroy();
        table = $("#tblJs").DataTable({
          processing: true,
          serverSide: true,
          filter: true,
          ajax: {
            url: '@Url.Page("yourPage","yourHandler")',
            beforeSend: function (xhr) {
              xhr.setRequestHeader("XSRF-TOKEN",
                $('input:hidden[name="__RequestVerificationToken"]').val());
            },
            type: "POST",
            datatype: "json"
          },
          language: {
            url: "/Persian.json"
          },
          responsive: true,
          select: true,

          columns: scheme,
          select: true,
        });
      }

processing: true,
          serverSide: true,
          filter: true,
          ajax: {
            url: '@Url.Page("yourPage","yourHandler  ")',
            beforeSend: function (xhr) {
              xhr.setRequestHeader("XSRF-TOKEN",
                $('input:hidden[name="__RequestVerificationToken"]').val());
            },
            type: "POST",
            datatype: "json"
          },

public class FiltersFromRequestDataTable
    {
        public string length { get; set; }
        public string start { get; set; }
        public string sortColumn { get; set; }
        public string sortColumnDirection { get; set; }
        public string sortColumnIndex { get; set; }
        public string draw { get; set; }
        public string searchValue { get; set; }
        public int pageSize { get; set; }
        public int skip { get; set; }
    }

 public static void GetDataFromRequest(this HttpRequest Request, out FiltersFromRequestDataTable filtersFromRequest)
        {
            //TODO: Make Strings Safe String
            filtersFromRequest = new();

            filtersFromRequest.draw = Request.Form["draw"].FirstOrDefault();
            filtersFromRequest.start = Request.Form["start"].FirstOrDefault();
            filtersFromRequest.length = Request.Form["length"].FirstOrDefault();
            filtersFromRequest.sortColumn = Request.Form["columns[" + Request.Form["order[0][column]"].FirstOrDefault() + "][name]"].FirstOrDefault();
            filtersFromRequest.sortColumnDirection = Request.Form["order[0][dir]"].FirstOrDefault();
            filtersFromRequest.searchValue = Request.Form["search[value]"].FirstOrDefault();
            filtersFromRequest.pageSize = filtersFromRequest.length != null ? Convert.ToInt32(filtersFromRequest.length) : 0;
            filtersFromRequest.skip = filtersFromRequest.start != null ? Convert.ToInt32(filtersFromRequest.start) : 0;
            filtersFromRequest.sortColumnIndex = Request.Form["order[0][column]"].FirstOrDefault();

            filtersFromRequest.searchValue = filtersFromRequest.searchValue?.ToLower();
        }

Request.GetDataFromRequest(out FiltersFromRequestDataTable filtersFromRequest);

 public static PaginationDataTableResult<T> ToDataTableJs<T>(this IEnumerable<T> source, FiltersFromRequestDataTable filtersFromRequest)
        {
            int recordsTotal = source.Count();
            CofingPaging(ref filtersFromRequest, recordsTotal);
            var result = new PaginationDataTableResult<T>()
            {
                draw = filtersFromRequest.draw,
                recordsFiltered = recordsTotal,
                recordsTotal = recordsTotal,
                data = source.OrderByIndex(filtersFromRequest).Skip(filtersFromRequest.skip).Take(filtersFromRequest.pageSize).ToList()
            };

            return result;
        }

        private static void CofingPaging(ref FiltersFromRequestDataTable filtersFromRequest, int recordsTotal)
        {
            if (filtersFromRequest.pageSize == -1)
            {
                filtersFromRequest.pageSize = recordsTotal;
                filtersFromRequest.skip = 0;
            }
        }

var result =  query.ToDataTableJs(filtersFromRequest);
return new JsonResult(result);

 public static IEnumerable<TSource> WhereSearchValue<TSource>(this IEnumerable<TSource> source, Func<TSource, bool> predicate)
        {
            return source.Where(predicate);
        }
        public static bool ContainsSearchValue(this string source, string toCheck)
        {
            return source != null && toCheck != null && source.IndexOf(toCheck, StringComparison.OrdinalIgnoreCase) >= 0;
        }

if (!string.IsNullOrEmpty(filtersFromRequest.searchValue))
query = query.WhereSearchValue(x => x.title.ContainsSearchValue(filtersFromRequest.searchValue) || x.id.ToString().ContainsSearchValue(filtersFromRequest.searchValue)).AsQueryable();

 public JsonResult OnPostList()
        {
            Request.GetDataFromRequest(out FiltersFromRequestDataTable filtersFromRequest);
            var query = _Repo.GetQueryable().Select(x => new VmAdminList()
            {
                title = x.Title,
            }
            );

            if (!string.IsNullOrEmpty(filtersFromRequest.searchValue))
                query = query.WhereSearchValue(x => x.title.ContainsSearchValue(filtersFromRequest.searchValue) || x.id.ToString().ContainsSearchValue(filtersFromRequest.searchValue)).AsQueryable();

            var result =  query.ToDataTableJs(filtersFromRequest);
            return new JsonResult(result);
        }

columns: [
        { data: 'name' },
        { data: 'position' },
        { data: 'salary' },
        { data: 'office' }
    ]

public class JsDataTblGeneretaor<T>
    {
        public readonly DataTableSchemaResult DataTableSchemaResult = new();
        public JsDataTblGeneretaor<T> CreateTableSchema()
        {
            var props = typeof(T).GetProperties();

            foreach (var prop in props)
            {
                DataTableSchemaResult.SchemaResult.Add(new()
                {
                    data = prop.Name,
                    sortable = (prop.PropertyType == typeof(int)) || (prop.PropertyType == typeof(bool)) || (prop.PropertyType == typeof(DateTime)),
                    width = "",
                    visible = (prop.PropertyType != typeof(DateTime))
                });
            }

            return this;
        }

        public JsDataTblGeneretaor<T> CreateTableColumns()
        {
            var props = typeof(T).GetProperties();

            CustomAttributeData displayAttribute;

            foreach (var prop in props)
            {
                string displayName = prop.Name;
                displayAttribute = prop.CustomAttributes.FirstOrDefault(x => x.AttributeType.Name == "DisplayAttribute");
                if (displayAttribute != null)
                {
                    displayName = displayAttribute.NamedArguments.FirstOrDefault().TypedValue.Value.ToString();
                }

                DataTableSchemaResult.Colums.Add(displayName);
            }

            return this;
        }

        public JsDataTblGeneretaor<T> AddCustomSchema(string data, bool? sortable = null, bool? visible = null, string width = null, string className = null)
        {
            if (DataTableSchemaResult.SchemaResult == null || !DataTableSchemaResult.SchemaResult.Any())
                return this;

            foreach (var item in DataTableSchemaResult.SchemaResult.Where(x => x.data == data))
            {
                if (sortable != null)
                    item.sortable = sortable.Value;

                if (visible != null)
                    item.visible = visible.Value;

                if (width != null)
                    item.width = width;

                if (className != null)
                    item.className = className;
            }

            return this;
        }
        public JsDataTblGeneretaor<T> SerializeSchema()
        {
            if (DataTableSchemaResult.SchemaResult == null || !DataTableSchemaResult.SchemaResult.Any())
                return this;

            DataTableSchemaResult.SerializedSchemaResult = JsonSerializer.Serialize(DataTableSchemaResult.SchemaResult);

            return this;
        }
    }
    public class DataTableSchema
    {
        public string data { get; set; }
        public bool sortable { get; set; }
        public string width { get; set; }
        public bool visible { get; set; }
        public string className { get; set; }
    }
    public class DataTableSchemaResult
    {
        public readonly List<DataTableSchema> SchemaResult = new();
        public readonly List<string> Colums = new();
        public string SerializedSchemaResult = "";
    }

public void OnGet()
        {
            //Create Data Table Js Schema and Columns Dynamicly
            JsDataTblGeneretaor<yourVM> tblGeneretaor = new();
            DataTableSchemaResult = tblGeneretaor.CreateTableColumns().CreateTableSchema().SerializeSchema().DataTableSchemaResult;
        }

.AddCustomSchema("yourProperty",visible:false)

var scheme = JSON.parse('@Html.Raw(Model.DataTableSchemaResult.SerializedSchemaResult)')

@foreach (var col in Model.DataTableSchemaResult.Colums)
              {
                <th>@col</th>
              }

using System.Collections.Generic;

namespace NHSample4.Domain
{
 public class Course
 {
     public virtual int Id { get; set; }
     public virtual string Teacher { get; set; }
     public virtual IList<Student> Students { get; set; }

     public Course()
     {
         Students = new List<Student>();
     }
 }
}

using System.Collections.Generic;

namespace NHSample4.Domain
{
 public class Student
 {
     public virtual int Id { get; set; }
     public virtual string Name { get; set; }
     public virtual IList<Course> Courses { get; set; }

     public Student()
     {
         Courses = new List<Course>();
     }
 }
}

using FluentNHibernate.Automapping;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate.Tool.hbm2ddl;

namespace NHSessionManager
{
 public class Config
 {
     public static FluentConfiguration GetConfig()
     {
         return
           Fluently.Configure()
                   .Database(
                      MsSqlConfiguration
                        .MsSql2008
                        .ConnectionString(x => x.FromConnectionStringWithKey("DbConnectionString"))                        
                       )
                     .Mappings(
                       m => m.AutoMappings.Add(
                          new AutoPersistenceModel()
                              .Where(x => x.Namespace.EndsWith("Domain"))
                              .AddEntityAssembly(typeof(NHSample4.Domain.Course).Assembly))                              
                              .ExportTo(System.Environment.CurrentDirectory)
                              );
     }

     public static void CreateDb()
     {
         bool script = false;//آیا خروجی در کنسول هم نمایش داده شود
         bool export = true;//آیا بر روی دیتابیس هم اجرا شود           
         bool dropTables = false;//آیا جداول موجود دراپ شوند
         new SchemaExport(GetConfig().BuildConfiguration()).Execute(script, export, dropTables);
     }
 }
}

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
 <!--NHSessionManager-->
 <add name="DbConnectionString"
      connectionString="Data Source=(local);Initial Catalog=HelloNHibernate;Integrated Security = true"/>
</connectionStrings>
</configuration>

using System;
using System.Linq;
using System.Linq.Expressions;

namespace NHSample4.NHRepository
{
 //Repository Interface
 public interface IRepository<T>
 {
     T Get(object key);

     T Save(T entity);
     T Update(T entity);
     void Delete(T entity);

     IQueryable<T> Find();
     IQueryable<T> Find(Expression<Func<T, bool>> predicate);
 }
}

using System;
using System.Linq;
using NHSessionManager;
using NHibernate;
using NHibernate.Linq;

namespace NHSample4.NHRepository
{
 public class Repository<T> : IRepository<T>, IDisposable
 {
     private ISession _session;
     private bool _disposed = false;

     public Repository()
     {
         _session = SingletonCore.SessionFactory.OpenSession();
         BeginTransaction();
     }

     ~Repository()
     {
         Dispose(false);
     }

     public T Get(object key)
     {
         if (!isSessionSafe) return default(T);

         return _session.Get<T>(key);
     }

     public T Save(T entity)
     {
         if (!isSessionSafe) return default(T);

         _session.Save(entity);
         return entity;
     }

     public T Update(T entity)
     {
         if (!isSessionSafe) return default(T);

         _session.Update(entity);
         return entity;
     }

     public void Delete(T entity)
     {
         if (!isSessionSafe) return;

         _session.Delete(entity);
     }

     public IQueryable<T> Find()
     {
         if (!isSessionSafe) return null;

         return _session.Linq<T>();
     }

     public IQueryable<T> Find(System.Linq.Expressions.Expression<Func<T, bool>> predicate)
     {
         if (!isSessionSafe) return null;

         return Find().Where(predicate);
     }

     void Commit()
     {
         if (!isSessionSafe) return;

         if (_session.Transaction != null &&
             _session.Transaction.IsActive &&
             !_session.Transaction.WasCommitted &&
             !_session.Transaction.WasRolledBack)
         {
             _session.Transaction.Commit();
         }
         else
         {
             _session.Flush();
         }
     }

     void Rollback()
     {
         if (!isSessionSafe) return;

         if (_session.Transaction != null && _session.Transaction.IsActive)
         {
             _session.Transaction.Rollback();
         }
     }

     private bool isSessionSafe
     {
         get
         {
             return _session != null && _session.IsOpen;
         }
     }

     void BeginTransaction()
     {
         if (!isSessionSafe) return;

         _session.BeginTransaction();
     }


     public void Dispose()
     {
         Dispose(true);
         // tell the GC that the Finalize process no longer needs to be run for this object.
         GC.SuppressFinalize(this);
     }

     protected virtual void Dispose(bool disposeManagedResources)
     {
         if (_disposed) return;
         if (!disposeManagedResources) return;
         if (!isSessionSafe) return;

         try
         {
             Commit();
         }
         catch (Exception ex)
         {
             Console.WriteLine(ex.ToString());
             Rollback();
         }
         finally
         {
             if (isSessionSafe)
             {
                 _session.Close();
                 _session.Dispose();
             }
         }

         _disposed = true;
     }
 }
}

using System;
using System.Collections.Generic;
using NHSample4.Domain;
using NHSample4.NHRepository;

namespace NHSample4
{
 class Program
 {
     static void Main(string[] args)
     {
         //ایجاد دیتابیس در صورت نیاز
         //NHSessionManager.Config.CreateDb();

      
         //ابتدا یک دانشجو را اضافه می‌کنیم
         Student student = null;
         using (var studentRepo = new Repository<Student>())
         {
             student = studentRepo.Save(new Student() { Name = "Vahid" });
         }

         //سپس یک واحد را اضافه می‌کنیم
         using (var courseRepo = new Repository<Course>())
         {
             var course = courseRepo.Save(new Course() { Teacher = "Shams" });
         }

         //اکنون یک واحد را به دانشجو انتساب می‌دهیم
         using (var courseRepo = new Repository<Course>())
         {
             courseRepo.Save(new Course() { Students = new List<Student>() { student } });
         }
      
         //سپس شماره دروس استادی خاص را نمایش می‌دهیم
         using (var courseRepo = new Repository<Course>())
         {
             var query = courseRepo.Find(t => t.Teacher == "Shams");

             foreach (var course in query)
                 Console.WriteLine(course.Id);
         }

         Console.WriteLine("Press a key...");
         Console.ReadKey();
     }
 }
}

LEAD ( scalar_expression [ ,offset ] , [ default ] ) 
    OVER ( [ partition_by_clause ] order_by_clause )

Create Table TestLead_LAG
(SalesOrderID int not null,
 SalesOrderDetailID int not null ,
 OrderQty smallint not null);
 GO
Insert Into TestLead_LAG 
       Values (43662,49,1),(43662,50,3),(43662,51,1),
          (43663,52,1),(43664,53,1),(43664,54,1),
  (43667,77,3),(43667,78,1),(43667,79,1),
  (43667,80,1),(43668,81,3),(43669,110,1),
  (43670,111,1),(43670,112,2),(43670,113,2),
  (43670,114,1),(43671,115,1),(43671,116,2)