اشتراک‌ها
ارسال اطلاعات به پراسیجر SQL‌ از طریق XML
بارها قصد ارسال اطلاعات با تعداد رکوردهای بالا را به SQL را داشته اید. برای این منظور یا باید به صورت رکورد، رکورد ارسال اطلاعات نمایید که مشکلات فراوانی مثل سرعت انتقال اطلاعات و هندل کردن قطعی سیستم در حال ارسال و غیره وجود دارد. برای حل این موضوع پیشنهاد می‌شود از ارسال اطلاعات از طریق XML اقدام نمایید و اطلاعات را در سمت SQL ارسال و یا تغییر دهید.
برای این منظور یک string ‌به صورت زیر در محیط نرم افزار ایجاد می‌شود و در سمت SQL هم پراسیجری با ورودی Text ایجاد می‌شود و سپس از طریق دستورات زیر می‌توانید Table‌معادل XML ارسالی را دریافت نمایید.
 DECLARE @xml xml
SET @xml = N'<polist>
               <po ponumber="100" podate="2008-09-10" />
               <po ponumber="101" podate="2008-09-11" />
             </polist>'
SELECT
 doc.col.value('@ponumber', 'nvarchar(10)') ponumber
,doc.col.value('@podate', 'datetime') podate 
FROM @xml.nodes('/polist/po') doc(col)
ارسال اطلاعات به پراسیجر SQL‌ از طریق XML
مطالب
استفاده از LINQ to XML جهت خواندن فیدهای RSS

مثال زیر را به عنوانی نمونه‌ای از کاربرد LINQ to XML برای خواندن فیدهای RSS که اساسا به فرمت XML هستند می‌توان ارائه داد.
ابتدا کد کامل مثال را در نظر بگیرید:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;

namespace LinqToRSS
{
public static class LanguageExtender
{
public static string SafeValue(this XElement input)
{
return (input == null) ? string.Empty : input.Value;
}

public static DateTime SafeDateValue(this XElement input)
{
return (input == null) ? DateTime.MinValue : DateTime.Parse(input.Value);
}
}

public class RssEntry
{
public string Title { set; get; }
public string Description { set; get; }
public string Link { set; get; }
public DateTime PublicationDate { set; get; }
public string Author { set; get; }
public string BlogName { set; get; }
public string BlogAddress { set; get; }
}

public class Rss
{
static XElement selectDate(XElement date1, XElement date2)
{
return date1 ?? date2;
}

public static List<RssEntry> GetEntries(string feedUrl)
{
//applying namespace in an XElement
XName xn = XName.Get("{http://purl.org/dc/elements/1.1/}creator");//{namespace}root
XName xn2 = XName.Get("{http://purl.org/dc/elements/1.1/}date");

var feed = XDocument.Load(feedUrl);
if (feed.Root == null) return null;

var items = feed.Root.Element("channel").Elements("item");
var feedQuery =
from item in items
select new RssEntry
{
Title = item.Element("title").SafeValue(),
Description = item.Element("description").SafeValue(),
Link = item.Element("link").SafeValue(),
PublicationDate =
selectDate(item.Element(xn2), item.Element("pubDate")).SafeDateValue(),
Author = item.Element(xn).SafeValue(),
BlogName = item.Parent.Element("title").SafeValue(),
BlogAddress = item.Parent.Element("link").SafeValue()
};

return feedQuery.ToList();
}
}

class Program
{
static void Main(string[] args)
{
List<RssEntry> entries = Rss.GetEntries("http://weblogs.asp.net/aspnet-team/rss.aspx");
if (entries != null)
foreach (var item in entries)
Console.WriteLine(item.Title);

Console.WriteLine("Press a key...");
Console.ReadKey();
}
}
}

توضیحات:
1- در این مثال فقط جهت سهولت بیان آن در یک صفحه، تمامی کلاس‌های تعریف شده در یک فایل آورده شدند. این روش صحیح نیست و باید به ازای هر کلاس یک فایل جدا در نظر گرفته شود.
2- کلاس LanguageExtender از قابلیت extension methods سی شارپ 3 استفاده می‌کند. به این صورت کلاس XElement دات نت بسط یافته و دو متد به آن اضافه می‌شود که به سادگی در کدهای خود می‌توان از آن‌ها استفاده کرد. هدف آن هم بررسی نال بودن یک آیتم دریافتی و ارائه‌ی حاصلی امن برای این مورد است.
3- کلاس RssEntry به جهت استفاده در خروجی کوئری LINQ تعریف شد. می‌خواهیم خروجی نهایی، یک لیست جنریک از نوع RssEntry باشد.
4- متد اصلی برنامه، GetEntries است. این متد آدرس اینترنتی یک فید را دریافت کرده و پس از آنالیز، آن‌را به صورت یک لیست بر می‌گرداند.

<?xml version="1.0" encoding="UTF-8" ?>
<?xml-stylesheet type="text/xsl" href="http://weblogs.asp.net/utility/FeedStylesheets/rss.xsl" media="screen"?>
<rss version="2.0" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:slash="http://purl.org/rss/1.0/modules/slash/" xmlns:wfw="http://wellformedweb.org/CommentAPI/">
<channel>
<title>Latest Microsoft Blogs</title>
<link>http://weblogs.asp.net/aspnet-team/default.aspx</link>
<description />
<dc:language>en</dc:language>
<generator>CommunityServer 2007 SP1 (Build: 20510.895)</generator>
<item>
<title>Comments on my recent benchmarks.</title>
<link>http://misfitgeek.com/blog/aspnet/comments-on-my-recent-benchmarks/</link>
<pubDate>Mon, 10 Aug 2009 23:33:59 GMT</pubDate>
<guid isPermaLink="false">c06e2b9d-981a-45b4-a55f-ab0d8bbfdc1c:7166225</guid>
<dc:creator>Misfit Geek: msft</dc:creator>
<slash:comments>0</slash:comments>
<wfw:commentRss xmlns:wfw="http://wellformedweb.org/CommentAPI/">http://weblogs.asp.net/aspnet-team/rsscomments.aspx?PostID=7166225</wfw:commentRss>
<comments>http://misfitgeek.com/blog/aspnet/comments-on-my-recent-benchmarks/#comments</comments>
<description>Overall I’ve been pretty impressed ...</description>
<category domain="http://weblogs.asp.net/aspnet-team/archive/tags/ASP.NET/default.aspx">ASP.NET</category>
</item>
</channel>
</rss>
برای نمونه خروجی یک فید می‌تواند به صورت فوق باشد. آیتم‌های آن به صورت قابل بیان است:
var items = feed.Root.Element("channel").Elements("item");
و نکته مهمی که اینجا وجود دارد، اعمال فضاهای نام بکار رفته در این فایل xml پیشرفته می‌باشد. برای اعمال فضاهای نام به یکی از دو روش زیر می‌توان عمل کرد:

XName.Get("{mynamespace}root");
//or
XName.Get("root", "mynamespace");

مطالب
نحوه انتقال اطلاعات استخراج شده از وب سرویس به SQL Server به کمک SSIS
ممکن است در مواقعی نیاز به اطلاعات استخراج شده از وب سرویسی داشته باشید که در همان مقطع زمانی به آن دسترسی ندارید . مسلما برای این منظور باید آن اطلاعات را ذخیره کرده تا در صورت نیاز بتوان به آنها رجوع کرد . یکی از راه حل‌ها ذخیره آن در پایگاه داده (در اینجا Sql Server) است که در این پست به کمک امکانات BIDS در پکیج‌های SSIS و کوئری‌های SQL این مشکل را برطرف میکنیم. برای مشاهده نحوه استخراج اطلاعات از وب سرویس به اینجا مراجعه کنید .
تنها تفاوتی که در این پست در کار با سرویس با پست اشاره شده در بالا وحود دارد ذخیره اطلاعات استخراج شده است که در آن پست در یک فایل xml ذخیره شدند ولی در اینجا ما نیاز داریم تا اطلاعات را در یک متغیر با حوزه کاری Package ذخیره کنیم (به این معنی که مختص به همان flow نباشد و در تمام پکیج دیده شود) 

به دلیل اینکه هدر xml خروجی از سرویس دارای چندین namespace هست هنگام کار با آنها به مشکل خواهیم خورد. (هم هنگام کار با xml task‌ها و هم هنگام کار با xml در sql) 

به همین دلیل باید این قسمت از محتوا را حذف کرد . برای همین پس از گرفتن اطلاعات از سرویس آن را به کمک یک Script task حذف می‌کنیم 

در این مرحله اطلاعات استخراج شده را باید در SQL درج کنیم . برای همین ساختاری که باید اطلاعات را در SQL نگه دارد را در دیتابیس ایجاد می‌کنیم : 

جدول person برای نگهداری اطلاعات سرویس و XmlContainer برای نگهداری xml‌های سرویس .(برای داشتن History) 

برای درج هم از SP استفاده می‌کنیم :

 

و پیکربندی SQL Task :
 

نکته اول ایجاد کانکشن به پایگاه داده است که در اینجا از نمایش جزییات آن صرف نظر شده است . نکته دیگری پارامتر SP است که چون یک پارامتر دارد یک علامت سوال قرار میگیرد . اگر چند پارامتر بود به صورت علامت‌های سوال با ویرگول از هم جدا می‌شوند . ?,?,?,?
سپس در بخش parameter mapping به ترتیب مقدار دهی می‌شوند : 

و مقدار خروجی SP که شناسه آیتم درج شده است را در یک متغیر نگهداری می‌کنیم :
 

برای قدم بعد می‌خواهیم اطلاعات موجود در XML استخراج شده در پایگاه داده را در جدول مربوطه ذخیره کنیم . برای این کار این SP را می‌نویسیم : 
 

نکات مهم موارد زیر هستند : 
1 - استفاده از OpenXml برای parse کردن xml 
2 - استفاده از sp سیستمی sp_xml_preparedocument و sp_xml_removedocument (بیشتر ) 
3- اطلاعات شناسه و نام و نام خانوادگی inner text‌های نود‌های xml هستند . اگر این موارد به صورت attribute باشند باید قبل از نام آنها علامت @ قرار بگیرند. 
پس از ایجاد این Sp باید آن را در package فراخوانی کنیم : 

و پیکربندی این SQL Task :
 

و پارامتر‌های این SP :

و ذخیره نتیجه تراکنش در متغیری در پکیج :
 

و اکنون پکیج ما ظاهری شبیه به این مورد خواهد داشت :
 

 

در نهایت به عنوان یک facility می‌توانیم وضعیت تراکنش را به کاربر نمایش دهیم ( به کمک Script Task ) : 

و تمام ...

 

موفق باشید 

مطالب
استفاده از فیلدهای XML در NHibernate

