نظرات مطالب
پیاده سازی SoftDelete در EF Core

حل مشکل حذف منطقی در جداولی که فیلد منحصر به فرد دارند

[Index(nameof(PhoneNumber), IsUnique = true)]
public class User
{
    public long Id { get; set; }

    [Required]
    [MaxLength(100)]
    public string FullName { get; set; } = null!;

    [Required]
    [MaxLength(11)]
    public string PhoneNumber { get; set; } = null!;

    public bool IsDeleted { get; set; }
}

در موجودیت User فیلد PhoneNumber منحصر به فرد است.

کاربری با شماره تلفن 123 ثبت نام میکند و بعد حذف میشود

الان شماره تلفن 123 آزاد است و کاربر دیگری میتواند با این شماره ثبت نام کند و این کاربر نیز حذف میشود و یک کاربر جدید با شماره تلفن 123 ثبت نام میکند

FullName    PhoneNumber    IsDeleted
User1       123            True
User2       123            True
User3       123            False

پس با وجود اینکه شماره تلفن Unique است ولی میتوان شماره تلفن تکراری داشت به شرطی که IsDeleted=True باشد و باید فقط یک شماره تلفن 123 با IsDeleted=False داشت.

برای اینکار از Filter ها استفاده میکنیم و تنها کافیست در فایل Configuration موجودیت User این کد را اضافه کرد

public class UserConfiguration : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder.HasIndex(x => x.PhoneNumber)
            .HasFilter("[IsDeleted] = 0");
    }
}

الان شرط Unique بودن فقط در صورتی کار میکند که IsDeleted=False باشد.

مطالب
تشخیص نوع فایل با استفاده از محتوای فایل
بی‌شک اگر در سایت خود بخشی را برای دریافت فایل‌های کاربر قرار داده باشید یکی از دغدغه‌های شما اعمال فیلتر و محدودیت روی نوع فایل‌های آپلود شده توسط کاربران خواهد بود. ممکن است سیاست شما پذیرای فایل هایی با پسوند خاص (برای مثال فقط عکس) باشد ولی هیچ تضمینی وجود ندارد که فایلی با پسوند مورد نظر شما محتوایی مشابه با پسوند خود داشته باشد
اگر بخواهیم دقیق‌تر به این موضوع نگاه کنیم فرض می‌کنیم شما در وب سایت خود قسمتی برای آپلود عکس‌های کاربر قرار داده باشید و با مکانیزمی مانند این روش صحت نوع فایل‌های آپلود شده توسط کاربر را بررسی کنید.
اگر شخصی به قصد تخریب و هدر دادن فضای ذخیره سازی شما فایلی با محتوایی غیر از عکس (برای مثال یک فایل اجرایی) را تغییر پسوند داده و به جای عکس آپلود کند چه اتفاقی می‌افتد؟!
دو دلیل مهم برای چک کردن محتوای فایل خواهیم داشت:
1- جلوگیری از اتلاف حافظه ذخیره سازی (ممکن است شخص مهاجم هزاران فایل چند مگابایتی با محتوایی غیر از پسوند فایل بر روی سرور قرار دهد)
2- جلوگیری از اختلال در نمایش فایل‌های آپلود شده (بی شک عدم نمایش عکس در سایت چهره خوبی نخواهد داشت و ممکن است اعتبار سایت را زیر سوال ببرد)

همیشه برای چک کردن نوع فایل باید به دونکته توجه داشت: یکی آنکه پسوند فایل را حتما چک کنیم تا مطابق فیلتر و سیاست مورد انتظار ما باشد ، دوم اینکه به روشی که در ادامه توضیح خواهیم داد، با استفاده از محتوای باینری فایل، MimeType آنرا تشخیص دهیم.
برای اینکار ما از یک تابع API استفاده میکنیم که با استفاده از 255 بایت ابتدایی محتوای فایل، نوع فایل را مشخص می‌کند. این تابع API که FindMimeFromData نام دارد، در فایل urlmon.dll قرار دارد و گویا توسط مرورگر IE برای چک کردن نوع فایل، بر اساس محتوای آن مورد استفاده قرار می‌گیرد. ما نیز از این تابع در دات نت بهره گرفته و با پاس دادن آرایه‌ای از بایتها به آن نوع فایل خود را مشخص می‌کنیم.
کلاسی برای اینکار تهیه شده که در زیر مشاهده میکنید:
using System;
using System.Runtime.InteropServices;
using System.Reflection;

namespace Parsnet.Core
{

    public class MimeTypeDetector
    {
        [DllImport(@"urlmon.dll", CharSet = CharSet.Auto)]
        private extern static System.UInt32 FindMimeFromData(
            System.UInt32 pBC,
            [MarshalAs(UnmanagedType.LPStr)] System.String pwzUrl,
            [MarshalAs(UnmanagedType.LPArray)] byte[] pBuffer,
            System.UInt32 cbSize,
            [MarshalAs(UnmanagedType.LPStr)] System.String pwzMimeProposed,
            System.UInt32 dwMimeFlags,
            out System.UInt32 ppwzMimeOut,
            System.UInt32 dwReserverd
        );

        public string GetMimeType(byte[] content)
        {
            var result = "unknown/unknown";

            try
            {
                byte[] buffer = new byte[256];
                var length = (content.Length > 256) ? 256 : content.Length;
                Array.Copy(content, buffer, length);

                System.UInt32 mimetype;
                FindMimeFromData(0, null, buffer, 256, null, 0, out mimetype, 0);
                System.IntPtr mimeTypePtr = new IntPtr(mimetype);
                result = Marshal.PtrToStringUni(mimeTypePtr);
                Marshal.FreeCoTaskMem(mimeTypePtr);
            }
            catch (Exception ex)
            {
                //Log.WriteError(MethodInfo.GetCurrentMethod(), ex);
            }
            

            return result;
        }

    }
}
تنها نکته قابل توجه در این کد این‌است که در بدنه‌ی متد GetMimeType اگر طول آرایه از 256 بایت بیشتر شود، فقط از 256 بایت ابتدایی آن استفاده میکنیم. در غیر اینصورت کل بایت‌های آرایه، برای چک کردن محتوای فایل به کار گرفته میشوند. برای افزایش کارآیی، بهتر است هنگام فراخوانی این تابع، فقط 256 بایت را به آن ارسال کنید. بخصوص اگر حجم فایلی که باید چک شود زیاد باشد.

نحوه‌ی استفاده از کلاس فوق هم بسیار ساده است. برای مثال هنگام آپلود فایل در MVC می‌توانیم از آن استفاده کنیم:
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Upload(HttpPostedFileBase file)
        {
            //ابتدا با مکانیزم مورد نظر خود پسوند فایل را چک میکنیم اگر پسوند معتبری بود بعد محتوای فایل را چک میکنیم

            var data = new byte[256];
            file.InputStream.Read(data, 0, 256);
            var detector = new Parsnet.Core.MimeTypeDetector();
            var mimeType = detector.GetMimeType(data);
            if (CheckWhiteList(mimeType) == true)
            {
                file.SaveAs("yourpath");
            }
            else
            {
                ModelState.AddModelError("InvalidFileContent", "فایل بارگزاری شده مورد پذیرش نیست.");
            }
            return View();
        }
همیشه از یک مکانیزم دیگر برای اعتبارسنجی پسوند فایل استفاده کنید. برای اینکار می‌توان با تلفیق روش فوق و این روش یک کنترل اعتبارسنجی خوب ایجاد کرد که در مطالب بعدی حتما به آن خواهم پرداخت.
نظرات مطالب
ارتقاء به ASP.NET Core 1.0 - قسمت 4 - فعال سازی پردازش فایل‌های استاتیک
یک موضوعی رو میخواستم مطرح کنم :
طبق یکی از مقالات سری ASP.net MVC سایت با استفاده از Controller فایل‌های آپلود شده رو با یک کلید ،خروجی میداد.

بنده همین موضوع رو در تکنولوژی جدید پیاده سازی کردم اما با مشکل عدم نمایش فایل یا تصویر در خروجی مواجه شدم


موجود بودن فیزیکی فایل هم در مسیر wwwroot/StaticImages/ و هم مسیر MyStaticImages/ :

و نحوه آدرس دهی :
<img src='@Url.Action("DownloadFile", "ImageHandler", new {Area = "", id = item.BaseFileGuids, imgSize = ImageHandlerController.ImgSize.M})' alt=""/>

مسیر به درستی نمایش داده شده و فایل هم پس از بررسی توسط : System.IO.File.Exists = true  می‌باشد.
اما در نمایش چه ادرس مستقیم و چه تگ <img>  خطای زیر نمایش داده میشود :


