-- Insert --
INSERT  INTO OPENQUERY(MyLinkServer, 'SELECT * FROM unit')
VALUES  ( 1, 5, 'fa', '0', GETDATE(), '1', GETDATE(), '1' );
----------------------------------------------------------------------------------------
-- Update --
--1
UPDATE  OPENQUERY(MyLinkServer , 'SELECT * FROM unit WHERE id=4')
SET   [is_deleted] = '0';
--2
EXEC('UPDATE unit set is_deleted=''1'' where id=4;') AT MyLinkServer ;
-- 3
UPDATE  OPENQUERY(MyLinkServer , 'SELECT * FROM unit')
SET   [is_deleted] = '0'
WHERE id = 4;
----------------------------------------------------------------------------------------
-- Delete --
DELETE  OPENQUERY(MyLinkServer, 'select * from unit where id=4;');

using Microsoft.AspNetCore.Identity;

namespace BlazorServer.Entities
{
    public class ApplicationUser : IdentityUser
    {
        public string Name { get; set; }
    }
}

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>

namespace BlazorWasm.WebApi
{
    public class Startup
    {
        // ...

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddIdentity<ApplicationUser, IdentityRole>()
                .AddEntityFrameworkStores<ApplicationDbContext>()
                .AddDefaultTokenProviders();
  
            // ...

dotnet tool update --global dotnet-ef --version 5.0.4
dotnet build
dotnet ef migrations --startup-project ../../BlazorWasm/BlazorWasm.WebApi/ add AddNameToAppUser --context ApplicationDbContext
dotnet ef --startup-project ../../BlazorWasm/BlazorWasm.WebApi/ database update --context ApplicationDbContext

using System.ComponentModel.DataAnnotations;

namespace BlazorServer.Models
{
    public class UserRequestDTO
    {
        [Required(ErrorMessage = "Name is required")]
        public string Name { get; set; }

        [Required(ErrorMessage = "Email is required")]
        [RegularExpression("^[a-zA-Z0-9_.-]+@[a-zA-Z0-9-]+.[a-zA-Z0-9-.]+$",
                ErrorMessage = "Invalid email address")]
        public string Email { get; set; }

        public string PhoneNo { get; set; }

        [Required(ErrorMessage = "Password is required.")]
        [DataType(DataType.Password)]
        public string Password { get; set; }

        [Required(ErrorMessage = "Confirm password is required")]
        [DataType(DataType.Password)]
        [Compare("Password", ErrorMessage = "Password and confirm password is not matched")]
        public string ConfirmPassword { get; set; }
    }
}

    public class RegistrationResponseDTO
    {
        public bool IsRegistrationSuccessful { get; set; }

        public IEnumerable<string> Errors { get; set; }
    }

using System;
using BlazorServer.Entities;
using BlazorServer.Models;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Mvc;
using System.Threading.Tasks;
using System.Linq;
using BlazorServer.Common;

namespace BlazorWasm.WebApi.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    [Authorize]
    public class AccountController : ControllerBase
    {
        private readonly SignInManager<ApplicationUser> _signInManager;
        private readonly UserManager<ApplicationUser> _userManager;
        private readonly RoleManager<IdentityRole> _roleManager;

        public AccountController(SignInManager<ApplicationUser> signInManager,
            UserManager<ApplicationUser> userManager,
            RoleManager<IdentityRole> roleManager)
        {
            _roleManager = roleManager ?? throw new ArgumentNullException(nameof(roleManager));
            _userManager = userManager ?? throw new ArgumentNullException(nameof(userManager));
            _signInManager = signInManager ?? throw new ArgumentNullException(nameof(signInManager));
        }