در مورد طراحی یک برنامه "فرم ساز" در مطلب قبلی بحث شد ... حدودا سه سال قبل اینکار را برای شرکتی انجام دادم. یک برنامه درخواست خدمات نوشته شده با ASP.NET که مدیران برنامه می‌توانستند برای آن فرم طراحی کنند؛ فرم درخواست پرینت، درخواست نصب نرم افزار، درخواست وام، درخواست پیک، درخواست آژانس و ... فرم‌هایی که تمامی نداشتند! آن زمان برای حل این مساله از فیلدهای XML استفاده کردم.
فیلدهای XML قابلیت نه چندان جدیدی هستند که از SQL Server 2005 به بعد اضافه شده‌اند. مهم‌ترین مزیت آن‌ها‌ هم امکان ذخیره سازی اطلاعات هر نوع شیء‌ایی به عنوان یک فیلد XML است. یعنی همان زیرساختی که برای ایجاد یک برنامه فرم ساز نیاز است. ذخیره سازی آن هم آداب خاصی را طلب نمی‌کند. به ازای هر فیلد مورد نظر کاربر، یک نود جدید به صورت رشته معمولی باید اضافه شود و نهایتا رشته تولیدی باید ذخیره گردد. از دید ما یک رشته‌ است، از دید SQL Server یک نوع XML واقعی؛ به همراه این مزیت مهم که به سادگی می‌توان با T-SQL/XQuery/XPath از جزئیات اطلاعات این نوع فیلدها کوئری گرفت و سرعت کار هم واقعا بالا است؛ به علاوه بر خلاف مطلب قبلی در مورد dynamic components ، اینبار نیازی نیست تا به ازای هر یک فیلد درخواستی کاربر، واقعا یک فیلد جدید را به جدول خاصی اضافه کرد. داخل این فیلد XML هر نوع ساختار دلخواهی را می‌توان ذخیره کرد. به عبارتی به کمک فیلدهایی از نوع XML می‌توان داخل یک سیستم بانک اطلاعاتی رابطه‌ای، schema-less کار کرد (un-typed XML) و همچنین از این اطلاعات ویژه، کوئری‌های پیچیده هم گرفت.
تا جایی که اطلاع دارم، چند شرکت دیگر هم در ایران دقیقا از همین ایده فیلدهای XML برای ساخت برنامه فرم ساز استفاده کرده‌اند ...؛ البته مطلب جدیدی هم نیست؛ برنامه‌های فرم ساز اوراکل و IBM هم سال‌ها است که از XML برای همین منظور استفاده می‌کنند. مایکروسافت هم به همین دلیل (شاید بتوان گفت مهم‌ترین دلیل وجودی فیلدهای XML در SQL Server)، پشتیبانی توکاری از XML به عمل آورده‌ است.
یا روش دیگری را که برای طراحی سیستم‌های فرم ساز پیشنهاد می‌کنند استفاده از بانک‌های اطلاعاتی مبتنی بر key-value‌ مانند Redis یا RavenDb است؛ یا استفاده از بانک‌های اطلاعاتی schema-less واقعی مانند CouchDb.


خوب ... اکنون سؤال این است که NHibernate برای کار با فیلدهای XML چه تمهیداتی را درنظر گرفته است؟
برای این منظور خاصیتی را که قرار است به یک فیلد از نوع XML نگاشت شود، با نوع XDocument مشخص خواهیم ساخت:
using System.Xml.Linq;

namespace TestModel
{
public class DynamicTable
{
public virtual int Id { get; set; }
public virtual XDocument Document { get; set; }
}
}

سپس باید جهت معرفی این نوع ویژه، به صورت صریح از XDocType استفاده کرد؛ یعنی نکته‌ی اصلی، استفاده از CustomType مرتبط است:
using FluentNHibernate.Automapping;
using FluentNHibernate.Automapping.Alterations;
using NHibernate.Type;

namespace TestModel
{
public class DynamicTableMapping : IAutoMappingOverride<DynamicTable>
{
public void Override(AutoMapping<DynamicTable> mapping)
{
mapping.Id(x => x.Id);
mapping.Map(x => x.Document).CustomType<XDocType>();
}
}
}

البته لازم به ذکر است که دو نوع NHibernate.Type.XDocType و NHibernate.Type.XmlDocType برای کار با فیلد‌های XML در NHibernate وجود دارند. XDocType برای کار با نوع System.Xml.Linq.XDocument طراحی شده است و XmlDocType مخصوص نگاشت نوع System.Xml.XmlDocument است.

اکنون اگر به کمک کلاس SchemaExport ، اسکریپت تولید جدول متناظر با اطلاعات فوق را ایجاد کنیم به حاصل زیر خواهیم رسید:
   if exists (select * from dbo.sysobjects
where id = object_id(N'[DynamicTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [DynamicTable]

create table [DynamicTable] (
Id INT IDENTITY NOT NULL,
Document XML null,
primary key (Id)
)

یک سری اعمال متداول ذخیره سازی اطلاعات و تهیه کوئری نیز در ادامه ذکر شده‌اند:
//insert
object savedId = 0;
using (var session = sessionFactory.OpenSession())
{
using (var tx = session.BeginTransaction())
{
var obj = new DynamicTable
{
Document = System.Xml.Linq.XDocument.Parse(
@"<Doc><Node1>Text1</Node1><Node2>Text2</Node2></Doc>"
)
};
savedId = session.Save(obj);
tx.Commit();
}
}

//simple query
using (var session = sessionFactory.OpenSession())
{
using (var tx = session.BeginTransaction())
{
var entity = session.Get<DynamicTable>(savedId);
if (entity != null)
{
Console.WriteLine(entity.Document.Root.ToString());
}

tx.Commit();
}
}

//advanced query
using (var session = sessionFactory.OpenSession())
{
using (var tx = session.BeginTransaction())
{
var list = session.CreateSQLQuery("select [Document].value('(//Doc/Node1)[1]','nvarchar(255)') from [DynamicTable] where id=:p0")
.SetParameter("p0", savedId)
.List();

if (list != null)
{
Console.WriteLine(list[0]);
}

tx.Commit();
}
}

و در پایان بدیهی است که جهت کار با امکانات پیشرفته‌تر موجود در SQL Server در مورد فیلد‌های XML ( برای نمونه: + و +) باید مثلا رویه ذخیره شده تهیه کرد (یا مستقیما از متد CreateSQLQuery همانند مثال فوق کمک گرفت) و آن‌را در NHibernate مورد استفاده قرار داد. البته به این صورت کار شما محدود به SQL Server خواهد شد و باید در نظر داشت که در کل تعداد کمی بانک اطلاعاتی وجود دارند که نوع‌های XML را به صورت توکار پشتیبانی می‌کنند.

مطالب
ردیابی تغییرات در Entity Framework، بخش اول
همان طور که می‌دانید، Entity Framework  تغییراتی را که بر روی اشیا انجام می‌دهید، ردیابی می‌کند. بدیهی است که EF از طریق ردیابی این تغییرات است که می‌تواند تغییرات انجام شده را شناسایی کند و آن‌ها را در مواقع مورد نیاز مانند ذخیره‌ی تغییرات  (DbContext.SaveChanges)، بر روی پایگاه داده اعمال  کند. شما می‌توانید به اطلاعات این ردیاب تغییر و اعمال مرتبط به آن از طریق ویژگی  DbContext.ChangeTracker دسترسی پیدا کنید. 
در این مقاله بیشتر سعی به بررسی مفاهیم ردیابی و روش هایی که EF برای ردیابی تغییرات استفاده می‌کند، بسنده می‌کنم و بررسی  API‌های مختلف آن را به مقاله ای دیگر موکول می‌کنم.
به طور کلی EF از دو روش برای ردیابی تغییرات رخ داده شده در اشیا استفاده می‌کند:
1) ردیابی تغییر عکس فوری! (Snapshot change tracking) 
2) پروکسی‌های ردیابی تغییر  (Change tracking proxies)

 ردیابی تغییر عکس فوری

به نظر من، اسم مناسبی برای این روش انتخاب کرده اند و دقیقا بیان گر کاری است که  EF انجام می‌دهد. در حالت عادی کلاس‌های دامین ما یا همان کلاس‌های POCO، هیچ منطق و کدی را برای مطلع ساختن EF از تغییراتی که در آن‌ها رخ می‌دهد پیاده سازی نکرده اند. چون هیچ راهی برای EF، برای مطلع شدن از تغییرات رخ داده وجود ندارد، EF راه جالبی را بر می‌گزیند. EF هر گاه شیئی را می‌بیند از مقادیر ویژگی‌های آن یک عکس فوری می‌گیرد! و آن‌ها را در حافظه ذخیره می‌کند.این عمل هنگامی که یک شی از پرس و جو (query) حاصل می‌شود، و یا شیئی را به DbSet اضافه می‌کنیم رخ می‌دهد.
زمانی که EF می‌خواهد بفهمد که چه تغییراتی رخ داده است، مقادیر کنونی موجود در کلیه اشیا را اسکن می‌کند و با مقادیری که در عکس فوری ذخیره کرده است مقایسه می‌کند و متوجه تغییرات رخ داده می‌شود. این فرآیند اسکن کردن کلیه اشیا زمانی رخ می‌دهد که متد DetectChanges ویژگی DbSet.ChangeTracker صدا زده شود.

 پروکسی‌های ردیابی تغییر

پروکسی‌های ردیابی تغییر، مکانیزم دیگری برای ردیابی تغییرات EF است و به EF این اجازه را می‌دهد تا از تغییرات رخ داده، مطلع شود.
اگر به یاد داشته باشید در مباحث Lazy loading نیز از واژه پروکسی‌های پویا استفاده شد. پروکسی‌های ردیابی تغییر نیز با استفاده از همان مکانیزم کار می‌کنند و علاوه بر فراهم کردن Lazy loading ،این امکان را می‌دهند تا تغییرات را به Context انتقال دهند.
برای استفاده از پروکسی‌های ردیابی تغییر، شما باید ساختار کلاس‌های خود را به گونه ای تغییر دهید، تا EF بتواند در زمان اجرا، نوع پویایی را که هریک، از کلاس‌های POCO شما مشتق می‌شوند ایجاد کند، و تک تک ویژگی‌های آن‌ها را تحریف (override) کند.
این نوع پویا که به عنوان پروکسی پویا نیز شناخته می‌شود، منطقی را در ویژگی‌های تحریف شده شامل می‌شود، تا EF را از تغییرات صورت گرفته در ویژگی هایش مطلع سازد.
 برای بیان ادامه‌ی مطلب، من مدل یک دفترچه تلفن ساده را به شرح زیر در نظر گرفتم که روابط مهم و اساسی در آن در نظر گرفته شده است. 
namespace EntitySample1.DomainClasses
{
    public class Person
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime BirthDate { get; set; }
        public virtual PersonInfo PersonInfo { get; set; }
        public virtual ICollection<PhoneNumber> PhoneNumbers { get; set; }
        public virtual ICollection<Address> Addresses { get; set; }
    }
}

namespace EntitySample1.DomainClasses
{
    public class PersonInfo
    {
        public int Id { get; set; }
        public string Note { get; set; }
        public string Major { get; set; }
    }
}

namespace EntitySample1.DomainClasses
{
    public enum PhoneType
    {
        Home,
        Mobile,
        Work
    }

    public class PhoneNumber
    {
        public int Id { get; set; }
        public string Number { get; set; }
        public PhoneType PhoneType { get; set; }
        public virtual Person Person { get; set; }
    }
}

