SELECT * FROM [SQL2].[test].[dbo].[emp]

 SELECT * from OPENQUERY([SQL2], 'SELECT * FROM [test].[dbo].[emp]')

SELECT * FROM [نام Linked Server].[AdventureWorks].[Person].[Person]
GO
SELECT * FROM OPENQUERY([نام Linked Server ],'SELECT * FROM [AdventureWorks].[Person].[Person]')

SELECT * FROM [نام Linked Server] .[AdventureWorks].[Person].[Address]
GO
SELECT * FROM OPENQUERY([نام Linked Server] ,'SELECT * FROM [AdventureWorks].[Person].[Address]')

INSERT INTO User_Table(Id,Username,Password,FirstName,SureName,PhoneNumber,x,Email)
VALUES (1,'VahidN','123456','Vahid','Nasiri','09120000000','vahid_xxx@example.com')

public void Insert(User user)
{
  _users.Insert(user);
  db.Save();
}

public ICollection<User> GetUsers(string firstName,string lastName,Func<User, bool> orderBy)
{
    var query = _users.where(u => u.LastName.StartsWith(lastName));
    query = query.where(u => u.FirstName.StartsWith(firstName));
    query = query.OrderBy(orderBy);
    return  query.ToList();
}

CREATE PROCEDURE DynamicWhere 
    @LastName varchar(50) = null,
    @FirstName varchar(50) = null,
    @Orderby varchar(50) = null
AS
BEGIN
    DECLARE @where nvarchar(max)
    SELECT @where = '1 = 1'
 
    IF @LastName IS NOT NULL
        SELECT @Where = @Where + " AND A.LastName LIKE @LastName + '%'"
 
    IF @FirstName IS NOT NULL
        SELECT @Where = @Where + " AND A.FirstName LIKE @FirstName + '%'"
 
    DECLARE @orderBySql nvarchar(max)
    SELECT @orderBySql = CASE
        WHEN @OrderBy = "LastName" THEN "A.LastName"
        ELSE @OrderBy = "FirstName" THEN "A.FirstName"
    END
 
    DECLARE @sql nvarchar(max)
    SELECT @sql = "
    SELECT A.Id , A.AccountNoId, A.LastName, A.FirstName, A.PostingDt, 
    A.BillingAmount
    FROM Users 
    WHERE " + @where + " 
    ORDER BY " + @orderBySql
 
    exec sp_executesql @sql,  N'@LastName varchar(50), @FirstName varchar(50)
        @LastName, @FirstName
END

 PM> install-package EntityFrameworkWithHierarchyId

public class Employee
{
    public int Id { get; set; }
 
    [Required, MaxLength(100)]
    public string Name { get; set; }
 
    [Required]
    public HierarchyId Node { get; set; } // نوع داده جدید
}

public class MyContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }
 
    public MyContext()
        : base("Connection1")
    {
        this.Database.Log = log => Console.WriteLine(log);
    }
}
 
public class Configuration : DbMigrationsConfiguration<MyContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
        AutomaticMigrationDataLossAllowed = true;
    }
 
    protected override void Seed(MyContext context)
    {
        if (context.Employees.Any())
            return;
 
        context.Database.ExecuteSqlCommand(
            "ALTER TABLE [dbo].[Employees] ADD NodePath as Node.ToString() persisted");
        context.Database.ExecuteSqlCommand(
            "ALTER TABLE [dbo].[Employees] ADD Level AS Node.GetLevel() persisted");
        context.Database.ExecuteSqlCommand(
            "ALTER TABLE [dbo].[Employees] ADD ManagerNode as Node.GetAncestor(1) persisted");
        context.Database.ExecuteSqlCommand(
            "ALTER TABLE [dbo].[Employees] ADD ManagerNodePath as Node.GetAncestor(1).ToString() persisted");
 
        context.Database.ExecuteSqlCommand(
            "ALTER TABLE [dbo].[Employees] ADD CONSTRAINT [UK_EmployeeNode] UNIQUE NONCLUSTERED (Node)");
        context.Database.ExecuteSqlCommand(
            "ALTER TABLE [dbo].[Employees]  WITH CHECK ADD CONSTRAINT [EmployeeManagerNodeNodeFK] " +
            "FOREIGN KEY([ManagerNode]) REFERENCES [dbo].[Employees] ([Node])");
 
        context.Employees.Add(new Employee { Name = "Root", Node = new HierarchyId("/") });
        context.Employees.Add(new Employee { Name = "Emp1", Node = new HierarchyId("/1/") });
        context.Employees.Add(new Employee { Name = "Emp2", Node = new HierarchyId("/2/") });
        context.Employees.Add(new Employee { Name = "Emp3", Node = new HierarchyId("/1/1/") });
        context.Employees.Add(new Employee { Name = "Emp4", Node = new HierarchyId("/1/1/1/") });
        context.Employees.Add(new Employee { Name = "Emp5", Node = new HierarchyId("/2/1/") });
        context.Employees.Add(new Employee { Name = "Emp6", Node = new HierarchyId("/1/2/") });
 
        base.Seed(context);
    }
}