هر دو مسیر تست شده با قطعه کد زیر ، اما خطا مشابه می‌باشد
چه این گزینه hostingEnvironment.WebRootPath_
و چه این گزینه hostingEnvironment.ContentRootPath _ 
public IActionResult DownloadFile([FromRoute]string id, [FromQuery] ImgSize imgSize)
        {
            var result = _baseFileService.GetFileNameAndFileNameOnDsAndFileType(id);
            if (result == null) return View("Error");

            var fileName = result.Item1;
            string userAgent = Request.Headers["User-Agent"];
            if (IsInternetExplorer(userAgent))
            {
                var htencode = HtmlEncoder.Create();
                var attachment = string.Format("attachment; filename=\"{0}\"", htencode.Encode(fileName));
                _httpContext.HttpContext.Response.Headers.Add("Content-Disposition", attachment);
            }
            var rootPath = Path.Combine(_hostingEnvironment.WebRootPath, _settingsAppPathConfig.Value.ServerImagesRootPath);
            var filepath = Path.Combine(rootPath, imgSize.ToString().ToLower(), result.Item2);
            if (!System.IO.File.Exists(filepath))
            {
                const string notFoundImage = "notFound.jpg";
                var notFoundpath = Path.Combine(rootPath , notFoundImage);
                string contentType;
                new FileExtensionContentTypeProvider().TryGetContentType(notFoundImage, out contentType);
                return File(notFoundpath, contentType, notFoundImage);
            }
            string contentTypebase;
            new FileExtensionContentTypeProvider().TryGetContentType(result.Item3, out contentTypebase);
            return File(filepath, contentTypebase, fileName);
        }
مطالب
چگونگی تعریف خاصیتی از نوع Enum در EF Code First
فرض می‌کنیم که یک Enum بصورت زیر داریم :
[Flags]
public enum Gender : byte
{
     None=0, Male=1, Female=2,
};
حال می‌خواهیم از این Enum در یک مدل ساده استفاده کنیم. از آنجا که EF هنوز قادر به ‍‍‍‍‍‍‍‍‍‍پشتیبانی از Enum نمی‌باشد باید به روش زیر عمل کنیم:
1) توسط data Annotation
public class User
{
    public int UserId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Username { get; set; }

    [Column(Name="Gender")]
    public int InternalGender { get; set; }
    [NotMapped]
    public Gender Gender
    {
        get { return (Gender)this.InternalGender; }
        set { this.InternalGender = (int)value; }
    }

    public DateTime DateOfBirth { get; set; }
}
2) توسط Fluent API
 modelBuilder.Entity<Participant>().Ignore(p => p.Gender);
 modelBuilder.Entity<Participant>().Property(p => p.InternalGender).HasColumnName("Gender");

مطالب
استفاده از SQLDom برای آنالیز عبارات T-SQL، قسمت دوم
مدتی قبل مطلبی را در مورد کتابخانه‌ی ویژه SQL Server که یک T-SQL Parser تمام عیار است، در این سایت مطالعه کردید. در این قسمت، همان مطلب را به نحو بهتر و ساده‌تری بازنویسی خواهیم کرد.
مشکلی که در دراز مدت با SQLDom وجود خواهد داشت، مواردی مانند SelectStarExpression و CreateProcedureStatement و امثال آن هستند. این‌ها را از کجا باید تشخیص داد؟ همچنین مراحل بررسی این اجزاء، نسبتا طولانی هستند و نیاز به یک راه حل عمومی‌تر در این زمینه وجود دارد.

راه حلی برای این مشکل در مطلب «XML ‘Visualizer’ for the TransactSql.ScriptDom parse tree» ارائه شده‌است. در اینجا تمام اجزای TSqlFragment توسط Reflection مورد بررسی و استخراج قرار گرفته و نهایتا یک فایل XML از آن حاصل می‌شود.
اگر نکات ذکر شده در این مقاله را تبدیل به یک برنامه با استفاده مجدد کنیم، به چنین شکلی خواهیم رسید:


این برنامه را از اینجا می‌توانید دریافت کنید:
DomToXml.zip

همانطور که در تصویر مشاهده می‌کنید، اینبار به سادگی، SelectStarExpression قابل تشخیص است و تنها کافی است در T-SQL پردازش شده، به دنبال SelectStarExpression‌ها بود. برای اینکار جهت ساده شدن آنالیز می‌توان با ارث بری از کلاس پایه TSqlFragmentVisitor شروع کرد:
using System;
using System.Linq;
using Microsoft.SqlServer.TransactSql.ScriptDom;

namespace DbCop
{
    public class SelectStarExpressionVisitor : TSqlFragmentVisitor
    {
        public override void ExplicitVisit(SelectStarExpression node)
        {
            Console.WriteLine(
                  "`Select *` detected @StartOffset:{0}, Line:{1}, T-SQL: {2}",
                  node.StartOffset,
                  node.StartLine,
                  string.Join(string.Empty, node.ScriptTokenStream.Select(x => x.Text)).Trim());

            base.ExplicitVisit(node);
        }
    }
}
در کلاس پایه TSqlFragmentVisitor به ازای تمام اشیاء شناخته شده‌ی ScriptDom، یک متد ExplicitVisit قابل بازنویسی درنظر گرفته شده‌است. در اینجا برای مثال نمونه‌ی SelectStarExpression آن را بازنویسی کرده‌ایم.
مرحله‌ی بعد، اجرای این کلاس Visitor است:
    public static class GenericVisitor
    {
        public static void Start(string tSql, TSqlFragmentVisitor visitor)
        {
            IList<ParseError> errors;
            TSqlScript sqlFragment;
            using (var reader = new StringReader(tSql))
            {
                var parser = new TSql120Parser(initialQuotedIdentifiers: true);
                sqlFragment = (TSqlScript)parser.Parse(reader, out errors);
            }

            if (errors != null && errors.Any())
            {
                var sb = new StringBuilder();
                foreach (var error in errors)
                    sb.AppendLine(error.Message);

                throw new InvalidOperationException(sb.ToString());
            }
            sqlFragment.Accept(visitor);
        }
    }
در اینجا متد Accept کلاس TSql120Parser، امکان پذیرش یک Visitor را دارد. به این معنا که Parser در حال کار، هر زمانیکه در حال آنالیز قسمتی از T-SQL دریافتی بود، نتیجه را به اطلاع یکی از متدهای کلاس پایه TSqlFragmentVisitor نیز خواهد رساند. بنابراین دیگر نیازی به نوشتن حلقه و بررسی تک تک اجزای خروجی TSql120Parser نیست. اگر نیاز به بررسی SelectStarExpression داریم، فقط کافی است Visitor آن‌را طراحی کنیم.

مثالی از نحوه‌ی استفاده از کلاس GenericVisitor فوق را در اینجا ملاحظه می‌کنید:
 var tsql = @"WITH ctex AS (
SELECT * FROM sys.objects
)
SELECT * FROM ctex";
GenericVisitor.Start(tsql, new SelectStarExpressionVisitor());
مطالب
چند ستونه کردن در iTextSharp

فرض کنید جدولی دارید با چند ستون محدود که نتیجه‌ی نهایی گزارش آن مثلا 100 صفحه است. جهت صرفه جویی در کاغذ مصرفی شاید بهتر باشد که این جدول را به صورت چند ستونی مثلا 5 ستون در یک صفحه نمایش داد؛ چیزی شبیه به شکل زیر:


روش انجام اینکار به کمک iTextSharp به صورت زیر است:


using System;

using System.IO;
using iTextSharp.text;
using iTextSharp.text.pdf;
using System.Diagnostics;

class Program
{
static void Main(string[] args)
{
using (var pdfDoc = new Document(PageSize.A4))
{
var pdfWriter = PdfWriter.GetInstance(pdfDoc, new FileStream("Test.pdf", FileMode.Create));
pdfDoc.Open();

var table1 = new PdfPTable(1);
table1.WidthPercentage = 100f;
table1.HeaderRows = 2;
table1.FooterRows = 1;

//header row
var headerCell = new PdfPCell(new Phrase("header"));
table1.AddCell(headerCell);

//footer row
var footerCell = new PdfPCell(new Phrase(" "));
table1.AddCell(footerCell);

//adding some rows
for (int i = 0; i < 400; i++)
{
var rowCell = new PdfPCell(new Phrase(i.ToString()));
table1.AddCell(rowCell);
}

// wrapping table1 in multiple columns
ColumnText ct = new ColumnText(pdfWriter.DirectContent);
ct.RunDirection = PdfWriter.RUN_DIRECTION_RTL;
ct.AddElement(table1);

int status = 0;
int count = 0;
int l = 0;
int columnsWidth = 100;
int columnsMargin = 7;
int columnsPerPage = 4;
int r = columnsWidth;
bool isRtl = true;

// render the column as long as it has content
while (ColumnText.HasMoreText(status))
{
if (isRtl)
{
ct.SetSimpleColumn(
pdfDoc.Right - l, pdfDoc.Bottom,
pdfDoc.Right - r, pdfDoc.Top
);
}
else
{
ct.SetSimpleColumn(
pdfDoc.Left + l, pdfDoc.Bottom,
pdfDoc.Left + r, pdfDoc.Top
);
}

var delta = columnsWidth + columnsMargin;
l += delta;
r += delta;

// render as much content as possible
status = ct.Go();

// go to a new page if you've reached the last column
if (++count > columnsPerPage)
{
count = 0;
l = 0;
r = columnsWidth;
pdfDoc.NewPage();
}
}
}

//open the final file with adobe reader for instance.
Process.Start("Test.pdf");
}
}


