private static void seedDb(ApplicationDbContext context)
        {
            if (!context.Chapters.Any())
            {
                var user1 = context.Users.Add(new User { Name = "Test User" });
                context.Chapters.Add(new Chapter
                {
                    Title = "Learn SQlite FTS5",
                    Text = "This tutorial teaches you how to perform full-text search in SQLite using FTS5",
                    User = user1.Entity
                });
                context.Chapters.Add(new Chapter
                {
                    Title = "Advanced SQlite Full-text Search",
                    Text = "Show you some advanced techniques in SQLite full-text searching",
                    User = user1.Entity
                });
                context.Chapters.Add(new Chapter
                {
                    Title = "SQLite Tutorial",
                    Text = "Help you learn SQLite quickly and effectively",
                    User = user1.Entity
                });
                context.Chapters.Add(new Chapter
                {
                    Title = "Handle markup in text",
                    Text = "<p>Isn't this <font face=\"Comic Sans\">funny</font>?",
                    User = user1.Entity
                });

                context.Chapters.Add(new Chapter
                {
                    Title = "آزمایش متن فارسی",
                    Text = "برای نمونه تهیه شده‌است",
                    User = user1.Entity
                });

                context.Chapters.Add(new Chapter
                {
                    Title = "Exclude test 1",
                    Text = "in the years 2018-2019 something happened.",
                    User = user1.Entity
                });
                context.Chapters.Add(new Chapter
                {
                    Title = "Exclude test 2",
                    Text = "It was 2018 and then it was 2019",
                    User = user1.Entity
                });

                context.SaveChanges();
            }
        }

select * from Chapters_FTS where Chapters_FTS match "fts5"

SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "fts5"

CREATE VIRTUAL TABLE "Chapters_FTS"
USING fts5("Text", "Title", content="Chapters", content_rowid="Id")

select * from Chapters_FTS where Chapters_FTS match "fts5" ORDER BY bm25(fts);
select * from Chapters_FTS where Chapters_FTS match "fts5" ORDER BY rank;

namespace EFCoreSQLiteFTS.Entities
{
    public class ChapterFTS
    {
        public int RowId { get; set; }
        public decimal? Rank { get; set; }

        public string Title { get; set; }
        public string Text { get; set; }
    } 
}

namespace EFCoreSQLiteFTS.DataLayer
{
    public class ApplicationDbContext : DbContext
    {
        //...

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);

            builder.Entity<ChapterFTS>().HasNoKey().ToView(null);
        }

        //...
    }
}

const string ftsSql = "SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH {0}";
foreach (var chapter in context.Set<ChapterFTS>().FromSqlRaw(ftsSql, "fts5"))
{
  Console.WriteLine($"Title: {chapter.Title}");
  Console.WriteLine($"Text: {chapter.Text}");
}

SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "fts5";
SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS = "fts5";

SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "fts5" ORDER by rank;

SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "learn SQLite" ORDER by rank;
SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "learn + SQLite" ORDER by rank;

 SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "search*" ORDER by rank;

SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "learn text" ORDER by rank;

SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "learn NOT text" ORDER by rank;

SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "search AND sqlite OR help" ORDER by rank;

SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "search AND (sqlite OR help)" ORDER by rank;

SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "text:some AND title:sqlite" ORDER by rank;

"in the years 2018-2019 something happened"
"It was 2018 and then it was 2019"

SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "2018-2019" ORDER by rank;

Execution finished with errors.
Result: no such column: 2019

SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH '"2018-2019"' ORDER by rank;

SELECT rowid, title, text, rank FROM Chapters_FTS WHERE Chapters_FTS MATCH "آزمایش"
ORDER by rank;

SELECT rowid, highlight(Chapters_FTS, title, '<b>', '</b>') as title,
snippet(Chapters_FTS, text, '<b>', '</b>', '...', 64) as text, rank FROM Chapters_FTS
WHERE Chapters_FTS MATCH "fts5" ORDER BY rank

namespace EFCorePgExercises.Entities
{
    public class Booking
    {
       // ...

        public int MemId { set; get; }
        public virtual Member Member { set; get; }

       // ...
    }
}

var startTimes = context.Bookings
                        .Where(booking => booking.Member.FirstName == "David"
                                            && booking.Member.Surname == "Farrell")
                        .Select(booking => new { booking.StartTime })
                        .ToList();

namespace EFCorePgExercises.Entities
{
    public class Booking
    {
       // ...

        public int FacId { set; get; }
        public virtual Facility Facility { set; get; }

       // ...
    }
}

int[] tennisCourts = { 0, 1 };
var date1 = new DateTime(2012, 09, 21);
var date2 = new DateTime(2012, 09, 22);
var startTimes = context.Bookings
                        .Where(booking => tennisCourts.Contains(booking.Facility.FacId)
                                && booking.StartTime >= date1
                                && booking.StartTime < date2)
                        .Select(booking => new { booking.StartTime, booking.Facility.Name })
                        .ToList();