        [HttpPost]
        [AllowAnonymous]
        public async Task<IActionResult> SignUp([FromBody] UserRequestDTO userRequestDTO)
        {
            var user = new ApplicationUser
            {
                UserName = userRequestDTO.Email,
                Email = userRequestDTO.Email,
                Name = userRequestDTO.Name,
                PhoneNumber = userRequestDTO.PhoneNo,
                EmailConfirmed = true
            };
            var result = await _userManager.CreateAsync(user, userRequestDTO.Password);
            if (!result.Succeeded)
            {
                var errors = result.Errors.Select(e => e.Description);
                return BadRequest(new RegistationResponseDTO { Errors = errors, IsRegistrationSuccessful = false });
            }

            var roleResult = await _userManager.AddToRoleAsync(user, ConstantRoles.Customer);
            if (!roleResult.Succeeded)
            {
                var errors = result.Errors.Select(e => e.Description);
                return BadRequest(new RegistationResponseDTO { Errors = errors, IsRegistrationSuccessful = false });
            }

            return StatusCode(201); // Created
        }
    }
}

[Route("api/[controller]/[action]")]
[ApiController]
[Authorize]

using System.ComponentModel.DataAnnotations;

namespace BlazorServer.Models
{
    public class AuthenticationDTO
    {
        [Required(ErrorMessage = "UserName is required")]
        [RegularExpression("^[a-zA-Z0-9_.-]+@[a-zA-Z0-9-]+.[a-zA-Z0-9-.]+$",
            ErrorMessage = "Invalid email address")]
        public string UserName { get; set; }

        [Required(ErrorMessage = "Password is required.")]
        [DataType(DataType.Password)]
        public string Password { get; set; }
    }
}

using System.Collections.Generic;

namespace BlazorServer.Models
{
    public class AuthenticationResponseDTO
    {
        public bool IsAuthSuccessful { get; set; }

        public string ErrorMessage { get; set; }

        public string Token { get; set; }

        public UserDTO UserDTO { get; set; }
    }

    public class UserDTO
    {
        public string Id { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public string PhoneNo { get; set; }
    }
}

namespace BlazorServer.Models
{
    public class BearerTokensOptions
    {
        public string Key { set; get; }

        public string Issuer { set; get; }

        public string Audience { set; get; }

        public int AccessTokenExpirationMinutes { set; get; }
    }
}

{
  "BearerTokens": {
    "Key": "This is my shared key, not so secret, secret!",
    "Issuer": "https://localhost:5001/",
    "Audience": "Any",
    "AccessTokenExpirationMinutes": 20
  }
}

namespace BlazorWasm.WebApi
{
    public class Startup
    {
        // ...

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddOptions<BearerTokensOptions>().Bind(Configuration.GetSection("BearerTokens"));
            // ...

using System;
using System.Collections.Generic;
using System.IdentityModel.Tokens.Jwt;
using System.Security.Claims;
using System.Text;
using System.Threading.Tasks;
using BlazorServer.Entities;
using BlazorServer.Models;
using Microsoft.AspNetCore.Identity;
using Microsoft.Extensions.Options;
using Microsoft.IdentityModel.Tokens;

namespace BlazorServer.Services
{

    public interface ITokenFactoryService
    {
        Task<string> CreateJwtTokensAsync(ApplicationUser user);
    }

    public class TokenFactoryService : ITokenFactoryService
    {
        private readonly UserManager<ApplicationUser> _userManager;
        private readonly BearerTokensOptions _configuration;

        public TokenFactoryService(
                UserManager<ApplicationUser> userManager,
                IOptionsSnapshot<BearerTokensOptions> bearerTokensOptions)
        {
            _userManager = userManager ?? throw new ArgumentNullException(nameof(userManager));
            if (bearerTokensOptions is null)
            {
                throw new ArgumentNullException(nameof(bearerTokensOptions));
            }
            _configuration = bearerTokensOptions.Value;
        }

        public async Task<string> CreateJwtTokensAsync(ApplicationUser user)
        {
            var signingCredentials = new SigningCredentials(
                new SymmetricSecurityKey(Encoding.UTF8.GetBytes(_configuration.Key)),
                SecurityAlgorithms.HmacSha256);
            var claims = await getClaimsAsync(user);
            var now = DateTime.UtcNow;
            var tokenOptions = new JwtSecurityToken(
                issuer: _configuration.Issuer,
                audience: _configuration.Audience,
                claims: claims,
                notBefore: now,
                expires: now.AddMinutes(_configuration.AccessTokenExpirationMinutes),
                signingCredentials: signingCredentials);
            return new JwtSecurityTokenHandler().WriteToken(tokenOptions);
        }

