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;
        }
    }
}

        private static void disposedContext()
        {
            using (var context = new MyContext())
            {
                Debug.WriteLine("Posts count: " + context.BlogPosts.Count());
            }
        }

        private static void nonDisposedContext()
        {
            var context = new MyContext();
            Debug.WriteLine("Posts count: " + context.BlogPosts.Count());
        }

using System.Data;
using System.Data.Common;
using System.Data.Entity.Infrastructure.Interception;

namespace EFNonDisposedContext.Core
{
    public class DatabaseInterceptor : IDbConnectionInterceptor
    {
        public void Closed(DbConnection connection, DbConnectionInterceptionContext interceptionContext)
        {
            Connections.AddOrUpdate(connection, ConnectionStatus.Closed);
        }

        public void Disposed(DbConnection connection, DbConnectionInterceptionContext interceptionContext)
        {
            Connections.AddOrUpdate(connection, ConnectionStatus.Disposed);
        }

        public void Opened(DbConnection connection, DbConnectionInterceptionContext interceptionContext)
        {
            Connections.AddOrUpdate(connection, ConnectionStatus.Opened);
        }
      
        // the rest of the IDbConnectionInterceptor methods ...

    }
}

    public enum ConnectionStatus
    {
        None,
        Opened,
        Closed,
        Disposed
    }

    public class ConnectionInfo
    {
        public string ConnectionId { set; get; }
        public string StackTrace { set; get; }
        public ConnectionStatus Status { set; get; }

        public override string ToString()
        {
            return string.Format("{0}:{1} [{2}]",ConnectionId, Status, StackTrace);
        }
    }

using System;
using System.Diagnostics;
using Microsoft.Win32;
using System.Windows.Forms;

namespace Core
{
    public static class UseLatestVersionOfIE
    {
        /// <summary>
        /// Use the latest version of IE in WebBrowser control
        /// </summary>
        public static void SetWebBrowserVersion()
        {
            RegistryKey regkey = null;
            try
            {
                regkey = Registry.CurrentUser.OpenSubKey(@"SOFTWARE\Microsoft\Internet Explorer\Main\FeatureControl\FEATURE_BROWSER_EMULATION", writable: true);
                if (regkey == null)
                {
                    return;
                }

                var regVal = getInstalledIEVersion();
                var appName = string.Format("{0}.exe", Process.GetCurrentProcess().ProcessName);
                regkey.SetValue(appName, regVal, RegistryValueKind.DWord);
            }
            catch (Exception ex)
            {
              // todo: log ...
            }
            finally
            {
                if (regkey != null)
                {
                    regkey.Close();
                }
            }
        }

        private static int getInstalledIEVersion()
        {
            int browserVer;
            using (var wb = new WebBrowser())
            {
                browserVer = wb.Version.Major;
            }

            int regVal;
            if (browserVer >= 11)
                regVal = 11001;
            else
                switch (browserVer)
                {
                    case 10:
                        regVal = 10001;
                        break;
                    case 9:
                        regVal = 9999;
                        break;
                    case 8:
                        regVal = 8888;
                        break;
                    default:
                        regVal = 7000;
                        break;
                }
            return regVal;
        }
    }
}

• SQL Server Database Engine از کلمه کلیدی SPARSE برای تعریف یک ستون که مقادیر آن می‌بایست بهینه شود استفاده می‌نماید.
• نمای Catalog  جداول با ستون sparse شبیه جداول معمولی می‌باشد.
• مقدار برگشتی از تابع COLUMNS_UPDATED با ستون sparce متفاوت از ستون معمولی است.

• sparse column می‌ بایست nullable باشد و نمی‌تواند ROWGUIDCOL یا IDENTITY باشد. 
• sparse column مقدار پیش فرض نمی‌تواند داشته باشد 
• ستون محاسبه ای نمی‌تواند sparse باشد
• sparse column نمی‌تواند بخشی از clustered index یا  unique primary key index باشد
• sparse column  نمی تواند بخشی از  user-defined table باشد

sp_spaceused 'Employees'
GO
sp_spaceused 'Employees_sparse' 

Provides access to GDI+ graphics functionality. 

Commonly Used Types:
System.Drawing.Bitmap
System.Drawing.BitmapData
System.Drawing.Brush
System.Drawing.Font
System.Drawing.Graphics
System.Drawing.Icon