using (var context = new MyContext())
{
    Console.WriteLine("\ngetItems OrderByDescending(employee => employee.Node)");
 
    var employees = context.Employees.OrderByDescending(employee => employee.Node).ToList();
    foreach (var employee in employees)
    {
        Console.WriteLine("{0} {1}", employee.Id, employee.Node);
    }
 }

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[Node] AS [Node]
    FROM [dbo].[Employees] AS [Extent1]
    ORDER BY [Extent1].[Node] DESC


6 /2/1/
3 /2/
7 /1/2/
5 /1/1/1/
4 /1/1/
2 /1/
1 /

using (var context = new MyContext())
{
    Console.WriteLine("\nGetAncestor(1) of /1/");
 
    var firstItem = context.Employees.Single(employee => employee.Node == new HierarchyId("/1/"));
    foreach (var item in context.Employees.Where(employee => firstItem.Node == employee.Node.GetAncestor(1)))
    {
        Console.WriteLine("{0} {1}", item.Id, item.Name);
    }
}

SELECT TOP (2)
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[Node] AS [Node]
    FROM [dbo].[Employees] AS [Extent1]
    WHERE cast('/1/' as hierarchyid) = [Extent1].[Node]

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[Node] AS [Node]
    FROM [dbo].[Employees] AS [Extent1]
    WHERE (@p__linq__0 = ([Extent1].[Node].GetAncestor(1))) OR ((@p__linq__0 IS
NULL) AND ([Extent1].[Node].GetAncestor(1) IS NULL))
-- p__linq__0: '/1/' (Type = Object)

4 Emp3
7 Emp6

var list = context.Employees.Where(
          employee => employee.Node.IsDescendantOf(new HierarchyId("/1/")) &&
                              employee.Node.GetLevel() == 2).ToList();

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[Node] AS [Node]
    FROM [dbo].[Employees] AS [Extent1]
    WHERE (([Extent1].[Node].IsDescendantOf(cast('/1/' as hierarchyid))) = 1) 
    AND (2 = ([Extent1].[Node].GetLevel()))

var items = context.Employees.Where(employee => employee.Node.IsDescendantOf(new HierarchyId("/1/")))
    .Select(employee => new
    {
        Id = employee.Id,
        OrigPath = employee.Node,
        ReparentedValue = employee.Node.GetReparentedValue(new HierarchyId("/1/"), HierarchyId.GetRoot()),
        Level = employee.Node.GetLevel()
    }).ToList();
 
foreach (var item in items)
{
    Console.WriteLine("Id:{0}; OrigPath:{1}; ReparentedValue:{2}; Level:{3}", item.Id, item.OrigPath, item.ReparentedValue, item.Level);
}

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Node] AS [Node],
    [Extent1].[Node].GetReparentedValue(cast('/1/' as hierarchyid), hierarchyid::GetRoot()) AS [C1],
    [Extent1].[Node].GetLevel() AS [C2]
    FROM [dbo].[Employees] AS [Extent1]
    WHERE ([Extent1].[Node].IsDescendantOf(cast('/1/' as hierarchyid))) = 1


Id:2; OrigPath:/1/; ReparentedValue:/; Level:1
Id:4; OrigPath:/1/1/; ReparentedValue:/1/; Level:2
Id:5; OrigPath:/1/1/1/; ReparentedValue:/1/1/; Level:3
Id:7; OrigPath:/1/2/; ReparentedValue:/2/; Level:2

 Prefix - Namespace
xml = http://www.w3.org/XML/1998/namespace
xs = http://www.w3.org/2001/XMLSchema
xsi = http://www.w3.org/2001/XMLSchema-instance
fn = http://www.w3.org/2004/07/xpath-functions
sqltypes = http://schemas.microsoft.com/sqlserver/2004/sqltypes
xdt = http://www.w3.org/2004/07/xpath-datatypes
(no prefix) = urn:schemas-microsoft-com:xml-sql
(no prefix) = http://schemas.microsoft.com/sqlserver/2004/SOAP

  <xsd:simpleType name="char">
    <xsd:restriction base="xsd:string"/>
  </xsd:simpleType>
  <xsd:simpleType name="nchar">
    <xsd:restriction base="xsd:string"/>
  </xsd:simpleType>
  <xsd:simpleType name="varchar">
    <xsd:restriction base="xsd:string"/>
  </xsd:simpleType>
  <xsd:simpleType name="nvarchar">
    <xsd:restriction base="xsd:string"/>
  </xsd:simpleType>
  <xsd:simpleType name="text">
    <xsd:restriction base="xsd:string"/>
  </xsd:simpleType>
  <xsd:simpleType name="ntext">
    <xsd:restriction base="xsd:string"/>
  </xsd:simpleType>

  <xsd:simpleType name="datetime">
    <xsd:restriction base="xsd:dateTime">
      <xsd:pattern value="((000[1-9])|(00[1-9][0-9])|(0[1-9][0-9]{2})|([1-9][0-9]{3}))-((0[1-9])|(1[012]))-((0[1-9])|([12][0-9])|(3[01]))T(([01][0-9])|(2[0-3]))(:[0-5][0-9]){2}(\.[0-9]{2}[037])?"/>
      <xsd:maxInclusive value="9999-12-31T23:59:59.997"/>
      <xsd:minInclusive value="1753-01-01T00:00:00.000"/>
    </xsd:restriction>
  </xsd:simpleType>