namespace EntitySample1.DomainClasses
{
    public class Address
    {
        public int Id { get; set; }
        public string City { get; set; }
        public string Street { get; set; }
        public virtual ICollection<Person> Persons { get; set; }
    }
}
طبق کلاس‌های فوق ، ما تعدادی شخص ، اطلاعات شخص ، شماره تلفن و آدرس داریم. رابطه‌ی بین شخص و اطلاعات آن شخص یک به یک، شخص و آدرس  چند به چند  و شخص با شماره تلفن یک به چند است. همچنین به این نکته توجه داشته باشید که  کلیه کلاس‌های فوق به صورت public تعریف، و کلیه خواص راهبری (navigation properties) به صورت virtual تعریف شده اند. دلیل این کار هم این است که این دو مورد، جز الزامات، برای فعال سازی Lazy loading هستند. 
تعریف کلاس context نیز به شکل زیر است:

namespace EntitySample1.DataLayer
{
    public class PhoneBookDbContext : DbContext
    {
        public DbSet<Person> Persons { get; set; }
        public DbSet<PhoneNumber> PhoneNumbers { get; set; }
        public DbSet<Address> Addresses { get; set; }

    }
}

استفاده از ردیابی تغییر عکس فوری

ردیابی تغییر عکس فوری، وابسته به این است که EF بفهمد، چه زمانی تغییرات رخ داده است. رفتار پیش فرض DbContext API ، این هست که به صورت خودکار بازرسی لازم را در نتیجه‌ی رخداد‌های DbContext انجام دهد. DetectChanges تنها اطلاعات مدیریت حالت context، که وظیفه‌ی انعکاس تغییرات صورت گرفته به پایگاه داده را دارد، به روز نمی‌کند، بلکه اصلاح رابطه(ralationship) ترکیبی از خواص راهبری مرجع ، مجموعه ای  و کلید‌های خارجی را انجام می‌دهد. این خیلی مهم خواهد بود که درک روشنی داشته باشیم از این که چگونه و چه زمانی تغییرات تشخیص داده می‌شوند،چه چیزی باید از آن انتظار داشته باشیم و چگونه کنترلش کنیم.

چه زمانی تشخیص خودکار تغییرات اجرا می‌شود؟

متد DetectChanges کلاس ObjectContext، از EF نسخه‌ی 4 به عنوان بخشی از الگوی ردیابی تغییر عکس فوری اشیای POCO ،در دسترس بوده است. تفاوتی که در مورد DataContext.ChangeTracker.DetectChanges( در حقیقت ObjectContext.DetectChanges فراخوانی می‌شود) وجود دارد این است که، رویداد‌های خیلی بیشتری وجود دارند که به صورت خودکار DetectChanges را فراخوانی می‌کنند.
لیستی از متدهایی که باعث انجام عمل تشخیص تغییرات (DetectChanges)، می‌شوند را  در ادامه مشاهده می‌کنید:
• DbSet.Add
• DbSet.Find
• DbSet.Remove
• DbSet.Local
• DbSet.SaveChanges
• فراخوانی Linq Query از DbSet
• DbSet.Attach
• DbContext.GetValidationErrors
• DbContext.Entry
• DbChangeTracker.Entries

 
کنترل زمان فراخوانی DetectChanges

بیشترین زمانی که EF احتیاج به فهمیدن تغییرات دارد، در زمان SaveChanges است، اما حالت‌های زیاد دیگری نیز هست. برای مثال، اگر ما از ردیاب تغییرات، درخواست وضعیت فعلی یک شی را بکنیم،EF احتیاج به اسکن کردن و بررسی تغییرات رخ داده را دارد. همچنین وضعیتی را در نظر بگیرید که شما از پایگاه داده یک شماره تلفن را واکشی می‌کنید و سپس آن را به مجموعه شماره تلفن‌های یک شخص جدید اضافه می‌کنید.آن شماره تلفن اکنون تغییر کرده است، چرا که انتساب آن به یک شخص جدید،خاصیت PersonId آن را تغییر داده است. ولی EF برای اینکه بفهمد تغییر رخ داده است(یا حتی نداده است) ، احتیاج به اسکن کردن همه‌ی اشیا Person دارد.
بیشتر عملیاتی که بر روی DbContext API انجام می‌دهید، موجب فراخوانی DetectChanges می‌شود. در بیشتر موارد DetectChanges به اندازه کافی سریع هست تا باعث ایجاد مشکل کارایی نشود. با این حال ممکن است ، شما تعداد خیلی زیادی اشیا در حافظه داشته باشید، و یا تعداد زیادی عملیات در DbContext ، در مدت خیلی کوتاهی انجام دهید، رفتار تشخیص خودکار تغییرات ممکن است، باعث نگرانی‌های کارایی شود. خوشبختانه گزینه ای برای خاموش کردن رفتار تشخیص خودکار تغییرات وجود دارد و هر زمانی که می‌دانید لازم است، می‌توانید آن را به صورت دستی فراخوانی کنید.
 EF بر مبنای این فرض ساخته شده است که شما ، در صورتی که در فراخوانی آخرین API، موجودیتی تغییر پیدا کرده است، قبل از فراخوانی API جدید، باید DetectChanges صدا زده شود. این شامل فراخوانی DetectChanges، قبل از اجرای هر query نیز می‌شود.اگر این عمل ناموفق یا نابجا انجام شود،ممکن است عواقب غیر منتظره ای در بر داشته باشد. DbContext انجام این وظیفه را بر عهده گرفته است و به همین دلیل به طور پیش فرض تشخیص تغییرات خودکار آن فعال است.

نکته:
تشخیص اینکه چه زمانی احتیاج به فراخوانی DetectChanges است،آن طور که ساده و بدیهی به نظر می‌آید نیست. تیم EF شدیدا توصیه کرده اند که فقط، وقتی با مشکلات عدم کارایی روبرو شدید، تشخیص تغییرات را به حالت دستی در بیاورید.همچنین توصیه شده که در چنین مواقعی، تشخیص خودکار تغییرات را فقط برای قسمتی از کد که با کارایی پایین مواجه شدید خاموش کنید و پس از اینکه اجرای آن قسمت از کد تمام شد،دوباره آن را روشن کنید.

برای خاموش یا روشن کردن تشخیص خودکار تغییرات، باید متغیر بولین DbContext.Configuration.AutoDetectChangesEnabled را تنظیم کنید.
در مثال زیر، ما در متد ManualDetectChanges، تشخیص خودکار تغییرات را خاموش کرده ایم و تاثیرات آن را بررسی کرده ایم.

        private static void ManualDetectChanges()
        {
            using (var context = new PhoneBookDbContext())
            {
                context.Configuration.AutoDetectChangesEnabled = false; // turn off Auto Detect Changes

                var p1 = context.Persons.Single(p => p.FirstName == "joe");

                p1.LastName = "Brown";

                Console.WriteLine("Before DetectChanges: {0}", context.Entry(p1).State);

                context.ChangeTracker.DetectChanges(); // call detect changes manually

                Console.WriteLine("After DetectChanges: {0}", context.Entry(p1).State);
            }
        }

در کدهای بالا ابتدا تشخیص خودکار تغییرات را خاموش کرده ایم و سپس یک شخص با نام joe را از دیتابیس فراخواندیم و سپس نام خانوادگی آن را به Brown تغییر دادیم. سپس در خط بعد، وضعیت فعلی موجودیت p1 را از context جاری پرسیدیم. در خط بعدی، DetectChanges را به صورت دستی صدا زده ایم و دوباره همان پروسه را برای به دست آوردن وضیعت شی p1، انجام داده ایم. همان طور که می‌بینید ، برای به دست آوردن وضعیت فعلی شی مورد نظر از متد Entry متعلق به ChangeTracker API استفاده می‌کنیم، که در آینده مفصل در مورد آن بحث خواهد شد. اگر شما متد Main را با صدا زدن ManualDetectChanges ویرایش کنید ، خروجی زیر را مشاهده خواهید کرد:

Before DetectChanges: Unchanged
After DetectChanges: Modified

همان طور که انتظار می‌رفت، به دلیل خاموش کردن تشخیص خودکار تغییرات، context قادر به تشخیص تغییرات صورت گرفته در شی p1 نیست، تا زمانی که متد DetectChanges را به صورت دستی صدا بزنیم. دلیل این که در دفعه اول، ما نتیجه‌ی غلطی مشاهده می‌کنیم، این است که ما قانون را نقض کرده ایم و قبل از صدا زدن هر API ، متد DetectChanges را صدا نزده ایم. خوشبختانه چون ما در اینجا وضعیت یک شی را بررسی کردیم، با عوارض جانبی آن روبرو نشدیم.

نکته: به این نکته توجه داشته باشید که متد Entry به صورت خودکار، DetectChanges را فراخوانی می‌کند. برای اینکه دانسته بخواهیم این رفتار را غیر فعال کنیم، باید AutoDetectChangesEnabled را غیر فعال کنیم.
در مثال فوق ،خاموش کردن تشخیص خودکار تغییرات، برای ما مزیتی به همراه نداشت و حتی ممکن بود برای ما دردسر ساز شود. ولی حالتی را  در نظر بگیرید که ما یک سری API را فراخوانی می‌کنیم ،بدون این که در این بین ،در حالت اشیا تغییری ایجاد کنیم.در نتیجه می‌توانیم از فراخوانی‌های بی جهت DetectChanges جلوگیری کنیم.
 
در متد AddMultiplePersons مثال بعدی، این کار را نشان داده ام:

        private static void AddMultiplePerson()
        {
            using (var context = new PhoneBookDbContext())
            {
                context.Configuration.AutoDetectChangesEnabled = false;

                context.Persons.Add(new Person
                    {
                        FirstName = "brad",
                        LastName = "watson",
                        BirthDate = new DateTime(1990, 6, 8)
                    });

                context.Persons.Add(new Person
                {
                    FirstName = "david",
                    LastName = "brown",
                    BirthDate = new DateTime(1990, 6, 8)
                });

                context.Persons.Add(new Person
                {
                    FirstName = "will",
                    LastName = "smith",
                    BirthDate = new DateTime(1990, 6, 8)
                });

                context.SaveChanges();

            }
        }
در مثال بالا ما از فراخوانی چهار DetectChanges غیر ضروری که شامل DbSet.Add و SaveChanges می‌شود، جلوگیری کرده ایم.

استفاده از DetectChanges برای فراخوانی اصلاح رابطه


DetectChanges همچنین مسئولیت انجام اصلاح رابطه ، برای هر رابطه ای که تشخیص دهد تغییر کرده است را دارد.اگر شما بعضی از روابط را تغییر دادید و مایل بودید تا همه‌ی خواص راهبری و خواص کلید خارجی را منطبق کنید، DetectChanges این کار را برای شما انجام می‌دهد. این قابلیت می‌تواند برای سناریوهای data-binding که در آن ممکن است در رابط کاربری(UI) یکی از خواص راهبری (یا حتی یک کلید خارجی) تغییر کند، و شما بخواهید که خواص دیگری این رابطه به روز شوند و تغییرات را نشان دهند، مفید واقع شود.
متد DetectRelationshipChanges در مثال زیر از DetectChanges برای انجام اصلاح رابطه استفاده می‌کند.
 
        private static void DetectRelationshipChanges()
        {
            using (var context = new PhoneBookDbContext())
            {

                var phone1 = context.PhoneNumbers.Single(x => x.Number == "09351234567");

                var person1 = context.Persons.Single(x => x.FirstName == "will");

                person1.PhoneNumbers.Add(phone1);

                Console.WriteLine("Before DetectChanges: {0}", phone1.Person.FirstName);

                context.ChangeTracker.DetectChanges(); // ralationships fix-up

                Console.WriteLine("After DetectChanges: {0}", phone1.Person.FirstName);
            }
        }

