در کوئریهای قسمتهای قبل نیز تعدادی از آنها بر اساس فیلتر اطلاعات یک روز خاص، گروه بندی اطلاعات بر اساس ماهها و یا گروه بندی اطلاعات بر اساس روزها، بدون درنظر گرفتن قسمت زمان تاریخ، تهیه شدند. در این قسمت مثالهای دیگری را از این دست بررسی میکنیم.
مثال 1: تعداد روزهای هر ماه سال 2012 را محاسبه کنید.
ستونهای این گزارش باید از سه مقدار عددی Year, Month, DaysInMonth تشکیل شوند.
در این گزارش تعداد ماهها را به تعداد ماههای موجود در جدول Bookings محدود کردهایم.
سپس این نکات در مورد کار با تاریخ و زمان در اینجا قابل مشاهده هستند:
1) خاصیت StartTime.Year به DATEPART(year, [b].[StartTime]) ترجمه میشود.
2) خاصیت StartTime.Month به DATEPART(month, [b].[StartTime]) ترجمه میشود.
3) برای یافتن عدد اختلاف تعداد روز بین دو تاریخ، میتوان از تابع کمکی استاندارد EF.Functions.DateDiffDay استفاده کرد که در نهایت به DATEDIFF ترجمه خواهد شد.
4) اگر میخواهید از قسمت زمان یک تاریخ صرفنظر کنید، از خاصیت Date آن مانند StartTime.Date استفاده کنید که به CONVERT(date, [b].[StartTime]) ترجمه میشود.
5) امکان استفادهی از متدهای استانداردی مانند AddDays و AddMonths در کوئرهای LINQ to Entities وجود دارد که به تابع DATEADD ترجمه میشوند.
مثال 2: لیست زمان شروع و پایان آخرین 10 مورد از رزروها را تهیه کنید.
زمان پایان هر رزرو با فرمول start time + (30 minutes * slots) محاسبه میشود. به همین جهت StartTime.AddMinutes را در اینجا مشاهده میکنید و برای یافتن آخرین 10 مورد نیاز است اطلاعات را به صورت نزولی مرتب کرد و سپس از متد Take استفاده نمود.
مثال 3: لیست تعداد رزروهای هر ماه موجود را تهیه کنید.
برای اینکار میتوان اطلاعات Bookings را بر اساس سال و ماه، گروه بندی کرد و سپس تعداد ردیفهای هر گروه را محاسبه نمود.
مثال 4: در هر ماه، چند درصد از امکانات موجود مورد استفاده قرار گرفتهاند؟
زمان شروع به کار، 8 صبح و زمان خاتمهی کار 8:30 شب است. بنابراین یک روز کاری از 25 slot نیم ساعته تشکیل میشود. هر ماه را هم میتوانید کامل درنظر بگیرید و مهم نیست که در این بین تعطیلی وجود دارد. بنابراین فرمول محاسبهی درصد استفادهی از امکانات موجود به صورت زیر است که نیاز است نتیجهی حاصل نیز round شود:
بنابراین مشکلترین قسمت این کوئری، محاسبهی DaysInMonth است که در مثال 1 این قسمت آنرا بررسی کردیم:
در اینجا در ابتدا با استفاده از روش مثال 1، مقدار DaysInMonthهای موجود محاسبه شدهاند. سپس چون میخواهیم جمع Slots را محاسبه کنیم، نیاز است اطلاعات هر امکانی را در یک سال و ماه خاص، گروه بندی کرد.
در این کوئری، از متد SqlDbFunctionsExtensions.SqlRound نیز استفاده شدهاست. روش تعریف این نوع متدها را در مطلب «امکان تعریف توابع خاص بانکهای اطلاعاتی در EF Core» پیشتر بررسی کردهایم که برای مثال در اینجا چنین شکلی را پیدا میکند:
پس از آن فقط کافی است متد AddCustomSqlFunctions را به Context برنامه معرفی کنیم:
کدهای کامل این قسمت را در اینجا میتوانید مشاهده کنید.
مثال 1: تعداد روزهای هر ماه سال 2012 را محاسبه کنید.
ستونهای این گزارش باید از سه مقدار عددی Year, Month, DaysInMonth تشکیل شوند.
var items = context.Bookings .Where(booking => booking.StartTime.Year == 2012) .Select(booking => new { booking.StartTime.Year, booking.StartTime.Month, DaysInMonth = EF.Functions.DateDiffDay( booking.StartTime.Date.AddDays(1 - booking.StartTime.Date.Day), booking.StartTime.Date.AddDays(1 - booking.StartTime.Date.Day).AddMonths(1) ) }) .Distinct() .OrderBy(r => r.Year) .ThenBy(r => r.Month) .ToList();
سپس این نکات در مورد کار با تاریخ و زمان در اینجا قابل مشاهده هستند:
1) خاصیت StartTime.Year به DATEPART(year, [b].[StartTime]) ترجمه میشود.
2) خاصیت StartTime.Month به DATEPART(month, [b].[StartTime]) ترجمه میشود.
3) برای یافتن عدد اختلاف تعداد روز بین دو تاریخ، میتوان از تابع کمکی استاندارد EF.Functions.DateDiffDay استفاده کرد که در نهایت به DATEDIFF ترجمه خواهد شد.
4) اگر میخواهید از قسمت زمان یک تاریخ صرفنظر کنید، از خاصیت Date آن مانند StartTime.Date استفاده کنید که به CONVERT(date, [b].[StartTime]) ترجمه میشود.
5) امکان استفادهی از متدهای استانداردی مانند AddDays و AddMonths در کوئرهای LINQ to Entities وجود دارد که به تابع DATEADD ترجمه میشوند.
مثال 2: لیست زمان شروع و پایان آخرین 10 مورد از رزروها را تهیه کنید.
var items = context.Bookings .Select(x => new { x.StartTime, EndTime = x.StartTime.AddMinutes(x.Slots * 30) }) .OrderByDescending(x => x.EndTime) .ThenByDescending(x => x.StartTime) .Take(10) .ToList();
مثال 3: لیست تعداد رزروهای هر ماه موجود را تهیه کنید.
var items = context.Bookings .GroupBy(x => new { x.StartTime.Year, x.StartTime.Month }) .Select(x => new { x.Key.Year, x.Key.Month, Count = x.Count() }) .OrderBy(x => x.Year) .ThenBy(x => x.Month) .ToList();
مثال 4: در هر ماه، چند درصد از امکانات موجود مورد استفاده قرار گرفتهاند؟
زمان شروع به کار، 8 صبح و زمان خاتمهی کار 8:30 شب است. بنابراین یک روز کاری از 25 slot نیم ساعته تشکیل میشود. هر ماه را هم میتوانید کامل درنظر بگیرید و مهم نیست که در این بین تعطیلی وجود دارد. بنابراین فرمول محاسبهی درصد استفادهی از امکانات موجود به صورت زیر است که نیاز است نتیجهی حاصل نیز round شود:
Round(100 * Sum(Slots) / (decimal)(25 * DaysInMonth), 1)
var items = context.Bookings .Select(booking => new { booking.Facility.Name, booking.StartTime.Year, booking.StartTime.Month, booking.Slots, DaysInMonth = EF.Functions.DateDiffDay( booking.StartTime.Date.AddDays(1 - booking.StartTime.Date.Day), booking.StartTime.Date.AddDays(1 - booking.StartTime.Date.Day).AddMonths(1) ) }) .GroupBy(b => new { b.Name, b.Year, b.Month, b.DaysInMonth }) .Select(g => new { g.Key.Name, g.Key.Year, g.Key.Month, Utilization = SqlDbFunctionsExtensions.SqlRound( 100 * g.Sum(b => b.Slots) / (decimal)(25 * g.Key.DaysInMonth), 1) }) .OrderBy(r => r.Name) .ThenBy(r => r.Year) .ThenBy(r => r.Month) .ToList();
در این کوئری، از متد SqlDbFunctionsExtensions.SqlRound نیز استفاده شدهاست. روش تعریف این نوع متدها را در مطلب «امکان تعریف توابع خاص بانکهای اطلاعاتی در EF Core» پیشتر بررسی کردهایم که برای مثال در اینجا چنین شکلی را پیدا میکند:
namespace EFCorePgExercises.Utils { public static class SqlDbFunctionsExtensions { public static decimal SqlRound(decimal value, int precision) => throw new InvalidOperationException($"{nameof(SqlRound)} method cannot be called from the client side."); private static readonly MethodInfo _sqlRoundMethodInfo = typeof(SqlDbFunctionsExtensions) .GetRuntimeMethod( nameof(SqlDbFunctionsExtensions.SqlRound), new[] { typeof(decimal), typeof(int) } ); public static void AddCustomSqlFunctions(this ModelBuilder modelBuilder) { modelBuilder.HasDbFunction(_sqlRoundMethodInfo) .HasTranslation(args => { return SqlFunctionExpression.Create("ROUND", args, _sqlRoundMethodInfo.ReturnType, typeMapping: null); }); } } }
namespace EFCorePgExercises.DataLayer { public class ApplicationDbContext : DbContext { // ... protected override void OnModelCreating(ModelBuilder modelBuilder) { // ... modelBuilder.AddCustomSqlFunctions(); // ... } } }
کدهای کامل این قسمت را در اینجا میتوانید مشاهده کنید.