WITH RCTE AS ( SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY Invoice.InsertDate ASC) AS RowNumber, Invoice.ID, Invoice.PreInvoiceNo, Invoice.InvoiceNo, Invoice.IssueDate, Invoice.CustomerID, ... FROM Invoice WHERE Invoice.HistorySequence = 1 ) SELECT DISTINCT RCTE.ID, RCTE.PreInvoiceNo, RCTE.InvoiceNo, (dbo.fnc_Calendar_Gregorian_to_Persian(RCTE.IssueDate) + 'T' + CONVERT(CHAR(8), RCTE.IssueDate, 14)) AS IssueDate, RCTE.CustomerID, Customer.NameEn AS CustomerNameEn, Customer.NameFa AS CustomerNameFa, FROM RCTE INNER JOIN Customer ON RCTE.CustomerID = Customer.ID WHERE RowNumber BETWEEN @StartFrom AND (@RowsCount + @StartFrom - 1)
اگر این عبارت را در اختیار لایههای دیگر قرار دهید، یعنی انتهای کار را بازگذاشتهاید و حد و حدود سیستم شما مشخص نیست. شما اگر IQueryable بازگشت دهید، در لایهای دیگر میشود یک join روی آن نوشت و اطلاعات چندین جدول دیگر را استخراج کرد؛ درحالیکه نام متد شما GetUsers بوده. بنابراین بهتر است به صورت صریح اطلاعات را به شکل List بازگشت دهید، تا انتهای کار باز نمانده و طراحی شما نشتی نداشته باشد.
طراحی یک لایه سرویس که خروجی IQueryable دارد نشتی دار درنظر گرفته شده و توصیه نمیشود. اصطلاحا leaky abstraction هم به آن گفته میشود؛ چون طراح نتوانسته حد و مرز سیستم خودش را مشخص کند و همچنین نتوانسته سازوکار درونی آنرا به خوبی کپسوله سازی و مخفی نماید.
برای مثال دو جدول شهرها و افراد را درنظر بگیرید. مقصود از تعریف جدول شهرها در اینجا، مشخص سازی محل تولد افراد است:
public class Person { public int Id { get; set; } public string Name { get; set; } [ForeignKey("BornInCityId")] public virtual City BornInCity { get; set; } public int BornInCityId { get; set; } } public class City { public int Id { get; set; } public string Name { get; set; } public virtual ICollection<Person> People { get; set; } }
public class MyContext : DbContext { public DbSet<City> Cities { get; set; } public DbSet<Person> People { get; set; } }
و همچنین تعدادی رکورد آغازین را نیز به جداول مرتبط اضافه میکنیم:
public class Configuration : DbMigrationsConfiguration<MyContext> { public Configuration() { AutomaticMigrationsEnabled = true; AutomaticMigrationDataLossAllowed = true; } protected override void Seed(MyContext context) { var city1 = new City { Name = "city-1" }; var city2 = new City { Name = "city-2" }; context.Cities.Add(city1); context.Cities.Add(city2); var person1 = new Person { Name = "user-1", BornInCity = city1 }; var person2 = new Person { Name = "user-2", BornInCity = city1 }; context.People.Add(person1); context.People.Add(person2); base.Seed(context); } }
public static class Test { public static void RunTests() { Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyContext, Configuration>()); using (var context = new MyContext()) { var peopleAndCitiesList = from person in context.People join city in context.Cities on person.BornInCityId equals city.Id select new { PersonName = person.Name, CityName = city.Name }; foreach (var item in peopleAndCitiesList) { Console.WriteLine("{0}:{1}", item.PersonName, item.CityName); } } } }
SELECT [Extent1].[BornInCityId] AS [BornInCityId], [Extent1].[Name] AS [Name], [Extent2].[Name] AS [Name1] FROM [dbo].[People] AS [Extent1] INNER JOIN [dbo].[Cities] AS [Extent2] ON [Extent1].[BornInCityId] = [Extent2].[Id]
var peopleAndCitiesList = context.People .Select(person => new { PersonName = person.Name, CityName = person.BornInCity.Name });
مثال دوم:
میخواهیم لیست شهرها را بر اساس تعداد کاربر متناظر به صورت نزولی مرتب کنیم:
var citiesList = context.Cities.OrderByDescending(x => x.People.Count()); foreach (var item in citiesList) { Console.WriteLine("{0}", item.Name); }
SELECT [Project1].[Id] AS [Id], [Project1].[Name] AS [Name] FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], (SELECT COUNT(1) AS [A1] FROM [dbo].[People] AS [Extent2] WHERE [Extent1].[Id] = [Extent2].[BornInCityId]) AS [C1] FROM [dbo].[Cities] AS [Extent1] ) AS [Project1] ORDER BY [Project1].[C1] DESC
مثال سوم:
در ادامه قصد داریم لیست شهرها را به همراه تعداد نفرات متناظر با آنها نمایش دهیم:
var peopleAndCitiesList = context.Cities .Select(city => new { InUseCount = city.People.Count(), CityName = city.Name }); foreach (var item in peopleAndCitiesList) { Console.WriteLine("{0}:{1}", item.CityName, item.InUseCount); }
خروجی SQL کوئری فوق به نحو ذیل است:
SELECT [Extent1].[Id] AS [Id], (SELECT COUNT(1) AS [A1] FROM [dbo].[People] AS [Extent2] WHERE [Extent1].[Id] = [Extent2].[BornInCityId]) AS [C1], [Extent1].[Name] AS [Name] FROM [dbo].[Cities] AS [Extent1]
- https://blog.greglow.com/2018/02/12/sql-design-entity-attribute-value-tables-part-1/
- https://blog.greglow.com/2018/02/19/sql-design-entity-attribute-value-tables-part-2-pros-cons/
- https://ekobit.com/blog/designing-an-entity-attribute-value-model-with-ef-core/
- https://sqlblog.org/2009/11/19/what-is-so-bad-about-eav-anyway
- https://modern-sql.com/use-case/pivot
- https://mariadb.com/kb/en/entity-attribute-value-implementation/
- https://vladmihalcea.com/how-to-store-schema-less-eav-entity-attribute-value-data-using-json-and-hibernate/
- https://www.2ndquadrant.com/en/blog/postgresql-anti-patterns-unnecessary-jsonhstore-dynamic-columns/
- https://coussej.github.io/2016/01/14/Replacing-EAV-with-JSONB-in-PostgreSQL/
- https://davidlaprade.github.io/storing-arbitrary-model-attributes
- https://inviqa.com/blog/understanding-eav-data-model-and-when-use-it
- https://mikesmithers.wordpress.com/2013/12/22/the-anti-pattern-eavil-database-design/
- http://duhallowgreygeek.com/entity-attribute-value-eav-model-sql-smell/
Redis Fundamentals for .NET Developers
Redis is an open source, in-memory data store used by millions of developers as a database, cache, streaming engine, and even a message broker.
In this live sessions, Stephen Lorello, Senior Field Engineer at Redis, joins us to show the the fundamental features .NET developers show know about using Redis
SQL Server tutorial for beginners
150 videos
In this tutorial, we will start from the very basics and cover topics like joins, views, triggers, system functions, stored procedures, user defined scalar and table valued functions etc. These video tutorials will be useful for frehsers, experienced .NET and SQL Database developers.
زمانیکه اولین نگارش ASP.NET حدود 10 سال قبل منتشر شد، تنها سیستم عاملی که از آن پشتیبانی میکرد، ویندوز سرور 2000 بود، تنها پروسهی اجرایی آن aspnet_wp نام داشت و تنها معماری پشتیبانی شده هم X86 بود. به پروسهی aspnet_wp محدودیت مصرف حافظهای اعمال شده بود که در حین آغاز آن بر اساس مقدار قابل تغییر processModel memoryLimit محاسبه و اعمال میشد (تعریف شده در فایل ماشین کانفیگ). این عدد به صورت درصدی از ظرفیت RAM فیزیکی سیستم، قابل تعریف و به صورت پیش فرض به 60 درصد تنظیم شده بود. به این ترتیب این پروسه مجاز نبود تا تمام حافظهی فیزیکی مهیا را مصرف کند و در صورت وجود نشتی حافظهای در برنامهای خاص، این پروسه امکان بازیابی مجدد حافظه را پیدا میکرد (recycling). همچنین یک مورد دیگر را هم باید در نظر داشت و آن هم وجود قابلیتی است به نام ASP.NET Cache است که امکان ذخیره سازی مقادیر اشیاء را در حافظهی مصرفی این پروسه مهیا میسازد. هر زمان که میزان این حافظهی مصرفی به حد نزدیکی از محدودیت تعریف شده برسد، این پروسه به صورت خودکار شروع به حذف آنها خواهد کرد.
محدودیت 60 درصدی تعریف شده، برای سیستمهایی با میزان RAM کم بسیار مفید بود اما در سیستمهایی با میزان RAM بیشتر، مثلا 4 گیگ به 2.4GB حافظه مهیا (60 درصد حافظه فیزیکی سیستم) محدود میشد و همچنین باید در نظر داشت که میزان user mode virtual address space مهیا نیز تنها 2 گیگابایت بود. بنابراین هیچگاه استفاده مؤثری از تمام ظرفیت RAM مهیا صورت نمیگرفت و گاها مشاهده میشد که یک برنامه تنها با مصرف 1.5GB RAM میتوانست پیغام OutOfMemoryException را صادر کند. در این حالت مطابق بررسیهای صورت گرفته مشخص شد که اگر مقدار processModel memoryLimit به حدود 800 مگابایت تنظیم شود، بهترین عملکرد را برای سیستمهای مختلف میتوان مشاهده کرد.
با ارائهی ویندوز سرور 2003 و همچنین ارائهی نسخهی 1.1 دات نت فریم ورک و ASP.NET ، این وضعیت تغییر کرد. پروسهی جدید در اینجا w3wp نام دارد و این پروسه تعاریف مرتبط با محدودیت حافظهی خود را از تنظیمات IIS دریافت میکند (قسمت Maximum Used Memory در برگهی Recycling مربوط به خواص Application Pool مرتبط). متاسفانه این عدد به صورت پیش فرض محدودیتی ندارد و به ظاهر برنامه مجاز است تا حد امکان از حافظهی مهیا استفاده کند. به همین جهت یکی از مواردی را که باید در نظر داشت، مقدار دهی Maximum Used Memory ذکر شده است. خصوصا اینکه در نگارش 1.1 ، تنظیمات میزان مصرف RAM مرتبط با ASP.NET Cache نیز با برنامه یکی است.
در نگارش 2.0 دات نت فریم ورک، تنظیمات مرتبط با ASP.NET cache از تنظیمات میزان RAM مصرفی یک برنامهی ASP.NET جدا شد و این مورد توسط قسمت cache privateBytesLimit قابل تنظیم و مدیریت است (در فایل IIS Metabase و همچنین فایل web.config برنامه).
نکته!
اگر process memory limit و همچنین cache memory limit را تنظیم نکنید، باز به همان عدد 60 درصد سابق بازخواهیم گشت و این مورد به صورت خودکار توسط IIS محاسبه و اعمال میشود. البته محدودیت ذکر شده برای پروسههای 64 بیتی در این حالت بسیار بهتر خواهد بود. اگر هر دوی اینها را تنظیم کنید، عدد حداقل بکارگرفته شده، مبنای کار خواهد بود و اگر تنها یکی را تنظیم کنید ، این عدد به هر دو حالت اعمال میگردد. برای بررسی بهتر میتوان به مقدار Cache.EffectivePrivateBytesLimit و Cache.EffectivePercentagePhysicalMemoryLimit مراجعه کرد.
و ... اکنون بهتر میتوانید به این سؤال پاسخ دهید که «سرور ما بیشتر از 4 گیگ رم دارد و برنامهی ASP.NET من الان فقط 850 مگ رم مصرف کرده (که البته این هم نشانی از عدم dispose صحیح منابع است یا عدم تعیین تقدم و تاخر و زمان منقضی شدن، حین تعریف اشیاء کش)، اما پیغام out of memory exception را دریافت میکنم. چرا؟!»
بنابراین ایجاد یک Application pool جدید به ازای هر برنامهی ASP.NET امری است بسیار مهم زیرا:
- به این ترتیب هر برنامهی ASP.NET در پروسهای ایزوله از پروسهی دیگر اجرا خواهد شد (این مساله از لحاظ امنیتی هم بسیار مهم است). در اینجا هر برنامه، از پروسهی w3wp.exe مجزای خاص خود استفاده خواهد کرد (شبیه به مرورگرهایی که هر tab را در یک پروسه جدید اجرا میکنند).
- اگر پروسهای به حد بالای مصرف حافظهی خود رسید با تنظیمات انجام شده در قسمت recycling مرتبط با Application pool اختصاصی آن، به صورت خودکار کار بازیابی حافظه صورت میگیرد و این امر بر روی سایر برنامهها تاثیر نخواهد داشت (کاربران سایر برنامهها مدام شکایت نمیکنند که سشنها پرید. کش خالی شد. زیرا در حالت وجود application pool اختصاصی به ازای هر برنامه، مدیریت حافظه برنامهها از هم ایزوله خواهند بود)
- کرش صورت گرفته در یک برنامه به دلیل عدم مدیریت خطاها، بر روی سایر برنامهها تاثیر منفی نخواهد گذاشت. (زمانیکه ASP.NET worker process به دلیل استثنایی مدیریت نشده خاتمه یابد بلافاصله و به صورت خودکار مجددا «وهلهی دیگری» از آن شروع به کار خواهد کرد؛ یعنی تمام سشنهای قبلی از بین خواهند رفت؛ که در صورت ایزوله سازی ذکر شده، سایر برنامهها در امان خواهند ماند؛ چون در پروسه ایزولهی خود مشغول به کار هستند)
- با وجود application pool اختصاصی به ازای هر برنامه، میتوان برای سایتهای کم ترافیک و پرترافیک، زمانهای recycling متفاوتی را اعمال کرد. به این ترتیب مدیریت حافظهی بهتری قابل پیاده سازی میباشد. همچنین در این حالت میتوان مشخص کرد کدام سایت از تعداد worker process بیشتر یا کمتری استفاده کند.
- کاربری که پروسهی ASP.NET تحت آن اجرا میشود نیز همینجا تعریف میگردد. بنابراین به این ترتیب میتوان به برنامهای دسترسی بیشتر و یا کمتر داد، بدون تاثیر گذاری بر روی سایر برنامههای موجود.
نتیجه گیری:
- از IIS استفاده میکنید؟ آیا میدانید Application pool چیست؟
- آیا میدانید در صورت عدم مقدار دهی پارامترهای حافظهی یک Application pool ، به صورت پیش فرض چند درصد از حافظهی فیزیکی مهیا در اختیار شما است؟
برای مطالعه بیشتر:
امروز اولین دستورات MDX را خواهیم نوشت. قبل از شروع کار فراموش نکنید موارد زیر را حتما انجام داده باشید :
- نصب پایگاه داده ی Adventure Work DW 2008 و همچنین نصب پایگاه دادهی چند بعدی Adventure Work DW 2008 روی SSAS
- مطاله قسمتهای قبلی برای آشنایی با مفاهیم پایه .
در صورتیکه پیش شرایط فوق را نداشته باشید، احتمالا در ادامه با مشکلاتی مواجه خواهید شد؛ زیرا برای آموزش MDX Query ها از پایگاه دادهی Adventure Work DW 2008 استفاده شده است.
دقت داشته باشید که MDX Query ها تا حدودی شبیه T/SQL میباشند؛ اما مطلقا از نظر مفهومی با هم شباهت ندارند. به عبارت دیگر ما در T/SQL با یک مدل رابطهای سرو کار داریم در حالیکه در MDX ها با یک پایگاه داده چند بعدی کار میکنیم. به بیان دیگر در پایگاه دادههای رابطهای صحبت از جداول، ردیفها، ستونها و ضرب دکارتی مجموعهها میباشد، اما در پایگاه دادههای چند بعدی در خصوص Dimension,Fact,Cube,Tuple و ... صحبت میکنیم. البته ماکروسافت تلاش کردهاست تا حد زیادی Syntax ها شبیه به یکدیگر باشند.
نحوهی نوشتن یک Select در MDX ها به صورت زیر میباشد :
Select {} On Columns , {} On Rows From <Cube_Name> Where <Condition>
در ادامه با اجرای هر کوئری، توضیحات لازم در خصوص آن ارایه میگردد و با پیگیری این آموزشها میتوانید مفاهیم، توابع و ... را در MDX Query ها بیاموزید.
برای اجرای دستورات زیر باید Microsoft SQL Server Management Studio را باز نمایید و به سرویس SSAS متصل شوید. سپس پایگاه دادهی Adventure Works DW 2008R2 را انتخاب نمایید و از Cubes Adventure Works را انتخاب نمایید.
حال دکمهی New Query را در بالای صفحه بزنید ( Ctrl + N )
سپس در صفحهی باز شده میتوانید Cube یا SubCube های آن Cube را انتخاب کرده و کمی پایینتر Measure Group را خواهیم داشت و در انتها Measure ها و Dimension ها قرار گرفتهاند. (در هنگام نوشتن Select میتوان از عمل Drag&Drop برای آسانتر شدن نوشتن MDX Query ها نیز استفاده کنید)
متاسفانه هنوز در IDE مربوط به SQL Server کلیدی برای مرتب سازی دستورات MDX وجود ندارد و البته در نرم افزار هایی مانند SQL Toll Belt هم چنین چیزی قرار داده نشده است . بنابر این توصیه میشود در نوشتن دستورات MDX تمام تلاش خود را بکنید تا دستوراتی مرتب و خوانا را تولید کنید.
با اجرای دستور زیر اولین کوئری خود را در پایگاه دادهی چند بعدی بنویسید (برای اجرا کلید F5 مانند T/SQL کار خواهد کرد.)
Select From [Adventure Works]
شاید تعجب کنید. کوئری فاقد قسمت Projection میباشد! در MDX ها میتوان هیچ سطر یا ستونی را انتخاب نکرد. اما چگونه؟ و خروجی نمایش داده شده چیست؟
برای توضیح مطلب فوق باید در خصوص Default Measure کمی اطلاعات داشته باشید. در هنگام Deploy کردن پروژه در SSAS برای هر Cube یک Measure به عنوان Measure پیش فرض انتخاب شده. بنابر این در صورتیکه هیچ گونه Projection یا Where ایی اعمال نشده باشد، SQL Server به صورت پیش فرض مقدار Mesaure پیش فرض را بدون اعمال هیچ بعدی نمایش میدهد.
خروجی دستور بالا مشابه تصویر زیر میباشد.
حال دستور زیر را اجرا میکنیم :
Select From [Adventure Works] Where [Measures].[Reseller Sales Amount]
تصویر خروجی به صورت زیر میباشد :
شاید باز هم تعجب کنید. نوشتن نام یک شاخص به جای عبارت شرط؟! آیا خروجی عبارات شرطی نباید Boolean باشند؟
خیر. اگر چنین پرسش هایی در ذهن شما ایجاد شده باشد، به دلیل مقایسهی MDX با T/SQL میباشد. در اینجا شرط Where بر روی ردیفهای جدول مدل رابطه ای اعمال نمیشود و عملا بیانگر واکشی اطلاعات از مدل چند بعدی میباشد. با اعمال شرط فوق به SSAS اعلام کرده ایم که خروجی بر اساس شاخص [Measures].[Reseller Sales Amount] باشد. با توجه به این که شاخص انتخاب شده با شاخص پیش فرض یکی میباشد خروجی با حالت قبل تفاوتی نخواهد کرد.
برای درک بهتر، کوئری زیر را اجرا کنید :
Select From [Adventure Works] where [Measures].[Internet Sales Amount]
استفاده از این شرط سبب استفاده نشدن از شاخص پیش فرض می شود . به عبارت دیگر این کوئری دارای سرجمع مبلغ فروش اینترنتی می باشد.
دستور زیر را اجرا کنید :
Select [Measures].[Reseller Sales Amount] on columns From [Adventure Works]
با اعمال یک شاخص خاص در ستون ، عملا فیلترینگ انجام می شود
استفاده از یک دایمنشن در ستون :
دستور زیر را اجرا کنید
Select [Date].[Calendar].[Calendar Year] on columns From [Adventure Works]
خروجی به شکل زیر خواهد بود
همان طور که مشاهده میکنید خروجی دارای چندین ستون میباشد و دارای مقادیری در هر ستون. اما این مقادیر از کجا آمده اند؟
همواره این نکته را به خاطر بسپارید که در صورت عدم ذکر نام یک Measure در کوئری ، SSAS از Measure پیش فرض استفاده میکند. حال کوئری فوق میزان فروش نمایندگان ( Reseller Sales Amount ) را در هر سال نمایش میدهد.
سوال بعدی این میباشد که این سالها از کجا آمده اند؟ خوب برای درک بهتر این مورد میتوانیم مانند تصویر زیر به دایمنشن Date رفته و در ساختار سلسله مراتبی ، اعضای سطح [Date].[Calendar].[Calendar Year] را مشاهده کنیم.
ایجاد سرجمع ستونها :
کوئری زیر را اجرا نمایید
Select {[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns From [Adventure Works]
بعد از اجرا
تصویر زیر را خواهید دید :
سوال اول این میباشد که کاربرد {} در انتخاب دایمنشنها چیست؟ در پاسخ میتوان گفت که اگر شاخص ها یا بعد ها ، مرتبط به یک سلسله مراتب باشند آنها را در یک {} قرار می دهیم ولی اگر سلسله مراتب متفاوت باشد، یا بعد و شاخص باشند باید در () قرار بگیرند .
خوب همان طور که مشخص است در ساختار سلسله مراتبی ابتدا سال و بعد یک سطح بالاتر را انتخاب کرده ایم این به معنی نمایش سرجمع در سطح بالاتر از سال میباشد(سرجمع تمامی سال ها).
استفاده از دایمنشن و Measure در سطر و ستون مجرا :
کوئری زیر را اجرا نمایید
Select {[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns, [Product].[Product Categories].[Category] on rows From [Adventure Works]
خروجی مشابه شکل زیر میباشد
در مثال فوق از بعدها در ستون و همزمان، نمایش نوع دسته بندی محصولات در ردیفها استفاده شده است. به عبارت دیگر نتیجه عبارت است از فروش نماینگان فروش ( Reseller Sales Amount ) براساس هر سال به تفکیک نوع دسته بندی محصول فروخته شده.
(کسانی که چنین گزارشی را با استفاده از T/SQL نوشته اند، احتمالا از آسانی نوشتن این گزارش توسط MDX ها شگفت زده شده اند.)
قراردادن فیلد سرجمع در ردیف :
برای این منظور کوئری زیر را اجرا نمایید
Select {[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns, {[Product].[Product Categories].[Category],[Product].[Product Categories]}on rows From [Adventure Works]
خروجی به صورت زیر میباشد
نحوهی نمایش سرجمع در ردیف، مشابه نمایش سرجمع در ستون میباشد.
استفاده از تابع non empty :
برای حذف ستون هایی که کاملا دارای مقدار null میباشند به صورت زیر عمل میکنیم :
Select non empty {[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns , {[Product].[Product Categories].[Category],[Product].[Product Categories]} on rows From [Adventure Works]
خروجی به صورت زیر میباشد:
انتخاب دو دایمنشن در سطر و ستون و مشخص نمودن یک Measure خاص برای کوئری :
برای این کار به صورت زیر عمل خواهیم کرد:
Select {[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns, {[Product].[Product Categories].[Category],[Product].[Product Categories]} on rows From [Adventure Works] Where [Measures].[Internet Sales Amount]
در اینجا با اعمال شرط Where عملا از SSAS خواستهایم خروجی برای شاخص مشخص شده واکشی شود.
در بالا میزان فروش اینترنتی برای دسته بندی محصولات و در سالهای مختلف ارائه و همچنین سرجمع ستون و سطر نیز نمایش داده شده است.
در صورتیکه بخواهیم ستون و سطرهایی را که دارای مقدار null در تمامی آن سطر یا ستون میباشند، حذف کنیم به صورت زیر عمل میکنیم:
Select non empty {[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns, non empty {[Product].[Product Categories].[Category],[Product].[Product Categories]} on rows From [Adventure Works] Where [Measures].[Internet Sales Amount]
اگر در یک دایمنشن فقط یک سلسله مراتب باشد یا اصلا سلسله مراتبی وجود نداشته باشد، می توان از نام خود دایمنشن استفاده کرد
Select [Sales Channel] on columns From [Adventure Works]
و دقت داشته باشید دایمنشنی که دارای بیش از یک سلسله مراتب باشد، حتما باید در Select مشخص شود که از کدام سلسله مراتب می خواهیم استفاده کنیم .در غیر این صورت با خطا مواجه خواهیم شد.
Select [Product] on columns From [Adventure Works]
استفاده از فیلدهای یک دایمنشن که دارای سلسه مراتب می باشد نیز جایز می باشد
Select [Product].[Category] on columns From [Adventure Works]
Select [Product].[Category].[all] on columns From [Adventure Works] -- Select [Product].[Category].[All] on columns From [Adventure Works] -- Select [Product].[Category].[(all)] on columns From [Adventure Works] -- Select [Product].[Category].[all products] on columns From [Adventure Works]
برای به دست آوردن سرجمع کل روی یک صفت از دایمنشن، باید از سه حالت آخر استفاده کرد. حالت اول خطا دارد و خروجی خالی نمایش داده می شود .
در صورتی که بخواهیم از یک دایمنشن تمامی Member های آن را واکشی کنیم به صورت زیر عمل خواهیم کرد
Select {[Product].[Category].members} on columns From [Adventure Works]
استفاده از Members روی یک خصوصیت در دایمنشن به معنی دریافت سرجمع آن صفت و سپس تک تک اجزای آن صفت میباشد.
اگر از یک صفت واکشی اطلاعات انجام شود در سطح اعضای آن، در آن صورت دیگر سرجمع نمایش داده نمی شود و فقط جمع هر عضو در آن صفت نمایش داده می شود .
Select [Product].[Category].[Category].members -- dimension.hierarchy.level.members on columns From [Adventure Works]
اگر بخواهیم دو ستون را داشته باشیم که هر دو برای یک دایمنشن میباشند باید از {} استفاده کرد . دستور اول خطا خواهد داشت.
Select [Product].[Category].[Category].members,[Product].[Category].[All Products] on columns From [Adventure Works]
در دستور دوم با استفاده از {} خروجی نمایش داده میشود که عبارت است از تمامی اعضای سطح [Product].[Category].[Category]. به همراه سرجمع تمامی محصولات.
Select {[Product].[Category].[Category].members,[Product].[Category].[All Products]} on columns From [Adventure Works]
یک راه کوتاهتر برای انتخاب تمامی اعضا و سرجمع آنها
Select {[Product].[Category].[Category],[Product].[Category]} on columns From [Adventure Works]
می توان از کلمات Members, All X استفاده نکرد.
انتخاب اولین دسته بندی محصول البته این ترتیب بر اساس Key Columns در SSAS می باشد .
Select [Product].[Category].&[1] on columns From [Adventure Works]
انتخاب دقیق یک عضو در خروجی
Select [Product].[Category].[Bikes] on columns From [Adventure Works]
انتخاب دو عضو از یک دایمنشن
Select {[Product].[Category].[Bikes],[Product].[Category].[Clothing]} on columns From [Adventure Works]
واکشی تمامی دسته بندی محصولات بر اساس Measure پیش فرض :
Select [Product].[Product Categories].members on columns From [Adventure Works]
در صورتیکه بخواهیم دو Dimension مختلف را در یک ستون یا سطر بیاوریم باید از Join استفاده کنیم. بنابر این دو دستور زیر با خطا روبرو میشوند
Select [Product].[Product Categories],[Product].[Category] on columns From [Adventure Works] Go Select {[Product].[Product Categories],[Product].[Category]} on columns From [Adventure Works]
تعریف Axis : به هر کدام از ستون یا سطر یک محور یا Axis گفته میشود.
با بررسی مثال فوق به نتایج زیر خواهیم رسید.
1. امکان استفاده از دو سلسله مراتب مختلف از یک دایمنشن در یک Axis وجود ندارد . مگر اینکه آنها را باهمدیگر CrossJoin کنیم .
2. امکان استفاده از دو سلسله مراتب مختلف از یک دایمنشن در دو Axis مختلف وجود دارد .
ترتیب انتخاب Axis ها به صورت زیر میباشد:
1. Columns
2. Rows
برای مشخص شدن موضوع کوئری زیر را اجرا کنید
Select [Product].[Product Categories].members on rows From [Adventure Works]
نمیتوانیم ردیفی را واکشی کنیم بدون اینکه ستونی برای کوئری مشخص کرده باشیم.
البته میتوان ستون خالی ایجاد نماییم مانند مثال زیر :
Select {} on columns, [Product].[Product Categories].members on rows From [Adventure Works]
البته در این صورت خروجی فقط نام دسته بندی محصولات خواهد بود زیرا هیچ ستونی مشخص نشده .
در مقالات بعدی به ادامهی مطالب MDX Query خواهیم پرداخت.
EF Code First #9
تنظیمات ارث بری کلاسها در EF Code first
بانکهای اطلاعاتی مبتنی بر SQL، تنها روابطی از نوع «has a» یا «دارای» را پشتیبانی میکنند؛ اما در دنیای شیءگرا روابطی مانند «is a» یا «هست» نیز قابل تعریف هستند. برای توضیحات بیشتر به مدلهای زیر دقت نمائید:
using System;
namespace EF_Sample05.DomainClasses.Models
{
public abstract class Person
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime DateOfBirth { get; set; }
}
}
namespace EF_Sample05.DomainClasses.Models
{
public class Coach : Person
{
public string TeamName { set; get; }
}
}
namespace EF_Sample05.DomainClasses.Models
{
public class Player : Person
{
public int Number { get; set; }
public string Description { get; set; }
}
}
در این مدلها که بر اساس ارث بری از کلاس شخص، تهیه شدهاند؛ بازیکن، یک شخص است. مربی نیز یک شخص است؛ و به این ترتیب خوانده میشوند:
Coach "is a" Person
Player "is a" Person
در EF Code first سه روش جهت کار با این نوع کلاسها و کلا ارث بری وجود دارد که در ادامه به آنها خواهیم پرداخت:
الف) Table per Hierarchy یا TPH
همانطور که از نام آن نیز پیدا است، کل سلسله مراتبی را که توسط ارث بری تعریف شده است، تبدیل به یک جدول در بانک اطلاعاتی میکند. این حالت، شیوه برخورد پیش فرض EF Code first با ارث بری کلاسها است و نیاز به هیچگونه تنظیم خاصی ندارد.
برای آزمایش این مساله، کلاس Context را به نحو زیر تعریف نمائید و سپس اجازه دهید تا EF بانک اطلاعاتی معادل آنرا تولید کند:
using System.Data.Entity;
using EF_Sample05.DomainClasses.Models;
namespace EF_Sample05.DataLayer.Context
{
public class Sample05Context : DbContext
{
public DbSet<Person> People { set; get; }
}
}
ساختار جدول تولید شده آن همانند تصویر زیر است:
همانطور که ملاحظه میکنید، تمام کلاسهای مشتق شده از کلاس شخص را تبدیل به یک جدول کرده است؛ به علاوه یک فیلد جدید را هم به نام Discriminator به این جدول اضافه نموده است. برای درک بهتر عملکرد این فیلد، چند رکورد را توسط برنامه به بانک اطلاعاتی اضافه میکنیم. حاصل آن به شکل زیر خواهد بود:
از فیلد Discriminator جهت ثبت نام کلاسهای متناظر با هر رکورد، استفاده شده است. به این ترتیب EF حین کار با اشیاء دقیقا میداند که چگونه باید خواص متناظر با کلاسهای مختلف را مقدار دهی کند.
به علاوه اگر به ساختار جدول تهیه شده دقت کنید، مشخص است که در حالت TPH، نیاز است فیلدهای متناظر با کلاسهای مشتق شده از کلاس پایه، همگی null پذیر باشند. برای نمونه فیلد Number که از نوع int تعریف شده، در سمت بانک اطلاعاتی نال پذیر تعریف شده است.
و برای کوئری نوشتن در این حالت میتوان از متد الحاقی OfType جهت فیلتر کردن اطلاعات بر اساس کلاسی خاص، کمک گرفت:
db.People.OfType<Coach>().FirstOrDefault(x => x.LastName == "Coach L1")
سفارشی سازی نحوه نگاشت TPH
همانطور که عنوان شد، TPH نیاز به تنظیمات خاصی ندارد و حالت پیش فرض است؛ اما برای مثال میتوان بر روی مقادیر و نوع ستون Discriminator تولیدی، کنترل داشت. برای این منظور باید از Fluent API به نحو زیر استفاده کرد:
using System.Data.Entity.ModelConfiguration;
using EF_Sample05.DomainClasses.Models;
namespace EF_Sample05.DataLayer.Mappings
{
public class CoachConfig : EntityTypeConfiguration<Coach>
{
public CoachConfig()
{
// For TPH
this.Map(m => m.Requires(discriminator: "PersonType").HasValue(1));
}
}
}
using System.Data.Entity.ModelConfiguration;
using EF_Sample05.DomainClasses.Models;
namespace EF_Sample05.DataLayer.Mappings
{
public class PlayerConfig : EntityTypeConfiguration<Player>
{
public PlayerConfig()
{
// For TPH
this.Map(m => m.Requires(discriminator: "PersonType").HasValue(2));
}
}
}
در اینجا توسط متد Map، نام فیلد discriminator به PersonType تغییر کرده. همچنین چون مقدار پیش فرض تعیین شده توسط متد HasValue عددی است، نوع این فیلد در سمت بانک اطلاعاتی به int null تغییر میکند.
ب) Table per Type یا TPT
در حالت TPT، به ازای هر کلاس موجود در سلسله مراتب تعیین شده، یک جدول در سمت بانک اطلاعاتی تشکیل میگردد.
در جداول متناظر با Sub classes، تنها همان فیلدهایی وجود خواهند داشت که در کلاسهای هم نام وجود دارد و فیلدهای کلاس پایه در آنها ذکر نخواهد گردید. همچنین این جداول دارای یک Primary key نیز خواهند بود (که دقیقا همان کلید اصلی جدول پایه است که به آن Shared primary key هم گفته میشود). این کلید اصلی، به عنوان کلید خارجی اشاره کننده به کلاس یا جدول پایه نیز تنظیم میگردد:
برای تنظیم این نوع ارث بری، تنها کافی است ویژگی Table را بر روی Sub classes قرار داد:
using System.ComponentModel.DataAnnotations;
namespace EF_Sample05.DomainClasses.Models
{
[Table("Coaches")]
public class Coach : Person
{
public string TeamName { set; get; }
}
}
using System.ComponentModel.DataAnnotations;
namespace EF_Sample05.DomainClasses.Models
{
[Table("Players")]
public class Player : Person
{
public int Number { get; set; }
public string Description { get; set; }
}
}
یا اگر حالت Fluent API را ترجیح میدهید، همانطور که در قسمتهای قبل نیز ذکر شد، معادل ویژگی Table در اینجا، متد ToTable است.
ج) Table per Concrete type یا TPC
در تعاریف ارث بری که تاکنون بررسی کردیم، مرسوم است کلاس پایه را از نوع abstract تعریف کنند. به این ترتیب هدف اصلی، Sub classes تعریف شده خواهند بود؛ چون نمیتوان مستقیما وهلهای را از کلاس abstract تعریف شده ایجاد کرد.
در حالت TPC، به ازای هر sub class غیر abstract، یک جدول ایجاد میشود. هر جدول نیز حاوی فیلدهای کلاس پایه میباشد (برخلاف حالت TPT که جداول متناظر با کلاسهای مشتق شده، تنها حاوی همان خواص و فیلدهای کلاسهای متناظر بودند و نه بیشتر). به این ترتیب عملا جداول تشکیل شده در بانک اطلاعاتی، از وجود ارث بری در سمت کدهای ما بیخبر خواهند بود.
برای پیاده سازی TPC نیاز است از Fluent API استفاده شود:
using System.ComponentModel.DataAnnotations;
using System.Data.Entity.ModelConfiguration;
using EF_Sample05.DomainClasses.Models;
namespace EF_Sample05.DataLayer.Mappings
{
public class PersonConfig : EntityTypeConfiguration<Person>
{
public PersonConfig()
{
// for TPC
this.Property(x => x.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
}
}
}
using System.Data.Entity.ModelConfiguration;
using EF_Sample05.DomainClasses.Models;
namespace EF_Sample05.DataLayer.Mappings
{
public class CoachConfig : EntityTypeConfiguration<Coach>
{
public CoachConfig()
{
// For TPH
//this.Map(m => m.Requires(discriminator: "PersonType").HasValue(1));
// for TPT
//this.ToTable("Coaches");
//for TPC
this.Map(m =>
{
m.MapInheritedProperties();
m.ToTable("Coaches");
});
}
}
}
using System.Data.Entity.ModelConfiguration;
using EF_Sample05.DomainClasses.Models;
namespace EF_Sample05.DataLayer.Mappings
{
public class PlayerConfig : EntityTypeConfiguration<Player>
{
public PlayerConfig()
{
// For TPH
//this.Map(m => m.Requires(discriminator: "PersonType").HasValue(2));
// for TPT
//this.ToTable("Players");
//for TPC
this.Map(m =>
{
m.MapInheritedProperties();
m.ToTable("Players");
});
}
}
}
ابتدا نوع فیلد Id از حالت Identity خارج شده است. این مورد جهت کار با TPC ضروری است در غیراینصورت EF هنگام ثبت، به مشکل بر میخورد، از این لحاظ که برای دو شیء، به یک Id خواهد رسید و امکان ثبت را نخواهد داد. بنابراین در یک چنین حالتی استفاده از نوع Guid برای تعریف primary key شاید بهتر باشد. بدیهی است در این حالت باید Id را به صورت دستی مقدار دهی نمود.
در ادامه توسط متد MapInheritedProperties، به همان مقصود لحاظ کردن تمام فیلدهای ارث بری شده در جدول حاصل، خواهیم رسید. همچنین نام جداول متناظر نیز ذکر گردیده است.
سؤال : از این بین، بهتر است از کدامیک استفاده شود؟
- برای حالتهای ساده از TPH استفاده کنید. برای مثال یک بانک اطلاعاتی قدیمی دارید که هر جدول آن 200 تا یا شاید بیشتر فیلد دارد! امکان تغییر طراحی آن هم وجود ندارد. برای اینکه بتوان به حس بهتری حین کارکردن با این نوع سیستمهای قدیمی رسید، میشود از ترکیب TPH و ComplexTypes (که در قسمتهای قبل در مورد آن بحث شد) برای مدیریت بهتر این نوع جداول در سمت کدهای برنامه استفاده کرد.
- اگر علاقمند به استفاده از روابط پلیمرفیک هستید ( برای مثال در کلاسی دیگر، ارجاعی به کلاس پایه Person وجود دارد) و sub classes دارای تعداد فیلدهای کمی هستند، از TPH استفاده کنید.
- اگر تعداد فیلدهای sub classes زیاد است و بسیار بیشتر است از کلاس پایه، از روش TPT استفاده کنید.
- اگر عمق ارث بری و تعداد سطوح تعریف شده بالا است، بهتر است از TPC استفاده کنید. حالت TPT از join استفاده میکند و حالت TPC از union برای تشکیل کوئریها کمک خواهد گرفت
EF Code First #9
CASE WHEN ((CASE WHEN ([Extent1].[Discriminator] = N'Person') THEN cast(1 as bit) ELSE cast(0 as bit) END) = 1) THEN CAST(NULL AS varchar(1)) WHEN (([Project6].[C1] = 1) AND ([Project6].[C1] IS NOT NULL) AND ( NOT (([Project6].[C2] = 1) AND ([Project6].[C2] IS NOT NULL))) AND ( NOT (([Project6].[C3] = 1) AND ([Project6].[C3] IS NOT NULL))) AND ( NOT (([Project6].[C4] = 1) AND ([Project6].[C4] IS NOT NULL)))) THEN [Project6].[Name] WHEN (([Project6].[C2] = 1) AND ([Project6].[C2] IS NOT NULL)) THEN [Project6].[Name] WHEN (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([Project6].[C4] = 1) AND ([Project6].[C4] IS NOT NULL)) THEN [Project6].[Name] WHEN (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([Project6].[C3] = 1) AND ([Project6].[C3] IS NOT NULL)) THEN [Project6].[Name] END AS [C2], CASE WHEN ((CASE WHEN ([Extent1].[Discriminator] = N'Person') THEN cast(1 as bit) ELSE cast(0 as bit) END) = 1) THEN CAST(NULL AS varchar(1)) WHEN (([Project6].[C1] = 1) AND ([Project6].[C1] IS NOT NULL) AND ( NOT (([Project6].[C2] = 1) AND ([Project6].[C2] IS NOT NULL))) AND ( NOT (([Project6].[C3] = 1) AND ([Project6].[C3] IS NOT NULL))) AND ( NOT (([Project6].[C4] = 1) AND ([Project6].[C4] IS NOT NULL)))) THEN [Project6].[Family] WHEN (([Project6].[C2] = 1) AND ([Project6].[C2] IS NOT NULL)) THEN [Project6].[Family] WHEN (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([Project6].[C4] = 1) AND ([Project6].[C4] IS NOT NULL)) THEN [Project6].[Family] WHEN (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)) THEN CAST(NULL AS varchar(1)) WHEN (([Project6].[C3] = 1) AND ([Project6].[C3] IS NOT NULL)) THEN [Project6].[Family] END AS [C3], CASE WHEN ((CASE WHEN ([Extent1].[Discriminator] = N'Person') THEN cast(1 as bit) ELSE cast(0 as bit) END) = 1) THEN CAST(NULL AS datetime2) WHEN (([Project6].[C1] = 1) AND ([Project6].[C1] IS NOT NULL) AND ( NOT (([Project6].[C2] = 1) AND ([Project6].[C2] IS NOT NULL))) AND ( NOT (([Project6].[C3] = 1) AND ([Project6].[C3] IS NOT NULL))) AND ( NOT (([Project6].[C4] = 1) AND ([Project6].[C4] IS NOT NULL)))) THEN [Project6].[DateOfBirth] WHEN (([Project6].[C2] = 1) AND ([Project6].[C2] IS NOT NULL)) THEN [Project6].[DateOfBirth] WHEN (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL)) THEN CAST(NULL AS datetime2) WHEN (([Project6].[C4] = 1) AND ([Project6].[C4] IS NOT NULL)) THEN [Project6].[DateOfBirth] WHEN (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)) THEN CAST(NULL AS datetime2) WHEN (([Project6].[C3] = 1) AND ([Project6].[C3] IS NOT NULL)) THEN [Project6].[DateOfBirth] END AS [C4],