using Serilog;
using Serilog.Exceptions;

ILogger logger = new LoggerConfiguration()
    .Enrich.WithExceptionDetails()   // This Line
    .WriteTo.RollingFile(
        new JsonFormatter(renderMessage: true), 
        @"C:\logs\log-{Date}.txt")    
    .CreateLogger();

var result = 
    from product in dbContext.Products
    where product.Category.Name == "Toys"
    where product.Price >= 2.50
    select product.Name;

• Fluent Syntax : 

var result = dbContext.Products
    .Where(p => p.Category.Name == "Toys" && p.Price >= 250)
    .Select(p => p.Name);

    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Field1 { get; set; }
        public string Field2 { get; set; }
        public string Field3 { get; set; }


        public static IEnumerable<Student> GetStudentSource()
        {
            for (int i = 0; i < 10; i++)
            {
                yield return new Student
                                 {
                                     Id = i,
                                     Name = "Name " + i,
                                     Field1 = "Field1 " + i,
                                     Field2 = "Field2 " + i,
                                     Field3 = "Field3 " + i
                                 };
            }
        }
    }

    public class Program
    {
        private static void Main(string[] args)
        {
            System.Console.WriteLine("Specify the desired fields : ");
            string fields = System.Console.ReadLine();
            IEnumerable<Student> students = Student.GetStudentSource();
            IQueryable output = students.AsQueryable().Select(string.Format("new({0})", fields));
            foreach (object item in output)
            {
                System.Console.WriteLine(item);
            }
          
            System.Console.ReadKey();
        }
  
    }

    public  class SelectBuilder <T>
    {
        public static Func<T, T> CreateNewStatement(string fields)
        {
            // input parameter "o"
            var xParameter = Expression.Parameter(typeof(T), "o");


            // new statement "new T()"
            var xNew = Expression.New(typeof(T));

            // create initializers
            var bindings = fields.Split(',').Select(o => o.Trim())
                .Select(o =>
                {

                    // property "Field1"
                    var property = typeof(T).GetProperty(o);

                    // original value "o.Field1"
                    var xOriginal = Expression.Property(xParameter, property);

                    // set value "Field1 = o.Field1"
                    return Expression.Bind(property, xOriginal);
                }
            ).ToList();

            // initialization "new T { Field1 = o.Field1, Field2 = o.Field2 }"
            var xInit = Expression.MemberInit(xNew, bindings);

            // expression "o => new T { Field1 = o.Field1, Field2 = o.Field2 }"
            var lambda = Expression.Lambda<Func<T, T>>(xInit, xParameter);

            // compile to Func<T, T>
            return lambda.Compile();
        }
    }

       IEnumerable<Student> result = students.Select(SelectBuilder<Student>.CreateNewStatement("Field1, Field2")).ToList();

            foreach (Student student in result)
            {
                System.Console.WriteLine(student.Field1);
            }

ابتدا فیلد‌های انتخابی کاربر که با "," جدا شده اند به ورودی پاس داده می‌شود سپس یک statement  خالی ایجاد می‌شود : 

o=>new Student()

 SELECT * INTO #temptable FROM [Northwind].[dbo].[Products]
UPDATE #temptable SET [UnitPrice] = [UnitPrice] + 10

DECLARE @tempTable TABLE
(
   [ProductID] [int] NOT NULL,
   [ProductName] [nvarchar](40) NOT NULL,
   [UnitPrice] [money] NULL
)
 
INSERT INTO @tempTable
SELECT
   [ProductID],
   [ProductName],
   [UnitPrice]
FROM [Northwind].[dbo].[Products]
 
UPDATE @temptable SET [UnitPrice] = [UnitPrice] + 10

WITH yourName [(Column1, Column2, ...)]
AS
(
   your query
)

WITH temp
AS
(
    SELECT
         [ProductName],
         [UnitPrice]
    FROM [Northwind].[dbo].[Products]
)
SELECT * FROM temp
ORDER BY [UnitPrice] DESC

WITH orderSales (OrderID, Total)
AS
(
   SELECT
      [OrderID],
     SUM([UnitPrice]*[Quantity]) AS Total
   FROM [Northwind].[dbo].[Order Details]
   GROUP BY [OrderID]
)
SELECT
   O.[ShipCountry],
   SUM(OS.[Total]) AS TotalSales
FROM [Northwind].[dbo].[Orders] AS O INNER JOIN [orderSales] AS OS
ON O.[OrderID] = OS.[OrderID]
GROUP BY O.[ShipCountry]
ORDER BY TotalSales DESC