در اینجا ابتدا ما شماره تلفنی را از دیتابیس لود می‌کنیم. سپس شخص دیگری را نیز با نام will از دیتابیس می‌خوانیم. قصد داریم شماره تلفن خوانده شده را به این شخص نسبت دهیم و مجموعه شماره تلفن‌های وی اضافه کنیم و ما این کار را با افزودن phone1 به مجموعه شماره تلفن‌های person1 انجام داده ایم. چون ما از اشیای POCO استفاده کرده ایم،EF  نمی‌فهمد که ما این تغییر را ایجاد کرده ایم و در نتیجه کلید خارجی PersonId شی phone1 را اصلاح نمی‌کند. ما می‌توانیم تا زمانی صبر کنیم تا متدی مثل SaveChanges، متد DetectChanges را فراخوانی کند،ولی اگر بخواهیم این عمل در همان لحظه انجام شود، می‌توانیم DetectChanges را دستی صدا بزنیم.

اگر ما متد Main را با اضافه کردن فرخوانی DetectRealtionShipsChanges تغییر بدهیم و آن را اجرا کنیم، نتیجه زیر را مشاهده می‌کنید:

Before DetectChanges: david
After DetectChanges: will

تا قبل از فراخوانی تشخیص تغییرات(DetectChanegs)، هنوز phone1 منتسب به شخص قدیمی(david) بوده، ولی پس از فراخوانی DetectChanges ، اصلاح رابطه رخ داده و همه  چیز با یکدیگر منطبق می‌شوند.

فعال سازی و کار با پروکسی‌های ردیابی تغییر

اگر پروفایلر کارایی شما، فراخوانی‌های بیش از اندازه DetectChnages را به عنوان یک مشکل شناسایی کند، و یا شما ترجیح می‌دهید که اصلاح رابطه به صورت بلادرنگ صورت گیرد ، ردیابی تغییر پروکسی‌های پویا، به عنوان گزینه ای دیگر مطرح می‌شود.فقط با چند تغییر کوچک در کلاس‌های POCO، EF قادر به ساخت پروکسی‌های پویا خواهد بود.پروکسی‌های ردیابی تغییر به EF اجازه ردیابی تغییرات در همان لحظه ای که ما تغییری در اشیای خود می‌دهیم را می‌دهند و همچنین امکان انجام اصلاح رابطه را در هر زمانی که تغییرات روابط را تشخیص دهد، دارد.
برای اینکه پروکسی ردیابی تغییر بتواند ساخته شود، باید قوانین زیر رعایت شود:
 
• کلاس باید public باشد و seald نباشد.
• همه‌ی خواص(properties) باید virtual تعریف شوند.
• همه‌ی خواص باید getter و setter با سطح دسترسی public داشته باشند.
• همه‌ی خواص راهبری مجموعه ای باید نوعشان، از نوع ICollection<T> تعریف شوند.


کلاس Person مثال خود را به گونه ای بازنویسی کرده ایم که تمام قوانین فوق را پیاده سازی کرده باشد.

نکته: توجه داشته باشید که ما دیگر در داخل سازنده کلاس ،کدی نمی‌نویسیم و منطقی  که باعث نمونه سازی اولیه خواص راهبری می‌شدند، را پیاده سازی نمی‌کنیم. این پروکسی ردیاب تغییر، همه‌ی خواص راهبری مجموعه ای را تحریف کرده و ار نوع مجموعه ای مخصوص خود(EntityCollection<T>) استفاده می‌کند. این نوع مجموعه ای، هر تغییری که در این مجموعه صورت می‌گیرد را زیر نظر گرفته و به ردیاب تغییر گزارش می‌دهد. اگر تلاش کنید تا نوع دیگری مانند List<T> که معمولا در سازنده کلاس از آن استفاده می‌کردیم را به آن انتساب دهیم، پروکسی، استثنایی را پرتاب می‌کند.

namespace EntitySample1.DomainClasses
{
    public class Person
    {
        public virtual int Id { get; set; }
        public virtual string FirstName { get; set; }
        public virtual string LastName { get; set; }
        public virtual DateTime BirthDate { get; set; }
        public virtual PersonInfo PersonInfo { get; set; }
        public virtual ICollection<PhoneNumber> PhoneNumbers { get; set; }
        public virtual ICollection<Address> Addresses { get; set; }
    }
}
همان طور که در مباحث مربوط به Lazy loading نیز مشاهده کردید،EF زمانی پروکسی‌های پویا را برای یک کلاس ایجاد می‌کند که یک یا چند خاصیت راهبری آن با virtual علامت گذاری شده باشند.آن پروکسی‌ها که از کلاس مورد نظر، مشتق شده اند، به خواص راهبری virtual امکان می‌دهند تا به صورت lazy لود شوند.پروکسی‌های ردیابی تغییر نیز به همان شکل در زمان اجرا ایجاد می‌شوند، با این تفاوت که این پروکسی ها، امکانات بیشتری دارند.
با این که احتیاجات رسیدن به پروکسی‌های ردیابی تغییر خیلی ساده هستند، اما ساده‌تر از آن ها، فراموش کردن یکی از آن هاست.حتی از این هم ساده‌تر می‌شود که در آینده تغییری در آن کلاس‌ها ایجاد کنید و ناخواسته یکی از آن قوانین را نقض کنید.به این خاطر، فکر خوبیست که یک آزمون واحد نیز اضافه کنیم تا مطمئن شویم که EF توانسته، پروکسی ردیابی تغییر را ایجاد کند یا نه.
در مثال زیر یک متد نوشته شده که این مورد را مورد آزمایش قرار می‌دهد. همچنین فراموش نکنید که فضای نام System.Data.Object.DataClasses را به using‌های خود اضافه کنید.
 
        private static void TestForChangeTrackingProxy()
        {
            using (var context = new PhoneBookDbContext())
            {
                var person = context.Persons.First();
                var isProxy = person is IEntityWithChangeTracker;
                Console.WriteLine("person is a proxy: {0}", isProxy);
            }
        }
زمانی که EF ، پروکسی پویا برای ردیابی تغییر ایجاد می‌کند، اینترفیس IentityWithChangeTrackerرا پیاده سازی  خواهد کرد.متد تست در مثال بالا، نمونه ای از Person را با دریافت آن از دیتابیس ایجاد می‌کند و سپس آن را با اینترفیس ذکر شده چک می‌کند تا مطمئن شود که Person ، توسط پروکسی ردیابی تغییر احاطه شده است. این نکته را نیز به یاد داشته باشید که چک کردن این که EF ، کلاس پروکسی ای که از کلاس ما مشتق شده است ایجاد کرده است یا نه،کفایت نمی‌کند، چرا که پروکسی‌های Lazy Loading نیز چنین کاری انجام می‌دهند. در حقیقت آن چیزی که سبب می‌شود EF به تغییرات صورت گرفته به صورت بلادرنگ گوش دهد،حضور IEntityWithChangeTracker است.
اکنون متد ManualDetectChanges را که کمی بالاتر بررسی کرده ایم را در نظر بگیرید و کد context.ChangeTracker.DetectChanges آن را حذف کنید و بار دیگر آن را فرا بخوانید  و نتیجه را مشاهده کنید:
 
Before DetectChanges: Modified
After DetectChanges: Modified
این دفعه،EF از تغییرات صورت گرفته آگاه است،حال چه DetectChanges فراخوانده شود یا نشود.

اکنون متد DetectRelationshipChanges را ویرایش کرده و برنامه را اجرا کنید:

Before DetectChanges: will
After DetectChanges: will
این بار می‌بینیم که EF، تغییر رابطه را تشخیص داده و اصلاح رابطه را بدون فراخوانی DetectChanges انجام داده است.

نکته: زمانی که شما از پروکسی‌های ردیابی تغییر استفاده می‌کنید،احتیاجی به غیرفعال کردن تشخیص خودکار تغییرات نیست. DetectChanges برای همه اشیایی که تغییرات را به صورت بلادرنگ گزارش می‌دهند،فرآیند تشخیص تغییرات را انجام نمی‌دهد. بنابراین فعال سازی پروکسی‌های ردیابی تغییر،برای رسیدن به مزایای کارایی بالا در هنگام عدم استفاده از DetectChanges کافی است. در حقیقت زمانی که EF، یک پروکسی ردیابی پیدا می‌کند، از مقادیر خاصیت ها، عکس فوری نمی‌گیرد. همچنین DetectChanges این را نیز می‌داند که نباید تغییرات موجودیت هایی که عکسی از مقادیر اصلی آنها ندارد را اسکن کند.

تذکر: اگر شما موجودیت هایی داشته باشید که شامل انواع پیچیده(Complex Types) می‌شوند،EF هنوز هم از ردیابی تغییر عکس فوری، برای خواص موجود در نوع پیچیده استفاده می‌کند، و از این جهت لازم است کهEF، برای نمونه‌ی نوع پیچیده، پروکسی ایجاد نمی‌کند.شما هنوز هم، تشخیص خودکار تغییرات خواصی که مستقیما درون آن موجودیت(Entity) تعریف شده اند را دارید، ولی تغییرات رخ داده درون نوع پیچیده، فقط از طریق DetectChanges قابل تشخیص است.

چگونگی اطمینان از اینکه نمونه‌های جدید ، پروکسی‌ها را دریافت خواهند کرد

EF به صورت خودکار برای نتایج حاصل از کوئری هایی که شما اجرا می‌کنید، پروکسی‌ها را ایجاد می‌کند. با این حال اگر شما فقط از سازنده‌ی کلاس POCO خود برای ایجاد نمونه‌ی جدید استفاده کنید،دیگر پروکسی‌ها ایجاد نخواهند شد.بدین منظور برای دریافت پروکسی ها، شما باید از متد DbSet.Create برای دریافت نمونه‌های جدید آن موجودیت استفاده کنید.
نکته: اگر شما، پروکسی‌های ردیابی تغییر را برای موجودیتی از مدلتان فعال کرده باشید،هنوز هم می‌توانید،نمونه‌های فاقد پروکسی آن موجودیت را ایجاد و بیافزایید.خوشبختانه EF با موجودیت‌های پروکسی و غیر پروکسی در همان مجموعه(set) کار می‌کند.شما باید آگاه باشید که ردیابی خودکار تغییرات و یا اصلاح رابطه، برای نمونه هایی که پروکسی هایی ردیابی تغییر نیستند، قابل استفاده نیستند.داشتن مخلوطی از نمونه‌های پروکسی و غیر پروکسی در همان مجموعه، می‌تواند گیج کننده باشد.بنابر این عموما توصیه می‌شود که برای ایجاد نمونه‌های جدید از DbSet.Create استفاده کنید، تا همه‌ی موجودیت‌های موجود در مجموعه، پروکسی‌های ردیابی تغییر باشند.
متد CreateNewProxies را به برنامه‌ی خود اضافه کرده و آن را اجرا کنید.

        private static void CreateNewProxies()
        {
            using (var context = new PhoneBookDbContext())
            {
                var phoneNumber = new PhoneNumber { Number = "987" };

                var davidPersonProxy = context.Persons.Create();
                davidPersonProxy.FirstName = "david";
                davidPersonProxy.PhoneNumbers.Add(phoneNumber);

                Console.WriteLine(phoneNumber.Person.FirstName);
            }
        }

