using FluentNHibernate;
using NHSample1.Domain;
namespace UnitTests
{
public class TestModel : PersistenceModel
{
public TestModel()
{
AddMappingsFromAssembly(typeof(CustomerMapping).Assembly);
}
}
}
using NUnit.Framework;
using NHibernate;
using FluentNHibernate;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
namespace UnitTests
{
public class FixtureBase
{
protected SessionSource SessionSource { get; set; }
protected ISession Session { get; private set; }
[SetUp]
public void SetupContext()
{
var cfg = Fluently.Configure().Database(SQLiteConfiguration.Standard.InMemory);
SessionSource = new SessionSource(
cfg.BuildConfiguration().Properties,
new TestModel());
Session = SessionSource.CreateSession();
SessionSource.BuildSchema(Session);
}
[TearDown]
public void TearDownContext()
{
Session.Close();
Session.Dispose();
}
}
}
using NUnit.Framework;
using FluentNHibernate.Testing;
using NHSample1.Domain;
namespace UnitTests
{
[TestFixture]
public class CustomerMapping_Fixture : FixtureBase
{
[Test]
public void can_correctly_map_customer()
{
new PersistenceSpecification<Customer>(Session)
.CheckProperty(c => c.Id, 1001)
.CheckProperty(c => c.FirstName, "Vahid")
.CheckProperty(c => c.LastName, "Nasiri")
.CheckProperty(c => c.AddressLine1, "Addr1")
.CheckProperty(c => c.AddressLine2, "Addr2")
.CheckProperty(c => c.PostalCode, "1234")
.CheckProperty(c => c.City, "Tehran")
.CheckProperty(c => c.CountryCode, "IR")
.VerifyTheMappings();
}
}
}
var cfg = Fluently.Configure().Database(SQLiteConfiguration.Standard.ShowSql().InMemory);
NHibernate: select next_hi from hibernate_unique_key
NHibernate: update hibernate_unique_key set next_hi = @p0 where next_hi = @p1;@p0 = 2, @p1 = 1
NHibernate: INSERT INTO "Customer" (FirstName, LastName, AddressLine1, AddressLine2, PostalCode, City, CountryCode, Id) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7);@p0 = 'Vahid', @p1 = 'Nasiri', @p2 = 'Addr1', @p3 = 'Addr2', @p4 = '1234', @p5 = 'Tehran', @p6 = 'IR', @p7 = 1001
NHibernate: SELECT customer0_.Id as Id0_0_, customer0_.FirstName as FirstName0_0_, customer0_.LastName as LastName0_0_, customer0_.AddressLine1 as AddressL4_0_0_, customer0_.AddressLine2 as AddressL5_0_0_, customer0_.PostalCode as PostalCode0_0_, customer0_.City as City0_0_, customer0_.CountryCode as CountryC8_0_0_ FROM "Customer" customer0_ WHERE customer0_.Id=@p0;@p0 = 1001
context.Chapters.Add(new Chapter
{
Title = "آزمایش متن فارسی",
Text = "برای نمونه تهیه شدهاست",
User = user1.Entity
}); 
"C:\Program Files (x86)\CodeBlocks\MinGW\bin\gcc.exe" -g -shared -fPIC -Wall D:\path\to\sqlite-amalgamation-3310100\spellfix.c -o spellfix1.dll
using System;
using System.Collections.Generic;
using System.IO;
using System.Runtime.InteropServices;
namespace EFCoreSQLiteFTS.DataLayer
{
public static class LoadSqliteExtensions
{
public static void AddToSystemPath(string extensionsDirectory)
{
if (!RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
{
throw new NotSupportedException("Modifying the path at runtime only works on Windows. On Linux and Mac, set LD_LIBRARY_PATH or DYLD_LIBRARY_PATH before running the app.");
}
var path = new HashSet<string>(Environment.GetEnvironmentVariable("PATH").Split(Path.PathSeparator));
if (path.Add(extensionsDirectory))
{
Environment.SetEnvironmentVariable("PATH", string.Join(Path.PathSeparator, path));
}
}
}
} LoadSqliteExtensions.AddToSystemPath("path to .dll file");
services.AddDbContext<ApplicationDbContext>((serviceProvider, optionsBuilder) =>
{
var connection = new SqliteConnection(connectionString);
connection.Open();
connection.LoadExtension("spellfix1");
// Passing in an already open connection will keep the connection open between requests.
optionsBuilder.UseSqlite(connection);
}); 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"");");
// 'SQLite Error 1: 'no such module: spellfix1'.' --> must be loaded ...
// EditCost for unicode support
context.Database.ExecuteSqlRaw("CREATE VIRTUAL TABLE IF NOT EXISTS Chapters_FTS_Vocab USING fts5vocab('Chapters_FTS', 'row');");
context.Database.ExecuteSqlRaw("CREATE TABLE IF NOT EXISTS Chapters_FTS_SpellFix_EditCost(iLang INT, cFrom TEXT, cTo TEXT, iCost INT);");
context.Database.ExecuteSqlRaw("CREATE VIRTUAL TABLE IF NOT EXISTS Chapters_FTS_SpellFix USING spellfix1(edit_cost_table=Chapters_FTS_SpellFix_EditCost);");
} 
database.ExecuteSqlRaw(@"INSERT INTO Chapters_FTS_SpellFix(word, rank)
SELECT term, cnt FROM Chapters_FTS_Vocab
WHERE term not in (SELECT word from Chapters_FTS_SpellFix_vocab)"); database.ExecuteSqlRaw("INSERT INTO Chapters_FTS_SpellFix(command) VALUES(\"reset\");"); namespace EFCoreSQLiteFTS.Entities
{
public class SpellCheck
{
public string Word { get; set; }
public decimal Rank { get; set; }
public decimal Distance { get; set; }
public decimal Score { get; set; }
public decimal Matchlen { get; set; }
}
} namespace EFCoreSQLiteFTS.DataLayer
{
public class ApplicationDbContext : DbContext
{
//...
protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);
builder.Entity<SpellCheck>().HasNoKey().ToView(null);
}
//...
}
} foreach (var item in context.Set<SpellCheck>().FromSqlRaw(
@"SELECT word, rank, distance, score, matchlen FROM Chapters_FTS_SpellFix
WHERE word MATCH {0} and top=6", "فارشی"))
{
Console.WriteLine($"Word: {item.Word}");
Console.WriteLine($"Distance: {item.Distance}");
} 
public class Car
{
public string Id { get; set; } public class Car
{
public string CarId { get; set; } public class Car
{
[Key]
public string LicensePlate { get; set; } public class MyContext : DbContext
{
public DbSet<Car> Cars { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Car>()
.HasKey(c => c.LicensePlate);
}
} {
"dependencies": {
"System.ComponentModel.Annotations": "4.1.0"
}
} protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Car>()
.HasKey(c => new { c.State, c.LicensePlate });
} public class Blog
{
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int BlogId { get; set; }
public string Url { get; set; }
} protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Blog>()
.Property(b => b.BlogId)
.ValueGeneratedNever();
} public class Blog
{
public int BlogId { get; set; }
public string Url { get; set; }
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public DateTime Inserted { get; set; }
} protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Blog>()
.Property(b => b.Inserted)
.ValueGeneratedOnAdd();
} protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Blog>()
.Property(b => b.Inserted)
.HasDefaultValueSql("getdate()");
} public class Blog
{
public int BlogId { get; set; }
public string Url { get; set; }
[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public DateTime LastUpdated { get; set; }
} protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Blog>()
.Property(b => b.LastUpdated)
.ValueGeneratedOnAddOrUpdate();
} public class Person
{
public int PersonId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string DisplayName { get; set; }
}
public class MyContext : DbContext
{
public DbSet<Person> People { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Person>()
.Property(p => p.DisplayName)
.HasComputedColumnSql("[LastName] + ', ' + [FirstName]");
}
} protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasSequence<int>("OrderNumbers", schema: "shared")
.StartsAt(1000).IncrementsBy(5);
modelBuilder.Entity<Order>()
.Property(o => o.OrderNo)
.HasDefaultValueSql("NEXT VALUE FOR shared.OrderNumbers");
} protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasSequence<int>("PrimaryKeyWithSequenceSequence");
modelBuilder.Entity<PrimaryKeyWithSequence>(entity =>
{
entity.Property(e => e.PrimaryKeyWithSequenceId).HasDefaultValueSql("NEXT VALUE FOR [PrimaryKeyWithSequenceSequence]");
});
} protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Blog>()
.HasIndex(b => b.Url)
.HasName("Index_Url"); modelBuilder.Entity<Blog>().HasIndex(b => b.Url).HasName("Index_Url").IsUnique(); modelBuilder.Entity<Person>()
.HasIndex(idx => new { idx.FirstName, idx.LastName })
.IsUnique(); protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Car>()
.HasAlternateKey(c => c.LicensePlate)
.HasName("AlteranteKey_LicensePlate");
} modelBuilder.Entity<Person>()
.HasAlternateKey(x => new { x.FirstName, x.LastName }); table.UniqueConstraint("AK_Persons_FirstName_LastName", x => new { x.FirstName, x.LastName });
<Employees>
<Employee>
<Name>Vahid</Name>
<Phone>11111111</Phone>
<Department>IT</Department>
<Age>52</Age>
</Employee>
<Employee>
<Name>Farid</Name>
<Phone>124578963</Phone>
<Department>Civil</Department>
<Age>35</Age>
</Employee>
<Employee>
<Name>Mehdi</Name>
<Phone>1245788754</Phone>
<Department>HR</Department>
<Age>30</Age>
</Employee>
</Employees>
using System.Xml.Linq;
XDocument xDoc = XDocument.Load("Employee.xml");
// loading XML from string
StringReader sr = new StringReader(stringXML);
XDocument xDoc = XDocument.Load(sr);
using System.Collections;
IEnumerable<XElement> empList = from e in xDoc.Root.Elements("Employee") select e;
foreach (XElement employee in empList)
{
foreach (XElement e in employee.Elements())
{
Console.WriteLine(e.Name + " = " + e.Value);
}
}
IEnumerable<XElement> hrList = from e in xDoc.Root.Elements("Employee")
where e.Element("Department").Value == "HR"
select e;
IEnumerable<XElement> tList = from e in xDoc.Root.Elements("Employee")
where int.Parse(e.Element("Age").Value) > 30
select e;
IEnumerable<XElement> tList = from e in xDoc.Root.Elements("Employee")
where int.Parse(e.Element("Age").Value) > 30
orderby e.Element("Name").Value
select e;
var empList = from e in xDoc.Root.Elements("Employee")
orderby e.Element("Name").Value
select new
{
Name = e.Element("Name").Value,
Phone = e.Element("Phone").Value,
Department = e.Element("Department").Value,
Age = int.Parse(e.Element("Age").Value)
};
foreach (var employee in empList)
{
Console.WriteLine("Name = " + employee.Name);
Console.WriteLine("Dep = " + employee.Department);
Console.WriteLine("Phone = " + employee.Phone);
Console.WriteLine("Age = " + employee.Age);
}
public class Employee
{
public string Name { get; set; }
public string Phone { get; set; }
public string Department { get; set; }
public int Age { get; set; }
}
public class Employee
{
public string Name { get; set; }
public string Phone { get; set; }
public string Department { get; set; }
public int Age { get; set; }
}
List<Employee> Get()
{
XDocument xDoc = XDocument.Load("Employee.xml");
var items =
from e in xDoc.Root.Elements("Employee")
orderby e.Element("Name").Value
select new Employee
{
Name = e.Element("Name").Value,
Phone = e.Element("Phone").Value,
Department = e.Element("Department").Value,
Age = int.Parse(e.Element("Age").Value)
};
return items.ToList();
}
USE master;
SELECT d.database_id,
d.object_id,
d.index_handle,
d.equality_columns,
d.inequality_columns,
d.included_columns,
d.statement AS fully_qualified_object,
gs.*
FROM sys.dm_db_missing_index_groups g
JOIN sys.dm_db_missing_index_group_stats gs
ON gs.group_handle = g.index_group_handle
JOIN sys.dm_db_missing_index_details d
ON g.index_handle = d.index_handle
CREATE NONCLUSTERED INDEX <unique index name>
ON <FULL_TABLE_NAME> (<EQUALITY_COLUMNS>,<INEQUALITY_COLUMNS>) -- exclude INEQUALITY_COLUMNS if NULL
INCLUDE (<INCLUDED_COLUMNS>); -- exclude INCLUDED_COLUMNS if NULL
SELECT mig.index_group_handle,
mid.index_handle,
migs.avg_total_user_cost AS AvgTotalUserCostThatCouldbeReduced,
migs.avg_user_impact AS AvgPercentageBenefit,
'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle)
+ '_' + CONVERT (varchar, mid.index_handle)
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE
WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns
IS NOT NULL THEN ','
ELSE ''
END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS
create_index_statement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
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 
ctx.Advertisements.Where(...).Project().To<AdvertisementViewModel>().ToList()
docker pull microsoft/mssql-server-linux:2017-latest
sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' \ -p 1433:1433 --name sql1 \ -d mcr.microsoft.com/mssql/server:2017-latest
Server Host: localhost Port: 1433 Authentication: SQL Server Authentication Login: SA Password: <StrongPasswordYouSet>
sudo docker exec -it sql1 "bash"
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<YourNewStrong!Passw0rd>'
CREATE DATABASE TestDB
CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)
INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154);
SELECT Name from sys.Databases
SELECT * FROM Inventory WHERE quantity > 152;
dotnet new webapi -o dockerapi
dotnet add package Microsoft.EntityFrameworkCore.SqlServer dotnet add package Microsoft.EntityFrameworkCore.Design
public class Students
{
public int Id { get; set; }
public string Name { get; set; }
public string Phone { get; set; }
} dotnet ef dbcontext scaffold "Server=localhost,1433\\Catalog=tutorial_database;Database=<YOUR_DATABASE_NAME>;User=SA;Password=<StrongPasswordYouSet>;" Microsoft.EntityFrameworkCore.SqlServer
"ConnectionStrings": {
"TestingDatabase": "Server=localhost:1433\\Database=<YourDatabaseName>;User=SA;Password=<StrongPasswordYouSet>;"
} dotnet ef migrations add <NAME_OF_MIGRATION>
ef database update
SELECT TABLE_NAME FROM dockerdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'