namespace EFCorePgExercises.Entities
{
    public class Member
    {
       // ...

        public virtual ICollection<Member> Children { get; set; }
        public virtual Member Recommender { set; get; }
        public int? RecommendedBy { set; get; }

       // ...
    }
}

var members = context.Members
                        .Where(member => member.Recommender != null)
                        .Select(member => new { member.Recommender.FirstName, member.Recommender.Surname })
                        .Distinct()
                        .OrderBy(member => member.Surname).ThenBy(member => member.FirstName)
                        .ToList();

var members = context.Members
                        .Select(member => new
                        {
                            memFName = member.FirstName,
                            memSName = member.Surname,
                            recFName = member.Recommender.FirstName ?? "",
                            recSName = member.Recommender.Surname ?? ""
                        })
                        .OrderBy(member => member.memSName).ThenBy(member => member.memFName)
                        .ToList();

A LEFT JOIN B            A LEFT OUTER JOIN B
A RIGHT JOIN B           A RIGHT OUTER JOIN B
A FULL JOIN B            A FULL OUTER JOIN B
A INNER JOIN B           A JOIN B

namespace EFCorePgExercises.Entities
{
    public class Booking
    {
       // ...

        public int FacId { set; get; }
        public virtual Facility Facility { set; get; }

        public int MemId { set; get; }
        public virtual Member Member { set; get; }

       // ...
    }
}

namespace EFCorePgExercises.Entities
{
    public class Member
    {
       // ...

        public virtual ICollection<Booking> Bookings { set; get; }
    }
}

int[] tennisCourts = { 0, 1 };
var members = context.Members
                        .SelectMany(x => x.Bookings)
                        .Where(booking => tennisCourts.Contains(booking.Facility.FacId))
                        .Select(booking => new
                        {
                            Member = booking.Member.FirstName + " " + booking.Member.Surname,
                            Facility = booking.Facility.Name
                        })
                        .Distinct()
                        .OrderBy(x => x.Member)
                        .ToList();

var date1 = new DateTime(2012, 09, 14);
var date2 = new DateTime(2012, 09, 15);

var items = context.Members
                        .SelectMany(x => x.Bookings)
                        .Where(booking => booking.StartTime >= date1 && booking.StartTime < date2
                        && (
                            (((booking.Slots * booking.Facility.GuestCost) > 30) && (booking.MemId == 0)) ||
                            (((booking.Slots * booking.Facility.MemberCost) > 30) && (booking.MemId != 0))
                        ))
                        .Select(booking => new
                        {
                            Member = booking.Member.FirstName + " " + booking.Member.Surname,
                            Facility = booking.Facility.Name,
                            Cost = booking.MemId == 0 ?
                                        booking.Slots * booking.Facility.GuestCost
                                        : booking.Slots * booking.Facility.MemberCost
                        })
                        .Distinct()
                        .OrderByDescending(x => x.Cost)
                        .ToList();

var members = context.Members
                        .Select(member =>
                        new
                        {
                            Member = member.FirstName + " " + member.Surname,
                            Recommender = context.Members
                                .Where(recommender => recommender.MemId == member.RecommendedBy)
                                .Select(recommender => recommender.FirstName + " " + recommender.Surname)
                                .FirstOrDefault() ?? ""
                        })
                        .Distinct()
                        .OrderBy(member => member.Member)
                        .ToList();

var date1 = new DateTime(2012, 09, 14);
var date2 = new DateTime(2012, 09, 15);

var items = context.Members
                        .SelectMany(x => x.Bookings)
                        .Where(booking => booking.StartTime >= date1 && booking.StartTime < date2)
                        .Select(booking => new
                        {
                            Member = booking.Member.FirstName + " " + booking.Member.Surname,
                            Facility = booking.Facility.Name,
                            Cost = booking.MemId == 0 ?
                                        booking.Slots * booking.Facility.GuestCost
                                        : booking.Slots * booking.Facility.MemberCost
                        })
                        .Where(x => x.Cost > 30)
                        .Distinct()
                        .OrderByDescending(x => x.Cost)
                        .ToList();

CREATE SEARCH PROPERTY LIST WordSearchPropertyList;
GO

ALTER SEARCH PROPERTY LIST WordSearchPropertyList
   ADD 'Authors'
   WITH (PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9',
             PROPERTY_INT_ID = 4,
             PROPERTY_DESCRIPTION = 'System.Authors - authors of a given item.');
GO

 -- Check the Stopwords list
SELECT w.stoplist_id,
   l.name,
   w.stopword,
   w.language
