DECLARE @doc XML
SET @doc ='
<p:people xmlns:p="http://www.people.com">
<p:person name="Vahid" />
<p:person name="Farid" />
</p:people>
'
SELECT @doc.query('/people/person') SELECT @doc.query('/p:people/p:person') XQuery [query()]: The name "p" does not denote a namespace.
SELECT @doc.query('
declare default element namespace "http://www.people.com";
/people/person
') SELECT @doc.query('
declare namespace aa="http://www.people.com";
/aa:people/aa:person
') WITH XMLNAMESPACES(DEFAULT 'http://www.people.com')
SELECT @doc.query('/people/person') WITH XMLNAMESPACES('http://www.people.com' AS aa)
SELECT @doc.query('/aa:people/aa:person') SELECT @data.query('
(: FLOWE :)
for $p in /people/person
where $p/age > 30
return $p
')
SELECT @data.query('
(: XPath :)
/people/person[age>30]
') /books/book[1]/title/chapter
DECLARE @doc XML
SET @doc='
<Team name="Project 1" xmlns:a="urn:annotations">
<Employee id="544" years="6.5">
<Name>User 1</Name>
<Title>Architect</Title>
<Expertise>Games</Expertise>
<Expertise>Puzzles</Expertise>
<Employee id="101" years="7.1" a:assigned-to="C1">
<Name>User 2</Name>
<Title>Dev lead</Title>
<Expertise>Video Games</Expertise>
<Employee id="50" years="2.3" a:assigned-to="C2">
<Name>User 3</Name>
<Title>Developer</Title>
<Expertise>Hardware</Expertise>
<Expertise>Entertainment</Expertise>
</Employee>
</Employee>
</Employee>
</Team>
' SELECT @doc.query('/Team/Employee/Name') SELECT @doc.query('/Team/Employee/child::Name') <Name>User 1</Name>
SELECT @doc.query('//Employee/Name') <Name>User 1</Name> <Name>User 2</Name> <Name>User 3</Name>
SELECT @doc.query('
declare namespace a = "urn:annotations";
//Employee[@a:assigned-to]/Name
') <Name>User 2</Name> <Name>User 3</Name>
SELECT @doc.query('
declare namespace a = "urn:annotations";
//Employee[attribute::a:assigned-to]/Name
') SELECT @doc.query('
declare namespace a = "urn:annotations";
//Employee[not(@a:assigned-to)]/Name
') <Name>User 1</Name>
SELECT @doc.query('count(//Employee[Name="User 1"]/Employee)') SELECT @doc.query('//Employee[../Name="User 1"]') Select p.name, p.job from people as p where p.age > 30 order by p.age
for $p in /people/person where $p.age > 30 order by $p.age[1] return ($p/name, $p/job)
let $p := /people/person return $p
DECLARE @x XML = '';
SELECT @x.query('
for $i in (1,2,3)
return $i
');
SELECT @x.query('
let $i := (1,2,3)
return $i
'); DECLARE @x XML = '';
SELECT @x.query('
for $i in (1,2,3)
order by $i descending
return $i
');
SELECT @x.query('
let $i := (1,2,3)
order by $i descending
return $i
'); XQuery [query()]: 'order by' requires a singleton (or empty sequence), found operand of type 'xs:integer +'
DECLARE @doc XML =' <people> <person> <name> <givenName>name1</givenName> <familyName>lname1</familyName> </name> <age>33</age> <height>short</height> </person> <person> <name> <givenName>name2</givenName> <familyName>lname2</familyName> </name> <age>40</age> <height>short</height> </person> <person> <name> <givenName>name3</givenName> <familyName>lname3</familyName> </name> <age>30</age> <height>medium</height> </person> </people> '
SELECT @doc.query('
for $p in /people/person
return <person>
{$p/name[1]/givenName[1]/text()}
</person>
'); <person>name1</person> <person>name2</person> <person>name3</person>
SELECT @doc.query('
<root>
{
for $p in /people/person
return <person>
{$p/name[1]/givenName[1]/text()}
</person>
}
</root>
'); <root> <person>name1</person> <person>name2</person> <person>name3</person> </root>
SELECT @doc.query('some $emp in //Employee satisfies $emp/@years >5')
-- true
SELECT @doc.query('every $emp in //Employee satisfies $emp/@years >5')
-- false SELECT Title FROM Book WHERE Desc LIKE '%cat%';
var cats = context.Chapters.Where(item => item.Text.Contains("cat")).ToList(); SELECT "c"."Text" FROM "Chapters" AS "c" WHERE ('cat' = '') OR (instr("c"."Text", 'cat') > 0) var cats = context.Chapters.Where(item => item.Text.StartsWith("cat")).ToList();
// SELECT "c"."Text", FROM "Chapters" AS "c" WHERE "c"."Text" IS NOT NULL AND ("c"."Text" LIKE 'cat%') var cats = context.Chapters.Where(item => item.Text.EndsWith("cat")).ToList();
// SELECT "c"."Title" FROM "Chapters" AS "c" WHERE "c"."Text" IS NOT NULL AND ("c"."Text" LIKE '%cat') using System.Collections.Generic;
namespace EFCoreSQLiteFTS.Entities
{
public class User
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<Chapter> Chapters { get; set; }
}
public class Chapter
{
public int Id { get; set; }
public string Title { get; set; }
public string Text { get; set; }
public User User { get; set; }
public int UserId { get; set; }
}
} CREATE VIRTUAL TABLE "Chapters_FTS"
USING fts5("Text", "Title", content="Chapters", content_rowid="Id") private static void createFtsTables(ApplicationDbContext context)
{
// For SQLite FTS
// Note: This can be added to the `protected override void Up(MigrationBuilder migrationBuilder)` method too.
context.Database.ExecuteSqlRaw(@"CREATE VIRTUAL TABLE IF NOT EXISTS ""Chapters_FTS""
USING fts5(""Text"", ""Title"", content=""Chapters"", content_rowid=""Id"");");
} 
-- Create a table. And an external content fts5 table to index it.
CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);
CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a');
-- Triggers to keep the FTS index up to date.
CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;
CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
END;
CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END; using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
namespace EFCoreSQLiteFTS.DataLayer
{
public static class EFChangeTrackerExtensions
{
public static List<(EntityState State, TEntity NewEntity, TEntity OldEntity)>
GetChangedEntities<TEntity>(this DbContext dbContext) where TEntity : class, new()
{
if (!dbContext.ChangeTracker.AutoDetectChangesEnabled)
{
// ChangeTracker.Entries() only calls `Try`DetectChanges() behind the scene.
dbContext.ChangeTracker.DetectChanges();
}
return dbContext.ChangeTracker.Entries<TEntity>()
.Where(IsEntityChanged)
.Select(entityEntry => (entityEntry.State,
entityEntry.Entity,
createWithValues<TEntity>(entityEntry.OriginalValues)))
.ToList();
}
private static bool IsEntityChanged(EntityEntry entry)
{
return entry.State == EntityState.Added
|| entry.State == EntityState.Modified
|| entry.State == EntityState.Deleted
|| entry.References.Any(r => r.TargetEntry?.Metadata.IsOwned() == true && IsEntityChanged(r.TargetEntry));
}
private static T createWithValues<T>(PropertyValues values) where T : new()
{
var entity = new T();
foreach (var prop in values.Properties)
{
var value = values[prop.Name];
if (value is PropertyValues)
{
throw new NotSupportedException("nested complex object");
}
else
{
prop.PropertyInfo.SetValue(entity, value);
}
}
return entity;
}
}
} using System.Collections.Generic;
using System.Data;
using System.Text.RegularExpressions;
using EFCoreSQLiteFTS.Entities;
using Microsoft.EntityFrameworkCore;
namespace EFCoreSQLiteFTS.DataLayer
{
public static class FtsNormalizer
{
private static readonly Regex _htmlRegex = new Regex("<[^>]*>", RegexOptions.Compiled);
public static string NormalizeText(this string text)
{
if (string.IsNullOrWhiteSpace(text))
{
return string.Empty;
}
// Remove html tags
text = _htmlRegex.Replace(text, string.Empty);
// TODO: add other normalizers here, such as `remove diacritics`, `fix Persian Ye-Ke` and so on ...
return text;
}
}
public static class UpdateFtsTriggers
{
public static void UpdateChapterFTS(
this DbContext context,
List<(EntityState State, Chapter NewEntity, Chapter OldEntity)> changedChapters)
{
var database = context.Database;
try
{
database.BeginTransaction(IsolationLevel.ReadCommitted);
foreach (var (State, NewEntity, OldEntity) in changedChapters)
{
var chapterNew = NewEntity;
var chapterOld = OldEntity;
var normalizedNewText = chapterNew.Text.NormalizeText();
var normalizedOldText = chapterOld.Text.NormalizeText();
var normalizedNewTitle = chapterNew.Title.NormalizeText();
var normalizedOldTitle = chapterOld.Title.NormalizeText();
switch (State)
{
case EntityState.Added:
if (shouldSkipAddedChapter(chapterNew))
{
continue;
}
database.ExecuteSqlRaw("INSERT INTO Chapters_FTS(rowid, Text, Title) values({0}, {1}, {2});",
chapterNew.Id, normalizedNewText, normalizedNewTitle);
break;
case EntityState.Modified:
if (shouldSkipModifiedChapter(chapterNew, chapterOld))
{
continue;
}
// This format is important! Otherwise we will get `SQLite Error 11: 'database disk image is malformed'.` error!
database.ExecuteSqlRaw(@"INSERT INTO Chapters_FTS(Chapters_FTS, rowid, Text, Title)
VALUES('delete', {0}, {1}, {2}); ",
chapterOld.Id, normalizedOldText, normalizedOldTitle);
database.ExecuteSqlRaw("INSERT INTO Chapters_FTS(rowid, Text, Title) values({0}, {1}, {2});",
chapterNew.Id, normalizedNewText, normalizedNewTitle);
break;
case EntityState.Deleted:
// This format is important! Otherwise we will get `SQLite Error 11: 'database disk image is malformed'.` error!
database.ExecuteSqlRaw(@"INSERT INTO Chapters_FTS(Chapters_FTS, rowid, Text, Title)
VALUES('delete', {0}, {1}, {2}); ",
chapterOld.Id, normalizedOldText, normalizedOldTitle);
break;
}
}
}
finally
{
database.CommitTransaction();
}
}
private static bool shouldSkipAddedChapter(Chapter chapterNew)
{
// TODO: add your logic to avoid indexing this item
return false;
}
private static bool shouldSkipModifiedChapter(Chapter chapterNew, Chapter chapterOld)
{
// TODO: add your logic to avoid indexing this item
return chapterNew.Text == chapterOld.Text && chapterNew.Title == chapterOld.Title;
}
}
} namespace EFCoreSQLiteFTS.DataLayer
{
public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions options)
: base(options)
{
}
public DbSet<Chapter> Chapters { get; set; }
public DbSet<User> Users { get; set; }
public override int SaveChanges()
{
var changedChapters = this.GetChangedEntities<Chapter>();
this.ChangeTracker.AutoDetectChangesEnabled = false; // for performance reasons, to avoid calling DetectChanges() again.
var result = base.SaveChanges();
this.ChangeTracker.AutoDetectChangesEnabled = true;
this.UpdateChapterFTS(changedChapters);
return result;
}
}
} 
CREATE TABLE xmlInvoice ( invoiceId INT IDENTITY PRIMARY KEY, invoice XML )
INSERT INTO xmlInvoice
VALUES('
<Invoice InvoiceId="1000" dept="hardware">
<CustomerName>Vahid</CustomerName>
<LineItems>
<LineItem><Description>Gear</Description><Price>9.5</Price></LineItem>
</LineItems>
</Invoice>
')
INSERT INTO xmlInvoice
VALUES('
<Invoice InvoiceId="1002" dept="garden">
<CustomerName>Mehdi</CustomerName>
<LineItems>
<LineItem><Description>Shovel</Description><Price>19.2</Price></LineItem>
</LineItems>
</Invoice>
')
INSERT INTO xmlInvoice
VALUES('
<Invoice InvoiceId="1003" dept="garden">
<CustomerName>Mohsen</CustomerName>
<LineItems>
<LineItem><Description>Trellis</Description><Price>8.5</Price></LineItem>
</LineItems>
</Invoice>
')
INSERT INTO xmlInvoice
VALUES('
<Invoice InvoiceId="1004" dept="hardware">
<CustomerName>Hamid</CustomerName>
<LineItems>
<LineItem><Description>Pen</Description><Price>1.5</Price></LineItem>
</LineItems>
</Invoice>
')
INSERT INTO xmlInvoice
VALUES('
<Invoice InvoiceId="1005" dept="IT">
<CustomerName>Ali</CustomerName>
<LineItems>
<LineItem><Description>Book</Description><Price>3.2</Price></LineItem>
</LineItems>
</Invoice>
')
INSERT INTO xmlInvoice
VALUES('
<Invoice InvoiceId="1006" dept="hardware">
<CustomerName>Reza</CustomerName>
<LineItems>
<LineItem><Description>M.Board</Description><Price>19.5</Price></LineItem>
</LineItems>
</Invoice>
') CREATE XML SCHEMA COLLECTION invoice_xsd AS
' <xs:schema attributeFormDefault="unqualified"
elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Invoice">
<xs:complexType>
<xs:sequence>
<xs:element name="CustomerName" type="xs:string" />
<xs:element name="LineItems">
<xs:complexType>
<xs:sequence>
<xs:element name="LineItem">
<xs:complexType>
<xs:sequence>
<xs:element name="Description" type="xs:string" />
<xs:element name="Price" type="xs:decimal" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="InvoiceId" type="xs:unsignedShort" use="required" />
<xs:attribute name="dept" type="xs:string" use="required" />
</xs:complexType>
</xs:element>
</xs:schema>'
Go
CREATE TABLE xmlInvoice2
(
invoiceId INT IDENTITY PRIMARY KEY,
invoice XML(document invoice_xsd)
)
Go UPDATE STATISTICS xmlInvoice DBCC FREEPROCCACHE
-- query 1
SELECT * FROM xmlInvoice
WHERE invoice.exist('/Invoice[@InvoiceId = "1003"]') = 1
-- query 2
SELECT * FROM xmlInvoice
WHERE invoice.exist('/Invoice/@InvoiceId[. = "1003"]') = 1
-- query 3
SELECT * FROM xmlInvoice
WHERE invoice.exist('/Invoice[1]/@InvoiceId[. = "1003"]') = 1
-- query 4
SELECT * FROM xmlInvoice
WHERE invoice.exist('(/Invoice/@InvoiceId)[1][. = "1003"]') = 1
-- query 5
SELECT * FROM xmlInvoice
WHERE invoice.exist('/Invoice[CustomerName = "Vahid"]') = 1
-- query 6
SELECT * FROM xmlInvoice
WHERE invoice.exist('/Invoice/CustomerName [.= "Vahid"]') = 1
-- query 7
SELECT * FROM xmlInvoice
WHERE invoice.exist('/Invoice/LineItems/LineItem[Description = "Trellis"]') = 1
-- query 8
SELECT * FROM xmlInvoice
WHERE invoice.exist('/Invoice/LineItems/LineItem/Description [.= "Trellis"]') = 1
-- query 9
SELECT * FROM xmlInvoice
WHERE invoice.exist('
for $x in /Invoice/@InvoiceId
where $x = 1003
return $x
') = 1
-- query 10
SELECT * FROM xmlInvoice
WHERE invoice.value('(/Invoice/@InvoiceId)[1]', 'VARCHAR(10)') = '1003'
-- یکبار هم با جدول شماره 2 که اسکیما دارد تمام این موارد تکرار شود
UPDATE STATISTICS xmlInvoice
DBCC FREEPROCCACHE
GO 
HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();
using System.Collections.Generic;
namespace EF_Sample06.Models
{
public class Department
{
public int DepartmentId { get; set; }
public string Name { get; set; }
//Creates Employee navigation property for Lazy Loading (1:many)
public virtual ICollection<Employee> Employees { get; set; }
}
}
namespace EF_Sample06.Models
{
public class Employee
{
public int EmployeeId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
//Creates Department navigation property for Lazy Loading
public virtual Department Department { get; set; }
}
}
using System.Data.Entity;
using EF_Sample06.Models;
namespace EF_Sample06.DataLayer
{
public class Sample06Context : DbContext
{
public DbSet<Department> Departments { set; get; }
public DbSet<Employee> Employees { set; get; }
}
}
using System.Collections.Generic;
using System.Data.Entity.Migrations;
using EF_Sample06.Models;
namespace EF_Sample06.DataLayer
{
public class Configuration : DbMigrationsConfiguration<Sample06Context>
{
public Configuration()
{
AutomaticMigrationsEnabled = true;
AutomaticMigrationDataLossAllowed = true;
}
protected override void Seed(Sample06Context context)
{
var employee1 = new Employee { FirstName = "f name1", LastName = "l name1" };
var employee2 = new Employee { FirstName = "f name2", LastName = "l name2" };
var employee3 = new Employee { FirstName = "f name3", LastName = "l name3" };
var employee4 = new Employee { FirstName = "f name4", LastName = "l name4" };
var dept1 = new Department { Name = "dept 1", Employees = new List<Employee> { employee1, employee2 } };
var dept2 = new Department { Name = "dept 2", Employees = new List<Employee> { employee3 } };
var dept3 = new Department { Name = "dept 3", Employees = new List<Employee> { employee4 } };
context.Departments.Add(dept1);
context.Departments.Add(dept2);
context.Departments.Add(dept3);
base.Seed(context);
}
}
}
void ExportMappings(DbContext context, string edmxFile)
{
var settings = new XmlWriterSettings { Indent = true };
using (XmlWriter writer = XmlWriter.Create(edmxFile, settings))
{
System.Data.Entity.Infrastructure.EdmxWriter.WriteEdmx(context, writer);
}
}
using (var db = new Sample06Context())
{
ExportMappings(db, "mappings.edmx");
}
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using EF_Sample06.DataLayer;
using EF_Sample06.Models;
namespace EF_Sample06
{
class Program
{
static IList<Employee> FindEmployees(string fName, string lName, bool byName, bool byLName)
{
using (var db = new Sample06Context())
{
IQueryable<Employee> query = db.Employees.AsQueryable();
if (byLName)
{
query = query.Where(x => x.LastName == lName);
}
if (byName)
{
query = query.Where(x => x.FirstName == fName);
}
return query.ToList();
}
}
static void Main(string[] args)
{
// note: remove this line if you received : create database is not supported by this provider.
HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();
Database.SetInitializer(new MigrateDatabaseToLatestVersion<Sample06Context, Configuration>());
var list = FindEmployees("f name1", "l name1", true, true);
foreach (var item in list)
{
Console.WriteLine(item.FirstName);
}
}
}
}
var list = FindEmployees("f name1", "l name1", true, true);
SELECT [Extent1].[EmployeeId] AS [EmployeeId],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Department_DepartmentId] AS [Department_DepartmentId]
FROM [dbo].[Employees] AS [Extent1]
WHERE ([Extent1].[LastName] = 'l name1' /* @p__linq__0 */)
AND ([Extent1].[FirstName] = 'f name1' /* @p__linq__1 */)
using (var db = new Sample06Context())
{
var dept1 = db.Departments.Find(1);
if (dept1 != null)
{
Console.WriteLine(dept1.Name);
foreach (var item in dept1.Employees)
{
Console.WriteLine(item.FirstName);
}
}
}
SELECT [Limit1].[DepartmentId] AS [DepartmentId],
[Limit1].[Name] AS [Name]
FROM (SELECT TOP (2) [Extent1].[DepartmentId] AS [DepartmentId],
[Extent1].[Name] AS [Name]
FROM [dbo].[Departments] AS [Extent1]
WHERE [Extent1].[DepartmentId] = 1 /* @p0 */) AS [Limit1]
SELECT [Extent1].[EmployeeId] AS [EmployeeId],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Department_DepartmentId] AS [Department_DepartmentId]
FROM [dbo].[Employees] AS [Extent1]
WHERE ([Extent1].[Department_DepartmentId] IS NOT NULL)
AND ([Extent1].[Department_DepartmentId] = 1 /* @EntityKeyValue1 */)
using (var db = new Sample06Context())
{
foreach (var dept in db.Departments)
{
Console.WriteLine(dept.Name);
foreach (var item in dept.Employees)
{
Console.WriteLine(item.FirstName);
}
}
}
There is already an open DataReader associated with this Command which must be closed first
<connectionStrings>
<clear/>
<add
name="Sample06Context"
connectionString="Data Source=(local);Initial Catalog=testdb2012;Integrated Security = true;MultipleActiveResultSets=True;"
providerName="System.Data.SqlClient"
/>
</connectionStrings>
using (var db = new Sample06Context())
{
foreach (var dept in db.Departments.Include(x => x.Employees))
{
Console.WriteLine(dept.Name);
foreach (var item in dept.Employees)
{
Console.WriteLine(item.FirstName);
}
}
}
SELECT [Project1].[DepartmentId] AS [DepartmentId],
[Project1].[Name] AS [Name],
[Project1].[C1] AS [C1],
[Project1].[EmployeeId] AS [EmployeeId],
[Project1].[FirstName] AS [FirstName],
[Project1].[LastName] AS [LastName],
[Project1].[Department_DepartmentId] AS [Department_DepartmentId]
FROM (SELECT [Extent1].[DepartmentId] AS [DepartmentId],
[Extent1].[Name] AS [Name],
[Extent2].[EmployeeId] AS [EmployeeId],
[Extent2].[FirstName] AS [FirstName],
[Extent2].[LastName] AS [LastName],
[Extent2].[Department_DepartmentId] AS [Department_DepartmentId],
CASE
WHEN ([Extent2].[EmployeeId] IS NULL) THEN CAST(NULL AS int)
ELSE 1
END AS [C1]
FROM [dbo].[Departments] AS [Extent1]
LEFT OUTER JOIN [dbo].[Employees] AS [Extent2]
ON [Extent1].[DepartmentId] = [Extent2].[Department_DepartmentId]) AS [Project1]
ORDER BY [Project1].[DepartmentId] ASC,
[Project1].[C1] ASC
public class Sample06Context : DbContext
{
public Sample06Context()
{
this.Configuration.LazyLoadingEnabled = false;
}
public class Department
{
public int DepartmentId { get; set; }
public string Name { get; set; }
public ICollection<Employee> Employees { get; set; }
public Department()
{
Employees = new HashSet<Employee>();
}
}