WITH customerList
AS
(
   SELECT
      [CustomerID],
      [ContactName]
   FROM [Northwind].[dbo].[Customers]
   WHERE [Country] ='UK'
)
,orderList
AS
(
   SELECT
      [CustomerID],
      [OrderDate]
   FROM [Northwind].[dbo].[Orders]
   WHERE YEAR([OrderDate])< 2000
)
SELECT
   cl.[ContactName],
   YEAR(ol.[OrderDate]) AS SalesYear
FROM customerList AS cl JOIN orderList AS ol
ON cl.[CustomerID] = ol.[CustomerID]

WITH digitList
AS
(
   SELECT [digit] from [digits]
)
SELECT
   a.[digit] * 10 + b.[digit] + 1 AS [Digit]
FROM [digitList] AS a CROSSJOIN [digitList] AS b

WITH CTE_1 AS
(
   ....
),
CTE_2 AS
(
   SELECT  ... FROM CTE_1 JOIN ...
)
SELECT   *
FROM FOO
LEFTJOIN  CTE_1
LEFTJOIN  CTE_2

WITH cteName AS
(
   query1
   UNION ALL
   query2
)

IFOBJECT_ID('Employees','U')ISNOTNULL
DROPTABLE dbo.Employees
GO 

CREATETABLE dbo.Employees
(
  EmployeeID intNOTNULLPRIMARYKEY,
  FirstName varchar(50)NOTNULL,
  LastName varchar(50)NOTNULL,
  ManagerID intNULL
)

GO


INSERTINTO Employees VALUES (101,'Alireza','Nematollahi',NULL)
INSERTINTO Employees VALUES (102,'Ahmad','Mofarrahzadeh', 101)
INSERTINTO Employees VALUES (103,'Mohammad','BozorgGhommi', 102)
INSERTINTO Employees VALUES (104,'Masoud','Narimani', 103)
INSERTINTO Employees VALUES (105,'Mohsen','Hashemi', 103)
INSERTINTO Employees VALUES (106,'Aref','Partovi', 102)
INSERTINTO Employees VALUES (107,'Hosain','Mahmoudi', 106)
INSERTINTO Employees VALUES (108,'Naser','Pourali', 106)
INSERTINTO Employees VALUES (109,'Reza','Bagheri', 102)
INSERTINTO Employees VALUES (110,'Abbas','Najafian', 102)

WITHcteReports(EmpID, FirstName, LastName, MgrID, EmpLevel)
AS
(
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
FROM Employees
WHERE ManagerID ISNULL
UNIONALL
SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,r.EmpLevel + 1
FROM Employees e INNERJOINcteReports r
ON e.ManagerID = r.EmpID
)
SELECT
FirstName +' '+ LastName AS FullName,
EmpLevel,
(SELECT FirstName +' '+ LastName FROM Employees
WHERE EmployeeID = cteReports.MgrID)AS Manager
FROMcteReports
ORDERBY EmpLevel, MgrID

WITHcteReports(EmpID, FirstName, LastName, MgrID, EmpLevel)
AS
(
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
FROM Employees
WHERE EmployeeID = 110
UNIONALL
SELECTe.EmployeeID, e.FirstName, e.LastName, e.ManagerID,r.EmpLevel + 1
FROM Employees e INNERJOINcteReports r
ON e.EmployeeID = r.MgrID
)
SELECT
FirstName +' '+ LastName AS FullName,
EmpLevel
FROMcteReports
ORDERBY EmpLevel

@page "/registration"

@inject IClientAuthenticationService AuthenticationService
@inject NavigationManager NavigationManager


