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

By creators, we mean enthusiasts who create content for Blogs, YouTube, Twitch, Books, and tutorial platforms (e.g. Pluralsight). The content can be free or paid. 

Completed Angular Material tutorial with practical examples.
discussed points :
- angular material form design with validation
- angular material datatable
- popup dialog
- notification tool
- crud operations 

use master;

RESTORE DATABASE WideWorldImporters 
FROM disk='D:\path\WideWorldImporters-Full.bak'
WITH MOVE 'WWI_Primary' TO 'D:\SQL_Data\WideWorldImporters.mdf',
MOVE 'WWI_Log' TO 'D:\SQL_Data\WideWorldImporters_log.ldf',
MOVE 'WWI_UserData' TO 'D:\SQL_Data\WideWorldImporters_UserData.ndf',
MOVE 'WWI_InMemory_Data_1' TO 'D:\SQL_Data\WideWorldImporters_InMemory_Data_1'

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 504 ms.

(53 rows affected)
Table 'Countries'. Scan count 0, logical reads 118, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StateProvinces'. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 10 ms.

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 7 ms.

(53 rows affected)
Table 'Countries'. Scan count 0, logical reads 118, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StateProvinces'. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 179 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

can’t be the only person who hates waiting for the code to compile and run… Especially when I want to test only a small part of it! That’s a total waste of time! But good news everyone, I found a way to run a part of my C# code without building the whole project! 

Ctrl + E, E – the magical shortcut! 

public class Person 
{ 
    public string _firstName; 
 
    public string FirstName 
    { 
        get 
        { 
            return _firstName; 
        } 
        set 
        { 
            _firstName = value; 
        } 
    }  
}

public string _firstName; 
public string FirstName 
{ 
    get 
    { 
        return _firstName; 
    } 
    private set 
    { 
        _firstName = value; 
    } 
}

public class Person
{
   public string FirstName { get; set; }
}

public class User
{
   public string Name { get; private set; }
}

public class User
{
   public string Name { get; }
}

public class Foo
{
   public string FirstName { get; set; } = "Initial Value";
}

public class Foo
{  
   public DateTime DateOfBirth { get; set; }
   public int Age => DateTime.Now.Year - DateOfBirth.Year;  
}

public class User
{
   public string Name { get; private set; }
}

var user = new User
{
   Name = "User 1" // Compile Error
};

The property or indexer 'User.Name' cannot be used in this context
because the set accessor is inaccessible [CS9Features]csharp(CS0272)

public class User
{
  public User(string name)
  {
    this.Name = name;
  }

  public void SetName(string name)
  {
    this.Name = name;
  }

  public string Name { get; private set; }
}

public class User
{
   public string Name { get; init; }
}

var user = new User
{
   Name = "User 1"
};

// Compile Time Error
// Init-only property or indexer 'User.Name' can only be assigned in an object initializer,
// or on 'this' or 'base' in an instance constructor or an 'init' accessor. [CS9Features]csharp(CS8852)
user.Name = "Test";

public class User
{
   public string Name { get; init; }

   public User(string name)
   {
     this.Name = name; // Works fine
   }

   public void SetName(string name)
   {
     this.Name = name; // Compile Time Error
   }
}

    public class Product
    {
        public Product(string name)
        {
            _name = name;
        }

        private readonly string _name;

        public string Name => _name;
    }

    public class Person
    {
        private readonly string _name;

        public string Name
        {
            get => _name;
            init => _name = value;
        }
    }

