-- Simple term
SELECT id, title, docexcerpt
FROM dbo.Documents
WHERE CONTAINS(docexcerpt, N'data');

 -- Simple term - phrase
SELECT id, title, docexcerpt
FROM dbo.Documents
WHERE CONTAINS(docexcerpt, N'"data warehouse"');

 -- Simple terms with logical OR
SELECT id, title, docexcerpt
FROM dbo.Documents
WHERE CONTAINS(docexcerpt, N'data OR index');

 -- Simple terms with logical AND NOT
SELECT id, title, docexcerpt
FROM dbo.Documents
WHERE CONTAINS(docexcerpt, N'data AND NOT mining');

 -- Simple terms with mny logical operators, order defined with parentheses
SELECT id, title, docexcerpt
FROM dbo.Documents
WHERE CONTAINS(docexcerpt, N'data OR (fact AND warehouse)');

 -- Prefix
SELECT id, title, docexcerpt
FROM dbo.Documents
WHERE CONTAINS(docexcerpt, N'"add*"');

 -- Simple proximity
SELECT id, title, docexcerpt
FROM dbo.Documents
WHERE CONTAINS(docexcerpt, N'NEAR(problem, data)');

 -- Proximity with max distance 5 words
SELECT id, title, docexcerpt
FROM dbo.Documents
WHERE CONTAINS(docexcerpt, N'NEAR((problem, data),5)');

-- Proximity with max distance 1 word
SELECT id, title, docexcerpt
FROM dbo.Documents
WHERE CONTAINS(docexcerpt, N'NEAR((problem, data),1)');

 -- Proximity with max distance and order
SELECT id, title, docexcerpt
FROM dbo.Documents
WHERE CONTAINS(docexcerpt, N'NEAR((problem, data),5, TRUE)');
GO

 SELECT id, title, docexcerpt
FROM dbo.Documents
WHERE CONTAINS((docexcerpt,doccontent), N'data');

 -- Inflectional forms

-- The next query does not return any rows
SELECT id, title, docexcerpt
FROM dbo.Documents
WHERE CONTAINS(docexcerpt, N'presentation');

-- The next query returns a row
SELECT id, title, docexcerpt
FROM dbo.Documents
WHERE CONTAINS(docexcerpt, N'FORMSOF(INFLECTIONAL, presentation)');
GO

 C:\...\MSSQL11.MSSQLSERVER\MSSQL\FTData

<XML ID="Microsoft Search Thesaurus">
    <thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
        <expansion>
            <sub>Internet Explorer</sub>
            <sub>IE</sub>
            <sub>IE5</sub>
        </expansion>
        <replacement>
            <pat>NT5</pat>
            <pat>W2K</pat>
            <sub>Windows 2000</sub>
        </replacement>
        <expansion>
            <sub>run</sub>
            <sub>jog</sub>
        </expansion>
        <expansion>
            <sub>need</sub>
            <sub>necessity</sub>
        </expansion>
    </thesaurus>
</XML>

 -- Load the US English file
EXEC sys.sp_fulltext_load_thesaurus_file 1033;
GO

 -- Synonyms

-- The next query does not return any rows
SELECT id, title, docexcerpt
FROM dbo.Documents
WHERE CONTAINS(docexcerpt, N'need');

-- The next query returns a row
SELECT id, title, docexcerpt
FROM dbo.Documents
WHERE CONTAINS(docexcerpt, N'FORMSOF(THESAURUS, need)');
GO

 -- Document properties
SELECT id, title, docexcerpt
FROM dbo.Documents
WHERE CONTAINS(PROPERTY(doccontent,N'Authors'), N'Test');

 -- FREETEXT
SELECT *
FROM dbo.Documents
WHERE FREETEXT(docexcerpt, N'data presentation need');

public abstract class AdoMapper<T> where T : class
{
    private readonly SqlConnection _connection;
 
    protected AdoMapper(string connectionString)
    {
        _connection = new SqlConnection(connectionString);
    }
 
    protected virtual IEnumerable<T> ExecuteCommand(SqlCommand command)
    {
        command.Connection = _connection;
        command.CommandType = CommandType.StoredProcedure;
        _connection.Open();
 
        try
        {
            var reader = command.ExecuteReader();
            try
            {
                return Mapper.Map<IDataReader, IEnumerable<T>>(reader);
            }
            finally
            {
                reader.Close();
            }
        }
        finally
        {
            _connection.Close();
        }
    }
 