خروجی مثال فوق david خواهد بود.همان طور که می‌بینید با استفاده از context.Persons.Create، نمونه‌ی ساخته شده، دیگر شی POCO نیست، بلکه davidPersonProxy، از جنس پروکسی ردیابی تغییر است و تغییرات آن به طور خودکار ردیابی شده و رابطه آن نیز به صورت خودکار اصلاح  می‌شود.در اینجا نیز با افزودن phoneNumber به شماره تلفن‌های davidPersonProxy، به طور خودکار رابطه‌ی بین phoneNumber و davidPersonPeroxy بر قرار شده است. همان طور که می‌دانید این عملیات بدون استفاده از پروکسی‌های ردیابی تغییرات امکان پذیر نیست و موجب بروز خطا می‌شود.


ایجاد نمونه‌های پروکسی برای انواع مشتق شده

اورلود جنریک دیگری برای DbSet.Create وجود  دارد که برای نمونه سازی کلاس‌های مشتق شده در مجموعه ما استفاده می‌شود .برای مثال، فراخوانی Create بر روی مجموعه‌ی Persons ،نمونه ای از کلاس Person را  بر می‌گرداند.ولی ممکن است کلاس هایی در مجموعه‌ی Persons وجود داشته باشند، که از آن مشتق شده باشند، مانند Student. برای دریافت نمونه‌ی پروکسی Student، از اورلود جنریک Create استفاده می‌کنیم.

var newStudent = context.Persons.Create<Student>();
واکشی موجودیت‌ها بدون ردیابی تغییرات

تا به این جای کار باید متوجه شده باشید که ردیابی تغییرات، فرآیندی ساده و بدیهی نیست و مقداری سربار در کار است. در بعضی از بخش‌های برنامه تان، احتمالا داده‌ها را به صورت فقط خواندنی در اختیار کاربران قرار می‌دهید و چون اطلاعات هیچ وقت تغییر نمی‌کنند، شما می‌خواهید که سربار ناشی از ردیابی تغییرات را حذف کنید.
خوشبختانه EF شامل متد AsNoTracking است که می‌توان از آن برای اجرای کوئری‌های بدون ردیابی استفاده کرد.یک کوئری بدون ردیابی، یک کوئری ساده هست که نتایج آن توسط context برای تشخیص تغییرات ردیابی نخواهد شد.

متد PrintPersonsWithoutChangeTracking را به برنامه اضافه کنید و آن را اجرا کنید: 
 
        private static void PrintPersonsWithoutChangeTracking()
        {
            using (var context = new PhoneBookDbContext())
            {
                var persons = context.Persons.AsNoTracking().ToList();

                foreach (var person in persons)
                {
                    Console.WriteLine(person.FirstName);
                }

            }
        }
در مثال بالا از متد AsNoTracking برای گرفتن کوئری فاقد ردیابی استفاده کردیم تا محتویات مجموعه Persons را دریافت کنیم. در نهایت با یک حلقه‌ی foreach، نتایج را بر روی کنسول به نمایش در آوردیم.به دلیل اینکه، این یک کوئری بدون ردیابی هست، context دیگر تغییراتی که روی Persons رخ می‌دهد را ردیابی نمی‌کند.در نتیجه اگر شما یکی از خواص یکی از Persons را تغییردهید و SaveChanges را صدا بزنید، تغییرات به دیتابیس ارسال نمی‌شوند.

نکته: واکشی داده‌ها بدون ردیابی تغییرات،معمولا وقتی باعث افزایش قابل توجه کارایی می‌شود که بخواهیم تعداد خیلی زیادی داده  را به صورت فقط خواندنی نمایش دهیم. اگر برنامه‌ی شما داده ای را تغییر می‌دهد و می‌خواهد آن را ذخیره کند، باید از AsNoTracking استفاده نکنید.

AsNoTracking یک متد الحاقی است، که در <IQueryable<T تعریف شده است، در نتیجه شما می‌توانید از آن، در کوئری‌های LINQ نیز استفاده کنید. شما می‌توانید از AsNoTracking، در انتهای DbSet ،در خط from کوئری استفاده کنید.

var query = from p in context.Persons.AsNoTracking()
                            where p.FirstName == "joe"
                            select p;

شما همچنین از AsNoTracking می‌توانید برای تبدیل یک کوئری LINQ موجود، به یک کوئری فاقد ردیابی استفاده کنید. این نکته را به یاد داشته باشید که فقط AsNoTracking بر روی کوئری، فرانخوانده شده است، بلکه متغیر query را با نتیجه‌ی حاصل از فراخوانی AsNoTracking بازنویسی(override)  کرده است و این، از این جهت لازم است که AsNoTracking ،تغییری در کوئری ای که بر روی آن فراخوانده شده نمی‌دهد، بلکه یک کوئری جدید بر می‌گرداند. 

 
                var query = from p in context.Persons
                            where p.FirstName == "joe"
                            select p;
                query = query.AsNoTracking();
نکته: به دلیل اینکه AsNoTracking یک متد الحاقی است، شما احتیاج به افزودن فضای نام System.Data.Entity به فضا‌های نام خود دارید.

منبع: ترجمه ای آزاد از کتاب Programming Entity Framework: DbContext
مطالب
دستکاری کردن عملیات Sort در SQL Server
گاهی اوقات لازم می‌باشد، در زمان Sort نمودن یک ستون، تمایل داشته باشیم Range خاصی از مقادیر آن ستون در ابتدا قرار گیرد، و عملیات Sort پس از آن Range، اعمال گردد. برای انجام چنین کاری می‌توانیداز روش زیر استفاده نمایید:
برای درک مطلب مثالی می‌زنیم:
در ابتدا Script زیر را اجرا نمایید، که شامل یک جدول و درج چند رکورد در آن می‌باشد:
Create Table TestSort
(ID  int identity(1,1),
Name nvarchar(30),
Color nvarchar(15)
)
درج رکورد:
Insert into TestSort (Name,color)
Values  ('Adjustable Race',null)
       ,('Bearing Ball',null)
       ,('Headset Ball Bearings',null)
       ,('LL Crankarm','Black')
       ,('ML Crankarm','Black')
       ,('Chainring','Black')
       ,('Front Derailleur Cage','Silver')
       ,('Front Derailleur Linkage','Silver')
       ,('Lock Ring','Silver')
       ,('HL Road Frame - Red, 62','Red')
       ,('HL Road Frame - Red, 48','Red')
       ,('LL Road Frame - Red, 44','Red')
       ,('Full-Finger Gloves, M','RED')
       ,('Road-550-W Yellow, 38','Yellow')
       ,('Road-550-W Yellow, 40','Yellow')
       ,('Road-550-W Yellow, 42','Yellow')
       ,('Classic Vest, S','Blue')
       ,('Classic Vest, M','Blue')
       ,('Classic Vest, L','Blue')
در جدول TestSort ستونی به نام Color داریم، که نام رنگها در آن درج شده است، رنگهایی همچون  Black ، Silver،Blue،Yellow و Red
درابتدا روی ستون Color بصورت نرمال Sort صعودی انجام می‌دهیم:
Select t.ID,t.Name,t.Color from TestSort as t order by t.Color
خروجی:

مطابق شکل،زمانی که Sort بصورت صعودی است، رکوردهایی را که ستون Color آنها دارای مقدار Null می‌باشند، در ابتدای جدول قرار گرفته اند.
در ادامه می‌خواهیم، عملیات Sort ی را روی ستون Color انجام دهیم، بطوریکه تمامی رکوردهایی که مقدار ستون Color شان Red است، در ابتدای جدول قرار گیرد، و پس از آن عملیات سورت روی رنگهای دیگر اعمال شود.
برای انجام چنین کاری کافیست Script زیر را اجرا نمایید:
Select t.ID,t.Name,t.Color from TestSort as t 
Where t.color is not null
order by  Case t.Color
When 'Red' Then Null
Else t.color 
End;
خروجی:

چطور اینکار انجام شد:
اگر بهScript ذکر شده دقت نمایید، در قسمت Order by اشاره کردیم، تمام مقادیر Red در ستون Color به Null تغییر کنند، بنابراین SQL Server، در ابتدا مقادیر Red را یافته آنها را به Null تغییر و سپس عملیات سورت را انجام می‌دهد،
برای درک بیشتر عملیاتی را که SQL Server پشت صحنه انجام می‌دهد با Script  زیر قابل شبیه سازی میباشد:
Select * into Simulation from 
(Select t.ID,t.Name,t.Color,
Case t.Color
When 'Red' Then Null
Else t.color 
End RedNull
from TestSort as t 
Where t.color is not null) A
سپس روی ستون RedNull از جدول Simulation سورت انجام می‌دهیم:
Select * from Simulation order by Rednull
خروجی:

مطابق شکل،پشت صحنه SQL Server چنین کاری را انجام می‌دهد، و در زمان نمایش ستون RedNull پنهان یا حذف می‌گردد، و ستون Color، Name و ID نمایش داده می‌شود.
امیدوارم مفید واقع شده باشد.
مطالب
استفاده از Flashback Table در Oracle
Oracle امکان جالبی دارد، به نام Flashback Table، که به کمک آن می‌توان یک جدول Drop شده را برگرداند. برای استفاده از قابلیت Flashback در دیتابیس Oracle، می‌بایست قابلیت Recyclebin در Oracle را فعال نمود. با فعال نمودن Recyclebin در Oracle ، جداول حذف شده بصورت منطقی حذف می‌گردند، تا در صورت نیاز بوسیله دستور Flashback Table، جداول مورد نظر برگردانده شود.
قبل از استفاده از دستور Flashback Table، ابتدا Recyclebin را فعال می‌نماییم:
ALTER SYSTEM SET recyclebin = ON;
در Script بالا، Recyclebin در سطح سیستم فعال شده است،اگر شما مایل باشید می‌توانید،Recyclebin را در سطح Session فعال نمایید.
ALTER SESSION SET recyclebin = ON;
برای غیر فعال نمودن Recyclebin نیز بصورت زیر عمل می‌نماییم:
ALTER SYSTEM SET recyclebin = OFF;
یا
ALTER SESSION SET recyclebin = OFF;
حال با فرض اینکه Recyclebin را فعال نموده ایم، با یک مثال نحوه عملکرد Flashback Table را نمایش می‌دهیم:
ابتدا یک جدول ایجاد نمایید، مطابق Script زیر:
Create Table Test
(ID int,
FirstName varchar(255),
LastName Varchar(255))
مقادیری را درون جدول فوق درج نمایید، سپس بوسیله دستور زیر جدول فوق را حذف نمایید:
Drop table Test
در ادامه برای برگرداندن جدول فوق می‌توانیم از دستور Falshback Table مطابق Script زیر عمل نماییم:
FLASHBACK TABLE Test TO BEFORE DROP;
برای مشاهده محتویات Recyclebin نیز می‌توانید از Script‌های زیر استفاده نمایید:
Select * From  recyclebin;
یا