توضیحات:
تا قسمت تعریف جدول و اضافه کردن سطرها و ستون‌های مورد نظر، همان بحث «تکرار خودکار سرستون‌های یک جدول در صفحات مختلف، توسط iTextSharp» می‌باشد.
اصل مطلب از قسمت ColumnText شروع می‌شود. با استفاده از شیء ColumnText می‌توان محتوای خاصی را در طی چند ستون در صفحه نمایش داد. عرض این ستون‌ها هم توسط متد SetSimpleColumn مشخص می‌شود و همچنین محل دقیق قرارگیری آن‌ها در صفحه. در اینجا دو حالت راست به چپ و چپ به راست در نظر گرفته شده است.
اگر حالت راست به چپ را در نظر بگیریم، محل قرارگیری اولین ستون از سمت راست صفحه (pdfDoc.Right) باید تعیین شود. سپس هربار به اندازه‌ی عرضی که مد نظر است باید محل شروع ستون را مشخص کرد (pdfDoc.Right - l). هر زمانیکه ct.Go فراخوانی می‌شود، تاجایی که میسر باشد، اطلاعات جدول 1 در یک ستون درج می‌شود. سپس بررسی می‌شود که تا این لحظه چند ستون در صفحه نمایش داده شده است. اگر تعداد مورد نظر ما (columnsPerPage) تامین شده باشد، کار را در صفحه‌ی بعد ادامه خواهیم داد (pdfDoc.NewPage)، در غیراینصورت مجددا مکان یک ستون دیگر در همان صفحه تعیین شده و کار افزودن اطلاعات به آن آغاز خواهد شد و این حلقه تا جایی که تمام محتوای جدول 1 را درج کند، ادامه خواهد یافت.


مطالب
بهینه سازی کوئری‌های LINQ - بخش اول
یکی از جذاب‌ترین لحظات کار با LINQ و EF زمانی است که به خاطر افزایش حجم دیتا، کوئری خود را بازنگری کرده و آن را بهینه می‌کنید.

برای یک مسئله می‌توان کوئری‌های متنوعی نوشت که همگی به یک جواب میرسند؛ ولی زمان اجرا و میزان حافظه‌ی مصرفی متفاوتی دارند. یک سناریوی رایج در نوشتن کوئری‌های LINQ، ترکیب اطلاعات جداول مختلف و محاسبه‌ی یک عدد معنی دار از ترکیب آن هاست.

برای نمونه دو Entity زیر را در مدل EF خود داریم:
public class User
{
   public int ID { get; set; }
   public string Name { get; set; }
   public int Age { get; set; }
}

public class Login
{
   public int ID { get; set; }
   public DateTime Date { get; set; }
   public int UserID { get; set; }
   public User User { get; set; }
}
موجودیت User، اطلاعات کاربر و موجودیت Login، اطلاعات مربوط به لوگین‌های هر کاربر را نگه می‌دارد. برای تست، یک دیتاست را به صورت تصادفی تولید کردیم که حاوی 1200 کاربر و 21000 لوگین هست.

برای تولید اطلاعات تصادفی می‌توان از کد زیر در LINQPad استفاده کرد:
int usersCount = 1200;
Random rnd = new Random();
for(int i=0; i<usersCount; i++)
{
   Users.Add(new User()
     {
       Name = $"User {i + 1}",
       Age = rnd.Next(10, i + 10) / 10
     });
}

SaveChanges();

$"Users: {Users.Count()}".Dump();

var usersID = Users.Select(x => x.ID).ToArray();

int loginsCount  = 20000;

for(int i=0; i<loginsCount; i++)
{
    Logins.Add(new Login()
    {
        UserID = usersID[rnd.Next(0, usersID.Length - 1)],
        Date = DateTime.Now.AddDays(rnd.Next(0, i))
    });

    if(i % 1000 == 0)
   {
      SaveChanges();
      $"Save {i + 1}".Dump();
   }
}

SaveChanges();
$"Logins: {Logins.Count()}".Dump();

$"Users: {Users.Count()}".Dump();
$"Logins: {Logins.Count()}".Dump();

Users: 1200
Logins: 21000

مسئله: نمایش اطلاعات پروفایل هر کاربر، به همراه تاریخ آخرین لوگین و تعداد کل لوگین‌های فرد

در سناریوهای این سبکی، باید خیلی با دقت عمل کرد و از تمام اطلاعات موجود استفاده کرد. اطلاعاتی که در اینجا برای ما مفید است، تعداد نسبی رکوردهای جداول دیتابیس است. مثلا در حال حاضر تعداد رکوردهای Logins تقریبا 17 برابر Users است و در آینده هم رشد Logins چند برابر Users خواهد بود. از طرفی در صورت مسئله، اطلاعات هر کاربر را می‌خواهیم، که به سادگی یک SELECT است. ولی بخش سنگین‌تر کوئری، محاسبه‌ی تعداد لوگین‌ها و تاریخ آخرین لوگین‌های هر فرد است که باز هم به جدول Logins بر می‌گردد.

روش اول:

راه حل اولی که به ذهن می‌رسد، JOIN کردن این دو جدول و محاسبه موارد لازم از ترکیب این دو جدول است:
var data =
(
   from u in Users
   join x in Logins on u.ID equals x.UserID into g
   from x in g.DefaultIfEmpty()
   select new
     {
        UserID = u.ID,
        Name = u.Name,
        Age = u.Age,
        Date = x.Date
     }
);

var result =
(
   from d in data
   group d by d.UserID into g
   select new
   {
       UserID = g.Key,
       Name = g.FirstOrDefault().Name,
       LoginsCount = g.Count(x => x.Date != null),
       LastLogin = g.Max(x => (DateTime?) x.Date) ?? null
   }
);
کد SQL تولید شده‌ی در این روش، ترکیبی از 11 دستور SELECT تو در تو و 4 دستور LEFT OUTER JOIN است که ممکن است در حجم اطلاعات بیشتر، کوئری را با کندی همراه کند. نکته‌ی جالب توجه اینست که دستور group by ما در خروجی ظاهر نشده است و تبدیل به دستور SELECT تو در تو شده است که مورد انتظار ما نبوده است.

Generated SQL
SELECT 
    [Project7].[ID] AS [ID], 
    [Project7].[C2] AS [C1], 
    [Project7].[C3] AS [C2], 
    [Project7].[C1] AS [C3]
    FROM ( SELECT 
        [Project6].[ID] AS [ID], 
        CASE WHEN ([Project6].[C3] IS NULL) THEN CAST(NULL AS datetime2) ELSE [Project6].[C4] END AS [C1], 
        [Project6].[C1] AS [C2], 
        [Project6].[C2] AS [C3]
        FROM ( SELECT 
            [Project5].[ID] AS [ID], 
            [Project5].[C1] AS [C1], 
            [Project5].[C2] AS [C2], 
            [Project5].[C3] AS [C3], 
            (SELECT 
                MAX( CAST( [Extent9].[Date] AS datetime2)) AS [A1]
                FROM  [dbo].[Users] AS [Extent8]
                LEFT OUTER JOIN [dbo].[Logins] AS [Extent9] ON [Extent8].[ID] = [Extent9].[UserID]
                WHERE [Project5].[ID] = [Extent8].[ID]) AS [C4]
            FROM ( SELECT 
                [Project4].[ID] AS [ID], 
                [Project4].[C1] AS [C1], 
                [Project4].[C2] AS [C2], 
                (SELECT 
                    MAX( CAST( [Extent7].[Date] AS datetime2)) AS [A1]
                    FROM  [dbo].[Users] AS [Extent6]
                    LEFT OUTER JOIN [dbo].[Logins] AS [Extent7] ON [Extent6].[ID] = [Extent7].[UserID]
                    WHERE [Project4].[ID] = [Extent6].[ID]) AS [C3]
                FROM ( SELECT 
                    [Project3].[ID] AS [ID], 
                    [Project3].[C1] AS [C1], 
                    (SELECT 
                        COUNT(1) AS [A1]
                        FROM [dbo].[Logins] AS [Extent5]
                        WHERE [Project3].[ID] = [Extent5].[UserID]) AS [C2]
                    FROM ( SELECT 
                        [Distinct1].[ID] AS [ID], 
                        (SELECT TOP (1) 
                            [Extent3].[Name] AS [Name]
                            FROM  [dbo].[Users] AS [Extent3]
                            LEFT OUTER JOIN [dbo].[Logins] AS [Extent4] ON [Extent3].[ID] = [Extent4].[UserID]
                            WHERE [Distinct1].[ID] = [Extent3].[ID]) AS [C1]
                        FROM ( SELECT DISTINCT 
                            [Extent1].[ID] AS [ID]
                            FROM  [dbo].[Users] AS [Extent1]
                            LEFT OUTER JOIN [dbo].[Logins] AS [Extent2] ON [Extent1].[ID] = [Extent2].[UserID]
                        )  AS [Distinct1]
                    )  AS [Project3]
                )  AS [Project4]
            )  AS [Project5]
        )  AS [Project6]
    )  AS [Project7]
    ORDER BY [Project7].[C3] ASC, [Project7].[ID] ASC