<EditForm Model="UserForRegistration" OnValidSubmit="RegisterUser" class="pt-4">
    <DataAnnotationsValidator />
    <div class="py-4">
        <div class=" row form-group ">
            <div class="col-6 offset-3 ">
                <div class="card border">
                    <div class="card-body px-lg-5 pt-4">
                        <h3 class="col-12 text-success text-center py-2">
                            <strong>Sign Up</strong>
                        </h3>
                        @if (ShowRegistrationErrors)
                        {
                            <div>
                                @foreach (var error in Errors)
                                {
                                    <p class="text-danger text-center">@error</p>
                                }
                            </div>
                        }
                        <hr style="background-color:aliceblue" />
                        <div class="py-2">
                            <InputText @bind-Value="UserForRegistration.Name" class="form-control" placeholder="Name..." />
                            <ValidationMessage For="(()=>UserForRegistration.Name)" />
                        </div>
                        <div class="py-2">
                            <InputText @bind-Value="UserForRegistration.Email" class="form-control" placeholder="Email..." />
                            <ValidationMessage For="(()=>UserForRegistration.Email)" />
                        </div>
                        <div class="py-2 input-group">
                            <div class="input-group-prepend">
                                <span class="input-group-text"> +1</span>
                            </div>
                            <InputText @bind-Value="UserForRegistration.PhoneNo" class="form-control" placeholder="Phone number..." />
                            <ValidationMessage For="(()=>UserForRegistration.PhoneNo)" />
                        </div>
                        <div class="form-row py-2">
                            <div class="col">
                                <InputText @bind-Value="UserForRegistration.Password" type="password" id="password" placeholder="Password..." class="form-control" />
                                <ValidationMessage For="(()=>UserForRegistration.Password)" />
                            </div>
                            <div class="col">
                                <InputText @bind-Value="UserForRegistration.ConfirmPassword" type="password" id="confirm" class="form-control" placeholder="Confirm Password..." />
                                <ValidationMessage For="(()=>UserForRegistration.ConfirmPassword)" />
                            </div>
                        </div>
                        <hr style="background-color:aliceblue" />
                        <div class="py-2">
                            @if (IsProcessing)
                            {
                                <button type="submit" class="btn btn-success btn-block disabled"><i class="fas fa-sign-in-alt"></i> Please Wait...</button>
                            }
                            else
                            {
                                <button type="submit" class="btn btn-success btn-block"><i class="fas fa-sign-in-alt"></i> Register</button>
                            }
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</EditForm>

@code{
    UserRequestDTO UserForRegistration = new UserRequestDTO();
    bool IsProcessing;
    bool ShowRegistrationErrors;
    IEnumerable<string> Errors;

    private async Task RegisterUser()
    {
        ShowRegistrationErrors = false;
        IsProcessing = true;
        var result = await AuthenticationService.RegisterUserAsync(UserForRegistration);
        if (result.IsRegistrationSuccessful)
        {
            IsProcessing = false;
            NavigationManager.NavigateTo("/login");
        }
        else
        {
            IsProcessing = false;
            Errors = result.Errors;
            ShowRegistrationErrors = true;
        }
    }
}

@page "/login"

@inject IClientAuthenticationService AuthenticationService
@inject NavigationManager NavigationManager

<div id="logreg-forms">
    <h1 class="h3 mb-3 pt-3 font-weight-normal text-primary" style="text-align:center;">Sign In</h1>
    <EditForm Model="UserForAuthentication" OnValidSubmit="LoginUser">
        <DataAnnotationsValidator />
        @if (ShowAuthenticationErrors)
        {
            <p class="text-center text-danger">@Errors</p>
        }
        <InputText @bind-Value="UserForAuthentication.UserName" id="email" placeholder="Email..." class="form-control mb-2" />
        <ValidationMessage For="(()=>UserForAuthentication.UserName)"></ValidationMessage>
        <InputText @bind-Value="UserForAuthentication.Password" type="password" placeholder="Password..." id="password" class="form-control mb-2" />
        <ValidationMessage For="(()=>UserForAuthentication.Password)"></ValidationMessage>
        @if (IsProcessing)
        {
            <button type="submit" class="btn btn-success btn-block disabled"><i class="fas fa-sign-in-alt"></i> Please Wait...</button>
        }
        else
        {
            <button type="submit" class="btn btn-success btn-block"><i class="fas fa-sign-in-alt"></i> Sign in</button>
        }
        <a href="/registration" class="btn btn-primary text-white mt-3"><i class="fas fa-user-plus"></i> Register as a new user</a>
    </EditForm>
</div>
@code
{
    AuthenticationDTO UserForAuthentication = new AuthenticationDTO();
    bool IsProcessing = false;
    bool ShowAuthenticationErrors;
    string Errors;
    string ReturnUrl;

    private async Task LoginUser()
    {
        ShowAuthenticationErrors = false;
        IsProcessing = true;
        var result = await AuthenticationService.LoginAsync(UserForAuthentication);
        if (result.IsAuthSuccessful)
        {
            IsProcessing = false;
            var absoluteUri = new Uri(NavigationManager.Uri);
            var queryParam = HttpUtility.ParseQueryString(absoluteUri.Query);
            ReturnUrl = queryParam["returnUrl"];
            if (string.IsNullOrEmpty(ReturnUrl))
            {
                NavigationManager.NavigateTo("/");
            }
            else
            {
                NavigationManager.NavigateTo("/" + ReturnUrl);
            }
        }
        else
        {
            IsProcessing = false;
            Errors = result.ErrorMessage;
            ShowAuthenticationErrors = true;
        }
    }
}

