public void Test()
{
// https://pgexercises.com/questions/updates/insert.html
// The club is adding a new facility - a spa. We need to add it into the facilities table.
// Use the following values:
// facid: 9, Name: 'Spa', membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800.
// insert into cd.facilities
// (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
// values (9, 'Spa', 20, 30, 100000, 800);
//
// insert into cd.facilities values (9, 'Spa', 20, 30, 100000, 800);
EFServiceProvider.RunInContext(context =>
{
context.Facilities.Add(new Facility
{
Name = "Spa",
MemberCost = 20,
GuestCost = 30,
InitialOutlay = 100000,
MonthlyMaintenance = 800
});
context.SaveChanges();
});
}
public void Test()
{
// https://pgexercises.com/questions/date/timestamp.html
// Produce a timestamp for 1 a.m. on the 31st of August 2012.
//
// select timestamp '2012-08-31 01:00:00';
// select '2012-08-31 01:00:00'::timestamp;
// select cast('2012-08-31 01:00:00' as timestamp);
EFServiceProvider.RunInContext(context =>
{
var date1 = new DateTime(2012, 08, 31, 01, 00, 00);
var item = context.Bookings
.Where(x => x.StartTime >= date1)
.Select(x => new
{
Timestamp = date1,
x.StartTime
}).FirstOrDefault();
/*
* SELECT TOP(1) @__date1_0 AS [Timestamp], [b].[StartTime]
* FROM [Bookings] AS [b]
* WHERE [b].[StartTime] >= @__date1_0
*/
Console.WriteLine($"Timestamp: {item.Timestamp}, StartTime: {item.StartTime}");
});
}
public void Test()
{
// https://pgexercises.com/questions/date/extract.html
// Get the day of the month from the timestamp '2012-08-31' as an integer.
//
// select extract(day from timestamp '2012-08-31');
EFServiceProvider.RunInContext(context =>
{
var date1 = new DateTime(2012, 08, 31, 01, 00, 00);
var item = context.Bookings
.Where(x => x.StartTime >= date1)
.Select(x => new
{
date1.Day,
x.StartTime
}).FirstOrDefault();
/*
* SELECT TOP(1) @__date1_Day_1 AS [Day], [b].[StartTime]
* FROM [Bookings] AS [b]
* WHERE [b].[StartTime] >= @__date1_0
*/
Console.WriteLine($"Day: {item.Day}, StartTime: {item.StartTime}");
});
}
public void Test()
{
// https://pgexercises.com/questions/basic/selectspecific.html
// You want to print out a list of all of the facilities and their cost to members. How would you retrieve a list of only facility names and costs?
// select name, membercost from cd.facilities;
EFServiceProvider.RunInContext(context =>
{
var facilities = context.Facilities.Select(x =>
new
{
x.Name,
x.MemberCost
}).ToList();
/*
* SELECT [f].[Name], [f].[MemberCost]
* FROM [Facilities] AS [f]
*/
var expectedResult = new[]
{
new { Name = "Tennis Court 1", MemberCost = 5M },
new { Name = "Tennis Court 2", MemberCost = 5M },
new { Name = "Badminton Court", MemberCost = 0M },
new { Name = "Table Tennis", MemberCost = 0M },
new { Name = "Massage Room 1", MemberCost = 35M },
new { Name = "Massage Room 2", MemberCost = 35M },
new { Name = "Squash Court", MemberCost = 3.5M },
new { Name = "Snooker Table", MemberCost = 0M },
new { Name = "Pool Table", MemberCost = 0M }
};
facilities.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/basic/where4.html
// How can you retrieve the details of facilities with ID 1 and 5? Try to do it without using the OR operator.
// select * from cd.facilities where facid in (1,5);
EFServiceProvider.RunInContext(context =>
{
int[] ids = { 1, 5 };
var facilities = context.Facilities.Where(x => ids.Contains(x.FacId)).ToList();
/*
* SELECT [f].[FacId], [f].[GuestCost], [f].[InitialOutlay], [f].[MemberCost], [f].[MonthlyMaintenance], [f].[Name]
* FROM [Facilities] AS [f]
* WHERE [f].[FacId] IN (1, 5)
*/
var expectedResult = new[]
{
new Facility {
FacId = 1, Name = "Tennis Court 2", MemberCost = 5, GuestCost = 25, InitialOutlay = 8000, MonthlyMaintenance = 200
},
new Facility {
FacId = 5, Name = "Massage Room 2", MemberCost = 35, GuestCost = 80, InitialOutlay = 4000, MonthlyMaintenance = 3000
}
};
facilities.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/date/interval.html
// Find the result of subtracting the timestamp '2012-07-30 01:00:00' from
// the timestamp '2012-08-31 01:00:00'
//
// select timestamp '2012-08-31 01:00:00' - timestamp '2012-07-30 01:00:00' as interval;
EFServiceProvider.RunInContext(context =>
{
var date1 = new DateTime(2012, 08, 31, 01, 00, 00);
var date2 = new DateTime(2012, 07, 30, 01, 00, 00);
var item = context.Bookings
.Where(x => x.StartTime >= date2 && x.StartTime <= date1)
.Select(x => new
{
Interval = (date1 - date2).Days,
x.StartTime
}).FirstOrDefault();
/*
* Executed DbCommand (114ms) [Parameters=[@__Days_2='32', @__date2_0='2012-07-30T01:00:00', @__date1_1='2012-08-31T01:00:00'], CommandType='Text', CommandTimeout='30']
* SELECT TOP(1) @__Days_2 AS [Interval], [b].[StartTime]
* FROM [Bookings] AS [b]
* WHERE ([b].[StartTime] >= @__date2_0) AND ([b].[StartTime] <= @__date1_1)
*/
Console.WriteLine($"Interval: {item.Interval}");
});
}
public void Test()
{
// https://pgexercises.com/questions/basic/where3.html
// How can you produce a list of all facilities with the word 'Tennis' in their name?
// select * from cd.facilities where name like '%Tennis%';
EFServiceProvider.RunInContext(context =>
{
var facilities = context.Facilities.Where(x => x.Name.Contains("Tennis")).ToList();
/*
* SELECT [f].[FacId], [f].[GuestCost], [f].[InitialOutlay], [f].[MemberCost], [f].[MonthlyMaintenance], [f].[Name]
* FROM [Facilities] AS [f]
* WHERE CHARINDEX(N'Tennis', [f].[Name]) > 0
*/
var expectedResult = new[]
{
new Facility {
FacId = 0, Name = "Tennis Court 1", MemberCost = 5, GuestCost = 25, InitialOutlay = 10000, MonthlyMaintenance = 200
},
new Facility {
FacId = 1, Name = "Tennis Court 2", MemberCost = 5, GuestCost = 25, InitialOutlay = 8000, MonthlyMaintenance = 200
},
new Facility {
FacId = 3, Name = "Table Tennis", MemberCost = 0, GuestCost = 5, InitialOutlay = 320, MonthlyMaintenance = 10
}
};
facilities.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/string/like.html
// Find all facilities whose name begins with 'Tennis'. Retrieve all columns.
//
// select * from cd.facilities where name like 'Tennis%';
EFServiceProvider.RunInContext(context =>
{
var facilities = context.Facilities
.Where(facility => facility.Name.StartsWith("Tennis"))
.ToList();
/*
* SELECT [f].[FacId], [f].[GuestCost], [f].[InitialOutlay], [f].[MemberCost], [f].[MonthlyMaintenance], [f].[Name]
* FROM [Facilities] AS [f]
* WHERE [f].[Name] LIKE N'Tennis%'
*/
var expectedResult = new[]
{
new Facility {
FacId = 0, Name = "Tennis Court 1", MemberCost = 5, GuestCost = 25, InitialOutlay = 10000, MonthlyMaintenance = 200
},
new Facility {
FacId = 1, Name = "Tennis Court 2", MemberCost = 5, GuestCost = 25, InitialOutlay = 8000, MonthlyMaintenance = 200
}
};
facilities.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/aggregates/fachours2.html
// Output the facility id that has the highest number of slots booked.
//select facid, sum(slots) as "Total Slots"
// from cd.bookings
// group by facid
//order by sum(slots) desc
//LIMIT 1;
EFServiceProvider.RunInContext(context =>
{
var item = context.Bookings
.GroupBy(booking => booking.FacId)
.Select(group => new
{
FacId = group.Key,
TotalSlots = group.Sum(booking => booking.Slots)
})
.OrderByDescending(result => result.TotalSlots)
.FirstOrDefault();
/*
* SELECT TOP(1) [b].[FacId], SUM([b].[Slots]) AS [TotalSlots]
* FROM [Bookings] AS [b]
* GROUP BY [b].[FacId]
* ORDER BY SUM([b].[Slots]) DESC
*/
var expectedResult = new { FacId = 4, TotalSlots = 1404 };
item.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/updates/insert2.html
// In the previous exercise, you learned how to add a facility.
// Now you're going to add multiple facilities in one command. Use the following values:
//facid: 9, Name: 'Spa', membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800.
//facid: 10, Name: 'Squash Court 2', membercost: 3.5, guestcost: 17.5, initialoutlay: 5000, monthlymaintenance: 80.
//insert into cd.facilities
// (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
// values
// (9, 'Spa', 20, 30, 100000, 800),
// (10, 'Squash Court 2', 3.5, 17.5, 5000, 80);
//
//insert into cd.facilities
// (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
// SELECT 9, 'Spa', 20, 30, 100000, 800
// UNION ALL
// SELECT 10, 'Squash Court 2', 3.5, 17.5, 5000, 80;
EFServiceProvider.RunInContext(context =>
{
context.Facilities.Add(new Facility
{
Name = "Squash Court 2",
MemberCost = 3.5M,
GuestCost = 17.5M,
InitialOutlay = 5000,
MonthlyMaintenance = 80
});
context.SaveChanges();
});
}
public void Test()
{
// https://pgexercises.com/questions/updates/updatecalculated.html
// We want to alter the price of the second tennis court so that it costs 10% more than the first one.
// Try to do this without using constant values for the prices, so that we can reuse the statement if we want to.
//update cd.facilities facs
// set
// membercost = (select membercost * 1.1 from cd.facilities where facid = 0),
// guestcost = (select guestcost * 1.1 from cd.facilities where facid = 0)
// where facs.facid = 1;
//
//update cd.facilities facs
// set
// membercost = facs2.membercost * 1.1,
// guestcost = facs2.guestcost * 1.1
// from (select * from cd.facilities where facid = 0) facs2
// where facs.facid = 1;
EFServiceProvider.RunInContext(context =>
{
var fac0 = context.Facilities.Where(x => x.FacId == 0).First();
var fac1 = context.Facilities.Where(x => x.FacId == 1).First();
fac1.MemberCost = fac0.MemberCost * 1.1M;
fac1.GuestCost = fac0.GuestCost * 1.1M;
context.SaveChanges();
});
}
public void Test()
{
// https://pgexercises.com/questions/basic/where2.html
// How can you produce a list of facilities that charge a fee to members, and that fee is less than 1/50th of the monthly maintenance cost?
// Return the facid, facility name, member cost, and monthly maintenance of the facilities in question.
// select facid, name, membercost, monthlymaintenance
// from cd.facilities where membercost > 0 and (membercost < monthlymaintenance/50.0);
EFServiceProvider.RunInContext(context =>
{
var facilities = context.Facilities.Where(x => x.MemberCost > 0 &&
x.MemberCost < (x.MonthlyMaintenance / 50))
.Select(x =>
new
{
x.FacId,
x.Name,
x.MemberCost,
x.MonthlyMaintenance
}).ToList();
/*
* SELECT [f].[FacId], [f].[Name], [f].[MemberCost], [f].[MonthlyMaintenance]
* FROM [Facilities] AS [f]
* WHERE ([f].[MemberCost] > 0.0) AND ([f].[MemberCost] < ([f].[MonthlyMaintenance] / 50.0))
*/
var expectedResult = new[]
{
new { FacId = 4, Name = "Massage Room 1", MemberCost = 35M, MonthlyMaintenance = 3000M },
new { FacId = 5, Name = "Massage Room 2", MemberCost = 35M, MonthlyMaintenance = 3000M },
};
facilities.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/date/series.html
// Produce a list of all the dates in October 2012. They can be output as
// a timestamp (with time set to midnight) or a date.
//
// select generate_series(timestamp '2012-10-01', timestamp '2012-10-31', interval '1 day') as ts;
EFServiceProvider.RunInContext(context =>
{
var date1 = new DateTime(2012, 10, 01);
var date2 = new DateTime(2012, 10, 31);
var items = context.Bookings
.Where(x => x.StartTime >= date1 && x.StartTime <= date2)
.Select(x => new { x.StartTime.Date })
.Distinct()
.ToList();
/*
* SELECT DISTINCT CONVERT(date, [b].[StartTime]) AS [Date]
* FROM [Bookings] AS [b]
* WHERE ([b].[StartTime] >= @__date1_0) AND ([b].[StartTime] <= @__date2_1)
*/
foreach (var item in items)
{
Console.WriteLine($"Date: {item.Date}");
}
});
}
public void Test()
{
// https://pgexercises.com/questions/string/case.html
// Perform a case-insensitive search to find all facilities whose name begins with 'tennis'.
// Retrieve all columns.
//
// select * from cd.facilities where upper(name) like 'TENNIS%';
EFServiceProvider.RunInContext(context =>
{
// `case-insensitive` search is default in SQL-Server's selected collation.
var facilities = context.Facilities
.Where(facility => facility.Name.StartsWith("TENNIS"))
.ToList();
var expectedResult = new[]
{
new Facility {
FacId = 0, Name = "Tennis Court 1", MemberCost = 5, GuestCost = 25, InitialOutlay = 10000, MonthlyMaintenance = 200
},
new Facility {
FacId = 1, Name = "Tennis Court 2", MemberCost = 5, GuestCost = 25, InitialOutlay = 8000, MonthlyMaintenance = 200
}
};
facilities.Should().BeEquivalentTo(expectedResult);
});
}
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_Method1()
{
// https://pgexercises.com/questions/recursive/getupward.html
// Find the upward recommendation chain for member ID 27: that is, the member who recommended them,
// and the member who recommended that member, and so on. Return member ID, first name,
// and surname. Order by descending member id.
//
//with recursive recommenders(recommender) as (
// select recommendedby from cd.members where memid = 27
// union all
// select mems.recommendedby
// from recommenders recs
// inner join cd.members mems
// on mems.memid = recs.recommender
//)
//select recs.recommender, mems.firstname, mems.surname
// from recommenders recs
// inner join cd.members mems
// on recs.recommender = mems.memid
//order by memid desc
//
//with recursive increment(num) as (
// select 1
// union all
// select increment.num + 1 from increment where increment.num < 5
//)
//select * from increment;
EFServiceProvider.RunInContext(context =>
{
var id = 27;
var entity27WithAllOfItsParents =
context.Members
.Where(member => member.MemId == id ||
member.Children.Any(m => member.MemId == m.RecommendedBy))
.ToList() //It's a MUST - get all children from the database
.FirstOrDefault(x => x.MemId == id); // then get the root of the tree
/*
* SELECT [m].[MemId], [m].[Address], [m].[FirstName], [m].[JoinDate], [m].[RecommendedBy], [m].[Surname], [m].[Telephone], [m].[ZipCode]
* FROM [Members] AS [m]
* WHERE ([m].[MemId] = @__id_0) OR EXISTS (
* SELECT 1
* FROM [Members] AS [m0]
* WHERE ([m].[MemId] = [m0].[RecommendedBy]) AND ([m].[MemId] = [m0].[RecommendedBy]))
*/
var expectedResult = new[]
{
new { Recommender = 20, FirstName = "Matthew", Surname = "Genting" },
new { Recommender = 5, FirstName = "Gerald", Surname = "Butters" },
new { Recommender = 1, FirstName = "Darren", Surname = "Smith" }
};
var actualResult = new List <dynamic>();
RecursiveUtils.FindParents(entity27WithAllOfItsParents, actualResult);
actualResult.Should().BeEquivalentTo(expectedResult);
});
}
public void Test_Method1()
{
EFServiceProvider.RunInContext(context =>
{
// The list of facilities that are booked only in 2012.
//
//SELECT Name
//FROM Facilities
//WHERE FacId IN (SELECT FacId
// FROM Bookings
// EXCEPT
// SELECT FacId
// FROM Bookings
// WHERE YEAR(StartTime) <> 2012);
var facilitiesBookedNotIn2012Query = context.Bookings
.Where(booking => booking.StartTime.Year != 2012)
.Select(Booking => Booking.FacId);
var facilitiesBookedOnlyIn2012Query = context.Bookings
.Where(booking => !facilitiesBookedNotIn2012Query.Contains(booking.FacId))
.Select(Booking => Booking.FacId);
var facilitiesBookedOnlyIn2012 = context.Facilities
.Where(facility => facilitiesBookedOnlyIn2012Query.Contains(facility.FacId))
.Select(facility => facility.Name)
.ToList();
/*
* SELECT [f].[Name]
* FROM [Facilities] AS [f]
* WHERE [f].[FacId] IN (
* SELECT [b].[FacId]
* FROM [Bookings] AS [b]
* WHERE [b].[FacId] NOT IN (
* SELECT [b0].[FacId]
* FROM [Bookings] AS [b0]
* WHERE (DATEPART(year, [b0].[StartTime]) <> 2012) OR DATEPART(year, [b0].[StartTime]) IS NULL
* )
* )
*/
string[] expectedResult =
{
"Tennis Court 1",
"Tennis Court 2",
"Badminton Court",
"Table Tennis",
"Massage Room 1",
"Massage Room 2",
"Squash Court",
"Snooker Table"
};
facilitiesBookedOnlyIn2012.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/basic/union.html
// You, for some reason, want a combined list of all surnames and all facility names.
// Yes, this is a contrived example :-). Produce that list!
// select surname from cd.members
// union
// select name from cd.facilities;
EFServiceProvider.RunInContext(context =>
{
var names = context.Members.Select(m => m.Surname).ToList()
.Union(context.Facilities.Select(f => f.Name).ToList()) // For now we have to use `.ToList()` here
.ToList();
string[] expectedResult =
{
"Tennis Court 2",
"Worthington-Smyth",
"Badminton Court",
"Pinker",
"Dare",
"Bader",
"Mackenzie",
"Crumpet",
"Massage Room 1",
"Squash Court",
"Tracy",
"Hunt",
"Tupperware",
"Smith",
"Butters",
"Rownam",
"Baker",
"Genting",
"Purview",
"Coplin",
"Massage Room 2",
"Joplette",
"Stibbons",
"Rumney",
"Pool Table",
"Sarwin",
"Boothe",
"Farrell",
"Tennis Court 1",
"Snooker Table",
"Owen",
"Table Tennis",
"GUEST",
"Jones"
};
names.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/string/concat.html
// Output the names of all members, formatted as 'Surname, Firstname'
//
// select surname || ', ' || firstname as name from cd.members
EFServiceProvider.RunInContext(context =>
{
var members = context.Members
.Select(member => new { Name = member.Surname + ", " + member.FirstName })
.ToList();
/*
* SELECT ([m].[Surname] + N', ') + [m].[FirstName] AS [Name]
* FROM [Members] AS [m]
*/
var expectedResult = new[]
{
new { Name = "GUEST, GUEST" },
new { Name = "Smith, Darren" },
new { Name = "Smith, Tracy" },
new { Name = "Rownam, Tim" },
new { Name = "Joplette, Janice" },
new { Name = "Butters, Gerald" },
new { Name = "Tracy, Burton" },
new { Name = "Dare, Nancy" },
new { Name = "Boothe, Tim" },
new { Name = "Stibbons, Ponder" },
new { Name = "Owen, Charles" },
new { Name = "Jones, David" },
new { Name = "Baker, Anne" },
new { Name = "Farrell, Jemima" },
new { Name = "Smith, Jack" },
new { Name = "Bader, Florence" },
new { Name = "Baker, Timothy" },
new { Name = "Pinker, David" },
new { Name = "Genting, Matthew" },
new { Name = "Mackenzie, Anna" },
new { Name = "Coplin, Joan" },
new { Name = "Sarwin, Ramnaresh" },
new { Name = "Jones, Douglas" },
new { Name = "Rumney, Henrietta" },
new { Name = "Farrell, David" },
new { Name = "Worthington-Smyth, Henry" },
new { Name = "Purview, Millicent" },
new { Name = "Tupperware, Hyacinth" },
new { Name = "Hunt, John" },
new { Name = "Crumpet, Erica" },
new { Name = "Smith, Darren" }
};
members.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/date/daysinmonth.html
// For each month of the year in 2012, output the number of days in that month.
// Format the output as an integer column containing the month of the year, and a
// second column containing an interval data type.
//
//select extract(month from cal.month) as month,
// (cal.month + interval '1 month') - cal.month as length
// from
// (
// select generate_series(timestamp '2012-01-01', timestamp '2012-12-01', interval '1 month') as month
// ) cal
//order by month;
EFServiceProvider.RunInContext(context =>
{
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();
/*
* SELECT [t].[c] AS [Year],
* [t].[c0] AS [Month],
* [t].[c1] AS [DaysInMonth]
* FROM (SELECT DISTINCT DATEPART(year, [b].[StartTime]) AS [c],
* DATEPART(month, [b].[StartTime]) AS [c0],
* 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 [c1]
* FROM [Bookings] AS [b]
* WHERE DATEPART(year, [b].[StartTime]) = 2012) AS [t]
* ORDER BY [t].[c], [t].[c0];
*/
var expectedResult = new[]
{
new { Year = 2012, Month = 7, DaysInMonth = 31 },
new { Year = 2012, Month = 8, DaysInMonth = 31 },
new { Year = 2012, Month = 9, DaysInMonth = 30 }
};
items.Should().BeEquivalentTo(expectedResult);
});
}
public static void Start()
{
EFServiceProvider.RunInContext(context =>
{
context.Database.Migrate();
createFtsTables(context);
seedDb(context);
});
}
public void Test()
{
// https://pgexercises.com/questions/aggregates/fachoursbymonth.html
// Produce a list of the total number of slots booked per facility in the month of September 2012.
// Produce an output table consisting of facility id and slots, sorted by the number of slots.
//select facid, sum(slots) as "Total Slots"
// from cd.bookings
// where
// starttime >= '2012-09-01'
// and starttime < '2012-10-01'
// group by facid
//order by sum(slots);
EFServiceProvider.RunInContext(context =>
{
var date1 = new DateTime(2012, 09, 01);
var date2 = new DateTime(2012, 10, 01);
var facilities = context.Bookings
.Where(booking => booking.StartTime >= date1 &&
booking.StartTime < date2)
.GroupBy(booking => booking.FacId)
.Select(group => new
{
FacId = group.Key,
TotalSlots = group.Sum(booking => booking.Slots)
})
.OrderBy(result => result.TotalSlots)
.ToList();
/*
* SELECT [b].[FacId], SUM([b].[Slots]) AS [TotalSlots]
* FROM [Bookings] AS [b]
* WHERE ([b].[StartTime] >= @__date1_0) AND ([b].[StartTime] < @__date2_1)
* GROUP BY [b].[FacId]
* ORDER BY SUM([b].[Slots])
*/
var expectedResult = new[]
{
new { FacId = 5, TotalSlots = 122 },
new { FacId = 3, TotalSlots = 422 },
new { FacId = 7, TotalSlots = 426 },
new { FacId = 8, TotalSlots = 471 },
new { FacId = 6, TotalSlots = 540 },
new { FacId = 2, TotalSlots = 570 },
new { FacId = 1, TotalSlots = 588 },
new { FacId = 0, TotalSlots = 591 },
new { FacId = 4, TotalSlots = 648 }
};
facilities.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/joins/simplejoin2.html
// How can you produce a list of the start times for bookings for tennis courts,
// for the date '2012-09-21'? Return a list of start time and facility name pairings,
// ordered by the time.
//select bks.starttime as start, facs.name as name
// from
// cd.facilities facs
// inner join cd.bookings bks
// on facs.facid = bks.facid
// where
// facs.facid in (0,1) and
// bks.starttime >= '2012-09-21' and
// bks.starttime < '2012-09-22'
//order by bks.starttime;
EFServiceProvider.RunInContext(context =>
{
int[] tennisCourts = { 0, 1 };
var date1 = new DateTime(2012, 09, 21);
var date2 = new DateTime(2012, 09, 22);
var startTimes = context.Bookings
.Where(booking => tennisCourts.Contains(booking.Facility.FacId) &&
booking.StartTime >= date1 &&
booking.StartTime < date2)
.Select(booking => new { booking.StartTime, booking.Facility.Name })
.ToList();
/*
* SELECT [b].[StartTime], [f].[Name]
* FROM [Bookings] AS [b]
* INNER JOIN [Facilities] AS [f] ON [b].[FacId] = [f].[FacId]
* WHERE ([f].[FacId] IN (0, 1) AND ([b].[StartTime] >= @__date1_1)) AND ([b].[StartTime] < @__date2_2)
*/
var expectedResult = new[]
{
new { StartTime = DateTime.Parse("2012-09-21 08:00:00", CultureInfo.InvariantCulture), Name = "Tennis Court 1" },
new { StartTime = DateTime.Parse("2012-09-21 08:00:00", CultureInfo.InvariantCulture), Name = "Tennis Court 2" },
new { StartTime = DateTime.Parse("2012-09-21 09:30:00", CultureInfo.InvariantCulture), Name = "Tennis Court 1" },
new { StartTime = DateTime.Parse("2012-09-21 10:00:00", CultureInfo.InvariantCulture), Name = "Tennis Court 2" },
new { StartTime = DateTime.Parse("2012-09-21 11:30:00", CultureInfo.InvariantCulture), Name = "Tennis Court 2" },
new { StartTime = DateTime.Parse("2012-09-21 12:00:00", CultureInfo.InvariantCulture), Name = "Tennis Court 1" },
new { StartTime = DateTime.Parse("2012-09-21 13:30:00", CultureInfo.InvariantCulture), Name = "Tennis Court 1" },
new { StartTime = DateTime.Parse("2012-09-21 14:00:00", CultureInfo.InvariantCulture), Name = "Tennis Court 2" },
new { StartTime = DateTime.Parse("2012-09-21 15:30:00", CultureInfo.InvariantCulture), Name = "Tennis Court 1" },
new { StartTime = DateTime.Parse("2012-09-21 16:00:00", CultureInfo.InvariantCulture), Name = "Tennis Court 2" },
new { StartTime = DateTime.Parse("2012-09-21 17:00:00", CultureInfo.InvariantCulture), Name = "Tennis Court 1" },
new { StartTime = DateTime.Parse("2012-09-21 18:00:00", CultureInfo.InvariantCulture), Name = "Tennis Court 2" }
};
startTimes.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/aggregates/fachours4.html
// Output the facility id that has the highest number of slots booked.
// Ensure that in the event of a tie, all tieing results get output.
//select facid, total from (
// select facid, sum(slots) total, rank() over (order by sum(slots) desc) rank
// from cd.bookings
// group by facid
// ) as ranked
// where rank = 1
//
//
//select facid, sum(slots) as totalslots
// from cd.bookings
// group by facid
// having sum(slots) = (select max(sum2.totalslots) from
// (select sum(slots) as totalslots
// from cd.bookings
// group by facid
// ) as sum2);
//
//
//select facid, total from (
// select facid, total, rank() over (order by total desc) rank from (
// select facid, sum(slots) total
// from cd.bookings
// group by facid
// ) as sumslots
// ) as ranked
//where rank = 1
EFServiceProvider.RunInContext(context =>
{
var item = context.Bookings
.GroupBy(booking => booking.FacId)
.Select(group => new
{
FacId = group.Key,
TotalSlots = group.Sum(booking => booking.Slots)
})
.OrderByDescending(result => result.TotalSlots)
.FirstOrDefault();
/*
* SELECT TOP(1) [b].[FacId], SUM([b].[Slots]) AS [TotalSlots]
* FROM [Bookings] AS [b]
* GROUP BY [b].[FacId]
* ORDER BY SUM([b].[Slots]) DESC
*/
var expectedResult = new { FacId = 4, TotalSlots = 1404 };
item.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/aggregates/fachours3.html
// Produce a list of the total number of hours booked per facility,
// remembering that a slot lasts half an hour. The output table should
// consist of the facility id, name, and hours booked, sorted by facility id.
// Try formatting the hours to two decimal places.
//
//select facs.facid, facs.name,
// trim(to_char(sum(bks.slots)/2.0, '9999999999999999D99')) as "Total Hours"
// from cd.bookings bks
// inner join cd.facilities facs
// on facs.facid = bks.facid
// group by facs.facid, facs.name
//order by facs.facid;
EFServiceProvider.RunInContext(context =>
{
var items = context.Bookings
.GroupBy(booking => new { booking.FacId, booking.Facility.Name })
.Select(group => new
{
group.Key.FacId,
group.Key.Name,
TotalHours = group.Sum(booking => booking.Slots) / 2M
})
.OrderBy(result => result.FacId)
.ToList();
/*
* SELECT [b].[FacId], [f].[Name], SUM([b].[Slots]) / 2 AS [TotalHours]
* FROM [Bookings] AS [b]
* INNER JOIN [Facilities] AS [f] ON [b].[FacId] = [f].[FacId]
* GROUP BY [b].[FacId], [f].[Name]
* ORDER BY [b].[FacId]
*/
var expectedResult = new[]
{
new { FacId = 0, Name = "Tennis Court 1", TotalHours = 660.00M },
new { FacId = 1, Name = "Tennis Court 2", TotalHours = 639.00M },
new { FacId = 2, Name = "Badminton Court", TotalHours = 604.50M },
new { FacId = 3, Name = "Table Tennis", TotalHours = 415.00M },
new { FacId = 4, Name = "Massage Room 1", TotalHours = 702.00M },
new { FacId = 5, Name = "Massage Room 2", TotalHours = 114.00M },
new { FacId = 6, Name = "Squash Court", TotalHours = 552.00M },
new { FacId = 7, Name = "Snooker Table", TotalHours = 454.00M },
new { FacId = 8, Name = "Pool Table", TotalHours = 455.50M }
};
items.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/joins/self.html
// How can you output a list of all members who have recommended another member?
// Ensure that there are no duplicates in the list, and that results are ordered by (surname, firstname).
//select distinct recs.firstname as firstname, recs.surname as surname
// from
// cd.members mems
// inner join cd.members recs
// on recs.memid = mems.recommendedby
//order by surname, firstname;
EFServiceProvider.RunInContext(context =>
{
var members = context.Members
.Where(member => member.Recommender != null)
.Select(member => new { member.Recommender.FirstName, member.Recommender.Surname })
.Distinct()
.OrderBy(member => member.Surname).ThenBy(member => member.FirstName)
.ToList();
/*
* SELECT [t].[FirstName], [t].[Surname]
* FROM (
* SELECT DISTINCT [m0].[FirstName], [m0].[Surname]
* FROM [Members] AS [m]
* LEFT JOIN [Members] AS [m0] ON [m].[RecommendedBy] = [m0].[MemId]
* WHERE [m0].[MemId] IS NOT NULL
* ) AS [t]
* ORDER BY [t].[Surname], [t].[FirstName]
*/
var expectedResult = new[]
{
new { FirstName = "Florence", Surname = "Bader" },
new { FirstName = "Timothy", Surname = "Baker" },
new { FirstName = "Gerald", Surname = "Butters" },
new { FirstName = "Jemima", Surname = "Farrell" },
new { FirstName = "Matthew", Surname = "Genting" },
new { FirstName = "David", Surname = "Jones" },
new { FirstName = "Janice", Surname = "Joplette" },
new { FirstName = "Millicent", Surname = "Purview" },
new { FirstName = "Tim", Surname = "Rownam" },
new { FirstName = "Darren", Surname = "Smith" },
new { FirstName = "Tracy", Surname = "Smith" },
new { FirstName = "Ponder", Surname = "Stibbons" },
new { FirstName = "Burton", Surname = "Tracy" }
};
members.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/string/substr.html
// You'd like to produce a count of how many members you have whose surname starts with
// each letter of the alphabet. Sort by the letter, and don't worry about printing out a
// letter if the count is 0.
//
//select substr (mems.surname,1,1) as letter, count(*) as count
// from cd.members mems
// group by letter
// order by letter
EFServiceProvider.RunInContext(context =>
{
var members = context.Members
.Select(member => new { Letter = member.Surname.Substring(0, 1) })
.GroupBy(m => m.Letter)
.Select(g => new
{
Letter = g.Key,
Count = g.Count()
})
.OrderBy(r => r.Letter)
.ToList();
/*
* SELECT SUBSTRING([m].[Surname], 0 + 1, 1) AS [Letter], COUNT(*) AS [Count]
* FROM [Members] AS [m]
* GROUP BY SUBSTRING([m].[Surname], 0 + 1, 1)
* ORDER BY SUBSTRING([m].[Surname], 0 + 1, 1)
*/
var expectedResult = new[]
{
new { Letter = "B", Count = 5 },
new { Letter = "C", Count = 2 },
new { Letter = "D", Count = 1 },
new { Letter = "F", Count = 2 },
new { Letter = "G", Count = 2 },
new { Letter = "H", Count = 1 },
new { Letter = "J", Count = 3 },
new { Letter = "M", Count = 1 },
new { Letter = "O", Count = 1 },
new { Letter = "P", Count = 2 },
new { Letter = "R", Count = 2 },
new { Letter = "S", Count = 6 },
new { Letter = "T", Count = 2 },
new { Letter = "W", Count = 1 }
};
members.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/basic/agg.html
// You'd like to get the signup date of your last member. How can you retrieve this information?
// select max(joindate) as latest from cd.members;
EFServiceProvider.RunInContext(context =>
{
var latest = context.Members.Max(x => x.JoinDate);
/*
* Returns null ― for nullable overloads
* Throws Sequence contains no element exception ― for non-nullable overloads
*/
/*
* SELECT MAX([m].[JoinDate])
* FROM [Members] AS [m]
*/
var expectedResult = DateTime.Parse("2012-09-26 18:08:45", CultureInfo.InvariantCulture);
latest.Should().Be(expectedResult);
var latest2 = context.Members.Select(m => m.JoinDate).DefaultIfEmpty().Max();
/*
* SELECT MAX([m].[JoinDate])
* FROM (
* SELECT NULL AS [empty]
* ) AS [empty]
* LEFT JOIN [Members] AS [m] ON 1 = 1
*/
latest2.Should().Be(expectedResult);
var latest3 = context.Members.Max(m => (DateTime?)m.JoinDate) ?? DateTime.Now;
/*
* SELECT MAX([m].[JoinDate])
* FROM [Members] AS [m]
*/
latest3.Should().Be(expectedResult);
var latest4 = context.Members.OrderByDescending(m => m.JoinDate).Select(m => m.JoinDate).FirstOrDefault();
/*
* SELECT TOP(1) [m].[JoinDate]
* FROM [Members] AS [m]
* ORDER BY [m].[JoinDate] DESC
*/
latest4.Should().Be(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/updates/deletewh.html
// We want to remove member 37, who has never made a booking, from our database. How can we achieve that?
// delete from cd.members where memid = 37;
EFServiceProvider.RunInContext(context =>
{
var mem37 = context.Members.Where(x => x.MemId == 37).First();
context.Members.Remove(mem37);
context.SaveChanges();
});
}
public void Test()
{
// https://pgexercises.com/questions/updates/delete.html
// As part of a clearout of our database, we want to delete all bookings from
// the cd.bookings table. How can we accomplish this?
// delete from cd.bookings;
// truncate cd.bookings;
EFServiceProvider.RunInContext(context =>
{
context.Bookings.RemoveRange(context.Bookings.ToList());
context.SaveChanges();
});
}