    protected virtual T GetRecord(SqlCommand command)
    {
        command.Connection = _connection;
        _connection.Open();
        try
        {
            var reader = command.ExecuteReader();
            try
            {
                reader.Read();
                return Mapper.Map<IDataReader, T>(reader);
            }
            finally
            {
                reader.Close();
            }
        }
        finally
        {
            _connection.Close();
        }
    }
 
    protected virtual IEnumerable<T> GetRecords(SqlCommand command)
    {
        command.Connection = _connection;
        _connection.Open();
        try
        {
            var reader = command.ExecuteReader();
            try
            {
                return Mapper.Map<IDataReader, IEnumerable<T>>(reader);
            }
            finally
            {
                reader.Close();
            }
        }
        finally
        {
            _connection.Close();
        }
    }
}

public class UsersService : AdoMapper<User>, IUsersService
{
    public UsersService(string connectionString)
        : base(connectionString)
    {
    }
 
    public IEnumerable<User> GetAll()
    {
        using (var command = new SqlCommand("SELECT * FROM Users"))
        {
            return GetRecords(command);
        }
    }
 
    public User GetById(int id)
    {
        using (var command = new SqlCommand("SELECT * FROM Users WHERE Id = @id"))
        {
            command.Parameters.Add(new SqlParameter("id", id));
            return GetRecord(command);
        }
    }
}

public class UsersProfile : Profile
{
    protected override void Configure()
    {
        this.CreateMap<IDataRecord, User>();
    }
 
    public override string ProfileName
    {
        get { return this.GetType().Name; }
    }
}

Mapper.Initialize(cfg => // In Application_Start()
{
    cfg.AddProfile<UsersProfile>();
});

public class Advertisement
{
    public int Id { set; get; }
    public string Title { get; set; }
    public string Description { get; set; }
    public int UserId { get; set; }
 
    public string TitleWithOtherName { get; set; }
}

public class AdvertisementsProfile : Profile
{
    protected override void Configure()
    {
        this.CreateMap<IDataRecord, Advertisement>()
            .ForMember(dest => dest.TitleWithOtherName,
                       options => options.MapFrom(src =>
                            src.GetString(src.GetOrdinal("Title"))));
    }
 
    public override string ProfileName
    {
        get { return this.GetType().Name; }
    }
}

 SELECT sum([Rating_TotalRating]), sum([Rating_TotalRaters]), sum([Rating_AverageRating]) FROM [BlogPosts]

                context.BlogPost.Select(r =>
                                        new
                                        {
                                            Sum1 = r.Sum(x => x.RatingTotalRating),
                                            Sum2 = r.Sum(x => x.RatingTotalRaters),
                                            Sum3 = r.Sum(x => x.RatingAverageRating)
                                        }).FirstOrDefault();

 var sum1 = context.BlogPost.Sum(x => x.RatingTotalRating);
var sum2 = context.BlogPost.Sum(x => x.RatingTotalRaters);
var sum2 = context.BlogPost.Sum(x => x.RatingAverageRating);

                context.BlogPost
                       .GroupBy(dummyNumber => 0)
                       .Select(r =>
                                        new
                                        {
                                            Sum1 = r.Sum(x => x.RatingTotalRating),
                                            Sum2 = r.Sum(x => x.RatingTotalRaters),
                                            Sum3 = r.Sum(x => x.RatingAverageRating)
                                        }).FirstOrDefault();

SELECT TOP (1) 
                        [Extent1].[K1] AS [K1], 
                        Sum([Extent1].[A1]) AS [A1], 
                        Sum([Extent1].[A2]) AS [A2],
                        Sum([Extent1].[A3]) AS [A3]
                        FROM ( SELECT 
                            0 AS [K1], 
                            [Extent1].[RatingTotalRating] AS [A1], 
                            [Extent1].[RatingTotalRaters] AS [A2],
       [Extent1].[RatingAverageRating] AS [A3]
                            FROM [dbo].[BlogPosts] AS [Extent1]
                        )  AS [Extent1]
                        GROUP BY [K1]