CREATE XML SCHEMA COLLECTION invcol AS
'<xs:schema ... targetNamespace="urn:invoices">
...
</xs:schema>
'

CREATE TABLE Invoices(
id int IDENTITY PRIMARY KEY,
invoice XML(invcol)
)

CREATE XML SCHEMA COLLECTION geocol AS
'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           targetNamespace="urn:geo"
           elementFormDefault="qualified"
           xmlns:tns="urn:geo">
  <xs:simpleType name="dim">
    <xs:restriction base="xs:int" />
  </xs:simpleType>
  <xs:complexType name="Point">
    <xs:sequence>
      <xs:element name="X" type="tns:dim"  minOccurs="0" maxOccurs="unbounded" />
      <xs:element name="Y" type="tns:dim"  minOccurs="0" maxOccurs="unbounded" />
    </xs:sequence>
  </xs:complexType>
  <xs:element name="Point" type="tns:Point" />
</xs:schema>'

 declare @geo_tab table(
 id int identity primary key,
 point xml(content geocol)
)
 
insert into @geo_tab values('<Point xmlns="urn:geo"><X>10</X><Y>20</Y></Point>')
insert into @geo_tab values('<Point xmlns="urn:geo"><X>10</X><Y>test</Y></Point>')

 XML Validation: Invalid simple type value: 'test'. Location: /*:Point[1]/*:Y[1]

 select * from sys.xml_schema_collections
select * from sys.xml_schema_namespaces
select * from sys.xml_schema_elements
select * from sys.xml_schema_attributes
select * from sys.xml_schema_types
select * from sys.column_xml_schema_collection_usages
select * from sys.parameter_xml_schema_collection_usages

 declare @x xml
select @x = xml_schema_namespace(N'dbo', N'geocol')
print convert(varchar(max), @x)

 alter XML SCHEMA COLLECTION geocol add @x

 declare @x xml
set @x = (select * from openrowset(bulk 'c:\path\file.xsd', single_blob) as x)
CREATE XML SCHEMA COLLECTION geocol2 AS @x

    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]

SELECT * FROM table
ORDER BY NEWID()

using System;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;

namespace Sample
{
    public class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }

    public class MyContext : DbContext
    {
        public DbSet<User> Users { get; set; }
    }

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

        protected override void Seed(MyContext context)
        {
            context.Users.Add(new User { Name = "User 1", Age = 20 });
            context.Users.Add(new User { Name = "User 2", Age = 25 });
            context.Users.Add(new User { Name = "User 3", Age = 30 });
            context.Users.Add(new User { Name = "User 4", Age = 35 });
            context.Users.Add(new User { Name = "User 5", Age = 40 });
            base.Seed(context);
        }
    }

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

            using (var context = new MyContext())
            {
               var randomListOfUsers =
                        context.Users
                               .Where(person => person.Age >= 25 && person.Age < 40)
                               .OrderBy(person => Guid.NewGuid())
                               .ToList();

               foreach (var person in randomListOfUsers)
                   Console.WriteLine("{0}:{1}", person.Name, person.Age);
            }
        }
    }
}

.OrderBy(person => Guid.NewGuid())

ORDER BY NEWID()

SELECT 
[Project1].[Id] AS [Id], 
[Project1].[Name] AS [Name], 
[Project1].[Age] AS [Age]
FROM ( SELECT 
NEWID() AS [C1], ------ Guid created here
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
[Extent1].[Age] AS [Age]
FROM [dbo].[Users] AS [Extent1]
WHERE ([Extent1].[Age] >= 25) AND ([Extent1].[Age] < 40)
)  AS [Project1]
ORDER BY [Project1].[C1] ASC  ------ Used for sorting here

public class Location   
{ 
     public string City { get; set; } 
     public string State { get; set; } 
 
     public Location(string city, string state) 
     { 
           City = city; 
           State = state; 
     } 
}

 var location = new Location("Lake Charles","LA");

 var location = new Tuple<string,string>("Lake Charles","LA");   
// Print out the address
var address = $"{location.Item1}, {location.Item2}";

static Tuple<int, string, string> GetHumanData()
{
   return Tuple.Create(10, "Marcus", "Miller");
}

 var data = GetHumanData();
Console.WriteLine("What is this value {0} or this {1}",  data.Item1, data.Item3);

(int x1, string s1) = (3, "one");
Console.WriteLine($"{x1} {s1}");

 error CS8179: Predefined type 'System.ValueTuple`2' is not defined or imported

 PM> install-package System.ValueTuple

int x2;
string s2;
(x2, s2) = (42, "two");
Console.WriteLine($"{x2} {s2}");

static (int, int) Divide(int x, int y)
{
   int result = x / y;
   int reminder = x % y;
 
   return (result, reminder);
}

 (int result, int reminder) = Divide(11, 3);
Console.WriteLine($"{result} {reminder}");

 (var result1, var reminder1) = Divide(11, 3);
Console.WriteLine($"{result1} {reminder1}");

 var (result1, reminder1) = Divide(11, 3);

static (int, string, string) GetHumanData()
{
   return (10, "Marcus", "Miller");
}

 (int Age, string FirstName, string LastName) results = GetHumanData();
Console.WriteLine(results.Age);
Console.WriteLine(results.FirstName);
Console.WriteLine(results.LastName);

 ValueTuple<int, int> tuple1 = Divide(11, 3);

 (int, int) n = (1,1);
System.Console.WriteLine(n.Item1);

 ValueTuple<int, int> n = new ValueTuple<int, int>(1, 1);
System.Console.WriteLine(n.Item1);

 (int,int,int,int,int,int,int,(int,int))

 var tuple2 = ("Stephanie", 7);
Console.WriteLine($"{tuple2.Item1}, {tuple2.Item2}");

 var tuple3 = (Name: "Matthias", Age: 6);
Console.WriteLine($"{tuple3.Name} {tuple3.Age}");

static (int radius, double area) CalculateAreaOfCircle(int radius)
{
   return (radius, Math.PI * Math.Pow(radius, 2));
}

 var circle = CalculateAreaOfCircle(2);
Console.WriteLine($"A circle of radius, {circle.radius}," +
 $" has an area of {circle.area:N2}.");

class Person
{
    private readonly string _firstName;
    private readonly string _lastName;
 
    public Person(string firstname, string lastname)
    {
        _firstName = firstname;
        _lastName = lastname;
    }
 
    public override String ToString() => $"{_firstName} {_lastName}";
 
    public void Deconstruct(out string firstname, out string lastname)
    {
        firstname = _firstName;
        lastname = _lastName;
    }
}

 var p1 = new Person("Katharina", "Nagel");
(string first, string last) = p1;
Console.WriteLine($"{first} {last}");

public class Rectangle
{
    public Rectangle(int height, int width)
    {
        Height = height;
        Width = width;
    }
 
    public int Width { get; }
    public int Height { get; }
}
 
public static class RectangleExtensions
{
    public static void Deconstruct(this Rectangle rectangle, out int height, out int width)
    {
        height = rectangle.Height;
        width = rectangle.Width;
    }
}

 var r1 = new Rectangle(100, 200);
(int height, int width) = r1;
Console.WriteLine($"height: {height}, width: {width}");

List<Employee> allEmployees = new List<Employee>()
{
  new Employee { ID = 1L, Name = "Fred", Salary = 50000M },
  new Employee { ID = 2L, Name = "Sally", Salary = 60000M },
  new Employee { ID = 3L, Name = "George", Salary = 70000M }
};
var wellPaid =
  from oneEmployee in allEmployees
  where oneEmployee.Salary > 50000M
  select new { EmpName = oneEmployee.Name,
               Income = oneEmployee.Salary };

var wellPaid =
  from oneEmployee in allEmployees
  where oneEmployee.Salary > 50000M
  orderby oneEmployee.Salary descending
  select (EmpName: oneEmployee.Name,
          Income: oneEmployee.Salary);
var highestPaid = wellPaid.First().EmpName;

public Task<(int index, T item)> FindAsync<T>(IEnumerable<T> input, Predicate<T> match)
{
   var dictionary = new Dictionary<(int, int), string>();
   throw new NotSupportedException();
}

var citiesContainPerson = context.Cities.Where(city => city.People.Any(person => person.Name == "user-1")).ToList();

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name]
FROM [dbo].[Cities] AS [Extent1]
WHERE  EXISTS (SELECT 
1 AS [C1]
FROM [dbo].[People] AS [Extent2]
WHERE ([Extent1].[Id] = [Extent2].[BornInCityId]) AND (N'user-1' = [Extent2].[Name])
)