<AuthorizeView>
    <Authorized>
        <li class="nav-item p-0">
          <NavLink class="nav-link" href="#">
             <span class="p-2">
                Hello, @context.User.Identity.Name!
             </span>
          </NavLink>
        </li>
        <li class="nav-item p-0">
          <NavLink class="nav-link" href="logout">
             <span class="p-2">
                Logout
             </span>
          </NavLink>
        </li>
    </Authorized>
    <NotAuthorized>
        <li class="nav-item p-0">
          <NavLink class="nav-link" href="registration">
            <span class="p-2">
               Register
            </span>
          </NavLink>
        </li>
        <li class="nav-item p-0">
          <NavLink class="nav-link" href="login">
            <span class="p-2">
              Login
            </span>
          </NavLink>
        </li>
    </NotAuthorized>
</AuthorizeView>

@page "/logout"

@inject IClientAuthenticationService AuthenticationService
@inject NavigationManager NavigationManager

@code
{
    protected async override Task OnInitializedAsync()
    {
        await AuthenticationService.LogoutAsync();
        NavigationManager.NavigateTo("/");
    }
}

namespace BlazorWasm.Client.Services
{
    public class AuthStateProvider : AuthenticationStateProvider
    {
        // ...

        public void NotifyUserLoggedIn(string token)
        {
            var authenticatedUser = new ClaimsPrincipal(
                                        new ClaimsIdentity(JwtParser.ParseClaimsFromJwt(token), "jwtAuthType")
                                    );
            var authState = Task.FromResult(new AuthenticationState(authenticatedUser));
            base.NotifyAuthenticationStateChanged(authState);
        }

        public void NotifyUserLogout()
        {
            var authState = Task.FromResult(new AuthenticationState(new ClaimsPrincipal(new ClaimsIdentity())));
            base.NotifyAuthenticationStateChanged(authState);
        }
    }
}

namespace BlazorWasm.Client.Services
{
    public class ClientAuthenticationService : IClientAuthenticationService
    {
        private readonly HttpClient _client;
        private readonly ILocalStorageService _localStorage;
        private readonly AuthenticationStateProvider _authStateProvider;

        public ClientAuthenticationService(
            HttpClient client,
            ILocalStorageService localStorage,
            AuthenticationStateProvider authStateProvider)
        {
            _client = client;
            _localStorage = localStorage;
            _authStateProvider = authStateProvider;
        }

        public async Task<AuthenticationResponseDTO> LoginAsync(AuthenticationDTO userFromAuthentication)
        {
            // ...
            if (response.IsSuccessStatusCode)
            {
                //...
                ((AuthStateProvider)_authStateProvider).NotifyUserLoggedIn(result.Token);

                return new AuthenticationResponseDTO { IsAuthSuccessful = true };
            }
            //...
        }

        public async Task LogoutAsync()
        {
            //...
            ((AuthStateProvider)_authStateProvider).NotifyUserLogout();
        }
    }
}

@attribute [Authorize(Roles = ‍ConstantRoles.Customer)]

@using Microsoft.AspNetCore.Authorization

@page "/hotel-room-details/{Id:int}"

// ...

@code {
     // ...

    protected override async Task OnInitializedAsync()
    {
        try
        {
            HotelBooking.OrderDetails = new RoomOrderDetailsDTO();
            if (Id != null)
            {
                // ...

                if (await LocalStorage.GetItemAsync<UserDTO>(ConstantKeys.LocalUserDetails) != null)
                {
                    var userInfo = await LocalStorage.GetItemAsync<UserDTO>(ConstantKeys.LocalUserDetails);
                    HotelBooking.OrderDetails.UserId = userInfo.Id;
                    HotelBooking.OrderDetails.Name = userInfo.Name;
                    HotelBooking.OrderDetails.Email = userInfo.Email;
                    HotelBooking.OrderDetails.Phone = userInfo.PhoneNo;
                }
            }
        }
        catch (Exception e)
        {
            await JsRuntime.ToastrError(e.Message);
        }
    }

public async Task<RoomOrderDetailsDTO> SaveRoomOrderDetailsAsync(RoomOrderDetailsDTO details)
{
   // details.UserId = "unknown user!";