SELECT * FROM USER_RECYCLEBIN;
این قابلیت چندی پیش در محیط اجرایی مشتری به کمکم آمد، بطوریکه من تقریبا اطلاعات 8 میلیون نفر را در یک جدول درج نموده بودم و پس از اعمال Migration ، آن را حذف کردم، صبح روز بعد متوجه شدم،که عملیات انجام شده اشتباه بوده و من می‌بایست دوباره جداول اصلی را بازسازی می‌کردم،از طرفی امکان Restore نمودن یا درج دوباره 8 میلیون رکورد به دیتابیس وجود نداشت، چون زمان زیادی می‌برد، و از طرفی هم نمی‌بایست بار زیادی روی دیتابیس می‌گذاشتم، در این زمان بود که دستور Flashback Table ، دیتابیس Oracle به کمکم آمد و در کوتاهترین زمان مشکل بوجود آمده را برطرف کردم.
البته من چنین امکانی را در SQL Server ندیدم، خوشنود می‌شوم، اگر کسی تجربه ای مشابه در SQL Server داشت، به اشتراک بگذارد.
موفق باشید.


مطالب
آشنایی با SQL Server Common Table Expressions - CTE
مقدمه

تکنولوژی CTE از نسخه SQL Server 2005 رسمیت یافته است و شامل یک result set موقتی[1] است که دارای نام مشخص بوده و می­توان از آن در دستورات SELECT, INSERT, UPDATE, DELETEاستفاده کرد. همچنین از CTE می‌توان در دستور CREATE VIEW و دستور SELECT مربوط به آن استفاده کرد. در نسخه SQL Server 2008 نیز امکان استفاده از CTE در دستور MERGE فراهم شده است.
در SQL Serverاز دو نوع CTE بازگشتی[2] و غیر بازگشتی[3] پشتیبانی می­شود. در این مقاله سعی شده است نحوه تعریف و استفاده از هر دو نوع آن آموزش داده شود.

انواع روش­های ایجاد جداول موقت
برای استفاده از جداول موقتی در سرور اسکیوال، سه راه زیر وجود دارد.

روش اول: استفاده از دستوری مانند زیر است که سبب ایجاد جدول موقتی در بانک سیستمی tempdb می­شود. زمانی­که شما ارتباط خود را با سرور SQL قطع می­کنید به صورت اتوماتیک جداول موقت شما از بانک tempdb حذف می­شوند. این روش در برنامه نویسی پیشنهاد نمی­شود و فقط در کارهای موقتی و آزمایشی مناسب است.
 SELECT * INTO #temptable FROM [Northwind].[dbo].[Products]
UPDATE #temptable SET [UnitPrice] = [UnitPrice] + 10

روش دوم: استفاده از متغیر نوع Table است، که نمونه آن در مثال زیر دیده می­شود. زمانیکه از محدوده[4] جاری کد[5] خودتان خارج شوید آن متغیر نیز از حافظه پاک میشود. از این روش، عموما در کدهای Stored Procedureها و UserDefined Functionها استفاده می­شود.
DECLARE @tempTable TABLE
(
   [ProductID] [int] NOT NULL,
   [ProductName] [nvarchar](40) NOT NULL,
   [UnitPrice] [money] NULL
)
 
INSERT INTO @tempTable
SELECT
   [ProductID],
   [ProductName],
   [UnitPrice]
FROM [Northwind].[dbo].[Products]
 
UPDATE @temptable SET [UnitPrice] = [UnitPrice] + 10

روش سوم: استفاده از CTE است که مزیت­هایی نسبت به دو روش قبلی دارد و در بخش بعدی به نحوه تعریف و استفاده از آن خواهیم پرداخت.


کار با CTE
ساده ­ترین شکل تعریف یک CTE به صورت زیر است:
WITH yourName [(Column1, Column2, ...)]
AS
(
   your query
)
با کلمه WITH شروع شده و یک نام اختیاری به آن داده می­شود. سپس فهرست فیلدهای جدول موقت را درون زوج پرانتز، مشخص می­کنید. تعریف این فیلدها اختیاری است و اگر حذف شود، فیلدهای جدول موقت، مانند فیلدهای کوئری مربوطه خواهد بود.
your query شامل دستوری است که سبب تولید یک result set می­شود. قواعد تعریف این کوئری مشابه قواعد تعریف کوئری است که در دستور CREATE VIEW کاربرد دارد.


شکل کلی دستور

همانطور که از این تصویر مشخص است می­توان چندین بلوک از این ساختار را به دنبال هم تعریف نمود که با کاما از هم جدا می­شوند. در واقع یکی از کاربردهای CTE ایجاد قطعات کوچکی است که امکان استفاده مجدد را به شما داده و می­تواند سبب خواناتر شدن کدهای پیچیده شود.
یکی دیگر از کاربردهای CTE آنجایی است که شما نمی‌خواهید یک شی Viewی عمومی تعریف کنید و در عین حال می‌خواهید از مزایای Viewها بهرمند شوید.
و همچنین از کاربردهای دیگر CTE تعریف جدول موقت و استفاده از آن جدول به صورت همزمان در یک دستور است.

بعد از آنکه CTE یا CTEهای خودتان را تعریف کردید آنگاه می­توانید مانند جداول معمولی از آنها استفاده کنید. استفاده از این جداول توسط دستوری خواهد بود که دقیقا بعد از تعریف CTE نوشته می­شود.


ایجاد یک CTE غیر بازگشتی[6]

مثال اول، یک CTE غیر بازگشتی ساده را نشان میدهد.
WITH temp
AS
(
    SELECT
         [ProductName],
         [UnitPrice]
    FROM [Northwind].[dbo].[Products]
)
SELECT * FROM temp
ORDER BY [UnitPrice] DESC

مثال دوم نمونه‌­ای دیگر از یک CTE غیر بازگشتی است.
WITH orderSales (OrderID, Total)
AS
(
   SELECT
      [OrderID],
     SUM([UnitPrice]*[Quantity]) AS Total
   FROM [Northwind].[dbo].[Order Details]
   GROUP BY [OrderID]
)
SELECT
   O.[ShipCountry],
   SUM(OS.[Total]) AS TotalSales
FROM [Northwind].[dbo].[Orders] AS O INNER JOIN [orderSales] AS OS
ON O.[OrderID] = OS.[OrderID]
GROUP BY O.[ShipCountry]
ORDER BY TotalSales DESC
هدف این کوئری، محاسبه کل میزان فروش کالاها، به ازای هر کشور می­باشد. ابتدا از جدول Order Details مجموع فروش هر سفارش محاسبه شده و نتیجه آن در یک CTE به نام orderSales قرار می­گیرد و از JOIN این جدول موقت با جدول Orders محاسبه نهایی انجام شده و نتیجه­‌ای مانند این تصویر حاصل می‌شود.

نتیجه خروجی

 مثال سوم استفاده از دو CTE را به صورت همزمان نشان می­دهد:
WITH customerList
AS
(
   SELECT
      [CustomerID],
      [ContactName]
   FROM [Northwind].[dbo].[Customers]
   WHERE [Country] ='UK'
)
,orderList
AS
(
   SELECT
      [CustomerID],
      [OrderDate]
   FROM [Northwind].[dbo].[Orders]
   WHERE YEAR([OrderDate])< 2000
)
SELECT
   cl.[ContactName],
   YEAR(ol.[OrderDate]) AS SalesYear
FROM customerList AS cl JOIN orderList AS ol
ON cl.[CustomerID] = ol.[CustomerID]

مثال چهارم استفاده مجدد از یک CTE را نشان می­دهد. فرض کنید جدولی به نام digits داریم که فقط یک فیلد digit دارد و دارای 10 رکورد با مقادیر 0 تا 9 است. مانند تصویر زیر

نتیجه خروجی

حال می­خواهیم از طریق CROSS JOIN اعداد 1 تا 100 را با استفاده از مقادیر این جدول تولید کنیم. کد زیر آنرا نشان می­دهد:
WITH digitList
AS
(
   SELECT [digit] from [digits]
)
SELECT
   a.[digit] * 10 + b.[digit] + 1 AS [Digit]
FROM [digitList] AS a CROSSJOIN [digitList] AS b
در این کد یک CTE تعریف شده و دو بار مورد استفاده قرار گرفته است. مثلا اگر بخواهید اعداد 1 تا 1000 را تولید کنید می­توانید سه بار از آن استفاده کنید. حاصل این دستور result setی مانند زیر است.

نتیجه

نتیجه

حتی می­توان از یک CTE در کوئری CTE بعدی مانند کد زیر استفاده کرد.
WITH CTE_1 AS
(
   ....
),
CTE_2 AS
(
   SELECT  ... FROM CTE_1 JOIN ...
)
SELECT   *
FROM FOO
LEFTJOIN  CTE_1
LEFTJOIN  CTE_2


ایجاد یک CTE بازگشتی[7]

از CTE بازگشتی برای پیمایش جداولی استفاده می­شود که رکوردهای آن دارای رابطه سلسله مراتبی یا درختی است. نمونه این جداول، جدول کارمندان است که مدیر هر کارمند نیز مشخص شده است یا جدولی که ساختار سازمانی را نشان می­دهد یا جدولی که موضوعات درختی را در خود ذخیره کرده است. یکی از مزایای استفاده از CTE بازگشتی، سرعت کار آن در مقایسه با روش­های پردازشی دیگر است.

ساختار کلی یک دستور CTE بازگشتی به صورت زیر است.
WITH cteName AS
(
   query1
   UNION ALL
   query2
)
در بدنه CTE حداقل دو عضو[8] (کوئری) وجود دارد که بایستی با یکی از عبارت­های زیر به هم متصل شوند.
UNION
UNION ALL
INTERSECT
EXCEPT

query1 شامل دستوری است که اولین سری از رکوردهای result set نهایی را تولید می­کند. اصطلاحا به این کوئری anchor memberمی­گویند.
بعد از دستور query1، حتما بایستی از UNION ALL و امثال آنها استفاده شود.
سپس query2 ذکر می­شود. اصطلاحا به این کوئری recursive member گفته می­شود. این کوئری شامل دستوری است که سطوح بعدی درخت را تولید خواهد کرد. این کوئری دارای شرایط زیر است.
  •   حتما بایستی به CTE که همان cteName است اشاره کرده و در جایی از آن استفاده شده باشد. به عبارت دیگر از رکوردهای موجود در جدول موقت استفاده کند تا بتواند رکوردهای بعدی را تشخیص دهد.
  • حتما بایستی مطمئن شوید که شرایط کافی برای پایان حلقه پیمایش رکوردها را داشته باشد در غیر این صورت سبب تولید حلقه بی پایان[9] خواهد شد.

بدنه CTE می­تواند حاوی چندین anchor member و چندین recursive member باشد ولی فقط recursive memberها هستند که به CTE اشاره می­کنند.

برای آنکه نکات فوق روشن شود به مثال­های زیر توجه کنید.
فرض کنید جدولی از کارمندان و مدیران آنها داریم که به صورت زیر تعریف و مقداردهی اولیه شده است.
IFOBJECT_ID('Employees','U')ISNOTNULL
DROPTABLE dbo.Employees
GO 

CREATETABLE dbo.Employees
(
  EmployeeID intNOTNULLPRIMARYKEY,
  FirstName varchar(50)NOTNULL,
  LastName varchar(50)NOTNULL,
  ManagerID intNULL
)

GO


