public void Test()
{
EFServiceProvider.RunInContext(context =>
{
// Quarterly count of booked items in 2012 and 2013.
//
//SELECT YEAR(StartTime) AS [Year],
// SUM(CASE WHEN DATEPART(QUARTER, StartTime) = 1 THEN 1 ELSE 0 END) AS FirstQuarter,
// SUM(CASE WHEN DATEPART(QUARTER, StartTime) = 2 THEN 1 ELSE 0 END) AS SecondQuarter,
// SUM(CASE WHEN DATEPART(QUARTER, StartTime) = 3 THEN 1 ELSE 0 END) AS ThirdQuarter,
// SUM(CASE WHEN DATEPART(QUARTER, StartTime) = 4 THEN 1 ELSE 0 END) AS ForthQuarter
//FROM Bookings
//WHERE YEAR(StartTime) BETWEEN 2012 AND 2013
//GROUP BY YEAR(StartTime);
var quarterlyCountOfBookedItems = context.Bookings
.Select(booking => new
{
booking.StartTime.Year,
Quarter = SqlDbFunctionsExtensions.SqlDatePart(SqlDatePart.Quarter, booking.StartTime)
})
.Where(result => result.Year >= 2012 && result.Year <= 2013)
.GroupBy(result => new
{
result.Year
})
.Select(group => new
{
group.Key.Year,
FirstQuarter = group.Sum(result => result.Quarter == 1 ? 1 : 0),
SecondQuarter = group.Sum(result => result.Quarter == 2 ? 1 : 0),
ThirdQuarter = group.Sum(result => result.Quarter == 3 ? 1 : 0),
ForthQuarter = group.Sum(result => result.Quarter == 4 ? 1 : 0),
})
.OrderBy(result => result.Year)
.ToList();
/*
* SELECT DATEPART(year, [b].[StartTime]) AS [Year],
* SUM(CASE WHEN DATEPART(Quarter, [b].[StartTime]) = 1 THEN 1 ELSE 0 END) AS [FirstQuarter],
* SUM(CASE WHEN DATEPART(Quarter, [b].[StartTime]) = 2 THEN 1 ELSE 0 END) AS [SecondQuarter],
* SUM(CASE WHEN DATEPART(Quarter, [b].[StartTime]) = 3 THEN 1 ELSE 0 END) AS [ThirdQuarter],
* SUM(CASE WHEN DATEPART(Quarter, [b].[StartTime]) = 4 THEN 1 ELSE 0 END) AS [ForthQuarter]
* FROM [Bookings] AS [b]
* WHERE (DATEPART(year, [b].[StartTime]) >= 2012)
* AND (DATEPART(year, [b].[StartTime]) <= 2013)
* GROUP BY DATEPART(year, [b].[StartTime])
* ORDER BY DATEPART(year, [b].[StartTime]);
*/
var expectedResult = new[]
{
new { Year = 2012, FirstQuarter = 0, SecondQuarter = 0, ThirdQuarter = 4043, ForthQuarter = 0 },
new { Year = 2013, FirstQuarter = 1, SecondQuarter = 0, ThirdQuarter = 0, ForthQuarter = 0 }
};
quarterlyCountOfBookedItems.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/string/pad.html
// The zip codes in our example dataset have had leading zeroes removed from them by
// virtue of being stored as a numeric type. Retrieve all zip codes from the members table,
// padding any zip codes less than 5 characters long with leading zeroes. Order by the
// new zip code.
//
// select lpad(cast(zipcode as char(5)),5,'0') zip from cd.members order by zip
EFServiceProvider.RunInContext(context =>
{
// Method - 1
var members = context.Members
.Select(member => new { ZipCode = Convert.ToString(member.ZipCode) })
.OrderBy(m => m.ZipCode)
.ToList();
/*
* SELECT CONVERT(nvarchar(max), [m].[ZipCode]) AS [Zip]
* FROM [Members] AS [m]
* ORDER BY CONVERT(nvarchar(max), [m].[ZipCode])
*/
// Now using LINQ to Objects
members = members.Select(member => new { ZipCode = member.ZipCode.PadLeft(5, '0') })
.OrderBy(m => m.ZipCode)
.ToList();
var expectedResult = new[]
{
new { ZipCode = "00000" },
new { ZipCode = "00234" },
new { ZipCode = "00234" },
new { ZipCode = "04321" },
new { ZipCode = "04321" },
new { ZipCode = "10383" },
new { ZipCode = "11986" },
new { ZipCode = "23423" },
new { ZipCode = "28563" },
new { ZipCode = "33862" },
new { ZipCode = "34232" },
new { ZipCode = "43532" },
new { ZipCode = "43533" },
new { ZipCode = "45678" },
new { ZipCode = "52365" },
new { ZipCode = "54333" },
new { ZipCode = "56754" },
new { ZipCode = "57392" },
new { ZipCode = "58393" },
new { ZipCode = "64577" },
new { ZipCode = "65332" },
new { ZipCode = "65464" },
new { ZipCode = "66796" },
new { ZipCode = "68666" },
new { ZipCode = "69302" },
new { ZipCode = "75655" },
new { ZipCode = "78533" },
new { ZipCode = "80743" },
new { ZipCode = "84923" },
new { ZipCode = "87630" },
new { ZipCode = "97676" },
};
members.Should().BeEquivalentTo(expectedResult);
// Method - 2, Using a custom DbFunction
var newMembers = context.Members
.Select(member => new
{
ZipCode =
SqlDbFunctionsExtensions.SqlReplicate(
"0", 5 - Convert.ToString(member.ZipCode).Length)
+ member.ZipCode
})
.OrderBy(m => m.ZipCode)
.ToList();
/*
* SELECT REPLICATE(N'0', 5 - CAST(LEN(CONVERT(nvarchar(max), [m].[ZipCode])) AS int)) + CAST([m].[ZipCode] AS nvarchar(max)) AS [Zip]
* FROM [Members] AS [m]
* ORDER BY REPLICATE(N'0', 5 - CAST(LEN(CONVERT(nvarchar(max), [m].[ZipCode])) AS int)) + CAST([m].[ZipCode] AS nvarchar(max))
*/
newMembers.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/date/utilisationpermonth.html
// Work out the utilisation percentage for each facility by month, sorted by name and month,
// rounded to 1 decimal place. Opening time is 8am, closing time is 8.30pm. You can treat
// every month as a full month, regardless of if there were some dates the club was not open.
//
//select name, month,
// round((100*slots)/
// cast(
// 25*(cast((month + interval '1 month') as date)
// - cast (month as date)) as numeric),1) as utilisation
// from (
// select facs.name as name, date_trunc('month', starttime) as month, sum(slots) as slots
// from cd.bookings bks
// inner join cd.facilities facs
// on bks.facid = facs.facid
// group by facs.facid, month
// ) as inn
//order by name, month
EFServiceProvider.RunInContext(context =>
{
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();
/*
* SELECT [f].[Name],
* DATEPART(year, [b].[StartTime]) AS [Year],
* DATEPART(month, [b].[StartTime]) AS [Month],
* ROUND(CAST((100 * SUM([b].[Slots])) AS decimal(18,2))
* / CAST((25 * DATEDIFF(DAY,
* DATEADD(day, CAST(CAST((1 - DATEPART(day, CONVERT(date, [b].[StartTime]))) AS float) AS int),
* CONVERT(date, [b].[StartTime])),
* DATEADD(month, CAST(1 AS int),
* DATEADD(day, CAST(CAST((1 - DATEPART(day, CONVERT(date, [b].[StartTime]))) AS float) AS int),
* CONVERT(date, [b].[StartTime]))))) AS decimal(18,2)), 1) AS [Utilization]
* FROM [Bookings] AS [b]
* INNER JOIN [Facilities] AS [f] ON [b].[FacId] = [f].[FacId]
* GROUP BY [f].[Name],
* DATEPART(year, [b].[StartTime]),
* DATEPART(month, [b].[StartTime]),
* DATEDIFF(DAY, DATEADD(day, CAST(CAST((1 - DATEPART(day, CONVERT(date, [b].[StartTime]))) AS float) AS int), CONVERT(date, [b].[StartTime])), DATEADD(month, CAST(1 AS int), DATEADD(day, CAST(CAST((1 - DATEPART(day, CONVERT(date, [b].[StartTime]))) AS float) AS int), CONVERT(date, [b].[StartTime]))))
* ORDER BY [f].[Name], DATEPART(year, [b].[StartTime]), DATEPART(month, [b].[StartTime])
*/
var expectedResult = new[]
{
new { Name = "Badminton Court", Year = 2012, Month = 7, Utilization = 23.2M },
new { Name = "Badminton Court", Year = 2012, Month = 8, Utilization = 59.2M },
new { Name = "Badminton Court", Year = 2012, Month = 9, Utilization = 76.0M },
new { Name = "Massage Room 1", Year = 2012, Month = 7, Utilization = 34.1M },
new { Name = "Massage Room 1", Year = 2012, Month = 8, Utilization = 63.5M },
new { Name = "Massage Room 1", Year = 2012, Month = 9, Utilization = 86.4M },
new { Name = "Massage Room 2", Year = 2012, Month = 7, Utilization = 3.1M },
new { Name = "Massage Room 2", Year = 2012, Month = 8, Utilization = 10.6M },
new { Name = "Massage Room 2", Year = 2012, Month = 9, Utilization = 16.3M },
new { Name = "Pool Table", Year = 2012, Month = 7, Utilization = 15.1M },
new { Name = "Pool Table", Year = 2012, Month = 8, Utilization = 41.5M },
new { Name = "Pool Table", Year = 2012, Month = 9, Utilization = 62.8M },
new { Name = "Pool Table", Year = 2013, Month = 1, Utilization = 0.1M },
new { Name = "Snooker Table", Year = 2012, Month = 7, Utilization = 20.1M },
new { Name = "Snooker Table", Year = 2012, Month = 8, Utilization = 42.1M },
new { Name = "Snooker Table", Year = 2012, Month = 9, Utilization = 56.8M },
new { Name = "Squash Court", Year = 2012, Month = 7, Utilization = 21.2M },
new { Name = "Squash Court", Year = 2012, Month = 8, Utilization = 51.6M },
new { Name = "Squash Court", Year = 2012, Month = 9, Utilization = 72.0M },
new { Name = "Table Tennis", Year = 2012, Month = 7, Utilization = 13.4M },
new { Name = "Table Tennis", Year = 2012, Month = 8, Utilization = 39.2M },
new { Name = "Table Tennis", Year = 2012, Month = 9, Utilization = 56.3M },
new { Name = "Tennis Court 1", Year = 2012, Month = 7, Utilization = 34.8M },
new { Name = "Tennis Court 1", Year = 2012, Month = 8, Utilization = 59.2M },
new { Name = "Tennis Court 1", Year = 2012, Month = 9, Utilization = 78.8M },
new { Name = "Tennis Court 2", Year = 2012, Month = 7, Utilization = 26.7M },
new { Name = "Tennis Court 2", Year = 2012, Month = 8, Utilization = 62.3M },
new { Name = "Tennis Court 2", Year = 2012, Month = 9, Utilization = 78.4M }
};
items.Should().BeEquivalentTo(expectedResult);
});
}