        private async Task<List<Claim>> getClaimsAsync(ApplicationUser user)
        {
            string issuer = _configuration.Issuer;
            var claims = new List<Claim>
            {
                // Issuer
                new Claim(JwtRegisteredClaimNames.Iss, issuer, ClaimValueTypes.String, issuer),
                // Issued at
                new Claim(JwtRegisteredClaimNames.Iat, DateTimeOffset.UtcNow.ToUnixTimeSeconds().ToString(), ClaimValueTypes.Integer64, issuer),
                new Claim(ClaimTypes.Name, user.Email, ClaimValueTypes.String, issuer),
                new Claim(ClaimTypes.Email, user.Email, ClaimValueTypes.String, issuer),
                new Claim("Id", user.Id, ClaimValueTypes.String, issuer),
                new Claim("DisplayName", user.Name, ClaimValueTypes.String, issuer),
            };

            var roles = await _userManager.GetRolesAsync(user);
            foreach (var role in roles)
            {
                claims.Add(new Claim(ClaimTypes.Role, role, ClaimValueTypes.String, issuer));
            }
            return claims;
        }
    }
}

namespace BlazorWasm.WebApi
{
    public class Startup
    {
        // ...

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddScoped<ITokenFactoryService, TokenFactoryService>();
            // ...

namespace BlazorWasm.WebApi.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    [Authorize]
    public class AccountController : ControllerBase
    {
        private readonly SignInManager<ApplicationUser> _signInManager;
        private readonly UserManager<ApplicationUser> _userManager;
        private readonly ITokenFactoryService _tokenFactoryService;

        public AccountController(
            SignInManager<ApplicationUser> signInManager,
            UserManager<ApplicationUser> userManager,
            ITokenFactoryService tokenFactoryService)
        {
            _userManager = userManager ?? throw new ArgumentNullException(nameof(userManager));
            _signInManager = signInManager ?? throw new ArgumentNullException(nameof(signInManager));
            _tokenFactoryService = tokenFactoryService;
        }

        [HttpPost]
        [AllowAnonymous]
        public async Task<IActionResult> SignIn([FromBody] AuthenticationDTO authenticationDTO)
        {
            var result = await _signInManager.PasswordSignInAsync(
                    authenticationDTO.UserName, authenticationDTO.Password,
                    isPersistent: false, lockoutOnFailure: false);
            if (!result.Succeeded)
            {
                return Unauthorized(new AuthenticationResponseDTO
                {
                    IsAuthSuccessful = false,
                    ErrorMessage = "Invalid Authentication"
                });
            }

            var user = await _userManager.FindByNameAsync(authenticationDTO.UserName);
            if (user == null)
            {
                return Unauthorized(new AuthenticationResponseDTO
                {
                    IsAuthSuccessful = false,
                    ErrorMessage = "Invalid Authentication"
                });
            }

            var token = await _tokenFactoryService.CreateJwtTokensAsync(user);
            return Ok(new AuthenticationResponseDTO
            {
                IsAuthSuccessful = true,
                Token = token,
                UserDTO = new UserDTO
                {
                    Name = user.Name,
                    Id = user.Id,
                    Email = user.Email,
                    PhoneNo = user.PhoneNumber
                }
            });
        }
    }
}

{
  "iss": "https://localhost:5001/",
  "iat": 1616396383,
  "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name": "vahid@dntips.ir",
  "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress": "vahid@dntips.ir",
  "Id": "582855fb-e95b-45ab-b349-5e9f7de40c0c",
  "DisplayName": "vahid@dntips.ir",
  "http://schemas.microsoft.com/ws/2008/06/identity/claims/role": "Admin",
  "nbf": 1616396383,
  "exp": 1616397583,
  "aud": "Any"
}

namespace BlazorWasm.WebApi
{
    public class Startup
    {
        // ...

