اشتراکها
این روش رو جداول حجیم سرعت رو میاره پایین تو محیط sql میشه از TABLESAMPLE استفاده کرد که متاسفانه معادلی براش تو linq نیست و...
آقای نصیری یه روش هم به این صورته
میخواستم بدونم این روش رو میشه به linq تبدیل کرد؟
آقای نصیری یه روش هم به این صورته
SELECT * FROM Table1 WHERE (ABS(CAST( (BINARY_CHECKSUM(*) * RAND()) as int)) % 100) < 10
ASP.NET Core به همراه زیر ساختیاست جهت خارج کردن امکانات Caching درون حافظهای آن از سرور جاری و انتقال آن به سرورهای دیگر جهت کاهش بار سرور و برنامه. این کش توزیع شده را میتوان به عنوان زیرساختی برای مدیریت سشنها، مدیریت اطلاعات کش و همچنین مدیریت کوکیهای حجیم ASP.NET Core Identity نیز بکار گرفت. برای مثال بجای ارسال یک کوکی حجیم بالای 5 کیلوبایت به کلاینت، فقط ID رمزنگاری شدهی آنرا ارسال کرد و اصل کوکی را در داخل دیتابیس ذخیره و بازیابی نمود. این مساله هم مقیاس پذیری برنامه را افزایش خواهد داد و هم امنیت آنرا با عدم ارسال اصل محتوای کوکی به سمت کلاینتها و یا ذخیره سازی اطلاعات سشنها در بانک اطلاعاتی، مشکلات راه اندازی مجدد برنامه را به طور کامل برطرف میکنند و در این حالت بازیابی Application pool و یا کرش برنامه و یا ری استارت شدن کل سرور، سبب از بین رفتن سشنهای کاربران نخواهند شد. بنابراین آشنایی با نحوهی راه اندازی این امکانات، حداقل از بعد امنیتی بسیار مفید هستند؛ حتی اگر سرور ذخیره کنندهی این اطلاعات، همان سرور و بانک اطلاعاتی اصلی برنامه باشند.
پیشنیازهای کار با کش توزیع شدهی مبتنی بر SQL Server
برای کار با کش توزیع شدهی با قابلیت ذخیره سازی در یک بانک اطلاعاتی SQL Server، نیاز است دو بستهی ذیل را به فایل project.json برنامه اضافه کرد:
وابستگی که در قسمت dependencies ذکر شدهاست، کلاسهای اصلی کار با کش توزیع شده را به برنامه اضافه میکند. ذکر وابستگی قسمت tools، اختیاری است و کار آن، ایجاد جدول مورد نیاز برای ذخیره سازی اطلاعات، در یک بانک اطلاعاتی SQL Server میباشد.
ایجاد جدول ذخیره سازی اطلاعات کش توزیع شده به کمک ابزار sql-cache
پس از افزودن و بازیابی ارجاعات فوق، با استفاده از خط فرمان، به پوشهی جاری برنامه وارد شده و دستور ذیل را صادر کنید:
توضیحات:
- در اینجا میتوان هر نوع رشتهی اتصالی معتبری را به انواع و اقسام بانکهای SQL Server ذکر کرد. برای نمونه در مثال فوق این رشتهی اتصالی به یک بانک اطلاعاتی از پیش ایجاد شدهی LocalDB اشاره میکند. نام دلخواه این بانک اطلاعاتی در اینجا sql_cache ذکر گردیده و نام دلخواه جدولی که قرار است این اطلاعات را ثبت کند AppSqlCache تنظیم شدهاست و dbo، نام اسکیمای جدول است:
در اینجا تصویر ساختار جدولی را که توسط ابزار dotnet sql-cache ایجاد شدهاست، مشاهده میکنید. اگر خواستید این جدول را خودتان دستی ایجاد کنید، یک چنین کوئری را باید بر روی دیتابیس مدنظرتان اجرا نمائید:
ایجاد جدول ذخیره سازی اطلاعات کش توزیع شده به کمک ابزار Migrations در EF Core
زیر ساخت کش توزیع شدهی مبتنی بر SQL Server هیچگونه وابستگی به EF Core ندارد و تمام اجزای آن توسط Async ADO.NET نوشته شدهاند. اما اگر خواستید قسمت ایجاد جدول مورد نیاز آنرا به ابزار Migrations در EF Core واگذار کنید، روش کار به صورت زیر است:
- ابتدا یک کلاس دلخواه جدید را با محتوای ذیل ایجاد کنید:
- سپس تنظیمات ایجاد جدول متناظر با آن را به نحو ذیل تنظیم نمائید:
به این ترتیب این جدول جدید به صورت خودکار در کنار سایر جداول برنامه ایجاد خواهند شد.
البته این مورد به شرطی است که بخواهید از یک دیتابیس، هم برای برنامه و هم برای ذخیره سازی اطلاعات کش استفاده کنید.
معرفی تنظیمات رشتهی اتصالی و نام جدول ذخیره سازی اطلاعات کش به برنامه
پس از ایجاد جدول مورد نیاز جهت ذخیره سازی اطلاعات کش، اکنون نیاز است این اطلاعات را به برنامه معرفی کرد. برای این منظور به کلاس آغازین برنامه مراجعه کرده و متد الحاقی AddDistributedSqlServerCache را بر روی مجموعهی سرویسهای موجود فراخوانی کنید؛ تا سرویسهای این کش توزیع شده نیز به برنامه معرفی شوند:
باتوجه به توزیع شده بودن این کش، هیچ الزامی ندارد که ConnectionString ذکر شدهی در اینجا با رشتهی اتصالی مورد استفادهی توسط EF Core یکی باشد (هرچند مشکلی هم ندارد).
آزمایش کش توزیع شدهی تنظیمی با فعال سازی سشنها
سشنها را همانند نکات ذکر شدهی در مطلب «ارتقاء به ASP.NET Core 1.0 - قسمت 16 - کار با Sessions» فعال کنید و سپس مقداری را در آن بنویسید:
اکنون از جدول AppSqlCache کوئری بگیرید:
همانطور که مشاهده میکنید، سیستم سشن اینبار بجای حافظه، به صورت خودکار از جدول بانک اطلاعاتی SQL Server تنظیم شده، برای ذخیره سازی اطلاعات خود استفاده کردهاست.
کار با کش توزیع شده از طریق برنامه نویسی
همانطور که در مقدمهی بحث نیز عنوان شد، استفادهی از زیر ساخت کش توزیع شده منحصر به استفادهی از آن جهت ذخیره سازی اطلاعات سشنها نیست و از آن میتوان جهت انواع و اقسام سناریوهای مختلف مورد نیاز استفاده کرد. در این حالت روش دسترسی به این زیر ساخت، از طریق اینترفیس IDistributedCache است. زمانیکه متد AddDistributedSqlServerCache را فراخوانی میکنیم، در حقیقت کار ثبت یک چنین سرویسی به صورت خودکار انجام خواهد شد:
به عبارتی کلاس SqlServerCache به صورت singleton به مجموعهی سرویسهای برنامه اضافه شدهاست و برای دسترسی به آن تنها کافی است اینترفیس IDistributedCache را به کنترلرها و یا سرویسهای برنامه تزریق و از امکانات آن استفاده کنیم.
در اینجا یک نمونه از این تزریق وابستگی و سپس استفادهی از متدهای Set و Get اینترفیس IDistributedCache را مشاهده میکنید:
در ابتدای بحث که ساختار جدول ذخیره سازی اطلاعات کش را بررسی کردیم، فیلد value آن یک چنین نوعی را دارد:
که در سمت کدهای دات نتی، به شکل آرایهای از بایتها قابل بیان است.
به همین جهت متد Set آن مقدار مدنظر را به صورت آرایهای از بایتها قبول میکند.
در این حالت اگر برنامه را اجرا و مسیر http://localhost:7742/CacheTest/SetCacheData را فراخوانی کنیم، اطلاعات ذخیره شدهی با کلید Test را میتوان در بانک اطلاعاتی مشاهده کرد:
Tag helper مخصوص کش توزیع شده
در ASP.NET Core، میتوان از یک Tag Helper جدید به نام distributed-cache برای کش سمت سرور توزیع شدهی محتوای قسمتی از یک View به نحو ذیل استفاده کرد:
که اطلاعات آن در بانک اطلاعاتی به نحو ذیل ذخیره میشود:
در اینجا name به صورت هش شده به صورت کلید کش مورد استفاده قرار میگیرد. سپس محتوای تگ distributed-cache رندر شده، تبدیل به آرایهای از بایتها گردیده و در بانک اطلاعاتی ذخیره میگردد.
ذکر name در اینجا اجباری است و باید دقت داشت که چون به عنوان کلید بازیابی کش مورد استفاده قرار خواهد گرفت، نباید به اشتباه در قسمتهای دیگر برنامه با همین نام وارد شود. در غیر اینصورت دو قسمتی که name یکسانی داشته باشند، یک محتوا را نمایش خواهند داد.
پیشنیازهای کار با کش توزیع شدهی مبتنی بر SQL Server
برای کار با کش توزیع شدهی با قابلیت ذخیره سازی در یک بانک اطلاعاتی SQL Server، نیاز است دو بستهی ذیل را به فایل project.json برنامه اضافه کرد:
{ "dependencies": { "Microsoft.Extensions.Caching.SqlServer": "1.1.0" }, "tools": { "Microsoft.Extensions.Caching.SqlConfig.Tools": "1.1.0-preview4-final" } }
ایجاد جدول ذخیره سازی اطلاعات کش توزیع شده به کمک ابزار sql-cache
پس از افزودن و بازیابی ارجاعات فوق، با استفاده از خط فرمان، به پوشهی جاری برنامه وارد شده و دستور ذیل را صادر کنید:
dotnet sql-cache create "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=sql_cache;Integrated Security=True;" "dbo" "AppSqlCache"
- در اینجا میتوان هر نوع رشتهی اتصالی معتبری را به انواع و اقسام بانکهای SQL Server ذکر کرد. برای نمونه در مثال فوق این رشتهی اتصالی به یک بانک اطلاعاتی از پیش ایجاد شدهی LocalDB اشاره میکند. نام دلخواه این بانک اطلاعاتی در اینجا sql_cache ذکر گردیده و نام دلخواه جدولی که قرار است این اطلاعات را ثبت کند AppSqlCache تنظیم شدهاست و dbo، نام اسکیمای جدول است:
در اینجا تصویر ساختار جدولی را که توسط ابزار dotnet sql-cache ایجاد شدهاست، مشاهده میکنید. اگر خواستید این جدول را خودتان دستی ایجاد کنید، یک چنین کوئری را باید بر روی دیتابیس مدنظرتان اجرا نمائید:
CREATE TABLE AppSqlCache ( Id NVARCHAR (449) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, Value VARBINARY (MAX) NOT NULL, ExpiresAtTime DATETIMEOFFSET NOT NULL, SlidingExpirationInSeconds BIGINT NULL, AbsoluteExpiration DATETIMEOFFSET NULL, CONSTRAINT pk_Id PRIMARY KEY (Id) ); CREATE NONCLUSTERED INDEX Index_ExpiresAtTime ON AppSqlCache(ExpiresAtTime);
ایجاد جدول ذخیره سازی اطلاعات کش توزیع شده به کمک ابزار Migrations در EF Core
زیر ساخت کش توزیع شدهی مبتنی بر SQL Server هیچگونه وابستگی به EF Core ندارد و تمام اجزای آن توسط Async ADO.NET نوشته شدهاند. اما اگر خواستید قسمت ایجاد جدول مورد نیاز آنرا به ابزار Migrations در EF Core واگذار کنید، روش کار به صورت زیر است:
- ابتدا یک کلاس دلخواه جدید را با محتوای ذیل ایجاد کنید:
public class AppSqlCache { public string Id { get; set; } public byte[] Value { get; set; } public DateTimeOffset ExpiresAtTime { get; set; } public long? SlidingExpirationInSeconds { get; set; } public DateTimeOffset? AbsoluteExpiration { get; set; } }
public class MyDBDataContext : DbContext { public virtual DbSet<AppSqlCache> AppSqlCache { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<AppSqlCache>(entity => { entity.ToTable(name: "AppSqlCache", schema: "dbo"); entity.HasIndex(e => e.ExpiresAtTime).HasName("Index_ExpiresAtTime"); entity.Property(e => e.Id).HasMaxLength(449); entity.Property(e => e.Value).IsRequired(); }); } }
البته این مورد به شرطی است که بخواهید از یک دیتابیس، هم برای برنامه و هم برای ذخیره سازی اطلاعات کش استفاده کنید.
معرفی تنظیمات رشتهی اتصالی و نام جدول ذخیره سازی اطلاعات کش به برنامه
پس از ایجاد جدول مورد نیاز جهت ذخیره سازی اطلاعات کش، اکنون نیاز است این اطلاعات را به برنامه معرفی کرد. برای این منظور به کلاس آغازین برنامه مراجعه کرده و متد الحاقی AddDistributedSqlServerCache را بر روی مجموعهی سرویسهای موجود فراخوانی کنید؛ تا سرویسهای این کش توزیع شده نیز به برنامه معرفی شوند:
public void ConfigureServices(IServiceCollection services) { services.AddDistributedSqlServerCache(options => { options.ConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=sql_cache;Integrated Security=True;"; options.SchemaName = "dbo"; options.TableName = "AppSqlCache"; });
آزمایش کش توزیع شدهی تنظیمی با فعال سازی سشنها
سشنها را همانند نکات ذکر شدهی در مطلب «ارتقاء به ASP.NET Core 1.0 - قسمت 16 - کار با Sessions» فعال کنید و سپس مقداری را در آن بنویسید:
public IActionResult Index() { HttpContext.Session.SetString("User", "VahidN"); return Json(true); } public IActionResult About() { var userContent = HttpContext.Session.GetString("User"); return Json(userContent); }
همانطور که مشاهده میکنید، سیستم سشن اینبار بجای حافظه، به صورت خودکار از جدول بانک اطلاعاتی SQL Server تنظیم شده، برای ذخیره سازی اطلاعات خود استفاده کردهاست.
کار با کش توزیع شده از طریق برنامه نویسی
همانطور که در مقدمهی بحث نیز عنوان شد، استفادهی از زیر ساخت کش توزیع شده منحصر به استفادهی از آن جهت ذخیره سازی اطلاعات سشنها نیست و از آن میتوان جهت انواع و اقسام سناریوهای مختلف مورد نیاز استفاده کرد. در این حالت روش دسترسی به این زیر ساخت، از طریق اینترفیس IDistributedCache است. زمانیکه متد AddDistributedSqlServerCache را فراخوانی میکنیم، در حقیقت کار ثبت یک چنین سرویسی به صورت خودکار انجام خواهد شد:
services.Add(ServiceDescriptor.Singleton<IDistributedCache, SqlServerCache>());
در اینجا یک نمونه از این تزریق وابستگی و سپس استفادهی از متدهای Set و Get اینترفیس IDistributedCache را مشاهده میکنید:
using System; using System.Text; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Caching.Distributed; namespace Core1RtmEmptyTest.Controllers { public class CacheTestController : Controller { readonly IDistributedCache _cache; public CacheTestController(IDistributedCache cache) { _cache = cache; } public IActionResult SetCacheData() { var time = DateTime.Now.ToLocalTime().ToString(); var cacheOptions = new DistributedCacheEntryOptions { AbsoluteExpiration = DateTime.Now.AddYears(1) }; _cache.Set("Time", Encoding.UTF8.GetBytes(time), cacheOptions); return View(); } public IActionResult GetCacheData() { var time = Encoding.UTF8.GetString(_cache.Get("Time")); ViewBag.data = time; return View(); } public bool RemoveCacheData() { _cache.Remove("Time"); return true; } } }
Value VARBINARY (MAX) NOT NULL,
public byte[] Value { get; set; }
در این حالت اگر برنامه را اجرا و مسیر http://localhost:7742/CacheTest/SetCacheData را فراخوانی کنیم، اطلاعات ذخیره شدهی با کلید Test را میتوان در بانک اطلاعاتی مشاهده کرد:
Tag helper مخصوص کش توزیع شده
در ASP.NET Core، میتوان از یک Tag Helper جدید به نام distributed-cache برای کش سمت سرور توزیع شدهی محتوای قسمتی از یک View به نحو ذیل استفاده کرد:
<distributed-cache name="MyCacheItem2" expires-sliding="TimeSpan.FromMinutes(30)"> <p>From distributed-cache</p> @DateTime.Now.ToString() </distributed-cache>
در اینجا name به صورت هش شده به صورت کلید کش مورد استفاده قرار میگیرد. سپس محتوای تگ distributed-cache رندر شده، تبدیل به آرایهای از بایتها گردیده و در بانک اطلاعاتی ذخیره میگردد.
ذکر name در اینجا اجباری است و باید دقت داشت که چون به عنوان کلید بازیابی کش مورد استفاده قرار خواهد گرفت، نباید به اشتباه در قسمتهای دیگر برنامه با همین نام وارد شود. در غیر اینصورت دو قسمتی که name یکسانی داشته باشند، یک محتوا را نمایش خواهند داد.
از چه دیتابیسی استفاده میکنید؟ اگر SQL Server است که تا قبل از نگارش 2008 آن چنین اجازهای رو به شما نمیده تا یک فیلد منحصربفرد نال پذیر داشته باشید. اگر 2008 به بعد است، باید ایندکس فیلتر شده برای اینکار تعریف کنید. مثلا:
اطلاعات بیشتر اینجا و اینجا
بر همین مبنا باید قسمت ADD CONSTRAINT متد ExecuteUniqueIndexes را در صورت نیاز بازنویسی کنید.
create unique nonclustered index idx on dbo.DimCustomer(emailAddress) where EmailAddress is not null;
بر همین مبنا باید قسمت ADD CONSTRAINT متد ExecuteUniqueIndexes را در صورت نیاز بازنویسی کنید.
نظرات مطالب
یافتن مقادیر نال در Entity framework
خروجی SQL شما منطبق با خروجی SQL حاصل از EF نیست. روش کار را اینجا توضیح دادم که چگونه میشود این خروجی را دقیقا به دست آورد.
در حالت
این خروجی حاصل میشود:
در حالت
دقیقا این خروجی را خواهیم داشت:
در حالت
var list1 = ctx.Users.Where(x => x.Name != null).ToList();
SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[Age] AS [Age] FROM [dbo].[People] AS [Extent1] WHERE [Extent1].[Name] IS NOT NULL
var list2 = ctx.Users.Where(x => x.Name == null).ToList();
SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[Age] AS [Age] FROM [dbo].[People] AS [Extent1] WHERE [Extent1].[Name] IS NULL
Timeouts، Deadlocks و قطعیهای احتمالی و موقت اتصال به بانک اطلاعاتی در شبکه، جزئی از ساختار دنیای واقعی هستند. در EF 6 برای پیاده سازی سعی مجدد در اتصال و انجام مجدد عملیات، ویژگی خاصی تحت عنوان connection resiliency اضافه شدهاست که در ادامه مثالی از آنرا بررسی خواهیم کرد.
پیاده سازیهای پیش فرض موجود
برای پیاده سازی منطق سعی مجدد در اتصال، باید اینترفیس IDbExecutionStrategy پیاده سازی شود. در EF 6 حداقل 4 نوع پیاده سازی پیش فرض از آن به صورت توکار ارائه شدهاست:
الف) DefaultExecutionStrategy : حالت پیش فرض است و در صورت بروز مشکل، سعی مجددی را در اتصال، به عمل نخواهد آورد.
ب) DefaultSqlExecutionStrategy : برای کارهای درونی EF از آن استفاده میشود. سعی مجددی در اتصال قطع شده نخواهد کرد؛ اما جزئیات خطاهای بهتری را در اختیار مصرف کننده قرار میدهد.
ج) DbExecutionStrategy : هدف از آن تهیه یک کلاس پایه است برای نوشتن استراتژیهای سعی مجدد سفارشی.
د) SqlAzureExecutionStrategy : یک نمونه DbExecutionStrategy سفارشی تهیه شده برای ویندوز اژور است. برای فعال سازی و تعریف آن نیز باید به نحو ذیل عمل کرد:
تهیه یک DbExecutionStrategy سفارشی برای SQL Server
همانطور که عنوان شد، هدف از کلاس DbExecutionStrategy، تهیه یک کلاس پایه، جهت نوشتن منطق سعی مجدد در اتصال به بانک اطلاعاتی است و این مورد از دیتابیسی به دیتابیس دیگر میتواند متفاوت باشد؛ زیرا خطاهایی را که ارائه میدهند، یکسان و یک دست نیستند. در ادامه یک پیاده سازی سفارشی را از DbExecutionStrategy، جهت SQL Server مرور خواهیم کرد:
در اینجا کار با بازنویسی متد ShouldRetryOn شروع میشود. این متد اگر پس از بررسی استثنای دریافتی، مقدار true را برگرداند، به معنای نیاز به سعی مجدد در اتصال است و برعکس. سازنده پیش فرض این کلاس طوری تنظیم شدهاست که 5 بار سعی مجدد کند؛ با فواصل زمانی 7 ثانیه. اگر میخواهید این زمان را صریحا تعیین کنید باید متد GetNextDelay کلاس پایه را نیز بازنویسی کرد:
در ادامه برای استفاده از آن خواهیم داشت:
این کلاس به صورت خودکار توسط EF از اسمبلی جاری استخراج شده و استفاده خواهد شد. بنابراین نیازی نیست جایی معرفی شود. فقط باید در کدها حضور داشته باشد. همچنین ذکر System.Data.SqlClient نیز ضروری است؛ از این جهت که خطاهای بازگشت داده شده مانند 1205 و امثال آن، در بانکهای اطلاعاتی مختلف، میتوانند کاملا متفاوت باشند.
پیاده سازیهای پیش فرض موجود
برای پیاده سازی منطق سعی مجدد در اتصال، باید اینترفیس IDbExecutionStrategy پیاده سازی شود. در EF 6 حداقل 4 نوع پیاده سازی پیش فرض از آن به صورت توکار ارائه شدهاست:
الف) DefaultExecutionStrategy : حالت پیش فرض است و در صورت بروز مشکل، سعی مجددی را در اتصال، به عمل نخواهد آورد.
ب) DefaultSqlExecutionStrategy : برای کارهای درونی EF از آن استفاده میشود. سعی مجددی در اتصال قطع شده نخواهد کرد؛ اما جزئیات خطاهای بهتری را در اختیار مصرف کننده قرار میدهد.
ج) DbExecutionStrategy : هدف از آن تهیه یک کلاس پایه است برای نوشتن استراتژیهای سعی مجدد سفارشی.
د) SqlAzureExecutionStrategy : یک نمونه DbExecutionStrategy سفارشی تهیه شده برای ویندوز اژور است. برای فعال سازی و تعریف آن نیز باید به نحو ذیل عمل کرد:
public class MyConfiguration : DbConfiguration { public MyConfiguration() { SetExecutionStrategy("System.Data.SqlClient", () => new SqlAzureExecutionStrategy()); } }
تهیه یک DbExecutionStrategy سفارشی برای SQL Server
همانطور که عنوان شد، هدف از کلاس DbExecutionStrategy، تهیه یک کلاس پایه، جهت نوشتن منطق سعی مجدد در اتصال به بانک اطلاعاتی است و این مورد از دیتابیسی به دیتابیس دیگر میتواند متفاوت باشد؛ زیرا خطاهایی را که ارائه میدهند، یکسان و یک دست نیستند. در ادامه یک پیاده سازی سفارشی را از DbExecutionStrategy، جهت SQL Server مرور خواهیم کرد:
public class SqlServerExecutionStrategy : DbExecutionStrategy { public SqlServerExecutionStrategy() { } public SqlServerExecutionStrategy(int maxRetryCount, TimeSpan maxDelay) : base(maxRetryCount, maxDelay) { } protected override bool ShouldRetryOn(Exception ex) { var sqlException = ex as SqlException; if (sqlException == null) return false; // don't retry foreach (var error in sqlException.Errors.Cast<SqlError>()) { switch (error.Number) { case 1205: // Deadlock case -1: // Timeout case -2: // Timeout return true; // retry } } return false; } }
protected override TimeSpan? GetNextDelay(Exception lastException) { return base.GetNextDelay(lastException); }
public class MyDbConfiguration : DbConfiguration { public MyDbConfiguration() { SetExecutionStrategy("System.Data.SqlClient", () => new SqlServerExecutionStrategy()); } }
پیشنیازها
- بررسی نحوه تعریف نگاشت جداول خود ارجاع دهنده (Self Referencing Entity)
- مباحث تکمیلی مدلهای خود ارجاع دهنده در EF Code first
- آشنایی با SQL Server Common Table Expressions - CTE
- بدست آوردن برگهای یک درخت توسط Recursive CTE
در پیشنیازهای بحث، روش تعریف روابط خود ارجاع دهنده و یا Self Referencing را تا EF 6.x میتوانید مطالعه کنید. در این قسمت قصد داریم معادل این روشها را در EF Core بررسی کنیم.
روش تعریف روابط خود ارجاع دهنده توسط Fluent API در EF Core
اگر همان مدل کامنتهای چندسطحی یک بلاگ را درنظر بگیریم:
اینبار تنظیمات Fluent API معادل EF Core آن به صورت ذیل خواهد بود:
هدف از مدلهای خود ارجاع دهنده، مدلسازی اطلاعات چند سطحی است؛ مانند منوهای چندسطحی یک سایت، کامنتهای چند سطحی یک مطلب، سلسله مراتب کارمندان یک شرکت و امثال آن.
نکتههای مهم مدلسازی این نوع روابط، موارد ذیل هستند:
الف) وجود خاصیتی از جنس کلاس اصلی در همان کلاس
ب) که در حقیقت مشخص میکند، والد رکورد جاری کدام رکورد قبلی است:
برای اینکه چنین رابطهای تشکیل شود، باید این فیلد، مقدارش را از کلید اصلی جدول تامین کند (تشکیل یک کلید خارجی که به کلید اصلی جدول اشاره میکند).
علت نال پذیر بودن این خاصیت، نیاز به ثبت ریشهای است که خودش والد است و فرزند رکوردی پیشین، نیست.
ج) همچنین برای سهولت دریافت فرزندان یک ریشه، مجموعهای از جنس همان کلاس نیز تشکیل میشود.
روش تعریف روابط خود ارجاع دهنده توسط Data Annotations در EF Core
در ادامه معادل تنظیمات فوق را توسط ویژگیها ملاحظه میکنید که در اینجا توسط ویژگیهای InverseProperty و ForeignKey، کار تشکیل روابط صورت گرفتهاست:
البته قسمت تشکیل ایندکس بر روی ReplyId را فقط توسط Fluent API میتوان انجام داد:
ثبت اطلاعات و کوئری گرفتن از روابط خود ارجاع دهنده در EF Core
در اینجا نحوهی ثبت دو سری نظر و زیر نظر را مشاهده میکنید:
نظرات اصلی، با ReplyId مساوی نال قابل تشخیص هستند. سایر نظرات، توسط همین ReplyId به یکی از Idهای موجود، متصل شدهاند.
در تصویر فوق و با توجه به اطلاعات ثبت شده، فرض کنید میخواهیم ریشهی با id مساوی 1 و تمام زیر ریشههای آنرا بیابیم. انجام یک چنین کاری نه در EF Core و نه در EF 6.x، پشتیبانی نمیشود. بدیهی است در اینجا هنوز روشهای Include و ThenInclue هم جواب میدهند؛ اما چون Lazy loading فعال نیست، عملا نمیتوان تمام زیر ریشهها را یافت و همچنین به چندین و چند رفت و برگشت به ازای هر زیر ریشه خواهیم رسید که اصلا بهینه نیست.
برای اینکار نیاز است مستقیما کوئری نویسی کرد که در مطلب «شروع به کار با EF Core 1.0 - قسمت 10 - استفاده از امکانات بومی بانکهای اطلاعاتی» زیر ساخت آنرا بررسی کردیم:
زمانیکه کدهای فوق اجرا میشوند، تنها دو کوئری ذیل به بانک اطلاعاتی ارسال خواهند شد:
که لیست Idهای تمام زیر ریشههای مربوط به id مساوی یک را بر میگرداند:
و پس از آن:
اکنون که Idهای مساوی 3 و 5 را یافتیم، با استفاده از متد Contains آنها را تبدیل به where in کرده و لیست نهایی گزارش را تهیه میکنیم:
یافتن Idهای زیر ریشهها توسط روش CTE (پیشنیازهای ابتدای بحث) و سپس کوئری گرفتن بر روی این Idها، بهینهترین روشیاست که در EF میتوان جهت کار با مدلهای خود ارجاع دهنده بکار گرفت.
- بررسی نحوه تعریف نگاشت جداول خود ارجاع دهنده (Self Referencing Entity)
- مباحث تکمیلی مدلهای خود ارجاع دهنده در EF Code first
- آشنایی با SQL Server Common Table Expressions - CTE
- بدست آوردن برگهای یک درخت توسط Recursive CTE
در پیشنیازهای بحث، روش تعریف روابط خود ارجاع دهنده و یا Self Referencing را تا EF 6.x میتوانید مطالعه کنید. در این قسمت قصد داریم معادل این روشها را در EF Core بررسی کنیم.
روش تعریف روابط خود ارجاع دهنده توسط Fluent API در EF Core
اگر همان مدل کامنتهای چندسطحی یک بلاگ را درنظر بگیریم:
public class BlogComment { public int Id { get; set; } public string Body { get; set; } public DateTime Date1 { get; set; } public virtual BlogComment Reply { get; set; } public int? ReplyId { get; set; } public virtual ICollection<BlogComment> Children { get; set; } }
public class MyDBDataContext : DbContext { protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(@"Data Source=(local);Initial Catalog=testdb2;Integrated Security = true"); } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<BlogComment>(entity => { entity.HasIndex(e => e.ReplyId); entity.HasOne(d => d.Reply) .WithMany(p => p.Children) .HasForeignKey(d => d.ReplyId); }); } public virtual DbSet<BlogComment> BlogComments { get; set; } }
نکتههای مهم مدلسازی این نوع روابط، موارد ذیل هستند:
الف) وجود خاصیتی از جنس کلاس اصلی در همان کلاس
public virtual BlogComment Reply { get; set; }
public int? ReplyId { get; set; }
علت نال پذیر بودن این خاصیت، نیاز به ثبت ریشهای است که خودش والد است و فرزند رکوردی پیشین، نیست.
ج) همچنین برای سهولت دریافت فرزندان یک ریشه، مجموعهای از جنس همان کلاس نیز تشکیل میشود.
public virtual ICollection<BlogComment> Children { get; set; }
روش تعریف روابط خود ارجاع دهنده توسط Data Annotations در EF Core
در ادامه معادل تنظیمات فوق را توسط ویژگیها ملاحظه میکنید که در اینجا توسط ویژگیهای InverseProperty و ForeignKey، کار تشکیل روابط صورت گرفتهاست:
public class BlogComment { public int Id { get; set; } public string Body { get; set; } public DateTime Date1 { get; set; } [ForeignKey("ReplyId")] [InverseProperty("Children")] public virtual BlogComment Reply { get; set; } public int? ReplyId { get; set; } [InverseProperty("Reply")] public virtual ICollection<BlogComment> Children { get; set; } }
protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<BlogComment>(entity => { entity.HasIndex(e => e.ReplyId); }); }
ثبت اطلاعات و کوئری گرفتن از روابط خود ارجاع دهنده در EF Core
در اینجا نحوهی ثبت دو سری نظر و زیر نظر را مشاهده میکنید:
var comment1 = new BlogComment { Body = "نظر من این است که" }; var comment12 = new BlogComment { Body = "پاسخی به نظر اول", Reply = comment1 }; var comment121 = new BlogComment { Body = "پاسخی به پاسخ به نظر اول", Reply = comment12 }; context.BlogComments.Add(comment121); var comment2 = new BlogComment { Body = "نظر من این بود که" }; var comment22 = new BlogComment { Body = "پاسخی به نظر قبلی", Reply = comment2 }; var comment221 = new BlogComment { Body = "پاسخی به پاسخ به نظر من اول", Reply = comment22 }; context.BlogComments.Add(comment221); context.SaveChanges();
نظرات اصلی، با ReplyId مساوی نال قابل تشخیص هستند. سایر نظرات، توسط همین ReplyId به یکی از Idهای موجود، متصل شدهاند.
در تصویر فوق و با توجه به اطلاعات ثبت شده، فرض کنید میخواهیم ریشهی با id مساوی 1 و تمام زیر ریشههای آنرا بیابیم. انجام یک چنین کاری نه در EF Core و نه در EF 6.x، پشتیبانی نمیشود. بدیهی است در اینجا هنوز روشهای Include و ThenInclue هم جواب میدهند؛ اما چون Lazy loading فعال نیست، عملا نمیتوان تمام زیر ریشهها را یافت و همچنین به چندین و چند رفت و برگشت به ازای هر زیر ریشه خواهیم رسید که اصلا بهینه نیست.
برای اینکار نیاز است مستقیما کوئری نویسی کرد که در مطلب «شروع به کار با EF Core 1.0 - قسمت 10 - استفاده از امکانات بومی بانکهای اطلاعاتی» زیر ساخت آنرا بررسی کردیم:
var id = 1; var childIds = new List<int>(); var connection = context.Database.GetDbConnection(); connection.Open(); var command = connection.CreateCommand(); command.CommandText = @"WITH Hierachy(ChildId, ParentId) AS ( SELECT Id, ReplyId FROM BlogComments UNION ALL SELECT h.ChildId, bc.ReplyId FROM BlogComments bc INNER JOIN Hierachy h ON bc.Id = h.ParentId ) SELECT h.ChildId FROM Hierachy h WHERE h.ParentId = @Id"; command.Parameters.Add(new SqlParameter("id", id)); var reader = command.ExecuteReader(); while (reader.Read()) { var childId = (int)reader[0]; childIds.Add(childId); } reader.Dispose(); var list = context.BlogComments .Where(blogComment => blogComment.Id == id || childIds.Contains(blogComment.Id)) .ToList();
exec sp_executesql N'WITH Hierachy(ChildId, ParentId) AS ( SELECT Id, ReplyId FROM BlogComments UNION ALL SELECT h.ChildId, bc.ReplyId FROM BlogComments bc INNER JOIN Hierachy h ON bc.Id = h.ParentId ) SELECT h.ChildId FROM Hierachy h WHERE h.ParentId = @Id',N'@id int',@id=1
و پس از آن:
exec sp_executesql N'SELECT [blogComment].[Id], [blogComment].[Body], [blogComment].[Date1], [blogComment].[ReplyId] FROM [BlogComments] AS [blogComment] WHERE [blogComment].[Id] IN (@__id_0, 3, 5)',N'@__id_0 int',@__id_0=1
یافتن Idهای زیر ریشهها توسط روش CTE (پیشنیازهای ابتدای بحث) و سپس کوئری گرفتن بر روی این Idها، بهینهترین روشیاست که در EF میتوان جهت کار با مدلهای خود ارجاع دهنده بکار گرفت.