مفهوم READ_COMMITTED_SNAPSHOT در EF 6
CREATE TABLE [BlogComments]( [Id] [int] IDENTITY(1,1) NOT NULL, [Body] [nvarchar](max) NULL, [Date1] [datetime] NOT NULL, CONSTRAINT [PK_BlogComments] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
-- در پنجره اول BEGIN TRAN UPDATE [BlogComments] SET Body='Test' WHERE id=1
-- در پنجره دوم SELECT TOP 1000 [Id] ,[Body] ,[Date1] FROM [BlogComments]
- الان پنجره دوم را اجرا کنید.
مشاهده خواهید کرد که ... به جواب نمیرسید. کوئری اجرا نمیشود و سیستم قفل شده چون تراکنش اول commit نشده (مثلا یک تراکنش طولانی را اینجا شبیه سازی کردیم؛ یا حتی یک اشتباه در تعاریف T-SQL انجام شده).
در ادامه، عملیات این پنجرهها را دستی متوقف کنید. بعد مطابق دستوراتی که پیشتر ذکر شد، READ_COMMITTED_SNAPSHOT را روی دیتابیس فعال کنید.
مجددا دو مرحله قبل را اجرا کنید. در این حالت کوئری دوم اجرا خواهد شد، چون اطلاعات را از کپی فقط خواندنی بانک اطلاعاتی شما دریافت میکند؛ بر اساس آخرین اطلاعات commit شده در سیستم.
ASP.NET MVC #17
فیلترهای امنیتی ASP.NET MVC
ASP.NET MVC به همراه تعدادی فیلتر امنیتی توکار است که در این قسمت به بررسی آنها خواهیم پرداخت.
بررسی اعتبار درخواست (Request Validation) در ASP.NET MVC
ASP.NET MVC امکان ارسال اطلاعاتی را که دارای تگهای HTML باشند، نمیدهد. این قابلیت به صورت پیش فرض فعال است و جلوی ارسال انواع و اقسام اطلاعاتی که ممکن است سبب بروز حملات XSS Cross site scripting attacks شود را میگیرد. نمونهای از خطای نمایش داده:
A potentially dangerous Request.Form value was detected from the client (Html="<a>").
بنابراین تصمیم گرفته شده صحیح است؛ اما ممکن است در قسمتی از سایت نیاز باشد تا کاربران از یک ویرایشگر متنی پیشرفته استفاده کنند. خروجی این نوع ویرایشگرها هم HTML است. در این حالت میتوان صرفا برای متدی خاص امکانات Request Validation را به کمک ویژگی ValidateInput غیرفعال کرد:
[HttpPost]
[ValidateInput(false)]
public ActionResult CreateBlogPost(BlogPost post)
از ASP.NET MVC 3.0 به بعد راه حل بهتری به کمک ویژگی AllowHtml معرفی شده است. غیرفعال کردن ValidateInput ایی که معرفی شد، بر روی تمام خواص شیء BlogPost اعمال میشود. اما اگر فقط بخواهیم که مثلا خاصیت Text آن از مکانیزم بررسی اعتبار درخواست خارج شود، بهتر است دیگر از ویژگی ValidateInput استفاده نشده و به نحو زیر عمل گردد:
using System;
using System.Web.Mvc;
namespace MvcApplication14.Models
{
public class BlogPost
{
public int Id { set; get; }
public DateTime AddDate { set; get; }
public string Title { set; get; }
[AllowHtml]
public string Text { set; get; }
}
}
در اینجا فقط خاصیت Text مجاز به دریافت محتوای HTML ایی خواهد بود. اما خاصیت Title چنین مجوزی را ندارد. همچنین دیگر نیازی به استفاده از ویژگی ValidateInput غیرفعال شده نیز نخواهد بود.
به علاوه همانطور که در قسمتهای قبل نیز ذکر شد، خروجی Razor به صورت پیش فرض Html encoded است مگر اینکه صریحا آنرا تبدیل به HTML کنیم (مثلا استفاده از متد Html.Raw). به عبارتی خروجی Razor در حالت پیش فرض در مقابل حملات XSS مقاوم است مگر اینکه آگاهانه بخواهیم آنرا غیرفعال کنیم.
مطلب تکمیلی
مقابله با XSS ؛ یکبار برای همیشه!
فیلتر RequireHttps
به کمک ویژگی یا فیلتر RequireHttps، تمام درخواستهای رسیده به یک متد خاص باید از طریق HTTPS انجام شوند و حتی اگر شخصی سعی به استفاده از پروتکل HTTP معمولی کند، به صورت خودکار به HTTPS هدایت خواهد شد:
[RequireHttps]
public ActionResult LogOn()
{
}
فیلتر ValidateAntiForgeryToken
نوع دیگری از حملات که باید در برنامههای وب به آنها دقت داشت به نام CSRF یا Cross site request forgery معروف هستند.
برای مثال فرض کنید کاربری قبل از اینکه بتواند در سایت شما کار خاصی را انجام دهد، نیاز به اعتبار سنجی داشته باشد. پس از لاگین شخص و ایجاد کوکی و سشن معتبر، همین شخص به سایت دیگری مراجعه میکند که در آن مهاجمی بر اساس وضعیت جاری اعتبار سنجی او مثلا لینک حذف کاربری یا افزودن اطلاعات جدیدی را به برنامه ارائه میدهد. چون سشن شخص و کوکی مرتبط به سایت اول هنوز معتبر هستند و شخص سایت را نبسته است، «احتمال» اجرا شدن درخواست مهاجم بالا است (خصوصا اگر از مرورگرهای قدیمی استفاده کند).
بنابراین نیاز است بررسی شود آیا درخواست رسیده واقعا از طریق فرمهای برنامه ما صادر شده است یا اینکه شخصی از طریق سایت دیگری اقدام به جعل درخواستها کرده است.
برای مقابله با این نوع خطاها ابتدا باید داخل فرمهای برنامه از متد Html.AntiForgeryToken استفاده کرد. کار این متد ایجاد یک فیلد مخفی با مقداری منحصربفرد بر اساس اطلاعات سشن جاری کاربر است، به علاوه ارسال یک کوکی خودکار تا بتوان از تطابق اطلاعات اطمینان حاصل کرد:
@using (Html.BeginForm()) {
@Html.AntiForgeryToken()
در مرحله بعد باید فیلتر ValidateAntiForgeryToken جهت بررسی مقدار token دریافتی به متد ثبت اطلاعات اضافه شود:
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult CreateBlogPost(BlogPost post)
در اینجا مقدار دریافتی از فیلد مخفی فرم :
<input name="__RequestVerificationToken" type="hidden" value="C0iPfy/3T....=" />
با مقدار موجود در کوکی سایت بررسی و تطابق داده خواهند شد. اگر این مقادیر تطابق نداشته باشند، یک استثنا صادر شده و از پردازش اطلاعات رسیده جلوگیری میشود.
علاوه بر اینها بهتر است حین استفاده از متد و فیلتر یاد شده، از یک salt مجزا نیز به ازای هر فرم، استفاده شود:
[ValidateAntiForgeryToken(Salt="1234")]
@Html.AntiForgeryToken(salt:"1234")
به این ترتیب tokenهای تولید شده در فرمهای مختلف سایت یکسان نخواهند بود.
به علاوه باید دقت داشت که ValidateAntiForgeryToken فقط با فعال بودن کوکیها در مرورگر کاربر کار میکند و اگر کاربری پذیرش کوکیها را غیرفعال کرده باشد، قادر به ارسال اطلاعاتی به برنامه نخواهد بود. همچنین این فیلتر تنها در حالت HttpPost قابل استفاده است. این مورد هم در قسمتهای قبل تاکید گردید که برای مثال بهتر است بجای داشتن لینک delete در برنامه که با HttpGet ساده کار میکند، آنرا تبدیل به HttpPost نمود تا میزان امنیت برنامه بهبود یابد. از HttpGet فقط برای گزارشگیری و خواندن اطلاعات از برنامه استفاده کنید و نه ثبت اطلاعات.
بنابراین استفاده از AntiForgeryToken را به چک لیست اجباری تولید تمام فرمهای برنامه اضافه نمائید.
مطلب مشابه
Anti CSRF module for ASP.NET
فیلتر سفارشی بررسی Referrer
یکی دیگر از روشهای مقابله با CSRF، بررسی اطلاعات هدر درخواست ارسالی است. اگر اطلاعات Referrer آن با دومین جاری تطابق نداشت، به معنای مشکل دار بودن درخواست رسیده است. فیلتر سفارشی زیر میتواند نمونهای باشد جهت نمایش نحوه بررسی UrlReferrer درخواست رسیده:
using System.Web.Mvc;
namespace MvcApplication14.CustomFilter
{
public class CheckReferrerAttribute : AuthorizeAttribute
{
public override void OnAuthorization(AuthorizationContext filterContext)
{
if (filterContext.HttpContext != null)
{
if (filterContext.HttpContext.Request.UrlReferrer == null)
throw new System.Web.HttpException("Invalid submission");
if (filterContext.HttpContext.Request.UrlReferrer.Host != "mysite.com")
throw new System.Web.HttpException("This form wasn't submitted from this site!");
}
base.OnAuthorization(filterContext);
}
}
}
و برای استفاده از آن:
[HttpPost]
[CheckReferrer]
[ValidateAntiForgeryToken]
public ActionResult DeleteTask(int id)
نکتهای امنیتی در مورد آپلود فایلها در ASP.NET
هر جایی که کاربر بتواند فایلی را به سرور شما آپلود کند، مشکلات امنیتی هم از همانجا شروع خواهند شد. مثلا در متد Upload قسمت 11 این سری، منعی در آپلود انواع فایلها نیست و کاربر میتواند انواع و اقسام شلها را جهت تحت کنترل گرفتن سایت و سرور آپلود و اجرا کند. راه حل چیست؟
از همان روش امنیتی مورد استفاده توسط تیم ASP.NET MVC استفاده میکنیم. فایل web.config قرار گرفته در پوشه Views را باز کنید (نه فایل وب کانفیگ ریشه اصلی سایترا). چنین تنظیمی را میتوان مشاهده کرد:
برای IIS6 :
<system.web>
<httpHandlers>
<add path="*" verb="*" type="System.Web.HttpNotFoundHandler"/>
</httpHandlers>
</system.web>
<system.webServer>
<handlers>
<remove name="BlockViewHandler"/>
<add name="BlockViewHandler" path="*" verb="*" preCondition="integratedMode" type="System.Web.HttpNotFoundHandler" />
</handlers>
</system.webServer>
تنظیم فوق، موتور اجرایی ASP.NET را در این پوشه خاص از کار میاندازد. به عبارتی اگر شخصی مسیر یک فایل aspx یا cshtml یا هر فایل قرار گرفته در پوشه Views را مستقیما در مرورگر خود وارد کند، با پیغام HttpNotFound مواجه خواهد شد.
این روش هم با ASP.NET Web forms سازگار است و هم با ASP.NET MVC؛ چون مرتبط است به موتور اجرایی ASP.NET که هر دوی این فریم ورکها برفراز آن معنا پیدا میکنند.
بنابراین در پوشه فایلهای آپلودی به سرور خود یک web.config را با محتوای فوق ایجاد کنید (و فقط باید مواظب باشید که این فایل حین آپلود فایلهای جدید، overwrite نشود. مهم!). به این ترتیب این مسیر دیگر از طریق مرورگر قابل دسترسی نخواهد بود (با هر محتوایی). سپس برای ارائه فایلهای آپلودی به کاربران از روش زیر استفاده کنید:
public ActionResult Download()
{
return File(Server.MapPath("~/Myfiles/test.txt"), "text/plain");
}
مزیت مهم روش ذکر شده این است که کاربران مجاز به آپلود هر نوع فایلی خواهند بود و نیازی نیست لیست سیاه تهیه کنید که مثلا فایلهایی با پسوندهای خاص آپلود نشوند (که در این بین ممکن است لیست سیاه شما کامل نباشد ...).
علاوه بر تمام فیلترهای امنیتی که تاکنون بررسی شدند، فیلتر دیگری نیز به نام Authorize وجود دارد که در قسمتهای بعدی بررسی خواهد شد.
روی گزینهی Next کلیک کنید و در پنجرهای که باز میشود در قسمت Address نشانی وبسایتی که در بخش پیشین تولید کردیم و ممکن است شما در IIS افزوده باشید؛ قرار دهید و روی دکمهی GO بفشارید تا سرویس در کادر پایین افزوده شود. سپس در قسمت Namespace نامی مناسب برای فراخوانی سرویس وارد کنید آنگاه دکمهی OK را بفشارید.
از پنجرهی بازشده روی دکمهی Finish کلیک کنید. پس از مکثی کوتاه سرویس به همراه دو موجودیت آن درون Data Sources دیده خواهد شد. از آنطرف در Solution Explorer نیز در پوشهی Service References سرویس تعریفشده ارجاع داده خواهد گرفت.
از Data Sources روی tblNews کلیک کنید سپس آنرا کشیده و به روی فرم رها کنید. خواهید دید که یک DataGridView شامل همهی ویژگیهای موجودیت tblNews و یک Binding Navigator که با موجودیت tblNews در پیوند است و یک منبع داده به نام tblNewsBindingSource به صورت خودکار در فرم افزوده خواهد شد.
چیدمان فرم، رنگها، اندازهها و فونت را آنگونه که میپسندید تنظیم کنید. سپس ستونهایی که به آنها نیازی ندارید حذف یا پنهان کرده و عنوان ستونهای مانده را ویرایش کنید. کلیدهای افزودن، حذف و ذخیره را روی Navigator ایجاد کنید و بقیهی کلیدها را اگر به آن نیازی ندارید حذف کنید. البته میتوانید بنا به سلیقهی کاریتان یک Panel برای اینکار اختصاص دهید. در اینجا یک فرم ساده در نظر گرفته شده است:
اکنون نوبت به کدنویسی است. سورس فرم را بازکنید و نخست سرویس را به این صورت در جای مناسب تعریف کنید:
MyNewsService.MyNewsServiceClient MyNews = new MyNewsService.MyNewsServiceClient();
یک تابع کوچک برای تبدیل تاریخ میلادی به شمسی بنویسید سپس رویداد Load فرم را به این صورت بنویسید:
string MiladiToShamsi(DateTime MyDate) { System.Globalization.PersianCalendar pers = new System.Globalization.PersianCalendar(); return string.Format("{0}/{1}/{2}", pers.GetYear(MyDate), pers.GetMonth(MyDate).ToString("D2"), pers.GetDayOfMonth(MyDate).ToString("D2")); } private void Form1_Load(object sender, EventArgs e) { tblNewsBindingSource.DataSource = MyNews.GetAllNews().Select(p => new {p.tblNewsId, p.tblCategory.CatName, p.Title, p.Description, RegDate= MiladiToShamsi( p.RegDate) }); }
پیش از اجرای پروژه از Solution Explorer روی نام راه حل راستکلیک کنید و گزینهی Properties را انتخاب کنید. در پنجرهی بازشده تنظیمات زیر را انجام دهید:
این کار باعث میشود که به طور همزمان پروژهی وبسایت و ویندوز اجرا شود. اکنون پروژه را اجرا کنید. اگر با پیغام خطا روبهرو شدید؛ تگ Connection String را از App.Config پروژه WCF Library به Web.Config پروژه وبسایت کپی کنید. در این صورت پروژه به راحتی اجرا خواهد شد.
در بخش پسین پیرامون افزودن، ویرایش و حذف و برخی توضیحات برای توسعهی کار خواهم نوشت.
همانطور که مطلع هستید قابلیت تهیه عبارات Insert از جداول یک دیتابیس، به صورت استاندارد به management studio 2008 اضافه شده است. برای استفاده از این قابلیت از طریق برنامه نویسی به صورت زیر میتوان عمل نمود:
الف) سه ارجاع را به اسمبلیهای زیر اضافه نمائید:
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
ب) اکنون کدی که عملیات Script Data را با استفاده از قابلیتهای SMO ارائه میدهد به صورت زیر خواهد بود:
using System;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;
/// <summary>
/// تهیه اسکریپت رکوردها
/// </summary>
/// <param name="dbName">نام دیتابیس مورد نظر</param>
/// <param name="table">نام جدولی که باید اسکریپت شود</param>
/// <param name="instance">وهله سرور</param>
/// <param name="userName">نام کاربری جهت اتصال</param>
/// <param name="pass">کلمه عبور جهت اتصال به سرور</param>
/// <returns>اسکریپت تهیه شده</returns>
public static string ScriptData(string dbName, string table, string instance, string userName, string pass)
{
try
{
ServerConnection serverConnection = new ServerConnection(instance, userName, pass);
Server server = new Server(serverConnection);
Database database = server.Databases[dbName];
if (database == null) return string.Empty;
Table tb = database.Tables[table];
Scripter scripter = new Scripter(server) {Options = {ScriptData = true}};
if (tb == null) return string.Empty;
StringBuilder sb = new StringBuilder();
foreach (string s in scripter.EnumScript(new Urn[] {tb.Urn}))
sb.AppendLine(s);
return sb.ToString();
}
catch(Exception ex)
{
//todo: log ...
return string.Empty;
}
}
نکته:
اسمبلیهای SMO به همراه مجموعه SQL Server 2008 نصب میشوند. بنابراین متد و برنامهی فوق بر روی سروری با این مشخصات بدون مشکل اجرا خواهد شد. اما اگر قصد توزیع برنامه خود را دارید باید Microsoft SQL Server 2008 Management Objects را نیز به همراه برنامه خود نصب نمائید.
services.AddTransient<DoEnableProductTasks>();
EF Code First #2
اگر تنها در محیط توسعه خودتان دارید با SQL Server لوکال سیستم کار میکنید، این خطا به معنای باز بودن یک کانکشن از management studio به SQL Server است که با بستن management studio مشکل حل میشود.
اگر دیتابیس کاری است یا دیتابیس راه دور است، باید بانک اطلاعاتی را به حالت single user دربیارید و بعد میتونید اون رو دراپ کنید.
برای مثال، جدول Post مدل بلاگ را در نظر بگیرید که در آن دو فیلد Body و Image تعریف شده اند.فیلد Body از نوع nvarchar max و فیلد Image از نوع varbinary max است و بدیهی است که این دو داده، به هنگام واکشی حافظهی زیادی مصرف میکنند.موارد بسیاری وجود دارند که ما به اطلاعات این دو فیلد احتیاجی نداریم از جمله: نمایش پستهای پر بازدید، پسته هایی که اخیرا ارسال شده اند و اصولا ما فقط به چند فیلد جدول Post احتیاج داریم و نه همهی آن ها.
namespace SplittingTableSample.DomainClasses { public class Post { public virtual int Id { get; set; } public virtual string Title { get; set; } public virtual DateTime CreatedDate { get; set; } public virtual string Body { get; set; } public virtual byte[] Image { get; set; } } }
دلیل اینکه در مدل فوق، تمامی خواص به صورت virtual تعریف شده اند، فعال سازی پروکسیهای ردیابی تغییر است. اگر دستور زیر را برای واکشی اطلاعات post با id=1 انجام دهیم:
using (var context = new MyDbContext()) { var post = context.Posts.Find(1); }
exec sp_executesql N'SELECT TOP (2) [Extent1].[Id] AS [Id], [Extent1].[Title] AS [Title], [Extent1].[CreatedDate] AS [CreatedDate], [Extent1].[Body] AS [Body], [Extent1].[Image] AS [Image] FROM [dbo].[Posts] AS [Extent1] WHERE [Extent1].[Id] = @p0',N'@p0 int',@p0=1
همان طور که مشاهده میکنید، با اجرای دستور فوق تمامی فیلدهای جدول Posts که id آنها برابر 1 بود واکشی شدند، ولی من تنها به فیلدهای Id و Title آن احتیاج داشتم. خب شاید بگویید که من به سادگی با projection، این مشکل را حل میکنم و تنها از فیلد هایی که به آنها احتیاج دارم، کوئری میگیرم. همهی اینها درست، اما projection هم مشکلات خود را دارد،به صورت پیش فرض، نوع بدون نام بر میگرداند و اگر بخواهیم این گونه نباشد، باید مقادیر آن را به یک کلاس(مثلا viewmodel) نگاشت کنیم و کلی مشکل دیگر.
راه حل دیگری که برای حل این مشکل ارائه میشود و برای نرمال سازی جداول نیز کاربرد دارد این است که، جدول Posts را به دو جدول مجزا که با یکدیگر رابطهی یک به یک دارند تقسیم کنیم، فیلدهای پر مصرف را در یک جدول و فیلدهای حجیم و کم مصرف را در جدول دیگری تعریف کنیم و سپس یک رابطهی یک به یک بین آن دو برقرار میکنیم.
به طور مثال این کار را بر روی جدول Posts ، به شکل زیر انجام شده است:
namespace SplittingTableSample.DomainClasses { public class Post { public virtual int Id { get; set; } public virtual string Title { get; set; } public virtual DateTime CreatedDate { get; set; } public virtual PostMetaData PostMetaData { get; set; } } } namespace SplittingTableSample.DomainClasses { public class PostMetaData { public virtual int PostId { get; set; } public virtual string Body { get; set; } public virtual byte[] Image { get; set; } public virtual Post Post { get; set; } } }
namespace SplittingTableSample.DomainClasses { public class PostMetaDataConfig : EntityTypeConfiguration<PostMetaData> { public PostMetaDataConfig() { HasKey(x => x.PostId); HasRequired(x => x.Post).WithRequiredDependent(x => x.PostMetaData); } } }
var post = context.Posts.Find(1);
exec sp_executesql N'SELECT TOP (2) [Extent1].[Id] AS [Id], [Extent1].[Title] AS [Title], [Extent1].[CreatedDate] AS [CreatedDate] FROM [dbo].[Posts] AS [Extent1] WHERE [Extent1].[Id] = @p0',N'@p0 int',@p0=1
اما راه حل ذکر شده نیز کاملا بدون ایراد نیست. مشکل اساسی آن تعدد تعداد جداول آن است. آیا جدول Post ، واقعا احتیاج به چنین سطح نرمال سازی و تبدیل آن به دو جدول مجزا را داشت؟ مطمئنا خیر. آیا واقعا راه حلی وجود دارد که ما در سمت کدهای خود با دو موجودیت مجزا کار کنیم، در صورتی که در دیتابیس این دو موجودیت، ساختار یک جدول را تشکیل دهند. در اینجا روشی مطرح میشود به نام تقسیم جدول (Table Splitting).
برای انجام این کار فقط چند تنظیم ساده لازم است:
1) فیلدهای موجودیت مورد نظر را به موجودیتهای کوچکتر، نگاشت میکنیم.
2) بین موجودیتهای کوچک تر، رابطهی یک به یک که هر دو سر رابطه Required هستند، رابطه برقرار میکنم.
3) با استفاده از Fluent API یا DataAnnotations، تمامی موجودیتها را به یک نام در دیتابیس نگاشت میکنیم.
برای مثال، تنظیمات Fluent برای کلاس Post و PostMetaData که رابطهی بین آنها یک به یک است را مشاهده میکنید:
namespace SplittingTableSample.DomainClasses { public class PostConfig : EntityTypeConfiguration<Post> { public PostConfig() { ToTable("Posts"); } } } namespace SplittingTableSample.DomainClasses { public class PostMetaDataConfig : EntityTypeConfiguration<PostMetaData> { public PostMetaDataConfig() { ToTable("Posts"); HasKey(x => x.PostId); HasRequired(x => x.Post).WithRequiredDependent(x => x.PostMetaData); } } }
اگر دستورات زیر را اجرا کنید:
var post = context.Posts.Find(1); Console.WriteLine(post.PostMetaData.Body);
برای متد Find خروجی زیر:
exec sp_executesql N'SELECT TOP (2) [Extent1].[Id] AS [Id], [Extent1].[Title] AS [Title], [Extent1].[CreatedDate] AS [CreatedDate] FROM [dbo].[Posts] AS [Extent1] WHERE [Extent1].[Id] = @p0',N'@p0 int',@p0=1
exec sp_executesql N'SELECT [Extent1].[Id] AS [Id], [Extent1].[Body] AS [Body], [Extent1].[Image] AS [Image] FROM [dbo].[Posts] AS [Extent1] WHERE [Extent1].[Id] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1
حال اگر بخواهیم با یک رفت و آمد به دیتابیس کلیه اطلاعات را واکشی کنیم، میتوانیم از Eager Loading استفاده کنیم:
var post = context.Posts.Include(x => x.PostMetaData).SingleOrDefault(x => x.Id == 1);
SELECT [Limit1].[Id] AS [Id], [Limit1].[Title] AS [Title], [Limit1].[CreatedDate] AS [CreatedDate], [Extent2].[Id] AS [Id1], [Extent2].[Body] AS [Body], [Extent2].[Image] AS [Image] FROM (SELECT TOP (2) [Extent1].[Id] AS [Id], [Extent1].[Title] AS [Title], [Extent1].[CreatedDate] AS [CreatedDate] FROM [dbo].[Posts] AS [Extent1] WHERE 1 = [Extent1].[Id] ) AS [Limit1] LEFT OUTER JOIN [dbo].[Posts] AS [Extent2] ON [Limit1].[Id] = [Extent2].[Id]
دریافت کدهای این بخش: SplittingTable-Sample.rar
private static string GetScript() { string path = AppDomain.CurrentDomain.BaseDirectory + @"Scripts\script.sql"; var file = new FileInfo(path); string script = file.OpenText().ReadToEnd(); return script; } private static void ExecuteScript() { string script = GetScript(); //split the script on "GO" commands var splitter = new[] {"\r\nGO\r\n"}; string[] commandTexts = script.Split(splitter, StringSplitOptions.RemoveEmptyEntries); foreach (string commandText in commandTexts) { using (var ctx = new DbContext()) { if (!string.IsNullOrEmpty(commandText)) { ctx.Database.ExecuteSqlCommand(commandText); } } } }
- Lead Function:
LEAD ( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] order_by_clause )
- Scalar_expression: در Scalar_expression، نام یک فیلد یا ستون درج میشود، و مقدار برگشتی فیلد مورد نظر، به مقدار تعیین شده offset نیز بستگی دارد. خروجی Scalar_expression فقط یک مقدار است.
- offset: منظور از Offset در این Syntax همانند عملکرد Offset در Syntax مربوط به Over میباشد. یعنی هر عددی برای offset در نظر گرفته شود، بیانگر نقطه آغازین سطر بعدی یا قبلی نسبت به سطر جاری است. به بیان دیگر، عدد تعیین شده در Offset به Sql server میفهماند چه تعداد سطر را در محاسبه در نظر نگیرد.
- Default: زمانی که برای Offset مقداری را تعیین مینمایید، SQL Server به تعداد تعیین شده در Offset، سطرها را در نظر نمیگیرد، بنابراین مقدار خروجی Scalar_expression بطور پیش فرض Null در نظر گرفته میشود، چنانچه بخواهید، مقداری غیر از Null درج نمایید، میتوانید مقدار دلخواه را در قسمت Default وارد کنید.
- (OVER ( [ partition_by_clause ] order_by_clause : در بخش اول بطور کامل توضیح داده شده است.
Create Table TestLead_LAG (SalesOrderID int not null, SalesOrderDetailID int not null , OrderQty smallint not null); GO Insert Into TestLead_LAG Values (43662,49,1),(43662,50,3),(43662,51,1), (43663,52,1),(43664,53,1),(43664,54,1), (43667,77,3),(43667,78,1),(43667,79,1), (43667,80,1),(43668,81,3),(43669,110,1), (43670,111,1),(43670,112,2),(43670,113,2), (43670,114,1),(43671,115,1),(43671,116,2)
مثال:قصد داریم در هر سطر مقدار بعدی فیلد SalesOrderDetailID در فیلد دیگری به نام LeadValue نمایش دهیم، بنابراین Script زیر را ایجاد میکنیم:SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty, LEAD(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID) LeadValue FROM TestLead_LAG s WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQtyخروجی بصورت زیر خواهد بود:
مطابق شکل، براحتی واضح است، که در هر سطر مقدار بعدی فیلد SalesOrderDetailID در فیلد LeadValue درج و نمایش داده میشود. فقط در سطر 10، چون مقدار بعدی برای فیلد SalesOrderDetailID وجود ندارد، SQL Server مقدار فیلد LeadValue را، Null در نظر میگیرد.در این مثال فقط از آرگومان Scalar_expression، استفاده کردیم، و Offset و Default را مقدار دهی ننمودیم، بنابراین SQL Server بطور پیش فرض هیچ سطری را حذف نمیکند و مقدار Default را Null در نظر میگیرد.مثال دوم: قصد داریم در هر سطر مقدار دو سطر بعدی فیلد SalesOrderDetailID را در فیلد LeadValue نمایش دهیم، و در صورت وجود نداشتن مقدار فیلد SalesOrderDetailID، مقدار پیش فرض صفر ،در فیلد LeadValue قرار دهیم،بنابراین Script آن بصورت زیر خواهد شد:SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty, LEAD(SalesOrderDetailID,2,0) OVER (ORDER BY SalesOrderDetailID) LeadValue FROM TestLead_LAG s WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQtyخروجی:
در صورت مسئله بیان کرده بودیم، در هر سطر،مقدار فیلد SalesOrderDetailID دو سطر بعدی، را نمایش دهیم، بنابراین مقداری که برای Offset در نظر میگیریم، برابر دو خواهد بود، سپس گفته بودیم، چنانچه در هر سطر مقدار فیلد SalesOrderDetailID وجود نداشت،بجای مقدار پیش فرض Null،از مقدار صفر استفاده شود، بنابراین به Default مقدار صفر را نسبت دادیم.LEAD(SalesOrderDetailID,2,0)در شکل، مطابق صورت مسئله، مقدار فیلد LeadValue سطر اول برابر است با 78،به بیان سادهتر برای بدست آوردن مقدار فیلد LaedValue هر سطر، میبایست هر سطر را به علاوه 2 (Offset) نماییم، تا سطر بعدی بدست آید، سپس مقدار SalesOrderDetailID را در فیلد LeadValue قرار میدهیم.به سطر 9 و 10 توجه نمایید، که مقدار فیلد LeadValue آنها برابر با صفر است، واضح است، سطر 10 + 2 برابر است با 12( 10+2=12 )، چنین سطری در خروجی نداریم، بنابراین بطور پیش فرض مقدار LeadVaule توسط Sql Server برابر Null در نظر گرفته میشود، اما نمیخواستیم، که این مقدار Null باشد، بنابراین به آرگومان Default مقدار صفر را نسبت دادیم، تا SQL Server ، به جای استفاده از Null، مقدار در نظر گرفته شده صفر را استفاده نماید.اگر چنین فانکشنی وجود نداشت، برای شبیه سازی آن میبایست از Join روی خود جدول استفاده مینمودیم، و یکسری محاسابت دیگر، که کار را سخت مینمود، مثال دوم را با Script زیر میتوان شبیه سازی نمود:WITH cteLead AS ( SELECT SalesOrderID,SalesOrderDetailID,OrderQty, ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS sn FROM TestLead_LAG WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ) SELECT m.SalesOrderID, m.SalesOrderDetailID, m.OrderQty, case when sLead.SalesOrderDetailID is null Then 0 Else sLead.SalesOrderDetailID END as leadvalue FROM cteLead AS m LEFT OUTER JOIN cteLead AS sLead ON sLead.sn = m.sn+2 ORDER BY m.SalesOrderID, m.SalesOrderDetailID, m.OrderQtyجدول موقتی ایجاد نمودیم، که ROW_Number را در آن اضافه کردیم، سپس جدول ایجاد شده را با خود Join کردیم، و گفتیم، که مقدار فیلدLeadValue هر سطر برابر است با مقدار فیلد SalesOrderDetailID دو سطر بعد از آن. و با Case نیز مقدار پیش فرض را صفر در نظر گرفتیم.
- LAG Function:
این فانکشن نیز در SQL Server 2012 ارائه شده است، و امکان دسترسی، به Dataهای سطر قبلی نسبت به سطر جاری را در نتیجه یک پرس و جو (Query)، ارائه میدهد. بدون آنکه از Self-join استفاده نمایید،Syntax آن شبیه به فانکشن Lead میباشد و بصورت زیر است:LAG (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause )Syntax مربوط به فانکشن LAG را شرح نمیدهم، بدلیل آنکه شبیه به فانکشن Lead میباشد، فقط تفاوت آن در Offset است، Offset در فانکشن LAG روی سطرهای ماقبل سطر جاری اعمال میگردد.مثال دوم را برای حالت LAG Function شبیه سازی مینماییم:SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty, LAG(SalesOrderDetailID,2,0) OVER (ORDER BY SalesOrderDetailID) LAGValue FROM TestLead_LAG s WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty goخروجی :
همانطور که گفتیم، LAG Function عکس LEAD Function میباشد. یعنی مقدار فیلد LAGValue سطر جاری برابر است با مقدار SalesOrderDetailID دو سطر ما قبل خود.مقدار فیلد LAGValue دو سطر اول و دوم نیز برابر صفر است، چون دو سطر ماقبل آنها وجود ندارد، و مقدار صفر نیز بدلیل این است که Default را برابر صفر در نظر گرفته بودیم.مثال: در این مثال از Laed Function و LAG Function بطور همزمان استفاده میکنیم، با این تفاوت، که از گروه بندی نیز استفاده شده است:Script زیر را اجرا نمایید:SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty, Lead(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID ORDER BY SalesOrderDetailID) LeadValue, LAG(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID ORDER BY SalesOrderDetailID) LAGValue FROM TestLead_LAG s WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty goخروجی:با بررسی هایی که در مثالهای قبل نمودیم،خروجی زیر را میتوان براحتی تشخیص داد، و توضیح بیشتری نمیدهم.موفق باشید.