FROM sys.fulltext_stopwords AS w
   INNER JOIN sys.fulltext_stoplists AS l
     ON w.stoplist_id = l.stoplist_id;

 -- Stopwords list
CREATE FULLTEXT STOPLIST SQLStopList;
GO

-- Add a stopword
ALTER FULLTEXT STOPLIST SQLStopList
 ADD 'SQL' LANGUAGE 'English';
GO

 create fulltext catalog catalog_name
on filegroup filegroup_name
in path  'rootpath'
with some_options
as default
authoriztion owner_name
accent_sensivity = {on|off}

 -- Full-text catalog
CREATE FULLTEXT CATALOG DocumentsFtCatalog;
GO

-- Full-text index
CREATE FULLTEXT INDEX ON dbo.Documents
(
  docexcerpt Language 1033,
  doccontent TYPE COLUMN doctype
  Language 1033
  STATISTICAL_SEMANTICS
)
KEY INDEX PK_Documents
ON DocumentsFtCatalog
WITH STOPLIST = SQLStopList,
  SEARCH PROPERTY LIST = WordSearchPropertyList,
  CHANGE_TRACKING AUTO;
GO

 CREATE TABLE dbo.Documents
(
  id INT IDENTITY(1,1) NOT NULL,
  title NVARCHAR(100) NOT NULL,
  doctype NCHAR(4) NOT NULL,
  docexcerpt NVARCHAR(1000) NOT NULL,
  doccontent VARBINARY(MAX) NOT NULL,
  CONSTRAINT PK_Documents
PRIMARY KEY CLUSTERED(id)
);

-- Insert data
-- First row
INSERT INTO dbo.Documents
(title, doctype, docexcerpt, doccontent)
SELECT N'Columnstore Indices and Batch Processing', 
 N'docx',
 N'You should use a columnstore index on your fact tables,
   putting all columns of a fact table in a columnstore index. 
   In addition to fact tables, very large dimensions could benefit 
   from columnstore indices as well. 
   Do not use columnstore indices for small dimensions. ',
 bulkcolumn
FROM OPENROWSET
 (BULK 'C:\Users\Vahid\Desktop\Updates\fts_docs\ColumnstoreIndicesAndBatchProcessing.docx', 
  SINGLE_BLOB) AS doc;

-- Second row
INSERT INTO dbo.Documents
(title, doctype, docexcerpt, doccontent)
SELECT N'Introduction to Data Mining', 
 N'docx',
 N'Using Data Mining is becoming more a necessity for every company 
   and not an advantage of some rare companies anymore. ',
 bulkcolumn
FROM OPENROWSET
 (BULK 'C:\Users\Vahid\Desktop\Updates\fts_docs\IntroductionToDataMining.docx', 
  SINGLE_BLOB) AS doc;

-- Third row
INSERT INTO dbo.Documents
(title, doctype, docexcerpt, doccontent)
SELECT N'Why Is Bleeding Edge a Different Conference', 
 N'docx',
 N'During high level presentations attendees encounter many questions. 
   For the third year, we are continuing with the breakfast Q&A session. 
   It is very popular, and for two years now, 
   we could not accommodate enough time for all questions and discussions! ',
 bulkcolumn
FROM OPENROWSET
 (BULK 'C:\Users\Vahid\Desktop\Updates\fts_docs\WhyIsBleedingEdgeADifferentConference.docx', 
  SINGLE_BLOB) AS doc;

-- Fourth row
INSERT INTO dbo.Documents
(title, doctype, docexcerpt, doccontent)
SELECT N'Additivity of Measures', 
 N'docx',
 N'Additivity of measures is not exactly a data warehouse design problem. 
   However, you have to realize which aggregate functions you will use 
   in reports for which measure, and which aggregate functions 
   you will use when aggregating over which dimension.',
 bulkcolumn
FROM OPENROWSET
 (BULK 'C:\Users\Vahid\Desktop\Updates\fts_docs\AdditivityOfMeasures.docx', 
  SINGLE_BLOB) AS doc;
GO

 -- Search property list
CREATE SEARCH PROPERTY LIST WordSearchPropertyList;
GO
ALTER SEARCH PROPERTY LIST WordSearchPropertyList
 ADD 'Authors'
 WITH (PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9',
PROPERTY_INT_ID = 4,
PROPERTY_DESCRIPTION = 'System.Authors - authors of a given item.');
GO

 -- Stopwords list
CREATE FULLTEXT STOPLIST SQLStopList;
GO
-- Add a stopword
ALTER FULLTEXT STOPLIST SQLStopList
 ADD 'SQL' LANGUAGE 'English';
GO

 -- Full-text catalog
CREATE FULLTEXT CATALOG DocumentsFtCatalog;
GO

 -- Full-text index