static void firstCompilation()
{
    var tree = CSharpSyntaxTree.ParseText("class Foo { void Bar(int x) {} }");
 
    var mscorlibReference = MetadataReference.CreateFromFile(typeof (object).Assembly.Location);
 
    var compilationOptions = new CSharpCompilationOptions(OutputKind.DynamicallyLinkedLibrary);
 
    var comp = CSharpCompilation.Create("Demo")
                                .AddSyntaxTrees(tree)
                                .AddReferences(mscorlibReference)
                                .WithOptions(compilationOptions);
 
    var res = comp.Emit("Demo.dll");
 
    if (!res.Success)
    {
        foreach (var diagnostic in res.Diagnostics)
        {
            Console.WriteLine(diagnostic.GetMessage());
        }
    }
}

        static void semanticQuestions()
        {
            var tree = CSharpSyntaxTree.ParseText(@"
using System;
 
class Foo
{
    public static explicit operator DateTime(Foo f)
    {
        throw new NotImplementedException();
    }
 
    void Bar(int x)
    {
    }
}");
 
            var mscorlib = MetadataReference.CreateFromFile(typeof (object).Assembly.Location);
            var options = new CSharpCompilationOptions(OutputKind.DynamicallyLinkedLibrary);
            var comp = CSharpCompilation.Create("Demo").AddSyntaxTrees(tree).AddReferences(mscorlib).WithOptions(options);
            // var res = comp.Emit("Demo.dll");
 
            // boxing
            var conv1 = comp.ClassifyConversion(
                comp.GetSpecialType(SpecialType.System_Int32),
                comp.GetSpecialType(SpecialType.System_Object)
                );
 
            // unboxing
            var conv2 = comp.ClassifyConversion(
                comp.GetSpecialType(SpecialType.System_Object),
                comp.GetSpecialType(SpecialType.System_Int32)
                );
 
            // explicit reference conversion
            var conv3 = comp.ClassifyConversion(
                comp.GetSpecialType(SpecialType.System_Object),
                comp.GetTypeByMetadataName("Foo")
                );
 
            // explicit user-supplied conversion
            var conv4 = comp.ClassifyConversion(
                comp.GetTypeByMetadataName("Foo"),
                comp.GetSpecialType(SpecialType.System_DateTime)
                );
        }

<location path="Glimpse.axd" >
    <system.web>
        <authorization>
            <allow users="Administrator" />
            <deny users="*" />
        </authorization>
    </system.web>
</location>

<glimpse defaultRuntimePolicy="Off" xdt:Transform="SetAttributes">
</glimpse>

using Glimpse.AspNet.Extensions;
using Glimpse.Core.Extensibility;

namespace Test
{
    public class GlimpseSecurityPolicy:IRuntimePolicy
    {
        public RuntimePolicy Execute(IRuntimePolicyContext policyContext)
        {
            // You can perform a check like the one below to control Glimpse's permissions within your application.
// More information about RuntimePolicies can be found at http://getglimpse.com/Help/Custom-Runtime-Policy
var httpContext = policyContext.GetHttpContext();
            if (!httpContext.User.IsInRole("Administrator "))
            {
                return RuntimePolicy.Off;
            }

            return RuntimePolicy.On;
        }

        public RuntimeEvent ExecuteOn
        {
            get { return RuntimeEvent.EndRequest; }
        }
    }
}

• نکته اول : اگر در تگ img مقدار alt را وارد نکنیم و یا alt در این تگ وجود نداشته باشد، یک هشدار توسط کامپایلر svelte برای ما با عنوان <img> element should have an alt attribute> ایجاد میشود. زمان ساخت یک برنامه بسیار مهم است تا قوانین نوشتن یک کد html خوب را رعایت کنیم تا برای تمامی کاربران احتمالی برنامه قابل استفاده باشد. در همین مثال با ایجاد یک هشدار Svelte تلاش میکند که ما را از اشتباه در نوشتن کد html مطلع سازد.

• نکته دوم : اگر نام یک آبجکت تعریف شده و یک attribute، برابر باشد میتوانیم از نسخه کوتاه شده یا Shorthand attributes در svelte استفاده کنیم. به طور مثال در مثال بالا میتوانیم از کد زیر در خط 6 استفاده کنیم.

<img {src} alt="{name} dancing">

 Hello world!

this is a nested component for third tutorial on dotnettips.info

در مثال بالا ما یک کامپوننت جدید را ایجاد کرده و از طریق دستور import به App.svelte اضافه کردیم. نکته‌ای که در اینجا وجود دارد، نحوه مقدار دهی props در کامپوننت‌ها است. اگر به خط 9 دقت کنیم، کامپوننت ما از طریق تگ جدیدی با نام (Nested) به بدنه html برنامه اضافه شده است که یک attribute به نام siteName دارد. siteName متغیر export شده در کامپوننت Nested.svelte است که در کامپوننت‌ها به این صورت مقدار دهی میشود. قبلا نحوه مقدار دهی این خصیصه‌ها را در فایل‌های جاوا اسکریپت مشاهده کرده بودیم. نکته دیگری که باید به آن دقت داشت این است که خصیصه siteName مقدار پیش فرض dotnettips را در Nested.svelte به خود اختصاص داده بود. به همین جهت اگر ما siteName را هنگام استفاده از کامپوننت مقدار دهی نکنیم، از مقدار پیش فرض خود استفاده خواهد کرد. ولی اینجا ما با مقدار دهی آن، siteName را به dotnettips.info تغییر داده‌ایم.

نکته مهم : دقت داشته باشید کامپوننت‌های شما همیشه باید با حروف بزرگ شروع شوند؛ به طور مثال در صورت نوشتن <nested/> محتوای کامپوننت نمایش داده نخواهد شد. svelte، از طریق زیر نظر گرفتن حروف کوچک و بزرگ در ابتدای تگ‌ها، بین تگ‌های html و کامپوننت‌ها تمایز قائل میشود.

Spread  props :

تا اینجا به صورت خلاصه با props یا خصیصه‌ها آشنا شده‌اید و دیدیم که با export کردن یک متغیر در یک کامپوننت، میتوانیم آن را هنگام استفاده مقدار دهی نماییم. برای اینکه تمرینی هم باشد با توجه به مطالبی که تاکنون گفته شده، پروژه‌ی جدیدی را ایجاد کنید و محتوای App.svelte را مانند کد زیر تغییر دهید.

<script>
import Info from './Info.svelte';

const pkg = {
name: 'svelte',
version: 3,
speed: 'blazing',
website: 'https://svelte.dev'
};
</script>

<Info name={pkg.name} version={pkg.version} speed={pkg.speed} website={pkg.website}/>

همانطور که در خط دوم کد می‌بینید، کامپوننتی به نام Info.svelte به این بخش اضافه شده‌است. این کامپوننت را با محتوای زیر ایجاد نمایید:

<script>
export let name;
export let version;
export let speed;
export let website;
</script>

<p>
The <code>{name}</code> package is {speed} fast.
Download version {version} from <a href="https://www.npmjs.com/package/{name}">npm</a>
and <a href={website}>learn more here</a>
</p>

اگر برنامه را اجرا کنید یک چنین خروجی را مشاهده خواهید کرد: 

 The svelte  package is  blazing  fast. Download version  3  from  npm  and  learn more here

<Info {...pkg}/>