روش دوم:
روش دوم اینست که داده‌های سنگین‌تر (اطلاعات Logins) را ابتدا محاسبه کرده و سپس JOIN را انجام دهیم:
var data =
(
  from x in Logins
  group x by x.UserID into g
  orderby g.Key descending
  select new
  {
    UserID = g.Key,
    LoginsCount = g.Count(),
    LastLogin = g.Max(d => d.Date)
  }
);

var result =
(
  from u in Users
  join d in data on u.ID equals d.UserID into g
  from d in g.DefaultIfEmpty()
  select new
  {
    UserID = u.ID,
    LoginsCount = d != null ? d.LoginsCount : 0,
    LastLogin = d != null ? (DateTime?)d.LastLogin : null
  }
);
در روش دوم، ابتدا فقط به Logins کوئری می‌زنیم و برای محاسبه‌ی تعداد لوگین و آخرین لوگین، از Group By استفاده می‌کنیم. استفاده از این دستور باعث می‌شود که محاسبه‌ی سنگین ما در سریعترین حالت ممکن توسط  SQL انجام شود. در مرحله‌ی بعد، این اطلاعات را با جدول Users از طریق LEFT OUTER JOIN ترکیب می‌کنیم. علت استفاده از DefaultIfEmpty بدین سبب است که برخی از کاربران ممکن است تاکنون لوگینی را انجام نداده باشند؛ در نتیجه باید تعداد صفر و تاریخ null برای آنها نمایش داده شود.

اکنون اگر کد SQL روش دوم را بررسی کنیم خواهیم دید که تنها 2 دستور SELECT ، یک LEFT OUTER JOIN به همراه یک GROUP BY تولید شده است که با توجه به ماهیت مسئله و ساختار دیتای ما، این دستورات منطقی‌ترین و بهینه‌ترین دستورات ممکن به نظر می‌رسد.

Generated SQL
SELECT 
    [Project1].[ID] AS [ID], 
    [Project1].[C1] AS [C1], 
    [Project1].[C2] AS [C2]
    FROM ( SELECT 
        [Extent1].[ID] AS [ID], 
        CASE WHEN ([GroupBy1].[K1] IS NOT NULL) THEN [GroupBy1].[A1] ELSE 0 END AS [C1], 
        CASE WHEN ([GroupBy1].[K1] IS NOT NULL) THEN  CAST( [GroupBy1].[A2] AS datetime2) END AS [C2]
        FROM  [dbo].[Users] AS [Extent1]
        LEFT OUTER JOIN  (SELECT 
            [Extent2].[UserID] AS [K1], 
            COUNT(1) AS [A1], 
            MAX([Extent2].[Date]) AS [A2]
            FROM [dbo].[Logins] AS [Extent2]
            GROUP BY [Extent2].[UserID] ) AS [GroupBy1] ON [Extent1].[ID] = [GroupBy1].[K1]
    )  AS [Project1]
    ORDER BY [Project1].[C1] ASC, [Project1].[ID] ASC
پس، همواره کد SQL دستورات LINQ خود را یا از طریق SQL Profiler یا برنامه‌ای مثل LINQPad حتما تست کنید و کوئری خود را در مقابل حجم زیاد اطلاعات هم بررسی کنید. چرا که LINQ به علت سادگی و قدرتی که دارد، گاهی شما را به اشتباه می‌اندازد و باعث می‌شود شما کوئری ای بزنید که جواب شما را می‌دهد، ولی فقط برای حجم کم دیتای کنونی بهینه است و در صورت افزایش رکوردها، یا خیلی کند می‌شود یا کلا شما را با  Timeout مواجه می‌کند.
مطالب
ویژگی Batching در EF Core
در EF 6.x به ازای هر عبارت insert/update/delete یکبار رفت و برگشت به بانک اطلاعاتی صورت می‌گیرد. به همین جهت کارآیی تعداد بالای ثبت، به روز رسانی و حذف رکوردها توسط آن پایین است. برای رفع این مشکل ویژگی Batching به EF Core اضافه شده‌است که توسط آن اینبار دسته‌ای از عبارات را به صورت یکجا و در طی یک رفت و برگشت، به سمت بانک اطلاعاتی ارسال می‌کند. به این ترتیب کارآیی و سرعت insert/update/delete آن به شدت افزایش خواهد یافت.


نحوه‌ی فعالسازی Batching در EF Core

Batching به صورت پیش فرض در EF Core بدون نیاز به هیچگونه تنظیم اضافه‌تری فعال است. اما اگر خواستید برای مثال، حالت پیش فرض EF 6.x را توسط آن شبیه سازی کنید، می‌توانید مقدار MaxBatchSize را به عدد 1 تنظیم نمائید (تا غیرفعال شود):
optionsBuilder.UseSqlServer(
   @"Server=(localdb)\mssqllocaldb;Database=Demo.Batching;Trusted_Connection=True;",
   options => options.MaxBatchSize(1)
);

مقدار پیش فرض MaxBatchSize را در کلاس SqlServerModificationCommandBatch می‌توانید مشاهده کنید:
public class SqlServerModificationCommandBatch : AffectedCountModificationCommandBatch
    {
        private const int DefaultNetworkPacketSizeBytes = 4096;
        private const int MaxScriptLength = 65536 * DefaultNetworkPacketSizeBytes / 2;
        private const int MaxParameterCount = 2100;
        private const int MaxRowCount = 1000;
در اینجا MaxRowCount همان MaxBatchSize پیش فرض است که به عدد 1000 تنظیم شده‌است. بنابراین اگر تنظیم options => options.MaxBatchSize(1) را ذکر نکنید، به معنای ارسال 1000 تایی دستورات insert/update/delete در طی یک درخواست به سمت سرور است.


آیا محدودیتی هم در مورد عملیات Batching وجود دارد؟

SQL Server به ازای هر batch تنها 2100 پارامتر را پشتیبانی می‌کند. در این حالت EF Core به صورت خودکار یک چنین کوئری‌های حجیمی را به چند Batch جهت تنظیم این محدودیت تقسیم خواهد کرد و در نهایت برنامه به مشکلی بر نمی‌خورد.


یک آزمایش: Batching پیش فرض به چه صورتی کار می‌کند و چه اثری را دارد؟

کدهای کامل این آزمایش را از اینجا می‌توانید دریافت کنید: Batching.zip
در اینجا کلاس Blog را به همراه Context متناظر با آن مشاهده می‌کنید:
    public class Blog
    {
        public int BlogId { get; set; }
        public string Name { get; set; }
        public string Url { get; set; }
    }

    public class BloggingContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(
                @"Server=(localdb)\mssqllocaldb;Database=Demo.Batching;Trusted_Connection=True;"/*,
                options => options.MaxBatchSize(2)*/
                );
            optionsBuilder.EnableSensitiveDataLogging();
        }
    }
در ابتدا MaxBatchSize را تنظیم نخواهیم کرد. یعنی از همان عدد 1000 پیش فرض استفاده می‌شود. تنظیم EnableSensitiveDataLogging نیز سبب می‌شود تا لاگ نهایی تهیه شده جهت نمایش، پرمحتواتر شود.
در این حالت اگر به روز رسانی‌ها (2 مورد) و ثبت‌های ذیل (6 مورد) را انجام دهیم:
            using (var db = new BloggingContext())
            {
                db.GetService<ILoggerFactory>().AddProvider(new MyLoggerProvider());

                // Modify some existing blogs
                var existing = db.Blogs.ToArray();
                existing[0].Url = "http://sample.com/blogs/dogs";
                existing[1].Url = "http://sample.com/blogs/cats";

                // Insert some new blogs
                db.Blogs.Add(new Blog { Name = "The Horse Blog", Url = "http://sample.com/blogs/horses" });
                db.Blogs.Add(new Blog { Name = "The Snake Blog", Url = "http://sample.com/blogs/snakes" });
                db.Blogs.Add(new Blog { Name = "The Fish Blog", Url = "http://sample.com/blogs/fish" });
                db.Blogs.Add(new Blog { Name = "The Koala Blog", Url = "http://sample.com/blogs/koalas" });
                db.Blogs.Add(new Blog { Name = "The Parrot Blog", Url = "http://sample.com/blogs/parrots" });
                db.Blogs.Add(new Blog { Name = "The Kangaroo Blog", Url = "http://sample.com/blogs/kangaroos" });

                db.SaveChanges();
            }