CREATE FULLTEXT INDEX ON dbo.Documents
(
  docexcerpt Language 1033,
  doccontent TYPE COLUMN doctype
  Language 1033
  STATISTICAL_SEMANTICS
)
KEY INDEX PK_Documents
ON DocumentsFtCatalog
WITH STOPLIST = SQLStopList,
  SEARCH PROPERTY LIST = WordSearchPropertyList,
  CHANGE_TRACKING AUTO;
GO

declare @cntr_value bigint

Select @cntr_value=cntr_value
from sys.dm_os_performance_counters
where instance_name='AdventureWorks' and
counter_name='Write Transactions/sec'

/* ایجاد یک تاخیر مثلاً یک ثانیه */
waitfor delay '00:00:01'

Select cntr_value -@cntr_value
from sys.dm_os_performance_counters
where instance_name='AdventureWorks' and
counter_name='Write Transactions/sec'

EXEC sys.sp_MSforeachtable 'sp_spaceused ''?''';

 USE [AdventureWorksDW2008R2]
GO

CREATE TABLE #TableSpaceUsed(
[name] [nvarchar](120) NULL,
[rows] [nvarchar](120) NULL,
[reserved] [nvarchar](120) NULL,
[data] [nvarchar](120) NULL,
[index_size] [nvarchar](120) NULL,
[unused] [nvarchar](120) NULL
) ON [PRIMARY]

Insert Into #TableSpaceUsed
EXEC sys.sp_MSforeachtable 'sp_spaceused ''?''';

Select * from #TableSpaceUsed
Order by CAST([rows] as int) desc

Drop table #TableSpaceUsed

  management studio -> select server -> expand Security -> right click Logins ->  select "New Login..."

Right click on db-> properties -> permission -> View Server permission

CREATE TABLE ftsXML(
id INT IDENTITY PRIMARY KEY,
doc XML NULL
CONSTRAINT UQ_FTS_Id UNIQUE(id)
)
GO
INSERT ftsXML VALUES('
<book>
<title>Sample book title 1</title>
<author>Vahid</author>
<chapter ID="1">
<title>Chapter 1</title>
<content>
"The quick brown fox jumps over the lazy dog" is an English-language 
pangram—a phrase that contains all of the letters of the English alphabet. 
It has been used to test typewriters and computer keyboards, and in other 
applications involving all of the letters in the English alphabet. Owing to its 
brevity and coherence, it has become widely known.
</content>
</chapter>
<chapter ID="2">
<title>Chapter 2</title>
<content>
In publishing and graphic design, lorem ipsum is a placeholder text commonly used 
to demonstrate the graphic elements of a document or visual presentation. 
By replacing the distraction of meaningful content with filler text of scrambled 
Latin it allows viewers to focus on graphical elements such as font, typography, 
and layout.
</content>
</chapter>
</book>
')

INSERT ftsXML VALUES('
<book>
<title>Sample book title 2</title>
<author>Farid</author>
<chapter ID="1">
<title>Chapter 1</title>
<content>
The original passage began: Neque porro quisquam est qui dolorem ipsum quia dolor sit 
amet consectetur adipisci velit 
</content>
</chapter>
<chapter ID="2">
<title>Chapter 2</title>
<content>
Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor 
incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis 
nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. 
Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore 
eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, 
sunt in culpa qui officia deserunt mollit anim id est laborum.
</content>
</chapter>
</book>
')
GO

 CREATE FULLTEXT CATALOG FT_CATALOG
GO
CREATE FULLTEXT INDEX ON ftsXML([doc])
KEY INDEX UQ_FTS_Id ON ([FT_CATALOG], FILEGROUP [PRIMARY])
GO

 SQL Server encountered error 0x80070422 while communicating with full-text filter daemon host (FDHost) process.

 sp_fulltext_service 'restart_all_fdhosts'
go

 -- استفاده از ایکس کوئری برای جستجو در نتایج حاصل
SELECT T.doc.value('(/book/title)[1]', 'varchar(100)') AS title
FROM
-- استفاده از اف تی اس برای جستجو
(SELECT * FROM ftsXML
WHERE CONTAINS(doc, '"Quick Brown Fox "')) AS T

 SELECT T.doc.value('(/book/title)[1]', 'varchar(100)') AS title
FROM
(SELECT * FROM ftsXML
WHERE CONTAINS(doc, 'FORMSOF (INFLECTIONAL ,"Quick Brown Fox jumped")')) AS T

 SELECT T.doc.value('(/book/title)[1]', 'varchar(100)') AS title
FROM
(SELECT * FROM ftsXML
WHERE CONTAINS(doc, 'quick NEAR fox')) AS T

 SELECT doc.value('(/book/title)[1]', 'varchar(100)') AS title
FROM ftsXML
WHERE doc.exist('/book/chapter/content[contains(., "Quick Brown Fox")]') = 1