namespace SplittingTableSample.DomainClasses
{
    public class Post
    {
        public virtual int Id { get; set; }
        public virtual string Title { get; set; }
        public virtual DateTime CreatedDate { get; set; }
        public virtual string  Body { get; set; }
        public virtual byte[] Image { get; set; }
    }
}

            using (var context = new MyDbContext())
            {
                var post = context.Posts.Find(1);
            }

exec sp_executesql N'SELECT TOP (2) 
[Extent1].[Id] AS [Id], 
[Extent1].[Title] AS [Title], 
[Extent1].[CreatedDate] AS [CreatedDate], 
[Extent1].[Body] AS [Body], 
[Extent1].[Image] AS [Image]
FROM [dbo].[Posts] AS [Extent1]
WHERE [Extent1].[Id] = @p0',N'@p0 int',@p0=1

namespace SplittingTableSample.DomainClasses
{
    public class Post
    {
        public virtual int Id { get; set; }
        public virtual string Title { get; set; }
        public virtual DateTime CreatedDate { get; set; }
        public virtual PostMetaData PostMetaData { get; set; }
    }
}
namespace SplittingTableSample.DomainClasses
{
    public class PostMetaData
    {
        public virtual int PostId { get; set; }
        public virtual string Body { get; set; }
        public virtual byte[] Image { get; set; }
        public virtual Post Post { get; set; }
    }
}

namespace SplittingTableSample.DomainClasses
{
    public class PostMetaDataConfig : EntityTypeConfiguration<PostMetaData>
    {
        public PostMetaDataConfig()
        {
            HasKey(x => x.PostId);
            HasRequired(x => x.Post).WithRequiredDependent(x => x.PostMetaData);
        }
    }
}

var post = context.Posts.Find(1);

exec sp_executesql N'SELECT TOP (2) 
[Extent1].[Id] AS [Id], 
[Extent1].[Title] AS [Title], 
[Extent1].[CreatedDate] AS [CreatedDate]
FROM [dbo].[Posts] AS [Extent1]
WHERE [Extent1].[Id] = @p0',N'@p0 int',@p0=1

namespace SplittingTableSample.DomainClasses
{
    public class PostConfig : EntityTypeConfiguration<Post>
    {
        public PostConfig()
        {
            ToTable("Posts");
        }
    }
}
namespace SplittingTableSample.DomainClasses
{
    public class PostMetaDataConfig : EntityTypeConfiguration<PostMetaData>
    {
        public PostMetaDataConfig()
        {
            ToTable("Posts");
            HasKey(x => x.PostId);
            HasRequired(x => x.Post).WithRequiredDependent(x => x.PostMetaData);
        }
    }
}

var post = context.Posts.Find(1);
Console.WriteLine(post.PostMetaData.Body);

exec sp_executesql N'SELECT TOP (2) 
[Extent1].[Id] AS [Id], 
[Extent1].[Title] AS [Title], 
[Extent1].[CreatedDate] AS [CreatedDate]
FROM [dbo].[Posts] AS [Extent1]
WHERE [Extent1].[Id] = @p0',N'@p0 int',@p0=1

exec sp_executesql N'SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Body] AS [Body], 
[Extent1].[Image] AS [Image]
FROM [dbo].[Posts] AS [Extent1]
WHERE [Extent1].[Id] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

var post = context.Posts.Include(x => x.PostMetaData).SingleOrDefault(x => x.Id == 1);

SELECT 
[Limit1].[Id] AS [Id], 
[Limit1].[Title] AS [Title], 
[Limit1].[CreatedDate] AS [CreatedDate], 
[Extent2].[Id] AS [Id1], 
[Extent2].[Body] AS [Body], 
[Extent2].[Image] AS [Image]
FROM   (SELECT TOP (2) [Extent1].[Id] AS [Id], [Extent1].[Title] AS [Title], [Extent1].[CreatedDate] AS [CreatedDate]
FROM [dbo].[Posts] AS [Extent1]
WHERE 1 = [Extent1].[Id] ) AS [Limit1]
LEFT OUTER JOIN [dbo].[Posts] AS [Extent2] ON [Limit1].[Id] = [Extent2].[Id]

 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)'