یک چنین خروجی SQL ایی تولید می‌شود:
Executed DbCommand (41ms) [Parameters=[@p1='57', @p0='http://sample.com/blogs/dogs' (Size = 4000), @p3='58', @p2='http://sample.com/blogs/cats' (Size = 4000), @p4='The Horse Blog' (Size = 4000), @p5='http://sample.com/blogs/horses' (Size = 4000), @p6='The Snake Blog' (Size = 4000), @p7='http://sample.com/blogs/snakes' (Size = 4000), @p8='The Fish Blog' (Size = 4000), @p9='http://sample.com/blogs/fish' (Size = 4000), @p10='The Koala Blog' (Size = 4000), @p11='http://sample.com/blogs/koalas' (Size = 4000), @p12='The Parrot Blog' (Size = 4000), @p13='http://sample.com/blogs/parrots' (Size = 4000), @p14='The Kangaroo Blog' (Size = 4000), @p15='http://sample.com/blogs/kangaroos' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [Blogs] SET [Url] = @p0
WHERE [BlogId] = @p1;
SELECT @@ROWCOUNT;

UPDATE [Blogs] SET [Url] = @p2
WHERE [BlogId] = @p3;
SELECT @@ROWCOUNT;

DECLARE @inserted2 TABLE ([BlogId] int, [_Position] [int]);
MERGE [Blogs] USING (
VALUES (@p4, @p5, 0),
(@p6, @p7, 1),
(@p8, @p9, 2),
(@p10, @p11, 3),
(@p12, @p13, 4),
(@p14, @p15, 5)) AS i ([Name], [Url], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([Name], [Url])
VALUES (i.[Name], i.[Url])
OUTPUT INSERTED.[BlogId], i._Position
INTO @inserted2;

SELECT [t].[BlogId] FROM [Blogs] t
INNER JOIN @inserted2 i ON ([t].[BlogId] = [i].[BlogId])
ORDER BY [i].[_Position];
در این دستورات موارد ذیل قابل توجه هستند:
- فقط یکبار Executed DbCommand مشاهده می‌شود.
- کل دستورات update و insert در طی یک درخواست و یک تراکنش به سمت بانک اطلاعاتی ارسال شده‌اند.
- ثبت دسته‌ای توسط merge using انجام شده‌است.
- در آخر نیز طبق معمول کار EF، شماره Idهای رکوردهای ثبت شده به سمت کلاینت بازگشت داده می‌شود.

در ادامه MaxBatchSize را به عدد 2 تنظیم می‌کنیم:
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
   optionsBuilder.UseSqlServer(
     @"Server=(localdb)\mssqllocaldb;Database=Demo.Batching;Trusted_Connection=True;",
     options => options.MaxBatchSize(2)
    );
    optionsBuilder.EnableSensitiveDataLogging();
}
در این حالت اگر برنامه را اجرا کنیم، یک چنین خروجی قابل مشاهده خواهد بود:
Executed DbCommand (17ms) [Parameters=[@p1='65', @p0='http://sample.com/blogs/dogs' (Size = 4000), @p3='66', @p2='http://sample.com/blogs/cats' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [Blogs] SET [Url] = @p0
WHERE [BlogId] = @p1;
SELECT @@ROWCOUNT;

UPDATE [Blogs] SET [Url] = @p2
WHERE [BlogId] = @p3;
SELECT @@ROWCOUNT;

Executed DbCommand (18ms) [Parameters=[@p0='The Horse Blog' (Size = 4000), @p1='http://sample.com/blogs/horses' (Size = 4000), @p2='The Snake Blog' (Size = 4000), @p3='http://sample.com/blogs/snakes' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
DECLARE @inserted0 TABLE ([BlogId] int, [_Position] [int]);
MERGE [Blogs] USING (
VALUES (@p0, @p1, 0),
(@p2, @p3, 1)) AS i ([Name], [Url], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([Name], [Url])
VALUES (i.[Name], i.[Url])
OUTPUT INSERTED.[BlogId], i._Position
INTO @inserted0;

SELECT [t].[BlogId] FROM [Blogs] t
INNER JOIN @inserted0 i ON ([t].[BlogId] = [i].[BlogId])
ORDER BY [i].[_Position];

Executed DbCommand (34ms) [Parameters=[@p0='The Fish Blog' (Size = 4000), @p1='http://sample.com/blogs/fish' (Size = 4000), @p2='The Koala Blog' (Size = 4000), @p3='http://sample.com/blogs/koalas' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
DECLARE @inserted0 TABLE ([BlogId] int, [_Position] [int]);
MERGE [Blogs] USING (
VALUES (@p0, @p1, 0),
(@p2, @p3, 1)) AS i ([Name], [Url], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([Name], [Url])
VALUES (i.[Name], i.[Url])
OUTPUT INSERTED.[BlogId], i._Position
INTO @inserted0;

SELECT [t].[BlogId] FROM [Blogs] t
INNER JOIN @inserted0 i ON ([t].[BlogId] = [i].[BlogId])
ORDER BY [i].[_Position];

Executed DbCommand (15ms) [Parameters=[@p0='The Parrot Blog' (Size = 4000), @p1='http://sample.com/blogs/parrots' (Size = 4000), @p2='The Kangaroo Blog' (Size = 4000), @p3='http://sample.com/blogs/kangaroos' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
DECLARE @inserted0 TABLE ([BlogId] int, [_Position] [int]);
MERGE [Blogs] USING (
VALUES (@p0, @p1, 0),
(@p2, @p3, 1)) AS i ([Name], [Url], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([Name], [Url])
VALUES (i.[Name], i.[Url])
OUTPUT INSERTED.[BlogId], i._Position
INTO @inserted0;

SELECT [t].[BlogId] FROM [Blogs] t
INNER JOIN @inserted0 i ON ([t].[BlogId] = [i].[BlogId])
ORDER BY [i].[_Position];
در این دستورات موارد ذیل قابل توجه هستند:
- اینبار تعداد 4 دستور Executed DbCommand مشاهده می‌شود ( برای انجام 2 به روز رسانی و 6 ثبت).
- هر batch بر اساس تنظیم MaxBatchSize به 2 دستور T-SQL محدود شده‌است که البته در انتها در حالت‌های insert، یک select هم برای بازگشت Idها به سمت کلاینت وجود دارد.
بنابراین اینبار بجای یکبار رفت و برگشت حالت قبل (استفاده از مقدار پیش فرض 1000 برای MaxBatchSize)، 4 بار رفت و برگشت به سمت بانک اطلاعاتی صورت گرفته‌است.

زمان کل انجام عملیات در حالت اول 41 میلی ثانیه و در حالت دوم 84 میلی ثانیه است که سرعت آن 51 درصد نسبت به حالت اول کاهش یافته‌است.
مطالب
استفاده از Full text search توسط Entity Framework
پیشنیاز مطلب:
پشتیبانی از Full Text Search در SQL Server

Full Text Search یا به اختصار FTS یکی از قابلیت‌های SQL Server جهت جستجوی پیشرفته در متون میباشد. این قابلیت تا کنون در EF 6.1.1 ایجاد نشده است.
در ادامه پیاده سازی از FTS در EF را مشاهده مینمایید.
جهت ایجاد قابلیت FTS از متد Contains در Linq استفاده شده است.
ابتدا متد‌های الحاقی جهت اعمال دستورات FREETEXT و CONTAINS اضافه میشود.سپس دستورات تولیدی EF را قبل از اجرا بر روی بانک اطلاعاتی توسط امکان Command Interception به دستورات FTS تغییر میدهیم.
همانطور که میدانید دستور Contains در Linq توسط EF به دستور LIKE تبدیل میشود. به جهت اینکه ممکن است بخواهیم از دستور LIKE نیز استفاده کنیم یک پیشوند به مقادیری که میخواهیم به دستورات FTS تبدیل شوند اضافه مینماییم.
جهت استفاده از Fts در EF کلاس‌های زیر را ایجاد نمایید.
 
کلاس FullTextPrefixes :
/// <summary>
    /// 
    /// </summary>
    public static class FullTextPrefixes
    {
        /// <summary>
        /// 
        /// </summary>
        public const string ContainsPrefix = "-CONTAINS-";

        /// <summary>
        /// 
        /// </summary>
        public const string FreetextPrefix = "-FREETEXT-";

        /// <summary>
        /// 
        /// </summary>
        /// <param name="searchTerm"></param>
        /// <returns></returns>
        public static string Contains(string searchTerm)
        {
            return string.Format("({0}{1})", ContainsPrefix, searchTerm);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="searchTerm"></param>
        /// <returns></returns>
        public static string Freetext(string searchTerm)
        {
            return string.Format("({0}{1})", FreetextPrefix, searchTerm);
        }

    }

کلاس جاری جهت علامت گذاری دستورات FTS میباشد و توسط متد‌های الحاقی و کلاس FtsInterceptor استفاده میگردد.

کلاس FullTextSearchExtensions :
public static class FullTextSearchExtensions
    {

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="source"></param>
        /// <param name="expression"></param>
        /// <param name="searchTerm"></param>
        /// <returns></returns>
        public static IQueryable<TEntity> FreeTextSearch<TEntity>(this IQueryable<TEntity> source, Expression<Func<TEntity, object>> expression, string searchTerm) where TEntity : class
        {
            return FreeTextSearchImp(source, expression, FullTextPrefixes.Freetext(searchTerm));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="source"></param>
        /// <param name="expression"></param>
        /// <param name="searchTerm"></param>
        /// <returns></returns>
        public static IQueryable<TEntity> ContainsSearch<TEntity>(this IQueryable<TEntity> source, Expression<Func<TEntity, object>> expression, string searchTerm) where TEntity : class
        {
            return FreeTextSearchImp(source, expression, FullTextPrefixes.Contains(searchTerm));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="source"></param>
        /// <param name="expression"></param>
        /// <param name="searchTerm"></param>
        /// <returns></returns>
        private static IQueryable<TEntity> FreeTextSearchImp<TEntity>(this IQueryable<TEntity> source, Expression<Func<TEntity, object>> expression, string searchTerm)
        {
            if (String.IsNullOrEmpty(searchTerm))
            {
                return source;
            }

            // The below represents the following lamda:
            // source.Where(x => x.[property].Contains(searchTerm))

            //Create expression to represent x.[property].Contains(searchTerm)
            //var searchTermExpression = Expression.Constant(searchTerm);
            var searchTermExpression = Expression.Property(Expression.Constant(new { Value = searchTerm }), "Value");
            var checkContainsExpression = Expression.Call(expression.Body, typeof(string).GetMethod("Contains"), searchTermExpression);

            //Join not null and contains expressions

            var methodCallExpression = Expression.Call(typeof(Queryable),
                                                       "Where",
                                                       new[] { source.ElementType },
                                                       source.Expression,
                                                       Expression.Lambda<Func<TEntity, bool>>(checkContainsExpression, expression.Parameters));

            return source.Provider.CreateQuery<TEntity>(methodCallExpression);
        }

در این کلاس متدهای الحاقی جهت اعمال قابلیت Fts ایجاد شده است.
متد FreeTextSearch جهت استفاده از دستور FREETEXT  استفاده میشود.در این متد پیشوند -FREETEXT- جهت علامت گذاری این دستور به ابتدای مقدار جستجو اضافه میشود. این متد دارای دو پارامتر میباشد ، اولی ستونی که میخواهیم بر روی آن جستجوی FTS انجام دهیم و دومی عبارت جستجو.
متد ContainsSearch جهت استفاده از دستور CONTAINS استفاده میشود.در این متد پیشوند -CONTAINS- جهت علامت گذاری این دستور به ابتدای مقدار جستجو اضافه میشود. این متد دارای دو پارامتر میباشد ، اولی ستونی که میخواهیم بر روی آن جستجوی FTS انجام دهیم و دومی عبارت جستجو. 
متد FreeTextSearchImp جهت ایجاد دستور Contains در Linq میباشد.
 
کلاس FtsInterceptor :
 public class FtsInterceptor : IDbCommandInterceptor
    {

        /// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        /// <param name="interceptionContext"></param>
        public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        /// <param name="interceptionContext"></param>
        public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        /// <param name="interceptionContext"></param>
        public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
            RewriteFullTextQuery(command);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        /// <param name="interceptionContext"></param>
        public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        /// <param name="interceptionContext"></param>
        public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
            RewriteFullTextQuery(command);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        /// <param name="interceptionContext"></param>
        public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmd"></param>
        public static void RewriteFullTextQuery(DbCommand cmd)
        {
            var text = cmd.CommandText;
            for (var i = 0; i < cmd.Parameters.Count; i++)
            {
                var parameter = cmd.Parameters[i];
                if (
                    !parameter.DbType.In(DbType.String, DbType.AnsiString, DbType.StringFixedLength,
                        DbType.AnsiStringFixedLength)) continue;
                if (parameter.Value == DBNull.Value)
                    continue;
                var value = (string)parameter.Value;
                if (value.IndexOf(FullTextPrefixes.ContainsPrefix, StringComparison.Ordinal) >= 0)
                {
                    parameter.Size = 4096;
                    parameter.DbType = DbType.AnsiStringFixedLength;
                    value = value.Replace(FullTextPrefixes.ContainsPrefix, ""); // remove prefix we added n linq query
                    value = value.Substring(1, value.Length - 2); // remove %% escaping by linq translator from string.Contains to sql LIKE
                    parameter.Value = value;
                    cmd.CommandText = Regex.Replace(text,
                        string.Format(
                            @"\[(\w*)\].\[(\w*)\]\s*LIKE\s*@{0}\s?(?:ESCAPE N?'~')", parameter.ParameterName),
                        string.Format(@"CONTAINS([$1].[$2], @{0})", parameter.ParameterName));
                    if (text == cmd.CommandText)
                        throw new Exception("FTS was not replaced on: " + text);
                    text = cmd.CommandText;
                }
                else if (value.IndexOf(FullTextPrefixes.FreetextPrefix, StringComparison.Ordinal) >= 0)
                {
                    parameter.Size = 4096;
                    parameter.DbType = DbType.AnsiStringFixedLength;
                    value = value.Replace(FullTextPrefixes.FreetextPrefix, ""); // remove prefix we added n linq query
                    value = value.Substring(1, value.Length - 2); // remove %% escaping by linq translator from string.Contains to sql LIKE
                    parameter.Value = value;
                    cmd.CommandText = Regex.Replace(text,
                        string.Format(
                            @"\[(\w*)\].\[(\w*)\]\s*LIKE\s*@{0}\s?(?:ESCAPE N?'~')", parameter.ParameterName),
                        string.Format(@"FREETEXT([$1].[$2], @{0})", parameter.ParameterName));
                    if (text == cmd.CommandText)
                        throw new Exception("FTS was not replaced on: " + text);
                    text = cmd.CommandText;
                }
            }
        }

    }

در این کلاس دستوراتی را که توسط متد‌های الحاقی جهت امکان Fts علامت گذاری شده بودند را یافته و دستور LIKE را با دستورات  CONTAINSو FREETEXT جایگزین میکنیم.

در ادامه برنامه ای جهت استفاده از این امکان به همراه دستورات تولیدی آنرا مشاهده مینمایید.
public class Note
    {
        /// <summary>
        /// 
        /// </summary>
        public int Id { get; set; }

        /// <summary>
        /// 
        /// </summary>
        public string NoteText { get; set; }
    }

  public class FtsSampleContext : DbContext
    {
        /// <summary>
        /// 
        /// </summary>
        public DbSet<Note> Notes { get; set; }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="modelBuilder"></param>
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.Add(new NoteMap());
        }
    }

/// <summary>
    /// 
    /// </summary>
    class Program
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            DbInterception.Add(new FtsInterceptor());
            const string searchTerm = "john";
            using (var db = new FtsSampleContext())
            {
                var result1 = db.Notes.FreeTextSearch(a => a.NoteText, searchTerm).ToList();
                //SQL Server Profiler result ===>>>
                //exec sp_executesql N'SELECT 
                //    [Extent1].[Id] AS [Id], 
                //    [Extent1].[NoteText] AS [NoteText]
                //    FROM [dbo].[Notes] AS [Extent1]
                //    WHERE FREETEXT([Extent1].[NoteText], @p__linq__0)',N'@p__linq__0 
                //char(4096)',@p__linq__0='(john)'
                var result2 = db.Notes.ContainsSearch(a => a.NoteText, searchTerm).ToList();
                //SQL Server Profiler result ===>>>
                //exec sp_executesql N'SELECT 
                //    [Extent1].[Id] AS [Id], 
                //    [Extent1].[NoteText] AS [NoteText]
                //    FROM [dbo].[Notes] AS [Extent1]
                //    WHERE CONTAINS([Extent1].[NoteText], @p__linq__0)',N'@p__linq__0 
                //char(4096)',@p__linq__0='(john)'
            }
            Console.ReadKey();
        }
    }

ابتدا کلاس FtsInterceptor را به EF معرفی مینماییم. سپس از دو متد الحاقی مذکور استفاده مینماییم. خروجی هر دو متد توسط SQL Server Profiler در زیر هر متد مشاهده مینمایید.
تمامی کدها و مثال مربوطه در آدرس https://effts.codeplex.com قرار گرفته است.
منبع:
Full Text Search in Entity Framework 6
مطالب
React 16x - قسمت 28 - احراز هویت و اعتبارسنجی کاربران - بخش 3 - فراخوانی منابع محافظت شده و مخفی کردن عناصر صفحه
ارسال خودکار هدرهای ویژه‌ی Authorization، به سمت سرور

در برنامه‌ی backend این سری (که از انتهای مطلب قابل دریافت است)، به Controllers\MoviesController.cs مراجعه کرده و متدهای Get/Delete/Create آن‌را با فیلتر [Authorize] مزین می‌کنیم تا دسترسی به آن‌ها، تنها به کاربران لاگین شده‌ی در سیستم، محدود شود. در این حالت اگر به برنامه‌ی React مراجعه کرده و برای مثال سعی در ویرایش رکوردی کنیم، اتفاقی رخ نخواهد داد:


علت را نیز در برگه‌ی network کنسول توسعه دهندگان مرورگر، می‌توان مشاهده کرد. این درخواست از سمت سرور با Status Code: 401، برگشت خورده‌است. برای رفع این مشکل باید JSON web token ای را که در حین لاگین، از سمت سرور دریافت کرده بودیم، به همراه درخواست خود، مجددا به سمت سرور ارسال کنیم. این ارسال نیز باید به صورت یک هدر مخصوص با کلید Authorization و مقدار "Bearer jwt" باشد.
به همین جهت ابتدا به src\services\authService.js مراجعه کرده و متدی را برای بازگشت JWT ذخیره شده‌ی در local storage به آن اضافه می‌کنیم:
export function getLocalJwt(){
  return localStorage.getItem(tokenKey);
}
سپس به src\services\httpService.js مراجعه کرده و از آن استفاده می‌کنیم:
import * as auth from "./authService";

axios.defaults.headers.common["Authorization"] = "Bearer " + auth.getLocalJwt();
کار این یک سطر که در ابتدای ماژول httpService قرار می‌گیرد، تنظیم هدرهای پیش‌فرض تمام انواع درخواست‌های ارسالی توسط Axios است. البته می‌توان از حالت‌های اختصاصی‌تری نیز مانند فقط post، بجای common استفاده کرد. برای نمونه در تنظیم فوق، تمام درخواست‌های HTTP Get/Post/Delete/Put ارسالی توسط Axios، دارای هدر Authorization که مقدار آن به ثابتی شروع شده‌ی با Bearer و سپس مقدار JWT دریافتی از سرور تنظیم می‌شود، خواهند بود.

مشکل! اگر برنامه را در این حالت اجرا کنید، یک چنین خطایی را مشاهده خواهید کرد:
Uncaught ReferenceError: Cannot access 'tokenKey' before initialization
علت اینجا است که سرویس httpService، دارای ارجاعی به سرویس authService شده‌است و برعکس (در httpService، یک import از authService را داریم و در authService، یک import از httpService را)! یعنی یک وابستگی حلقوی و دو طرفه رخ‌داده‌است.
برای رفع این خطا باید ابتدا مشخص کنیم که کدامیک از این ماژول‌ها، اصلی است و کدامیک باید وابسته‌ی به دیگری باشد. در این حالت httpService، ماژول اصلی است و بدون آن و با نبود امکان اتصال به backend، دیگر authService قابل استفاده نخواهد بود.
به همین جهت به httpService مراجعه کرده و import مربوط به authService را از آن حذف می‌کنیم. سپس در همینجا متدی را برای تنظیم هدر Authorizationاضافه کرده و آن‌را به لیست default exports این ماژول نیز اضافه می‌کنیم:
function setJwt(jwt) {
  axios.defaults.headers.common["Authorization"] = "Bearer " + jwt;
}

//...

export default {
  // ...
  setJwt
};
در آخر، در authService که ارجاعی را به httpService دارد، فراخوانی متد setJwt را در ابتدای ماژول، انجام خواهیم داد:
http.setJwt(getLocalJwt());
به این ترتیب، وابستگی حلقوی بین این دو ماژول برطرف می‌شود و اکنون این authService است که به httpService وابسته‌است و نه برعکس.

تا اینجا اگر تغییرات را ذخیره کرده و سعی در ویرایش یکی از رکوردهای فیلم‌های نمایش داده شده کنیم، این‌کار با موفقیت انجام می‌شود؛ چون اینبار درخواست ارسالی، دارای هدر ویژه‌ی authorization است:



روش بررسی انقضای توکن‌ها در سمت کلاینت

اگر JWT قدیمی و منقضی شده‌ی از روز گذشته را آزمایش کنید، باز هم از سمت سرور، Status Code: 401 دریافت خواهد شد. اما اینبار در لاگ‌های برنامه‌ی سمت سرور، OnChallenge error مشخص است. در این حالت باید یکبار logout کرد تا JWT قدیمی حذف شود. سپس نیاز به لاگین مجدد است تا یک JWT جدید دریافت گردد. می‌توان اینکار را پیش از ارسال اطلاعات به سمت سرور، در سمت کلاینت نیز بررسی کرد:
function checkExpirationDate(user) {
  if (!user || !user.exp) {
    throw new Error("This access token doesn't have an expiration date!");
  }

  user.expirationDateUtc = new Date(0); // The 0 sets the date to the epoch
  user.expirationDateUtc.setUTCSeconds(user.exp);

  const isAccessTokenTokenExpired =
    user.expirationDateUtc.valueOf() < new Date().valueOf();
  if (isAccessTokenTokenExpired) {
    throw new Error("This access token is expired!");
  }
}
در اینجا user، همان شیء حاصل از const user = jwtDecode(jwt) است که در قسمت قبل به آن پرداختیم. سپس خاصیت exp آن با زمان جاری مقایسه شده و در صورت وجود مشکلی، استثنایی را صادر می‌کند. می‌توان این متد را پس از فراخوانی jwtDecode، قرار داد.


محدود کردن حذف رکوردهای فیلم‌ها به نقش Admin در Backend

تا اینجا تمام کاربران وارد شده‌ی به سیستم، می‌توانند علاوه بر ویرایش فیلم‌ها، آن‌ها را نیز حذف کنند. به همین جهت می‌خواهیم دسترسی حذف را از کاربرانی که ادمین نیستند، بگیریم. برای این منظور، در سمت سرور کافی است در کنترلر MoviesController، ویژگی [Authorize(Policy = CustomRoles.Admin)] را به اکشن متد Delete، اضافه کنیم. به این ترتیب اگر کاربری در سیستم ادمین نبود و درخواست حذف رکوردی را صادر کرد، خطای 403 را از سمت سرور دریافت می‌کند:


در برنامه‌ی مثال backend این سری، در فایل Services\UsersDataSource.cs، یک کاربر ادمین پیش‌فرض ثبت شده‌است. مابقی کاربرانی که به صورت معمولی در سایت ثبت نام می‌کنند، ادمین نیستند.
در این حالت اگر کاربری ادمین بود، چون در توکن او که در فایل Services\TokenFactoryService.cs صادر می‌شود، یک User Claim ویژه‌ی از نوع Role و با مقدار Admin وجود دارد:
if (user.IsAdmin)
{
   claims.Add(new Claim(ClaimTypes.Role, CustomRoles.Admin, ClaimValueTypes.String, _configuration.Value.Issuer));
}
این مقدار، در payload توکن نهایی او نیز ظاهر خواهد شد:
{
  // ...
  "http://schemas.microsoft.com/ws/2008/06/identity/claims/role": "Admin",
  // ...
}
بنابراین هربار که برنامه‌ی React ما، هدر Bearer jwt را به سمت سرور ارسال می‌کند، فیلتر Authorize محدود شده‌ی به نقش ادمین، این نقش را در صورت وجود در توکن او، پردازش کرده و دسترسی‌های لازم را به صورت خودکار صادر می‌کند و همانطور که پیشتر نیز عنوان شد، اگر کاربری این نقش را به صورت دستی به توکن ارسالی به سمت سرور اضافه کند، به دلیل دسترسی نداشتن به کلیدهای خصوصی تولید مجدد امضای دیجیتال توکن، درخواست او در سمت سرور تعیین اعتبار نشده و برگشت خواهد خورد.


نکته 1: اگر در اینجا چندین بار یک User Claim را با مقادیر متفاوتی، به لیست claims اضافه کنیم، مقادیر آن در خروجی نهایی، به شکل یک آرایه ظاهر می‌گردند.

نکته 2: پیاده سازی سمت سرور backend این سری، یک باگ امنیتی مهم را دارد! در حین ثبت نام، کاربران می‌توانند مقدار خاصیت isAdmin شیء User را:
    public class User : BaseModel
    {
        [Required, MinLength(2), MaxLength(50)]
        public string Name { set; get; }

        [Required, MinLength(5), MaxLength(255)]
        public string Email { set; get; }

        [Required, MinLength(5), MaxLength(1024)]
        public string Password { set; get; }

        public bool IsAdmin { set; get; }
    }
خودشان دستی تنظیم کرده و ارسال کنند تا به صورت ادمین ثبت شوند! به این مشکل مهم، اصطلاحا mass assignment گفته می‌شود.
راه حل اصولی مقابله‌ی با آن، داشتن یک DTO و یا ViewModel خاص قسمت ثبت نام و جدا کردن مدل متناظر با موجودیت User، از شیءای است که اطلاعات نهایی را از کاربر، دریافت می‌کند. شیءای که اطلاعات را از کاربر دریافت می‌کند، نباید دارای خاصیت isAdmin قابل تنظیم در حین ثبت نام معمولی کاربران سایت باشد. یک روش دیگر حل این مشکل، استفاده از ویژگی Bind و ذکر صریح نام خواصی است که قرار است bind شوند و نه هیچ خاصیت دیگری از شیء User:
[HttpPost]
public ActionResult<User> Create(
            [FromBody]
            [Bind(nameof(Models.User.Name), nameof(Models.User.Email), nameof(Models.User.Password))]
            User data)
        {
و یا حتی می‌توان ویژگی [BindNever] را بر روی خاصیت IsAdmin، در مدل User قرار داد.

نکته 3: اگر می‌خواهید در برنامه‌ی React، با مواجه شدن با خطای 403 از سمت سرور، کاربر را به یک صفحه‌ی عمومی «دسترسی ندارید» هدایت کنید، می‌توانید از interceptor سراسری که در قسمت 24 تعریف کردیم، استفاده کنید. در اینجا status code = 403 را جهت history.push به یک آدرس access-denied سفارشی و جدید، پردازش کنید.


نمایش یا مخفی کردن المان‌ها بر اساس سطوح دسترسی کاربر وارد شده‌ی به سیستم

می‌خواهیم در صفحه‌ی نمایش لیست فیلم‌ها، دکمه‌ی new movie را که بالای صفحه قرار دارد، به کاربرانی که لاگین نکرده‌اند، نمایش ندهیم. همچنین نمی‌خواهیم اینگونه کاربران، بتوانند فیلمی را ویرایش و یا حذف کنند؛ یعنی لینک به صفحه‌ی جزئیات ویرایشی فیلم‌ها و ستونی که دکمه‌ها‌ی حذف هر ردیف را نمایش می‌دهد، به کاربران وارد نشده‌ی به سیستم نمایش داده نشوند.

در قسمت قبل، در فایل app.js، شیء currentUser را به state اضافه کردیم و با استفاده از ارسال آن به کامپوننت NavBar:
<NavBar user={this.state.currentUser} />
 نام کاربر وارد شده‌ی به سیستم را نمایش دادیم. با استفاده از همین روش می‌توان شیء currentUser را به کامپوننت Movies ارسال کرد و سپس بر اساس محتوای آن، قسمت‌های مختلف صفحه را مخفی کرد و یا نمایش داد. البته در اینجا (در فایل app.js) خود کامپوننت Movies درج نشده‌است؛ بلکه مسیریابی آن‌را تعریف کرده‌ایم که با روش ارسال پارامتر به یک مسیریابی، در قسمت 15، قابل تغییر و پیاده سازی است:
<Route
   path="/movies"
   render={props => <Movies {...props} user={this.state.currentUser} />}
/>
در اینجا برای ارسال props به یک کامپوننت، نیاز است از ویژگی render استفاده شود. سپس پارامتر arrow function را به همان props تنظیم می‌کنیم. همچنین با استفاده از spread operator، این props را در المان JSX تعریف شده، گسترده و تزریق می‌کنیم؛ تا از سایر خواص پیشینی که تزریق شده بودند مانند history، location و match، محروم نشویم و آن‌ها را از دست ندهیم. در نهایت المان کامپوننت مدنظر را همانند روش متداولی که برای تعریف تمام کامپوننت‌های React و تنظیم ویژگی‌های آن‌ها استفاده می‌شود، بازگشت می‌دهیم.

پس از این تغییر به فایل src\components\movies.jsx مراجعه کرده و شیء user را در متد رندر، دریافت می‌کنیم:
class Movies extends Component {
  // ...

  render() {
    const { user } = this.props;
    // ...
اکنون که در کامپوننت Movies به این شیء user دسترسی پیدا کردیم، توسط آن می‌توان قسمت‌های مختلف صفحه را مخفی کرد و یا نمایش داد:
{user && (
  <Link
    to="/movies/new"
    className="btn btn-primary"
    style={{ marginBottom: 20 }}
  >
    New Movie
  </Link>
)}
با این تغییر، اگر شیء user مقدار دهی شده باشد، عبارت پس از &&، در صفحه درج خواهد شد و برعکس:


در این تصویر همانطور که مشخص است، کاربر هنوز به سیستم وارد نشده‌است؛ بنابراین به علت null بودن شیء user، دکمه‌ی New Movie را مشاهده نمی‌کند.


روش دریافت نقش‌های کاربر وارد شده‌ی به سیستم در سمت کلاینت

همانطور که پیشتر در مطلب جاری عنوان شد، نقش‌های دریافتی از سرور، یک چنین شکلی را در jwtDecode نهایی (یا user در اینجا) دارند:
{
  // ...
  "http://schemas.microsoft.com/ws/2008/06/identity/claims/role": "Admin",
  // ...
}
که البته اگر چندین Role تعریف شده باشند، مقادیر آن‌ها در خروجی نهایی، به شکل یک آرایه ظاهر می‌گردد. بنابراین برای بررسی آن‌ها می‌توان نوشت:
function addRoles(user) {
  const roles =
    user["http://schemas.microsoft.com/ws/2008/06/identity/claims/role"];
  if (roles) {
    if (Array.isArray(roles)) {
      user.roles = roles.map(role => role.toLowerCase());
    } else {
      user.roles = [roles.toLowerCase()];
    }
  }
}
کار این متد، دریافت نقش و یا نقش‌های ممکن از jwtDecode، و بازگشت آن‌ها (افزودن آن‌ها به صورت یک خاصیت جدید، به نام roles، به شیء user دریافتی) به صورت یک آرایه‌ی با عناصری LowerCase است. سپس اگر نیاز به بررسی نقش‌، یا نقش‌های کاربری خاص بود، می‌توان از یکی از متدهای زیر استفاده کرد:
export function isAuthUserInRoles(user, requiredRoles) {
  if (!user || !user.roles) {
    return false;
  }

  if (user.roles.indexOf(adminRoleName.toLowerCase()) >= 0) {
    return true; // The `Admin` role has full access to every pages.
  }

  return requiredRoles.some(requiredRole => {
    if (user.roles) {
      return user.roles.indexOf(requiredRole.toLowerCase()) >= 0;
    } else {
      return false;
    }
  });
}

export function isAuthUserInRole(user, requiredRole) {
  return isAuthUserInRoles(user, [requiredRole]);
}
متد isAuthUserInRoles، آرایه‌ای از نقش‌ها را دریافت می‌کند و سپس بررسی می‌کند که آیا کاربر انتخابی، دارای این نقش‌ها هست یا خیر و متد isAuthUserInRole، تنها یک نقش را بررسی می‌کند.
در این کدها، adminRoleName به صورت زیر تامین شده‌است:
import { adminRoleName, apiUrl } from "../config.json";
یعنی محتویات فایل config.json تعریف شده را به صورت زیر با افزودن نام نقش ادمین، تغییر داده‌ایم:
{
  "apiUrl": "https://localhost:5001/api",
  "adminRoleName": "Admin"
}


عدم نمایش ستون Delete ردیف‌های لیست فیلم‌ها، به کاربرانی که Admin نیستند

اکنون که امکان بررسی نقش‌های کاربر لاگین شده‌ی به سیستم را داریم، می‌خواهیم ستون Delete ردیف‌های لیست فیلم‌ها را فقط به کاربری که دارای نقش Admin است، نمایش دهیم. برای اینکار نیاز به دریافت شیء user، در src\components\moviesTable.jsx وجود دارد. یک روش دریافت کاربر جاری وارد شده‌ی به سیستم، همانی است که تا به اینجا بررسی کردیم: شیء currentUser را به صورت props، از بالاترین کامپوننت، به پایین‌تر کامپوننت موجود در component tree ارسال می‌کنیم. روش دیگر اینکار، دریافت مستقیم کاربر جاری از خود src\services\authService.js است و ... اینکار ساده‌تر است! به علاوه اینکه همیشه بررسی تاریخ انقضای توکن را نیز به صورت خودکار انجام می‌دهد و در صورت انقضای توکن، کاربر را در قسمت catch متد getCurrentUser، از سیستم خارج خواهد کرد.
بنابراین در src\components\moviesTable.jsx، ابتدا authService را import می‌کنیم:
import * as auth from "../services/authService";
در ادامه ابتدا تعریف ستون حذف را از آرایه‌ی columns خارج کرده و تبدیل به یک خاصیت می‌کنیم. یعنی در ابتدای کار، چنین ستونی تعریف نشده‌است:
class MoviesTable extends Component {
  columns = [ ... ];
  // ...

  deleteColumn = {
    key: "delete",
    content: movie => (
      <button
        onClick={() => this.props.onDelete(movie)}
        className="btn btn-danger btn-sm"
      >
        Delete
      </button>
    )
  };
در آخر در متد سازنده‌ی این کامپوننت، کاربر جاری را از authService دریافت کرده و اگر این کاربر دارای نقش Admin بود، ستون deleteColumn را به لیست ستون‌های موجود، اضافه می‌کنیم تا نمایش داده شود:
  constructor() {
    super();
    const user = auth.getCurrentUser();
    if (user && auth.isAuthUserInRole(user, "Admin")) {
      this.columns.push(this.deleteColumn);
    }
  }
اکنون برای آزمایش برنامه، یکبار از آن خارج شوید؛ دیگر نباید ستون Delete نمایش داده شود. همچنین یکبار هم تحت عنوان یک کاربر معمولی در سایت ثبت نام کنید. این کاربر نیز چنین ستونی را مشاهده نمی‌کند.

کدهای کامل این قسمت را از اینجا می‌توانید دریافت کنید: sample-28-backend.zip و sample-28-frontend.zip