INSERTINTO Employees VALUES (101,'Alireza','Nematollahi',NULL)
INSERTINTO Employees VALUES (102,'Ahmad','Mofarrahzadeh', 101)
INSERTINTO Employees VALUES (103,'Mohammad','BozorgGhommi', 102)
INSERTINTO Employees VALUES (104,'Masoud','Narimani', 103)
INSERTINTO Employees VALUES (105,'Mohsen','Hashemi', 103)
INSERTINTO Employees VALUES (106,'Aref','Partovi', 102)
INSERTINTO Employees VALUES (107,'Hosain','Mahmoudi', 106)
INSERTINTO Employees VALUES (108,'Naser','Pourali', 106)
INSERTINTO Employees VALUES (109,'Reza','Bagheri', 102)
INSERTINTO Employees VALUES (110,'Abbas','Najafian', 102)

مثال اول: می­خواهیم فهرست کارمندان را به همراه نام مدیر آنها و شماره سطح درخت نمایش دهیم. کوئری زیر نمونه‌ای از یک کوئری بر اساس CTE بازگشتی می­باشد.
WITHcteReports(EmpID, FirstName, LastName, MgrID, EmpLevel)
AS
(
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
FROM Employees
WHERE ManagerID ISNULL
UNIONALL
SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,r.EmpLevel + 1
FROM Employees e INNERJOINcteReports r
ON e.ManagerID = r.EmpID
)
SELECT
FirstName +' '+ LastName AS FullName,
EmpLevel,
(SELECT FirstName +' '+ LastName FROM Employees
WHERE EmployeeID = cteReports.MgrID)AS Manager
FROMcteReports
ORDERBY EmpLevel, MgrID
کوئری اول در بدنه CTE رکورد مدیری را می­دهد که ریشه درخت بوده و بالاسری ندارد و شماره سطح این رکورد را 1 در نظر می­گیرد.
کوئری دوم در بدنه CTE از یک JOIN بین Employees و cteReports استفاده کرده و کارمندان زیر دست هر کارمند قبلی (فرزندان) را بدست آورده و مقدار شماره سطح آنرا به صورت Level+1 تنظیم می­کند.
در نهایت با استفاده از CTE و یک subquery جهت بدست آوردن نام مدیر هر کارمند، نتیجه نهایی تولید می­شود.

مثال دوم: می­خواهیم شناسه یک کارمند را بدهیم و نام او و نام مدیران وی را به عنوان جواب در خروجی بگیریم.
WITHcteReports(EmpID, FirstName, LastName, MgrID, EmpLevel)
AS
(
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
FROM Employees
WHERE EmployeeID = 110
UNIONALL
SELECTe.EmployeeID, e.FirstName, e.LastName, e.ManagerID,r.EmpLevel + 1
FROM Employees e INNERJOINcteReports r
ON e.EmployeeID = r.MgrID
)
SELECT
FirstName +' '+ LastName AS FullName,
EmpLevel
FROMcteReports
ORDERBY EmpLevel
اگر دقت کنید اولین تفاوت در خط اول مشاهده می­شود. در اینجا مشخص می­کند که اولین سری از رکوردها چگونه انتخاب شود. مثلا کارمندی را می­خواهیم که شناسه آن 110 باشد.
دومین تفاوت اصلی این کوئری با مثال قبلی، در قسمت دوم دیده می­شود. شما می‌خواهید مدیر (پدر) کارمندی که در آخرین پردازش در جدول موقت قرار گرفته است را استخراج کنید.

    


[1] a temporary named result set
[2] recursive
[3] nonrecursive
[4] Scope
[5]مثلا محدوده کدهای یک روال یا یک تابع
[6] nonrecursive
[7] recursive
[8] member
[9] Infinite loop
  
مطالب
آشنایی با Column Store Index در SQL Server 2012

 Column Store Index یکی از ویژگیهای جدید SQL Server 2012 می باشد، که کارایی Query های قایل اجرا روی دیتابیس‌های با حجم داده ای بسیار بالا را (که اصطلاحا به آنها Data Warehouse یا انبار داده گویند)، چندین برابر بهبود بخشیده است

 قبل از توضیح در مورد Column Store مختصری در مورد نحوه ذخیره سازی داده‌ها در SQL Server می پردازیم. می‌توان گفت در SQL Server دو روش ذخیره سازی وجود دارد،یکی بصورت ردیفی که اصطلاحا به آن  Row Storeیا  Row-Wise گویند، و دیگری بصورت ستونی که اصطلاحا به آن Column Store گویند

در روش ذخیره سازی Row Store، مقادیر ستونها در یک سطر بصورت متوالی ذخیره می‌شوند، در این روش ذخیره سازی از ساختار B-Tree یا Heap استفاده می‌شود.

یادآوری: در ساختار B-Tree، یک گره Root وجود دارد، و گره بعد از Root گره ای است که آدرس گره راست بعدی و آدرس گره چپ بعدی را در خود نگه می‌دارد.

شکل زیر نمای یک درخت B-Tree می‌باشد:

جهت کسب اطلاعات بیشتر درمورد ساختار B-Tree 

یادآوری: وقتی در یک جدول، ایندکسی از نوع Clustered ایجاد نماییم، SQL Server، در ابتدا یک کپی از جدول ایجاد و داده‌های جدول را از نو مرتب می‌نماید، و ساختار صفحه ریشه و دیگر صفحات را ایجاد می‌کند و سپس جدول اصلی را حذف می‌نماید. به جدولی که Clustered Index ندارد، اصطلاحا Heap گویند.

  برخلاف ذخیره سازی Row Store، در ذخیره سازی Column Store، داده‌ها بصورت ستونی ذخیره می‌شوند،در این روش داده ها، فشرده سازی می‌شوند و اینکار باعث می‌شود،در زمان درخواست یک Query، نیاز به Disk I/o به حداقل برسد، در نتیجه، زمان و سرعت پاسخگویی به پرس و جو‌ها بسیار افزایش می‌یابد.

شکل زیر نحوه ذخیره سازی داده ها،بصورت Row Store را نمایش می‌دهد:

  شکل بالا ذخیره سازی داده ها، در ساختار B-Tree یا Heap را نمایش می‌دهد، در شکل فوق یک جدول چهار ستونی با N سطر (Row) در نظر گرفته شده است.بطوریکه ستونهای هر Row بطور متوالی در یک صفحه (Page) یکسان ذخیره می‌شوند.

 شکل زیر نحوه ذخیره سازی داده ها،بصورت Column Store را نمایش می‌دهد:

  مطابق شکل،ستونهای مربوط به هر Row،همگی در یک صفحه (Page) یکسان ذخیره شده اند. به عنوان مثال ستون C1 که مربوط به سطر اول (Row1) می‌باشد، با ستون C1 که مربوط به سطر دوم (Row2) می‌باشد، در یک ستون و در یک صفحه (Page1) ذخیره شده اند، و الی آخر ...

   سئوال: یکبار دیگر به هردو شکل با دقت نگاهی بیاندازید، عمده تفاوت آنها در چیست؟

   جواب: درست حدس زدید، تفاوت بارز بین دو روش Column Store و Row Store در نحوه ذخیره سازی داده‌ها می‌باشد. بطور مثال، فرض کنید،در روش ذخیره سازی Row Store، به دنبال مقادیری از ستون C2 می‌باشید، SQL Server می‌بایست کل رکورد‌های جدول (منظور همه Row‌ها در همه Page ها)را Scan نماید، تا مقادیر مربوط به ستون C2 را بدست آورد.درحالیکه در روش ذخیره سازی Column Store، جهت یافتن مقادیر ستون C2، نیازی به Scan نمودن کل جدول نیست،بلکه SQL Server فقط به Scan نمودن ستون دوم (C2) یا Page2 بسنده می‌نماید.همین امر باعث افزایش چندین برابری، زمان پاسخگویی به هر Query می‌شود.

  سئوال: در روش ذخیره سازی Column Store، چگونه مصرف حافظه بهینه می‌شود؟

  جواب: واضح است،  که در روش SQL Server، Row Store مجبور است، برای بدست آوردن داده‌های مورد نظرتان،کل اطلاعات جدول را وارد حافظه نماید(اطلاعات اضافه ای که به هیچ وجه بدرد، نتیجه پرس و جوی شما نمی‌خورد)، و شروع به Scan داده‌های مد نظر شما می‌نماید.بطوریکه در روش SQL Server، Column Store، فقط ستون داده‌های مورد پرس و جو را در حافظه قرار می‌دهد.(در واقع فقط داده هایی را در حافظه قرار می‌دهد، که شما به آن نیاز دارید)،بنابراین،طبیعی است که در روش Column Store مقدار حافظه کمتری نسبت به روش Row Store در هنگام اجرای Query استفاده می‌شود. به عبارت دیگر می‌توان گفت که در روش Column Store به دلیل، به حداقل رساندن استفاده از Disk I/o سرعت و زمان پاسخگویی به پرس و جو‌ها چندین برابر می‌شود.

  برای درک بیشتر Row Store و Column Store مثالی می‌زنیم:

   فرض کنید،قصد بدست آوردن ستونهای C1 و C2 از جدول A را داریم، بنابراین خواهیم داشت:

Select C1, C2 from A

روش Row Store:

    در این روش همه صفحات دیسک (مربوط به جدول A) درون حافظه قرار داده می‌شود، یعنی علاوه بر ستونهای C1 و C2، اطلاعات مربوط به ستونهای C3 و C4 نیز درون حافظه قرار می‌گیرد،بطوریکه مقادیر ستونهای C3 و C4 به هیچ وجه مورد قبول ما نیست، و در خروجی پرس و جوی ما تاثیری ندارد، و فقط بی جهت حافظه اشغال می‌نماید.

روش Column Store:

  در این روش فقط صفحات مروبط به ستون C1 و C2 در حافظه قرار می‌گیرد.(منظور Page1 و Page2 می‌باشد) بنابراین فقط اطلاعات مورد نیاز در خروجی، در حافظه قرار می‌گیرد.

  •  از دیگر مزایای استفاده از روش Column Store، فشرده سازی داده می‌باشد،برای درک بیشتر توضیح می‌دهم:
      همانطور که در اوایل مطلبم به عرض رساندم، در روش Row Store ، داده‌ها در یک سطر و در یک Page ذخیره می‌شوند، بنابراین امکان وجود داده‌های تکراری در یک سطر به حداقل می‌رسد، چرا که، اگر فرض کنیم چهار ستون  به نام‌های ID،FirstName،LastName و City، داشته باشیم،در آن صورت بطور حتم،در یک سطر، داده تکراری وجود نخواهد داشت، اما ممکن است در تعداد سطرهای زیاد داده‌های تکراری مانند Firstname یا City و غیرو بوجود بیاید، این موضوع را بیان کردم، چون می‌خواستم عنوان کنم،بسیاری از الگوریتم‌های فشرده سازی از الگوی تکراری بودن داده، جهت فشرده سازی داده‌ها استفاده می‌کنند، به همین جهت فشرده سازی در روش Row Store به حداقل می‌رسد و فضای اشغال شده در حافظه دراین روش بسیار زیاد خواهد بود. اما در روش Column Store ، امکان تکراری بودن مقادیر یک ستون بسیار زیاد  است، بطور مثال ممکن است تعداد افرادی را که نام شهر  آنها "تهران" باشد مثلا 20 بار تکرار شده باشد، و چون در روش Column Store، ستون‌ها در یک Page ذخیره می‌شوند، بنابراین امکان استفاده از الگوریتمهای فشرده سازی در این روش بسیار بالا می‌باشد، در نتیجه مقدار فضایی را که در حافظه یا دیسک سخت توسط این روش اشغال می‌شود، بسیار کمتر از روش Row Store است.

