Semantic Search جزو تازههای SQL Server 2012 است (البته این مورد خاص، زبانهای محدودی را پشتیبانی میکند).
نظرات مطالب
public static T ParseIt<T>(string content, IFormatProvider? provider) where T : IParsable<T> { return T.Parse(content, provider); } public IEnumerable<T> ParseCsvRow<T>(string content, IFormatProvider? provider) where T : IParsable<T> { return content.Split(',').Select(str => T.Parse(str, provider)); }
public static T ParseIt<T>(string content, IFormatProvider? provider) { var type = typeof(T); var method = type.GetMethod("Parse", BindingFlags.Static | BindingFlags.Public, new[] { typeof(string), typeof(IFormatProvider) }); return (T)method!.Invoke(null, new object?[] { content, provider })!; }
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
public class Person { public int Id { get; set; } public string Name { get; set; } public int? Age { get; set; } }
string name = null; var list1 = ctx.Users.Where(x => x.Name == name).ToList();
SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[Age] AS [Age] FROM [dbo].[People] AS [Extent1] WHERE [Extent1].[Name] = @p__linq__0 -- p__linq__0 (dbtype=String, size=-1, direction=Input) = null
var list2 = ctx.Users.Where(x => !x.Age.HasValue).ToList();
int? age = null; var list2 = ctx.Users.Where(x => object.Equals(x.Age, age)).ToList();
var list1 = ctx.Users.Where(x => string.IsNullOrEmpty(x.Name)).ToList();
string name = null; var list1 = ctx.Users.Where(x => name == null ? x.Name == null : x.Name == name).ToList();
var list1 = ctx.Users.Where(x => x.Name == null).ToList();
SELECT DEPARTMENTNAME, GENDER, COUNT(1) AS COUNT FROM DBO.DIMEMPLOYEE GROUP BY DEPARTMENTNAME,GENDER ORDER BY DEPARTMENTNAME,GENDER
SELECT EMPLOYEEKEY,FIRSTNAME,LASTNAME, MIDDLENAME,TITLE,HIREDATE, BIRTHDATE,EMAILADDRESS,PHONE,GENDER FROM DBO.DIMEMPLOYEE WHERE DEPARTMENTNAME=@DEPARTMENTNAME AND GENDER=@GENDER