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 Person
    {
        public int Id { get; set; }
        public string Name { get; set; }

        [ForeignKey("BornInCityId")]
        public virtual City BornInCity { get; set; }
        public int BornInCityId { get; set; }
    }

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

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

    public class MyContext : DbContext
    {
        public DbSet<City> Cities { get; set; }
        public DbSet<Person> People { get; set; }
    }

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

        protected override void Seed(MyContext context)
        {
            var city1 = new City { Name = "city-1" };
            var city2 = new City { Name = "city-2" };
            context.Cities.Add(city1);
            context.Cities.Add(city2);

            var person1 = new Person { Name = "user-1", BornInCity = city1 };
            var person2 = new Person { Name = "user-2", BornInCity = city1 };
            context.People.Add(person1);
            context.People.Add(person2);

            base.Seed(context);
        }
    }

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

            using (var context = new MyContext())
            {
                var peopleAndCitiesList = from person in context.People
                           join city in context.Cities
                           on person.BornInCityId equals city.Id
                           select new
                           {
                              PersonName = person.Name,
                              CityName = city.Name
                           };

                foreach (var item in peopleAndCitiesList)
                {
                    Console.WriteLine("{0}:{1}", item.PersonName, item.CityName);
                }
            }
        }
    }

SELECT 
          [Extent1].[BornInCityId] AS [BornInCityId], 
          [Extent1].[Name] AS [Name], 
          [Extent2].[Name] AS [Name1]
FROM  [dbo].[People] AS [Extent1]
INNER JOIN [dbo].[Cities] AS [Extent2] ON [Extent1].[BornInCityId] = [Extent2].[Id]

var peopleAndCitiesList = context.People
                                  .Select(person => new
                                                         {
                                                             PersonName = person.Name,
                                                             CityName = person.BornInCity.Name
                                                         });

var citiesList = context.Cities.OrderByDescending(x => x.People.Count());
foreach (var item in citiesList)
{
    Console.WriteLine("{0}", item.Name);
}

SELECT 
[Project1].[Id] AS [Id], 
[Project1].[Name] AS [Name]
FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Name] AS [Name], 
        (SELECT 
                COUNT(1) AS [A1]
                FROM [dbo].[People] AS [Extent2]
                WHERE [Extent1].[Id] = [Extent2].[BornInCityId]) AS [C1]
        FROM [dbo].[Cities] AS [Extent1]
)  AS [Project1]
ORDER BY [Project1].[C1] DESC

 var peopleAndCitiesList = context.Cities
                                     .Select(city => new
                                                 {
                                                     InUseCount = city.People.Count(),
                                                     CityName = city.Name
                                                 });

foreach (var item in peopleAndCitiesList)
{
     Console.WriteLine("{0}:{1}", item.CityName, item.InUseCount);
}

SELECT 
[Extent1].[Id] AS [Id], 
(SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[People] AS [Extent2]
        WHERE [Extent1].[Id] = [Extent2].[BornInCityId]) AS [C1], 
[Extent1].[Name] AS [Name]
FROM [dbo].[Cities] AS [Extent1]

/* Identify SQL Server Collation Settings*/
USE Master
GO
SELECT SERVERPROPERTY('collation') AS SQLServerCollation
GO

پیش زمینه: سیستم عضویت در ASP.NET

[HttpPost]
[AllowAnonymous]
[ValidateAntiForgeryToken]
public async Task<ActionResult> Register(RegisterViewModel model)
{
    if (ModelState.IsValid)
    {
        var user = new ApplicationUser() { UserName = model.UserName };
        var result = await UserManager.CreateAsync(user, model.Password);
        if (result.Succeeded)
        {
            await SignInAsync(user, isPersistent: false);
            return RedirectToAction("Index", "Home");
        }
        else
        {
            AddErrors(result);
        }
    }

    // If we got this far, something failed, redisplay form
    return View(model);
}

[HttpPost]
[AllowAnonymous]
[ValidateAntiForgeryToken]
public async Task<ActionResult> Register(RegisterViewModel model)
{
    if (ModelState.IsValid)
    {
        var user = new ApplicationUser() { UserName = model.UserName };
        var result = await UserManager.CreateAsync(user, model.Password);
        if (result.Succeeded)
        {
            await SignInAsync(user, isPersistent: false);
            return RedirectToAction("Index", "Home");
        }
        else
        {
            AddErrors(result);
        }
    }

    // If we got this far, something failed, redisplay form
    return View(model);
}

private async Task SignInAsync(ApplicationUser user, bool isPersistent)
{
    AuthenticationManager.SignOut(DefaultAuthenticationTypes.ExternalCookie);

    var identity = await UserManager.CreateIdentityAsync(
       user, DefaultAuthenticationTypes.ApplicationCookie);

    AuthenticationManager.SignIn(
       new AuthenticationProperties() { 
          IsPersistent = isPersistent 
       }, identity);
}

// POST: /Account/LogOff
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult LogOff()
{
    AuthenticationManager.SignOut();
    return RedirectToAction("Index", "Home");
}

public abstract class BaseEntity
{
    public int Id { set; get; }
}

public class User : BaseEntity
{
        public string Name { set; get; }
 
        public virtual ICollection<Advertisement> Advertisements { get; set; }
}

public class Advertisement : BaseEntity
{
    public string Title { get; set; }
    public string Description { get; set; }
 
    [ForeignKey("UserId")]
    public virtual User User { get; set; }
    public int UserId { get; set; }
}

public class AdvertisementViewModel
{
    public int Id { get; set; }
    public string Title { get; set; }
    public int UserId { get; set; }
}
 
public class UserViewModel
{
    public int Id { set; get; }
    public string Name { set; get; }
    public List<AdvertisementViewModel> Advertisements { get; set; }
}

this.CreateMap<AdvertisementViewModel, Advertisement>()
      .ForMember(advertisement => advertisement.Description, opt => opt.Ignore())
      .ForMember(advertisement => advertisement.User, opt => opt.Ignore());

var uiUser1 = new UserViewModel
{
    Id = 1,
    Name = "user 1",
    Advertisements = new List<AdvertisementViewModel>
    {
        new AdvertisementViewModel
        {
            Id = 1,
            Title = "Adv 1",
            UserId = 1
        },
        new AdvertisementViewModel
        {
            Id = 2,
            Title = "Adv 2",
            UserId = 1
        }
    }
};

 var dbUser1 = ctx.Users.Include(user => user.Advertisements).First(x => x.Id == uiUser1.Id);
Mapper.Map(source: uiUser1, destination: dbUser1);

 this.CreateMap<UserViewModel, User>()

this.CreateMap<UserViewModel, User>().ForMember(user => user.Advertisements, opt => opt.Ignore());

using (var ctx = new MyContext())
{
    var dbUser1 = ctx.Users.Include(user => user.Advertisements).First(x => x.Id == uiUser1.Id);
    Mapper.Map(source: uiUser1, destination: dbUser1);
 
    foreach (var uiUserAdvertisement in uiUser1.Advertisements)
    {
        var dbUserAdvertisement = dbUser1.Advertisements.FirstOrDefault(ad => ad.Id == uiUserAdvertisement.Id);
        if (dbUserAdvertisement == null)
        {
            // Add new record
            var advertisement = Mapper.Map<AdvertisementViewModel, Advertisement>(uiUserAdvertisement);
            dbUser1.Advertisements.Add(advertisement);
        }
        else
        {
            // Update the existing record
            Mapper.Map(uiUserAdvertisement, dbUserAdvertisement);
        }
    }
 
    ctx.SaveChanges();
}