چه موقع می‌توانیم از Column Store استفاده نماییم:

   در تعریف Column Store گفته بودم، روش فوق، جهت بهبود بخشیدن به زمان و سرعت پاسخگویی به Query‌های اجرا شده روی دیتابیس‌های با حجم داده ای بسیار بالا(Data Warehouse ) می‌باشد، به بیان ساده‌تر Column Store را روی دیتابیس‌های offline یا دیتابیسهایی که صرفا جهت گزارش گیری مورد استفاده قرار می‌گیرند، تنظیم می‌نمایند.در واقع با تنظیم Column Store Index روی Database‌های بزرگ مانند Database‌های بانک‌ها که حجم داده ای میلیونی در جداول آنها وجود دارد، سرعت پاسخگویی Query ها، چندین برابر افزایش می‌یابد.

  •      در یک جدول می‌توانید، هم Column Store Index داشته باشید و هم یک Row Store Index (منظور یک  Clustered Index می باشد)
  • Syntax برای ایجاد  Column Store Index به شرح ذیل می‌باشد:
CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name 
    ON <object> ( column  [ ,...n ] )
    [ WITH ( <column_index_option> [ ,...n ] ) ]
    [ ON {
           { partition_scheme_name ( column_name ) } 
           | filegroup_name 
           | "default" 
         }
    ]
[ ; ]

<object> ::=
{
    [database_name. [schema_name ] . | schema_name . ]
     table_name
{

<column_index_option> ::=
{
      DROP_EXISTING = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
 }
  • یک Column Store Index می‌بایست از نوع NONCLUSTERED باشد.
مثال از یک Column Store Index :
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_MyFirstName_ColumnStore]
ON [Test]
(Firstname)
در قطعه کد بالا، یک Column Store Index به نام Ix_MyFirstname_ColumnStore روی فیلد Firstname از جدول Test ایجاد شده است.
محدودیت‌های استفاده از Column Store Index به اختصار به شرح ذیل می‌باشد:
  • زمانی که در یک جدول، یک Column Store Index ایجاد نماییم، جدول ما  در حالت Read-only قرار می‌گیرد، بطوریکه از آن پس  اختیار Delete،Update و Insert روی جدول فوق را نخواهیم داشت. برای اینکه بتوانید عملیات Insert، Update یا Delete را انجام دهید، میبایست Column Store Index جدول مربوطه را Disable نمایید، و برای فعال نمودن Column Store Index، می‌بایست آن را Rebuild نمایید، با کلیک راست روی ایندکس ایجاد شده در SQL Server2012 موارد Disable و Rebuild قابل مشاهده می‌باشد.

یا بوسیله Script‌های زیر می‌توانید، عملیات Disable یا Rebuild را روی Column Store Index انجام دهید:
ALTER INDEX [IX_MyFirstName_ColumnStore] ON [Test] DISABLE

ALTER INDEX [IX_MyFirstName_ColumnStore] ON [Test] Rebuild
  • بیشتر از یک Column Store Index نمی‌توانید روی یک جدول ایجاد نمایید.
  • در صورتی که تمایل داشته باشید بوسیله Alter ، نوع فیلدی (Type)، را که Column Store Index روی آنها اعمال گردیده است، تغییر دهید، در ابتدا می‌بایست Column Store Index، خود را Drop یا حذف نمایید، سپس عملیات Alter را اعمال کنید، در غیر اینصورت با خطای SQL Server مواجه می‌شوید.
  • یک Column Store Index می‌تواند روی 1024 ستون در یک جدول اعمال گردد.
  • یک Column Store Index  نمی توانند، Unique باشد و نمی‌توان از آن به عنوان Primary Key یا Foreign Key استفاده نمود.
یاد آوری: با توجه به مزایای استفاده از Column Store Index، باید بگویم که در حجم‌های داده ای کم استفاده از Row Store Index بهتر می‌باشد. پیشنهاد مایکروسافت برای استفاده از Column Store Index برای دیتابیس‌های با حجم داده ای بسیار بالا می‌باشد.
موفق باشید
منابع:

مطالب
تهیه خروجی XML از یک بانک اطلاعاتی، توسط EF Code first
نگارش کامل SQL Server امکان تهیه خروجی XML از یک بانک اطلاعاتی را دارد. اما اگر بخواهیم از سایر بانک‌های اطلاعاتی که چنین توابع توکاری ندارند، استفاده کنیم چطور؟ برای تهیه خروجی XML توسط Entity framework و مستقل از نوع بانک اطلاعاتی در حال استفاده، حداقل دو روش وجود دارد:

الف) استفاده از امکانات Serialization توکار دات نت

using System.IO;
using System.Xml;
using System.Xml.Serialization;

namespace DNTViewer.Common.Toolkit
{
    public static class Serializer
    {
        public static string Serialize<T>(T type)
        {
            var serializer = new XmlSerializer(type.GetType());
            using (var stream = new MemoryStream())
            {
                serializer.Serialize(stream, type);
                stream.Seek(0, SeekOrigin.Begin);
                using (var reader = new StreamReader(stream))
                {
                    return reader.ReadToEnd();
                }
            }
        }
    }
}
در اینجا برای نمونه، لیستی از اشیاء مدنظر خود را تهیه کرده و به متد Serialize فوق ارسال کنید. نتیجه کار، تهیه معادل XML آن است.
امکانات سفارشی سازی محدودی نیز برای XmlSerializer درنظر گرفته شده است؛ برای نمونه قرار دادن ویژگی‌هایی مانند XmlIgnore بالای خواصی که نیازی به حضور آن‌ها در خروجی نهایی XML نمی‌باشد.


ب) استفاده از امکانات LINQ to XML دات نت

روش فوق بدون مشکل کار می‌کند، اما اگر بخواهیم قسمت Reflection خودکار ثانویه آن‌را (برای نمونه جهت استخراج مقادیر از لیست دریافتی) حذف کنیم، می‌توان از LINQ to XML استفاده کرد که قابلیت سفارشی سازی بیشتری را نیز در اختیار ما قرار می‌دهد (کاری که در سایت جاری برای تهیه خروجی XML از بانک اطلاعاتی آن انجام می‌شود).

        private string createXmlFile(string dir)
        {
            var xLinq = new XElement("ArrayOfPost",
                        _blogPosts
                            .AsNoTracking()
                            .Include(x => x.Comments)
                            .Include(x => x.User)
                            .Include(x => x.Tags)
                            .OrderBy(x => x.Id)
                            .ToList()
                            .Select(x => new XElement("Post", postXElement(x)))
                            );

            var xmlFile = Path.Combine(dir, "dot-net-tips-database.xml");
            xLinq.Save(xmlFile);
            return xmlFile;
        }

        private static XElement[] postXElement(BlogPost x)
        {
            return new XElement[]
            {
                new XElement("Id", x.Id),
                new XElement("Title", x.Title),
                new XElement("Body", x.Body),
                new XElement("CreatedOn", x.CreatedOn),
                tagElement(x),
                new XElement("User",
                                new XElement("Id", x.UserId.Value),
                                new XElement("FriendlyName", x.User.FriendlyName))
            }.Where(item => item != null).ToArray();
        }

        private static XElement tagElement(BlogPost x)
        {
            var tags = x.Tags.Any() ?
                            x.Tags.Select(y =>
                                        new XElement("Tag",
                                                new XElement("Id", y.Id),
                                                new XElement("Name", y.Name)))
                                  .ToArray() : null;
            if (tags == null)
                return null;

            return new XElement("Tags", tags);
        }
خلاصه‌ای از نحوه تبدیل اطلاعات لیستی از مطالب را به معادل XML آن در کدهای فوق مشاهده می‌کنید. یک سری نکات ریز نیز باید در اینجا رعایت شوند:
1) کار با یک new XElement که دارای متد Save با فرمت XML نیز هست، شروع می‌شود. مقدار آن‌را مساوی یک کوئری از بانک اطلاعاتی قرار می‌دهیم. این کوئری چون قرار است تنها اطلاعاتی را از بانک اطلاعاتی دریافت کند و نیازی به تغییر در آن‌ها نیست، با استفاده از متد AsNoTracking، حالت فقط خواندنی پیدا کرده است.
2) اطلاعاتی را که نیاز است در فایل نهایی XML وجود داشته باشند، تنها کافی است در قسمت Select این کوئری با فرمت new XElement‌های تو در تو قرار دهیم. به این ترتیب قسمت Relection خودکار XmlSerializer روش مطرح شده در ابتدای بحث دیگر وجود نداشته و عملیات نهایی بسیار سریعتر خواهد بود.
3) چون در این حالت، کار انجام شده دستی است، باید نام‌های گره‌های صحیحی را انتخاب کنیم تا اگر قرار است توسط همان XmlSerializer مجددا کار serializer.Deserialize صورت گیرد، عملیات با شکست مواجه نشود. بهترین کار برای کم شدن سعی و خطاها، تهیه یک لیست اطلاعات آزمایشی و سپس ارسال آن به روش ابتدای بحث است. سپس می‌توان با بررسی خروجی آن مثلا دریافت که روش serializer.Deserialize به صورت پیش فرض به دنبال ریشه‌ای به نام ArrayOfPost برای دریافت لیستی از مطالب می‌گردد و نه Posts یا هر نام دیگری.
4) در کوئری LINQ to Entites نوشته شده، پیش از Select، یک ToList قرار دارد. متاسفانه EF اجازه استفاده مستقیم از Select هایی از نوع XElement را نمی‌دهد و باید ابتدا اطلاعات را تبدیل به LINQ to Objects کرد.
5) در حین تهیه XElement‌ها اگر قرار است عنصری نال باشد، باید آن‌را در خروجی نهایی ذکر نکرد. به این ترتیب serializer.Deserialize بدون نیاز به تنظیمات اضافه‌تری بدون مشکل کار خواهد کرد. در غیراینصورت باید وارد مباحثی مانند تعریف یک فضای نام جدید برای خروجی XML به نام XSI رفت و سپس به کمک ویژگی‌ها، xsi:nil را به true مقدار دهی کرد. اما همانطور که در متد postXElement ملاحظه می‌کنید، برای وارد نشدن به مبحث فضای نام xsi، مواردی که null بوده‌اند، اصلا در آرایه نهایی ظاهر نمی‌شوند و نهایتا در خروجی، حضور نخواهند داشت. به این ترتیب متد ذیل، بدون مشکل و بدون نیاز به تنظیمات اضافه‌تری قادر است فایل XML نهایی را تبدیل به معادل اشیاء دات نتی آن کند.

using System.IO;
using System.Xml;
using System.Xml.Serialization;

namespace DNTViewer.Common.Toolkit
{
    public static class Serializer
    {
        public static T DeserializePath<T>(string xmlAddress)
        {
            using (var xmlReader = new XmlTextReader(xmlAddress))
                {
                    var serializer = new XmlSerializer(typeof(T));
                    return (T)serializer.Deserialize(xmlReader);
                }
        }
    }
}