        public void ConfigureServices(IServiceCollection services)
        {
            var bearerTokensSection = Configuration.GetSection("BearerTokens");
            services.AddOptions<BearerTokensOptions>().Bind(bearerTokensSection);
            // ...

            var apiSettings = bearerTokensSection.Get<BearerTokensOptions>();
            var key = Encoding.UTF8.GetBytes(apiSettings.Key);
            services.AddAuthentication(opt =>
            {
                opt.DefaultAuthenticateScheme = JwtBearerDefaults.AuthenticationScheme;
                opt.DefaultChallengeScheme = JwtBearerDefaults.AuthenticationScheme;
                opt.DefaultScheme = JwtBearerDefaults.AuthenticationScheme;
            })
            .AddJwtBearer(cfg =>
            {
                cfg.RequireHttpsMetadata = false;
                cfg.SaveToken = true;
                cfg.TokenValidationParameters = new TokenValidationParameters
                {
                    ValidateIssuerSigningKey = true,
                    IssuerSigningKey = new SymmetricSecurityKey(key),
                    ValidateAudience = true,
                    ValidateIssuer = true,
                    ValidAudience = apiSettings.Audience,
                    ValidIssuer = apiSettings.Issuer,
                    ClockSkew = TimeSpan.Zero,
                    ValidateLifetime = true
                };
            });

            // ...

namespace BlazorWasm.WebApi
{
    public class Startup
    {
        // ...

        public void ConfigureServices(IServiceCollection services)
        {
            // ...

            services.AddSwaggerGen(c =>
            {
                c.SwaggerDoc("v1", new OpenApiInfo { Title = "BlazorWasm.WebApi", Version = "v1" });
                c.AddSecurityDefinition("Bearer", new OpenApiSecurityScheme
                {
                    In = ParameterLocation.Header,
                    Description = "Please enter the token in the field",
                    Name = "Authorization",
                    Type = SecuritySchemeType.ApiKey
                });
                c.AddSecurityRequirement(new OpenApiSecurityRequirement {
                    {
                        new OpenApiSecurityScheme
                        {
                            Reference = new OpenApiReference
                            {
                                Type = ReferenceType.SecurityScheme,
                                Id = "Bearer"
                            }
                        },
                        new string[] { }
                    }
                });
            });
        }

        // ...

public enum Fabric
{
    [Description("پنبه")]
    Cotton,

    [Description("ابریشم")]
    Silk,

    [Description("پشم")]
    Wool,

    [Description("ابریشم مصنوعی")]
    Rayon,

    [Description("پارچه‌های دیگر")]
    Other
}

public class MyViewModel
{
    public Fabric Fabric { get; set; }
}

[Flags]
public enum Fabric
{
    [Description("پنبه")]
    Cotton = 1,

    [Description("ابریشم")]
    Silk = 2,

    [Description("پشم")]
    Wool = 4,

    [Description("ابریشم مصنوعی")]
    Rayon = 8,

    [Description("پارچه‌های دیگر")]
    Other = 128
}

Fabric cotWool = Fabric.Cotton | Fabric.Wool;
int cotWoolValue = (int) cotWool;

@Html.CheckBoxesForEnumFlagsFor(x => x.Fabric)

public class FlagEnumerationModelBinder : DefaultModelBinder
{
    public override object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
    {
        if (bindingContext == null) throw new ArgumentNullException("bindingContext");

        if (bindingContext.ValueProvider.ContainsPrefix(bindingContext.ModelName))
        {
            var values = GetValue<string[]>(bindingContext, bindingContext.ModelName);

            if (values.Length > 1 && (bindingContext.ModelType.IsEnum && bindingContext.ModelType.IsDefined(typeof(FlagsAttribute), false)))
            {
                long byteValue = 0;
                foreach (var value in values.Where(v => Enum.IsDefined(bindingContext.ModelType, v)))
                {
                    byteValue |= (int)Enum.Parse(bindingContext.ModelType, value);
                }

                return Enum.Parse(bindingContext.ModelType, byteValue.ToString());
            }
            else
            {
                return base.BindModel(controllerContext, bindingContext);
            }
        }

        return base.BindModel(controllerContext, bindingContext);
    }

    private static T GetValue<T>(ModelBindingContext bindingContext, string key)
    {
        if (bindingContext.ValueProvider.ContainsPrefix(key))
        {
            ValueProviderResult valueResult = bindingContext.ValueProvider.GetValue(key);
            if (valueResult != null)
            {
                bindingContext.ModelState.SetModelValue(key, valueResult);
                return (T)valueResult.ConvertTo(typeof(T));
            }
        }
        return default(T);
    }
}

ModelBinders.Binders.Add(typeof(Fabric), new FlagEnumerationModelBinder());

public class ModelEnums
{
    public static IEnumerable<Type> Types
    {
        get
        {
            var types = new List<Type> { typeof(Fabric) };
            return types;
        }
    }
}

foreach (var type in ModelEnums.Types)
{
   ModelBinders.Binders.Add(type, new FlagEnumerationModelBinder())
}

var productsList1 = ctx.Products.Where(product => product.Id > 1)
    .Include(product => product.Category)
    .Include(product => product.User)
    .Where(
        product =>
            product.Category.Title.Contains("t") && product.Category.Id > 1 && product.Price > 100)
    .OrderBy(product => product.Price)
    .ToList();

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[Price] AS [Price],
    [Extent1].[CategoryId] AS [CategoryId],
    [Extent1].[UserId] AS [UserId],
    [Extent3].[Id] AS [Id1],
    [Extent3].[Name] AS [Name1],
    [Extent3].[Title] AS [Title],
    [Extent3].[UserId] AS [UserId1],
    [Extent4].[Id] AS [Id2],
    [Extent4].[Name] AS [Name2]
    FROM    [dbo].[Products] AS [Extent1]
    INNER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryId] = [Extent2].[Id]
    LEFT OUTER JOIN [dbo].[Categories] AS [Extent3] ON [Extent1].[CategoryId] = [Extent3].[Id]
    LEFT OUTER JOIN [dbo].[Users] AS [Extent4] ON [Extent1].[UserId] = [Extent4].[Id]
    WHERE ([Extent1].[Id] > 1) AND ([Extent2].[Title] LIKE N'%t%') AND ([Extent1].[CategoryId] > 1) AND ([Extent1].[Price] > 100)
    ORDER BY [Extent1].[Price] ASC

var query2 = from product in ctx.Products
             let category = product.Category
             where product.Id > 1
             where category.Title.Contains("t") && category.Id > 1 && product.Price > 100
             select new { product, category }; 
var productsList2 = query2.ToList();

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[Price] AS [Price],
    [Extent1].[CategoryId] AS [CategoryId],
    [Extent1].[UserId] AS [UserId],
    [Extent2].[Id] AS [Id1],
    [Extent2].[Name] AS [Name1],
    [Extent2].[Title] AS [Title],
    [Extent2].[UserId] AS [UserId1]
    FROM  [dbo].[Products] AS [Extent1]
    INNER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryId] = [Extent2].[Id]
    WHERE ([Extent1].[Id] > 1) AND ([Extent2].[Title] LIKE N'%t%') AND ([Extent2].[Id] > 1) AND ([Extent1].[Price] > 100)

var query2ChainedVersion = ctx.Products
     .Select(product => new { product, category = product.Category })
     .Where(@t => @t.product.Id > 1)
     .Where(@t => @t.category.Title.Contains("t") && @t.category.Id > 1 && @t.product.Price > 100)
     .Select(@t => new { @t.product , @t.category });

 declare @data xml
set @data = (select * from openrowset(bulk 'c:\path\data.xml', single_blob) as x)

  for $p in doc("data.xml")/people/person
 where $p/age > 30
 return $p/name/givenName/text()

 SELECT @data.query('
 for $p in /people/person
 where $p/age > 30
 return $p/name/givenName/text()
 ')

 SELECT @data.value('/people/person/age', 'int')

 XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

 SELECT @data.value('(/people/person/age)[1]', 'int')

 Infer.exe people.xml -o schema.xsd

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="people">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" name="person">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="name">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="givenName" type="xs:string" />
                    <xs:element name="familyName" type="xs:string" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
              <xs:element name="age" type="xs:unsignedByte" />
              <xs:element name="height" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

 declare @schema xml
set @schema = (select * from openrowset(bulk 'c:\path\schema_1.xsd', single_blob) as x)

 CREATE XML SCHEMA COLLECTION poeple_xsd AS @schema

 DECLARE @data XML(poeple_xsd)
SET @data = 'مانند قبل با همان محتوایی که در ابتدای بحث عنوان شد'
 

 SELECT @data.value('/people/person[1]/age', 'int')

 XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xs:unsignedByte *'

 DECLARE @data XML(document poeple_xsd)

 DECLARE @x XML
SET @x=''
SELECT @x.query(
'
1,2
(: 1,2 :)
')

 DECLARE @x XML
SET @x=''
SELECT @x.query(
'
for $x in (1,2,3)
for $y in (4,5)
return ($x,$y)
')

 DECLARE @x XML
SET @x=''
SELECT @x.query(
'
1, <node/>
')

 XQuery [query()]: Heterogeneous sequences are not allowed: found 'xs:integer' and 'element(node,xdt:untyped)'

augustoproiete/i-am-root-nuget-package: 📦🏴‍☠️ NuGet package that shows we can run arbitrary code from any NuGet package 

{
    "version": "1.0.0-*",
 
    "testRunner": "mstest",
    "dependencies": {
        "Microsoft.NETCore.App": {
            "type": "platform",
            "version": "1.0.0"
        },
        "dotnet-test-mstest": "1.1.1-preview",
        "MSTest.TestFramework": "1.0.1-preview",
        "NETStandard.Library": "1.6.0",
        "Microsoft.EntityFrameworkCore": "1.0.0",
        "Microsoft.EntityFrameworkCore.InMemory": "1.0.0",
        "Core1RtmEmptyTest.DataLayer": "1.0.0-*",
        "Core1RtmEmptyTest.Entities": "1.0.0-*",
        "Core1RtmEmptyTest.Services": "1.0.0-*",
        "Core1RtmEmptyTest.ViewModels": "1.0.0-*"
    },
 
    "frameworks": {
        "netcoreapp1.0": {
            "imports": [
                "dnxcore50",
                "portable-net45+win8"
            ]
        }
    }
}

using Microsoft.VisualStudio.TestTools.UnitTesting;
 
namespace EFCore.MsTests
{
    [TestClass]
    public class CoreTests
    {
        [TestMethod]
        public void Test1()
        {
            Assert.IsTrue(true);
        }
    }
}

public class ApplicationDbContext : DbContext, IUnitOfWork
{
    private readonly IConfigurationRoot _configuration;
 
    public ApplicationDbContext(IConfigurationRoot configuration)
    {
        _configuration = configuration;
    }
 
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
    {
    } 
 
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer(
                _configuration["ConnectionStrings:ApplicationDbContextConnection"]
                , serverDbContextOptionsBuilder =>
                {
                    var minutes = (int)TimeSpan.FromMinutes(3).TotalSeconds;
                    serverDbContextOptionsBuilder.CommandTimeout(minutes);
                });
        }
    }

using System;
using System.Linq;
using Core1RtmEmptyTest.DataLayer;
using Core1RtmEmptyTest.Entities;
using Core1RtmEmptyTest.Services;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.VisualStudio.TestTools.UnitTesting;
 
namespace Core1RtmEmptyTest.MsTests
{
    [TestClass]
    public class CoreTests
    {
        private readonly IServiceProvider _serviceProvider;
 
        public CoreTests()
        {
            var services = new ServiceCollection();
            services.AddEntityFrameworkInMemoryDatabase()
                        .AddDbContext<ApplicationDbContext>(options => options.UseInMemoryDatabase());
 
            services.AddScoped<IUnitOfWork, ApplicationDbContext>();
            services.AddScoped<IBlogService, BlogService>();
 
            _serviceProvider = services.BuildServiceProvider();
        }
 
        [TestMethod]
        public void Find_searches_url()
        {
            // Insert seed data into the database using one instance of the context
            using (var serviceScope = _serviceProvider.GetRequiredService<IServiceScopeFactory>().CreateScope())
            {
                using (var context = serviceScope.ServiceProvider.GetRequiredService<IUnitOfWork>())
                {
                    context.Set<Blog>().Add(new Blog { Url = "http://sample.com/cats" });
                    context.Set<Blog>().Add(new Blog { Url = "http://sample.com/catfish" });
                    context.Set<Blog>().Add(new Blog { Url = "http://sample.com/dogs" });
                    context.SaveAllChanges();
                }
            }
 
            // Use a separate instance of the context to verify correct data was saved to database
            using (var serviceScope = _serviceProvider.GetRequiredService<IServiceScopeFactory>().CreateScope())
            {
                using (var context = serviceScope.ServiceProvider.GetRequiredService<IUnitOfWork>())
                {
                    Assert.AreEqual(3, context.Set<Blog>().Count());
                    Assert.AreEqual("http://sample.com/cats", context.Set<Blog>().First().Url);
                }
            }
 
            // Use a clean instance of the context to run the test
            using (var serviceScope = _serviceProvider.GetRequiredService<IServiceScopeFactory>().CreateScope())
            {
                var blogService = serviceScope.ServiceProvider.GetRequiredService<IBlogService>();
                var results = blogService.GetPagedBlogsAsNoTracking(pageNumber: 0, recordsPerPage: 10);
                Assert.AreEqual(3, results.Count);
            }
        }
    }
}

        private readonly IServiceProvider _serviceProvider;
 
        public CoreTests()
        {
            var services = new ServiceCollection();
            services.AddEntityFrameworkInMemoryDatabase()
                        .AddDbContext<ApplicationDbContext>(options => options.UseInMemoryDatabase());
 
            services.AddScoped<IUnitOfWork, ApplicationDbContext>();
            services.AddScoped<IBlogService, BlogService>();
 
            _serviceProvider = services.BuildServiceProvider();
        }

            using (var serviceScope = _serviceProvider.GetRequiredService<IServiceScopeFactory>().CreateScope())
            {
                using (var context = serviceScope.ServiceProvider.GetRequiredService<IUnitOfWork>())
                {

 var connectionStringBuilder = new SqliteConnectionStringBuilder { DataSource = ":memory:" };
var connectionString = connectionStringBuilder.ToString();
var connection = new SqliteConnection(connectionString);
services.AddEntityFrameworkSqlite().AddDbContext<CmsDbContext>(options => options.UseSqlite(connection));

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

        public virtual ICollection<Person> Personnel { set; get; }
    }

    public class Person
    {
        public int Id { set; get; }
        public string FirstName { set; get; }
        public string LastName { set; get; }

        [ForeignKey("BirthPlaceId")]
        public virtual Place BirthPlace { set; get; }
        public int BirthPlaceId { set; get; }

        [ForeignKey("IssuanceLocationId")]
        public virtual Place IssuanceLocation { set; get; }
        public int? IssuanceLocationId { set; get; }
    }

var query = from personnel in context.Personnel
                            join issuanceLocation in context.Places on
                                  personnel.IssuanceLocationId equals issuanceLocation.Id into aIssuanceLocation
                            from IL in aIssuanceLocation.DefaultIfEmpty()
                            join birthLocation in context.Places on
                                  personnel.BirthPlaceId equals birthLocation.Id into aBirthLocation
                            from BL in aBirthLocation.DefaultIfEmpty()
                            select new
                               {
                                   personnel.Id,
                                   personnel.FirstName,
                                   personnel.LastName,
                                   IssuanceLocation = IL.Name,
                                   BirthLocation = BL.Name
                               };

SELECT
                        [Extent1].[Id] AS [Id],
                        [Extent1].[FirstName] AS [FirstName],
                        [Extent1].[LastName] AS [LastName],
                        [Extent2].[Name] AS [Name],
                        [Extent3].[Name] AS [Name1]
                        FROM [dbo].[People] AS [Extent1]
                        LEFT OUTER JOIN [dbo].[Places] AS [Extent2] ON [Extent1].[IssuanceLocationId] = [Extent2].[Id]
                        INNER JOIN [dbo].[Places] AS [Extent3] ON [Extent1].[BirthPlaceId] = [Extent3].[Id]

var query = context.Personnel.Select(x => new
             {
              x.Id,
              x.FirstName,
              x.LastName,
              BirthPlaceName = x.BirthPlace.Name,
              IssuanceLocationName = x.IssuanceLocation == null ? "" : x.IssuanceLocation.Name
             });

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[FirstName] AS [FirstName],
    [Extent1].[LastName] AS [LastName],
    [Extent2].[Name] AS [Name],
    CASE WHEN ([Extent3].[Id] IS NULL) THEN N'' ELSE [Extent3].[Name] END AS [C1]
    FROM   [dbo].[People] AS [Extent1]
    INNER JOIN [dbo].[Places] AS [Extent2] ON [Extent1].[BirthPlaceId] = [Extent2].[Id]
    LEFT OUTER JOIN [dbo].[Places] AS [Extent3] ON [Extent1].[IssuanceLocationId] = [Extent3].[Id]