public void Test()
{
// https://pgexercises.com/questions/basic/classify.html
// How can you produce a list of facilities, with each labelled as 'cheap' or 'expensive'
// depending on if their monthly maintenance cost is more than $100? Return the name and
// monthly maintenance of the facilities in question.
// select name,
// case when (monthlymaintenance > 100) then 'expensive' else 'cheap' end as cost
// from cd.facilities;
EFServiceProvider.RunInContext(context =>
{
var facilities = context.Facilities
.Select(x =>
new
{
x.Name,
Cost = x.MonthlyMaintenance > 100 ? "expensive" : "cheap"
}).ToList();
/*
* SELECT [f].[Name], CASE
* WHEN [f].[MonthlyMaintenance] > 100.0 THEN N'expensive'
* ELSE N'cheap'
* END AS [Cost]
* FROM [Facilities] AS [f]
*/
var expectedResult = new[]
{
new { Name = "Tennis Court 1", Cost = "expensive" },
new { Name = "Tennis Court 2", Cost = "expensive" },
new { Name = "Badminton Court", Cost = "cheap" },
new { Name = "Table Tennis", Cost = "cheap" },
new { Name = "Massage Room 1", Cost = "expensive" },
new { Name = "Massage Room 2", Cost = "expensive" },
new { Name = "Squash Court", Cost = "cheap" },
new { Name = "Snooker Table", Cost = "cheap" },
new { Name = "Pool Table", Cost = "cheap" }
};
facilities.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/date/bookingspermonth.html
// Return a count of bookings for each month, sorted by month
//
//select date_trunc('month', starttime) as month, count(*)
// from cd.bookings
// group by month
// order by month
EFServiceProvider.RunInContext(context =>
{
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();
/*
* SELECT DATEPART(year, [b].[StartTime]) AS [Year],
* DATEPART(month, [b].[StartTime]) AS [Month],
* COUNT(*) AS [Count]
* FROM [Bookings] AS [b]
* GROUP BY DATEPART(year, [b].[StartTime]), DATEPART(month, [b].[StartTime])
* ORDER BY DATEPART(year, [b].[StartTime]), DATEPART(month, [b].[StartTime])
*/
var expectedResult = new[]
{
new { Year = 2012, Month = 07, Count = 658 },
new { Year = 2012, Month = 08, Count = 1472 },
new { Year = 2012, Month = 09, Count = 1913 },
new { Year = 2013, Month = 01, Count = 1 }
};
items.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/aggregates/members1.html
// Find the total number of members who have made at least one booking.
// select count(distinct memid) from cd.bookings
EFServiceProvider.RunInContext(context =>
{
var count = context.Bookings.Select(booking => booking.MemId).Distinct().Count();
/*
* SELECT COUNT(*)
* FROM (
* SELECT DISTINCT [b].[MemId]
* FROM [Bookings] AS [b]
* ) AS [t]
*/
count.Should().Be(30);
});
}
public ApplicationDbContext CreateDbContext(string[] args)
{
var services = new ServiceCollection();
var basePath = Directory.GetCurrentDirectory();
Console.WriteLine($"Using `{basePath}` as the ContentRootPath");
var configuration = new ConfigurationBuilder()
.SetBasePath(basePath)
.AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
.Build();
services.AddSingleton(_ => configuration);
var optionsBuilder = new DbContextOptionsBuilder <ApplicationDbContext>();
optionsBuilder.UseSqlServer(EFServiceProvider.GetConnectionString(basePath, configuration),
builder => builder.UseNetTopologySuite());
return(new ApplicationDbContext(optionsBuilder.Options));
}
public void Test()
{
// https://pgexercises.com/questions/date/endtimes.html
// Return a list of the start and end time of the last 10 bookings (ordered by the time at which
// they end, followed by the time at which they start) in the system.
//
// select starttime, starttime + slots*(interval '30 minutes') endtime
// from cd.bookings
// order by endtime desc, starttime desc
// limit 10
EFServiceProvider.RunInContext(context =>
{
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();
/*
* SELECT TOP(@__p_0) [b].[StartTime], DATEADD(minute, CAST(CAST(([b].[Slots] * 30) AS float) AS int), [b].[StartTime]) AS [EndTime]
* FROM [Bookings] AS [b]
* ORDER BY DATEADD(minute, CAST(CAST(([b].[Slots] * 30) AS float) AS int), [b].[StartTime]) DESC, [b].[StartTime] DESC
*/
var expectedResult = new[]
{
new { StartTime = DateTime.Parse("2013-01-01 15:30:00", CultureInfo.InvariantCulture), EndTime = DateTime.Parse("2013-01-01 16:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-30 19:30:00", CultureInfo.InvariantCulture), EndTime = DateTime.Parse("2012-09-30 20:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-30 19:00:00", CultureInfo.InvariantCulture), EndTime = DateTime.Parse("2012-09-30 20:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-30 19:30:00", CultureInfo.InvariantCulture), EndTime = DateTime.Parse("2012-09-30 20:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-30 19:00:00", CultureInfo.InvariantCulture), EndTime = DateTime.Parse("2012-09-30 20:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-30 19:00:00", CultureInfo.InvariantCulture), EndTime = DateTime.Parse("2012-09-30 20:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-30 18:30:00", CultureInfo.InvariantCulture), EndTime = DateTime.Parse("2012-09-30 20:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-30 18:30:00", CultureInfo.InvariantCulture), EndTime = DateTime.Parse("2012-09-30 20:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-30 19:00:00", CultureInfo.InvariantCulture), EndTime = DateTime.Parse("2012-09-30 19:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-30 18:30:00", CultureInfo.InvariantCulture), EndTime = DateTime.Parse("2012-09-30 19:30:00", CultureInfo.InvariantCulture) }
};
items.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/basic/date.html
// How can you produce a list of members who joined after the start of September 2012?
// Return the memid, surname, firstname, and joindate of the members in question.
// select memid, surname, firstname, joindate from cd.members where joindate >= '2012-09-01';
EFServiceProvider.RunInContext(context =>
{
var date = new DateTime(2012, 09, 01);
var members = context.Members.Where(x => x.JoinDate >= date)
.Select(x =>
new
{
x.MemId,
x.Surname,
x.FirstName,
x.JoinDate
}).ToList();
/*
* SELECT [m].[MemId], [m].[Surname], [m].[FirstName], [m].[JoinDate]
* FROM [Members] AS [m]
* WHERE [m].[JoinDate] >= @__date_0
*/
var expectedResult = new[]
{
new { MemId = 24, Surname = "Sarwin", FirstName = "Ramnaresh", JoinDate = DateTime.Parse("2012-09-01 08:44:42", CultureInfo.InvariantCulture) },
new { MemId = 26, Surname = "Jones", FirstName = "Douglas", JoinDate = DateTime.Parse("2012-09-02 18:43:05", CultureInfo.InvariantCulture) },
new { MemId = 27, Surname = "Rumney", FirstName = "Henrietta", JoinDate = DateTime.Parse("2012-09-05 08:42:35", CultureInfo.InvariantCulture) },
new { MemId = 28, Surname = "Farrell", FirstName = "David", JoinDate = DateTime.Parse("2012-09-15 08:22:05", CultureInfo.InvariantCulture) },
new { MemId = 29, Surname = "Worthington-Smyth", FirstName = "Henry", JoinDate = DateTime.Parse("2012-09-17 12:27:15", CultureInfo.InvariantCulture) },
new { MemId = 30, Surname = "Purview", FirstName = "Millicent", JoinDate = DateTime.Parse("2012-09-18 19:04:01", CultureInfo.InvariantCulture) },
new { MemId = 33, Surname = "Tupperware", FirstName = "Hyacinth", JoinDate = DateTime.Parse("2012-09-18 19:32:05", CultureInfo.InvariantCulture) },
new { MemId = 35, Surname = "Hunt", FirstName = "John", JoinDate = DateTime.Parse("2012-09-19 11:32:45", CultureInfo.InvariantCulture) },
new { MemId = 36, Surname = "Crumpet", FirstName = "Erica", JoinDate = DateTime.Parse("2012-09-22 08:36:38", CultureInfo.InvariantCulture) },
new { MemId = 37, Surname = "Smith", FirstName = "Darren", JoinDate = DateTime.Parse("2012-09-26 18:08:45", CultureInfo.InvariantCulture) }
};
members.Should().BeEquivalentTo(expectedResult);
});
}
public void Test_Method2()
{
// 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 entry = context.Entry(new Member {
MemId = 37
});
entry.State = EntityState.Deleted;
context.SaveChanges();
/*
* SET NOCOUNT ON;
* DELETE FROM [Members]
* WHERE [MemId] = @p0;
* SELECT @@ROWCOUNT;
*/
});
}
public void Test()
{
// https://pgexercises.com/questions/basic/unique.html
// How can you produce an ordered list of the first 10 surnames in the members table?
// The list must not contain duplicates.
// select distinct surname from cd.members order by surname limit 10;
EFServiceProvider.RunInContext(context =>
{
var members = context.Members.OrderBy(x => x.Surname)
.Select(x =>
new
{
x.Surname
})
.Distinct()
.Take(10)
.ToList();
/*
* SELECT DISTINCT TOP(@__p_0) [m].[Surname]
* FROM [Members] AS [m]
*/
var expectedResult = new[]
{
new { Surname = "Bader" },
new { Surname = "Baker" },
new { Surname = "Boothe" },
new { Surname = "Butters" },
new { Surname = "Coplin" },
new { Surname = "Crumpet" },
new { Surname = "Dare" },
new { Surname = "Farrell" },
new { Surname = "GUEST" },
new { Surname = "Genting" }
};
members.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/basic/agg2.html
// You'd like to get the first and last name of the last member(s) who signed up - not just the date. How can you do that?
// select firstname, surname, joindate from cd.members
// where joindate = (select max(joindate) from cd.members);
EFServiceProvider.RunInContext(context =>
{
var lastMember = context.Members.OrderByDescending(m => m.JoinDate)
.Select(x => new { x.FirstName, x.Surname, x.JoinDate })
.FirstOrDefault();
/*
* SELECT TOP(1) [m].[FirstName], [m].[Surname], [m].[JoinDate]
* FROM [Members] AS [m]
* ORDER BY [m].[JoinDate] DESC
*/
var expectedResult = new[]
{
new { FirstName = "Darren", Surname = "Smith", JoinDate = DateTime.Parse("2012-09-26 18:08:45", CultureInfo.InvariantCulture) }
};
lastMember.Should().BeEquivalentTo(expectedResult[0]);
var members = context.Members.Select(x => new { x.FirstName, x.Surname, x.JoinDate })
.Where(x => x.JoinDate == context.Members.Max(x => x.JoinDate))
.ToList();
/*
* SELECT [m].[FirstName], [m].[Surname], [m].[JoinDate]
* FROM [Members] AS [m]
* WHERE [m].[JoinDate] = (SELECT MAX([m0].[JoinDate]) FROM [Members] AS [m0])
*/
members.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/updates/updatemultiple.html
// We want to increase the price of the tennis courts for both members and guests.
// Update the costs to be 6 for members, and 30 for guests.
//update cd.facilities
// set
// membercost = 6,
// guestcost = 30
// where facid in (0,1);
EFServiceProvider.RunInContext(context =>
{
int[] facIds = { 0, 1 };
var tennisCourts = context.Facilities.Where(x => facIds.Contains(x.FacId)).ToList();
foreach (var tennisCourt in tennisCourts)
{
tennisCourt.MemberCost = 6;
tennisCourt.GuestCost = 30;
}
context.SaveChanges();
});
}
public void Test()
{
// https://pgexercises.com/questions/basic/where.html
// How can you produce a list of facilities that charge a fee to members?
// select * from cd.facilities where membercost > 0;
EFServiceProvider.RunInContext(context =>
{
var facilities = context.Facilities.Where(x => x.MemberCost > 0).ToList();
/*
* SELECT [f].[FacId], [f].[GuestCost], [f].[InitialOutlay], [f].[MemberCost], [f].[MonthlyMaintenance], [f].[Name]
* FROM [Facilities] AS [f]
* WHERE [f].[MemberCost] > 0.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 = 4, Name = "Massage Room 1", MemberCost = 35, GuestCost = 80, InitialOutlay = 4000, MonthlyMaintenance = 3000
},
new Facility {
FacId = 5, Name = "Massage Room 2", MemberCost = 35, GuestCost = 80, InitialOutlay = 4000, MonthlyMaintenance = 3000
},
new Facility {
FacId = 6, Name = "Squash Court", MemberCost = 3.5M, GuestCost = 17.5M, InitialOutlay = 5000, MonthlyMaintenance = 80
}
};
facilities.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/string/translate.html
// The telephone numbers in the database are very inconsistently formatted.
// You'd like to print a list of member ids and numbers that have had '-','(',')',
// and ' ' characters removed. Order by member id.
//
//select memid, translate(telephone, '-() ', '') as telephone
// from cd.members
// order by memid;
//
//select memid, regexp_replace(telephone, '[^0-9]', '', 'g') as telephone
// from cd.members
// order by memid;
EFServiceProvider.RunInContext(context =>
{
// TODO: Use regexp_replace. SQL Server doesn't have a native regexp_replace function!
var members = context.Members
.Select(member => new
{
member.MemId,
Telephone = member.Telephone.Replace("-", "")
.Replace("(", "")
.Replace(")", "")
.Replace(" ", "")
})
.OrderBy(r => r.MemId)
.ToList();
/*
* SELECT [m].[MemId], REPLACE(REPLACE(REPLACE(REPLACE([m].[Telephone], N'-', N''), N'(', N''), N')', N''), N' ', N'') AS [Telephone]
* FROM [Members] AS [m]
* ORDER BY [m].[MemId]
*/
var expectedResult = new[]
{
new { MemId = 0, Telephone = "0000000000" },
new { MemId = 1, Telephone = "5555555555" },
new { MemId = 2, Telephone = "5555555555" },
new { MemId = 3, Telephone = "8446930723" },
new { MemId = 4, Telephone = "8339424710" },
new { MemId = 5, Telephone = "8440784130" },
new { MemId = 6, Telephone = "8223549973" },
new { MemId = 7, Telephone = "8337764001" },
new { MemId = 8, Telephone = "8114332547" },
new { MemId = 9, Telephone = "8331603900" },
new { MemId = 10, Telephone = "8555425251" },
new { MemId = 11, Telephone = "8445368036" },
new { MemId = 12, Telephone = "8440765141" },
new { MemId = 13, Telephone = "8550160163" },
new { MemId = 14, Telephone = "8221633254" },
new { MemId = 15, Telephone = "8334993527" },
new { MemId = 16, Telephone = "8339410824" },
new { MemId = 17, Telephone = "8114096734" },
new { MemId = 20, Telephone = "8119721377" },
new { MemId = 21, Telephone = "8226612898" },
new { MemId = 22, Telephone = "8224992232" },
new { MemId = 24, Telephone = "8224131470" },
new { MemId = 26, Telephone = "8445368036" },
new { MemId = 27, Telephone = "8229898876" },
new { MemId = 28, Telephone = "8557559876" },
new { MemId = 29, Telephone = "8558943758" },
new { MemId = 30, Telephone = "8559419786" },
new { MemId = 33, Telephone = "8226655327" },
new { MemId = 35, Telephone = "8997206978" },
new { MemId = 36, Telephone = "8117324816" },
new { MemId = 37, Telephone = "8225773541" }
};
members.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/aggregates/payback.html
// Based on the 3 complete months of data so far, calculate the amount of time each
// facility will take to repay its cost of ownership. Remember to take into account
// ongoing monthly maintenance. Output facility name and payback time in months,
// order by facility name. Don't worry about differences in month lengths,
// we're only looking for a rough value here!
//select facs.name as name,
// facs.initialoutlay/((sum(case
// when memid = 0 then slots * facs.guestcost
// else slots * membercost
// end)/3) - facs.monthlymaintenance) as months
// from cd.bookings bks
// inner join cd.facilities facs
// on bks.facid = facs.facid
// group by facs.facid
//order by name;
//
//
//select name,
// initialoutlay / (monthlyrevenue - monthlymaintenance) as repaytime
// from
// (select facs.name as name,
// facs.initialoutlay as initialoutlay,
// facs.monthlymaintenance as monthlymaintenance,
// sum(case
// when memid = 0 then slots * facs.guestcost
// else slots * membercost
// end)/3 as monthlyrevenue
// from cd.bookings bks
// inner join cd.facilities facs
// on bks.facid = facs.facid
// group by facs.name, facs.initialoutlay, facs.monthlymaintenance
// ) as subq
//order by name;
//
//
//with monthdata as (
// select mincompletemonth,
// maxcompletemonth,
// (extract(year from maxcompletemonth)*12) +
// extract(month from maxcompletemonth) -
// (extract(year from mincompletemonth)*12) -
// extract(month from mincompletemonth) as nummonths
// from (
// select date_trunc('month',
// (select max(starttime) from cd.bookings)) as maxcompletemonth,
// date_trunc('month',
// (select min(starttime) from cd.bookings)) as mincompletemonth
// ) as subq
//)
//select name,
// initialoutlay / (monthlyrevenue - monthlymaintenance) as repaytime
//
// from
// (select facs.name as name,
// facs.initialoutlay as initialoutlay,
// facs.monthlymaintenance as monthlymaintenance,
// sum(case
// when memid = 0 then slots * facs.guestcost
// else slots * membercost
// end)/(select nummonths from monthdata) as monthlyrevenue
//
// from cd.bookings bks
// inner join cd.facilities facs
// on bks.facid = facs.facid
// where bks.starttime < (select maxcompletemonth from monthdata)
// group by facs.facid
// ) as subq
//order by name;
EFServiceProvider.RunInContext(context =>
{
var facilities =
context.Bookings.Select(booking =>
new
{
booking.Facility.Name,
booking.Facility.InitialOutlay,
booking.Facility.MonthlyMaintenance,
Revenue = booking.MemId == 0 ?
booking.Slots * booking.Facility.GuestCost
: booking.Slots * booking.Facility.MemberCost
})
.GroupBy(b => new
{
b.Name,
b.InitialOutlay,
b.MonthlyMaintenance
})
.Select(group => new
{
group.Key.Name,
RepayTime =
group.Key.InitialOutlay /
((group.Sum(b => b.Revenue) / 3) - group.Key.MonthlyMaintenance)
})
.OrderBy(result => result.Name)
.ToList();
/*
* SELECT [f].[Name], [f].[InitialOutlay] / ((SUM(CASE
* WHEN [b].[MemId] = 0 THEN CAST([b].[Slots] AS decimal(18,6)) * [f].[GuestCost]
* ELSE CAST([b].[Slots] AS decimal(18,6)) * [f].[MemberCost]
* END) / 3.0) - [f].[MonthlyMaintenance]) AS [RepayTime]
* FROM [Bookings] AS [b]
* INNER JOIN [Facilities] AS [f] ON [b].[FacId] = [f].[FacId]
* GROUP BY [f].[Name], [f].[InitialOutlay], [f].[MonthlyMaintenance]
* ORDER BY [f].[Name]
*/
var expectedResult = new[]
{
new { Name = "Badminton Court", RepayTime = 6.831767719897523M }, // decimal(18, 6)
new { Name = "Massage Room 1", RepayTime = 0.188857412653446M },
new { Name = "Massage Room 2", RepayTime = 1.762114537444933M },
new { Name = "Pool Table", RepayTime = 5.333333333333333M },
new { Name = "Snooker Table", RepayTime = 6.923076923076923M },
new { Name = "Squash Court", RepayTime = 1.133958270335652M },
new { Name = "Table Tennis", RepayTime = 6.400000000000000M },
new { Name = "Tennis Court 1", RepayTime = 2.262443438914027M },
new { Name = "Tennis Court 2", RepayTime = 1.750547045951859M }
};
facilities.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/joins/simplejoin.html
// How can you produce a list of the start times for bookings by members named 'David Farrell'?
// select bks.starttime
// from
// cd.bookings bks
// inner join cd.members mems
// on mems.memid = bks.memid
// where
// mems.firstname='David' and mems.surname='Farrell';
EFServiceProvider.RunInContext(context =>
{
var startTimes = context.Bookings
.Where(booking => booking.Member.FirstName == "David" &&
booking.Member.Surname == "Farrell")
.Select(booking => new { booking.StartTime })
.ToList();
/*
* SELECT [b].[StartTime]
* FROM [Bookings] AS [b]
* INNER JOIN [Members] AS [m] ON [b].[MemId] = [m].[MemId]
* WHERE ([m].[FirstName] = N'David') AND ([m].[Surname] = N'Farrell')
*/
var expectedResult = new[]
{
new { StartTime = DateTime.Parse("2012-09-18 09:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-18 17:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-18 13:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-18 20:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-19 09:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-19 15:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-19 12:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-20 15:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-20 11:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-20 14:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-21 10:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-21 14:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-22 08:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-22 17:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-23 08:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-23 17:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-23 19:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-24 08:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-24 16:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-24 12:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-25 15:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-25 17:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-26 13:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-26 17:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-27 08:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-28 11:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-28 09:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-28 13:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-29 16:00:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-29 10:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-29 13:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-29 14:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-29 17:30:00", CultureInfo.InvariantCulture) },
new { StartTime = DateTime.Parse("2012-09-30 14:30:00", CultureInfo.InvariantCulture) }
};
startTimes.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);
});
}
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)
)
})
.GroupBy(b => new { b.Year, b.Month, b.DaysInMonth })
.Select(g => new
{
g.Key.Year,
g.Key.Month,
g.Key.DaysInMonth
})
.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
* GROUP BY 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]))))
* ) 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 void Test()
{
// https://pgexercises.com/questions/joins/sub.html
// How can you output a list of all members, including the individual who recommended them (if any), without using any joins?
// Ensure that there are no duplicates in the list, and that each firstname + surname pairing is formatted as a column and ordered.
//select distinct mems.firstname || ' ' || mems.surname as member,
// (select recs.firstname || ' ' || recs.surname as recommender
// from cd.members recs
// where recs.memid = mems.recommendedby
// )
// from
// cd.members mems
//order by member;
EFServiceProvider.RunInContext(context =>
{
var members = context.Members
.Select(member =>
new
{
Member = member.FirstName + " " + member.Surname,
Recommender = context.Members
.Where(recommender => recommender.MemId == member.RecommendedBy)
.Select(recommender => recommender.FirstName + " " + recommender.Surname)
.FirstOrDefault() ?? ""
})
.Distinct()
.OrderBy(member => member.Member)
.ToList();
/*
* SELECT [t].[c] AS [Member], [t].[c0] AS [Recommender]
* FROM (
* SELECT DISTINCT ([m0].[FirstName] + N' ') + [m0].[Surname] AS [c], COALESCE((
* SELECT TOP(1) ([m].[FirstName] + N' ') + [m].[Surname]
* FROM [Members] AS [m]
* WHERE [m].[MemId] = [m0].[RecommendedBy]), N'') AS [c0]
* FROM [Members] AS [m0]
* ) AS [t]
* ORDER BY [t].[c]
*/
var expectedResult = new[]
{
new { Member = "Anna Mackenzie", Recommender = "Darren Smith" },
new { Member = "Anne Baker", Recommender = "Ponder Stibbons" },
new { Member = "Burton Tracy", Recommender = "" },
new { Member = "Charles Owen", Recommender = "Darren Smith" },
new { Member = "Darren Smith", Recommender = "" },
new { Member = "David Farrell", Recommender = "" },
new { Member = "David Jones", Recommender = "Janice Joplette" },
new { Member = "David Pinker", Recommender = "Jemima Farrell" },
new { Member = "Douglas Jones", Recommender = "David Jones" },
new { Member = "Erica Crumpet", Recommender = "Tracy Smith" },
new { Member = "Florence Bader", Recommender = "Ponder Stibbons" },
new { Member = "GUEST GUEST", Recommender = "" },
new { Member = "Gerald Butters", Recommender = "Darren Smith" },
new { Member = "Henrietta Rumney", Recommender = "Matthew Genting" },
new { Member = "Henry Worthington-Smyth", Recommender = "Tracy Smith" },
new { Member = "Hyacinth Tupperware", Recommender = "" },
new { Member = "Jack Smith", Recommender = "Darren Smith" },
new { Member = "Janice Joplette", Recommender = "Darren Smith" },
new { Member = "Jemima Farrell", Recommender = "" },
new { Member = "Joan Coplin", Recommender = "Timothy Baker" },
new { Member = "John Hunt", Recommender = "Millicent Purview" },
new { Member = "Matthew Genting", Recommender = "Gerald Butters" },
new { Member = "Millicent Purview", Recommender = "Tracy Smith" },
new { Member = "Nancy Dare", Recommender = "Janice Joplette" },
new { Member = "Ponder Stibbons", Recommender = "Burton Tracy" },
new { Member = "Ramnaresh Sarwin", Recommender = "Florence Bader" },
new { Member = "Tim Boothe", Recommender = "Tim Rownam" },
new { Member = "Tim Rownam", Recommender = "" },
new { Member = "Timothy Baker", Recommender = "Jemima Farrell" },
new { Member = "Tracy Smith", Recommender = "" }
};
members.Should().BeEquivalentTo(expectedResult);
});
}
public void Test_Method2()
{
// https://pgexercises.com/questions/recursive/getupwardall.html
// Produce a CTE that can return the upward recommendation chain for any member.
// You should be able to select recommender from recommenders where member=x.
// Demonstrate it by getting the chains for members 12 and 22. Results table should have
// member and recommender, ordered by member ascending, recommender descending.
// Using
// https://github.com/linq2db/linq2db.EntityFrameworkCore
// https://linq2db.github.io/articles/sql/CTE.html
EFServiceProvider.RunInContext(context =>
{
var memberHierarchyCte =
context.CreateLinqToDbContext().GetCte <MemberHierarchyCTE>(memberHierarchy =>
{
return
((
from member in context.Members
select new MemberHierarchyCTE
{
ChildId = member.MemId,
ParentId = member.RecommendedBy
}
)
.Concat
(
from member in context.Members
from hierarchy in memberHierarchy
.InnerJoin(hierarchy => member.MemId == hierarchy.ParentId)
select new MemberHierarchyCTE
{
ChildId = hierarchy.ChildId,
ParentId = member.RecommendedBy
}
));
});
var parentIdsQuery = memberHierarchyCte.Where(mh => (mh.ChildId == 12 || mh.ChildId == 22) && mh.ParentId != null)
.Select(mh => mh.ParentId);
var parents = context.Members.Where(member => parentIdsQuery.Contains(member.MemId))
.Select(member => new
{
Recommender = member.MemId,
member.FirstName,
member.Surname
})
.OrderByDescending(result => result.Recommender)
.ToLinqToDB()
.ToList();
/*
* WITH [memberHierarchy] ([ChildId], [ParentId])
* AS
* (
* SELECT
* [member_1].[MemId],
* [member_1].[RecommendedBy]
* FROM
* [Members] [member_1]
* UNION ALL
* SELECT
* [hierarchy_1].[ChildId],
* [member_2].[RecommendedBy]
* FROM
* [Members] [member_2]
* INNER JOIN [memberHierarchy] [hierarchy_1] ON [member_2].[MemId] = [hierarchy_1].[ParentId]
* )
* SELECT
* [member_3].[MemId],
* [member_3].[FirstName],
* [member_3].[Surname]
* FROM
* [Members] [member_3]
* WHERE
* EXISTS(
* SELECT
*
* FROM
* [memberHierarchy] [mh]
* WHERE
* ([mh].[ChildId] = 12 OR [mh].[ChildId] = 22) AND [mh].[ParentId] IS NOT NULL AND
* [mh].[ParentId] = [member_3].[MemId]
* )
* ORDER BY
* [member_3].[MemId] DESC
*/
var expectedResult = new[]
{
new { Recommender = 16, FirstName = "Timothy", Surname = "Baker" },
new { Recommender = 13, FirstName = "Jemima", Surname = "Farrell" },
new { Recommender = 9, FirstName = "Ponder", Surname = "Stibbons" },
new { Recommender = 6, FirstName = "Burton", Surname = "Tracy" }
};
parents.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/aggregates/facrev2.html
// Produce a list of facilities with a total revenue less than 1000.
// Produce an output table consisting of facility name and revenue, sorted by revenue.
// Remember that there's a different cost for guests and members!
//select name, revenue from (
// select facs.name, sum(case
// when memid = 0 then slots * facs.guestcost
// else slots * membercost
// end) as revenue
// from cd.bookings bks
// inner join cd.facilities facs
// on bks.facid = facs.facid
// group by facs.name
// ) as agg where revenue < 1000
//order by revenue;
EFServiceProvider.RunInContext(context =>
{
var facilities =
context.Bookings.Select(booking =>
new
{
booking.Facility.Name,
Revenue = booking.MemId == 0 ?
booking.Slots * booking.Facility.GuestCost
: booking.Slots * booking.Facility.MemberCost
})
.GroupBy(b => b.Name)
.Select(group => new
{
Name = group.Key,
TotalRevenue = group.Sum(b => b.Revenue)
})
.Where(result => result.TotalRevenue < 1000)
.OrderBy(result => result.TotalRevenue)
.ToList();
/*
* SELECT [f].[Name], SUM(CASE
* WHEN [b].[MemId] = 0 THEN CAST([b].[Slots] AS decimal(18,6)) * [f].[GuestCost]
* ELSE CAST([b].[Slots] AS decimal(18,6)) * [f].[MemberCost]
* END) AS [TotalRevenue]
* FROM [Bookings] AS [b]
* INNER JOIN [Facilities] AS [f] ON [b].[FacId] = [f].[FacId]
* GROUP BY [f].[Name]
* HAVING SUM(CASE
* WHEN [b].[MemId] = 0 THEN CAST([b].[Slots] AS decimal(18,6)) * [f].[GuestCost]
* ELSE CAST([b].[Slots] AS decimal(18,6)) * [f].[MemberCost]
* END) < 1000.0
* ORDER BY SUM(CASE
* WHEN [b].[MemId] = 0 THEN CAST([b].[Slots] AS decimal(18,6)) * [f].[GuestCost]
* ELSE CAST([b].[Slots] AS decimal(18,6)) * [f].[MemberCost]
* END)
*/
var expectedResult = new[]
{
new { Name = "Table Tennis", TotalRevenue = 180M },
new { Name = "Snooker Table", TotalRevenue = 240M },
new { Name = "Pool Table", TotalRevenue = 270M }
};
facilities.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/aggregates/classify.html
// Classify facilities into equally sized groups of high, average, and low
// based on their revenue. Order by classification and facility name.
//
//select name, case when class=1 then 'high'
// when class=2 then 'average'
// else 'low'
// end revenue
// from (
// select facs.name as name, ntile(3) over (order by sum(case
// when memid = 0 then slots * facs.guestcost
// else slots * membercost
// end) desc) as class
// from cd.bookings bks
// inner join cd.facilities facs
// on bks.facid = facs.facid
// group by facs.name
// ) as subq
//order by class, name;
EFServiceProvider.RunInContext(context =>
{
var facilities =
context.Bookings.Select(booking =>
new
{
booking.Facility.Name,
Revenue = booking.MemId == 0 ?
booking.Slots * booking.Facility.GuestCost
: booking.Slots * booking.Facility.MemberCost
})
.GroupBy(b => b.Name)
.Select(group => new
{
Name = group.Key,
TotalRevenue = group.Sum(b => b.Revenue)
})
.OrderByDescending(result => result.TotalRevenue)
.ToList();
/*
* SELECT [f].[Name], SUM(CASE
* WHEN [b].[MemId] = 0 THEN CAST([b].[Slots] AS decimal(18,6)) * [f].[GuestCost]
* ELSE CAST([b].[Slots] AS decimal(18,6)) * [f].[MemberCost]
* END) AS [TotalRevenue]
* FROM [Bookings] AS [b]
* INNER JOIN [Facilities] AS [f] ON [b].[FacId] = [f].[FacId]
* GROUP BY [f].[Name]
* ORDER BY SUM(CASE
* WHEN [b].[MemId] = 0 THEN CAST([b].[Slots] AS decimal(18,6)) * [f].[GuestCost]
* ELSE CAST([b].[Slots] AS decimal(18,6)) * [f].[MemberCost]
* END) DESC
*/
// Then using LINQ to Objects
var n = 3;
var tiledFacilities = facilities.Select((item, index) =>
new
{
Item = item,
Index = (index / n) + 1
})
.GroupBy(x => x.Index)
.Select(g =>
g.Select(z =>
new
{
z.Item.Name,
z.Item.TotalRevenue,
Tile = g.Key,
GroupName = g.Key == 1 ? "High" : (g.Key == 2 ? "Average" : "Low")
})
.OrderBy(x => x.GroupName)
.ThenBy(x => x.Name)
)
.ToList();
var flatTiledFacilities = tiledFacilities.SelectMany(group => group)
.Select(tile => new { tile.Name, Revenue = tile.GroupName })
.ToList();
var expectedResult = new[]
{
new { Name = "Massage Room 1", Revenue = "High" },
new { Name = "Massage Room 2", Revenue = "High" },
new { Name = "Tennis Court 2", Revenue = "High" },
new { Name = "Badminton Court", Revenue = "Average" },
new { Name = "Squash Court", Revenue = "Average" },
new { Name = "Tennis Court 1", Revenue = "Average" },
new { Name = "Pool Table", Revenue = "Low" },
new { Name = "Snooker Table", Revenue = "Low" },
new { Name = "Table Tennis", Revenue = "Low" }
};
flatTiledFacilities.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/aggregates/facrev3.html
// Produce a list of the top three revenue generating facilities (including ties).
// Output facility name and rank, sorted by rank and facility name.
//
//select name, rank from (
// select facs.name as name, rank() over (order by sum(case
// when memid = 0 then slots * facs.guestcost
// else slots * membercost
// end) desc) as rank
// from cd.bookings bks
// inner join cd.facilities facs
// on bks.facid = facs.facid
// group by facs.name
// ) as subq
// where rank <= 3
//order by rank;
EFServiceProvider.RunInContext(context =>
{
var facilitiesQuery =
context.Bookings.Select(booking =>
new
{
booking.Facility.Name,
Revenue = booking.MemId == 0 ?
booking.Slots * booking.Facility.GuestCost
: booking.Slots * booking.Facility.MemberCost
})
.GroupBy(b => b.Name)
.Select(group => new
{
Name = group.Key,
TotalRevenue = group.Sum(b => b.Revenue)
})
.OrderBy(result => result.TotalRevenue);
var rankedFacilities = facilitiesQuery.Select(thisItem => new
{
thisItem.Name,
thisItem.TotalRevenue,
Rank = facilitiesQuery.Count(mainItem => mainItem.TotalRevenue > thisItem.TotalRevenue) + 1
})
.Where(result => result.Rank <= 3)
.OrderBy(result => result.Rank)
.ToList();
/*
* SELECT [f0].[Name], SUM(CASE
* WHEN [b0].[MemId] = 0 THEN CAST([b0].[Slots] AS decimal(18,6)) * [f0].[GuestCost]
* ELSE CAST([b0].[Slots] AS decimal(18,6)) * [f0].[MemberCost]
* END) AS [TotalRevenue], (
* SELECT COUNT(*)
* FROM (
* SELECT [f].[Name], SUM(CASE
* WHEN [b].[MemId] = 0 THEN CAST([b].[Slots] AS decimal(18,6)) * [f].[GuestCost]
* ELSE CAST([b].[Slots] AS decimal(18,6)) * [f].[MemberCost]
* END) AS [c]
* FROM [Bookings] AS [b]
* INNER JOIN [Facilities] AS [f] ON [b].[FacId] = [f].[FacId]
* GROUP BY [f].[Name]
* ) AS [t]
* WHERE [t].[c] > SUM(CASE
* WHEN [b0].[MemId] = 0 THEN CAST([b0].[Slots] AS decimal(18,6)) * [f0].[GuestCost]
* ELSE CAST([b0].[Slots] AS decimal(18,6)) * [f0].[MemberCost]
* END)) + 1 AS [Rank]
* FROM [Bookings] AS [b0]
* INNER JOIN [Facilities] AS [f0] ON [b0].[FacId] = [f0].[FacId]
* GROUP BY [f0].[Name]
* HAVING ((
* SELECT COUNT(*)
* FROM (
* SELECT [f1].[Name], SUM(CASE
* WHEN [b1].[MemId] = 0 THEN CAST([b1].[Slots] AS decimal(18,6)) * [f1].[GuestCost]
* ELSE CAST([b1].[Slots] AS decimal(18,6)) * [f1].[MemberCost]
* END) AS [c]
* FROM [Bookings] AS [b1]
* INNER JOIN [Facilities] AS [f1] ON [b1].[FacId] = [f1].[FacId]
* GROUP BY [f1].[Name]
* ) AS [t0]
* WHERE [t0].[c] > SUM(CASE
* WHEN [b0].[MemId] = 0 THEN CAST([b0].[Slots] AS decimal(18,6)) * [f0].[GuestCost]
* ELSE CAST([b0].[Slots] AS decimal(18,6)) * [f0].[MemberCost]
* END)) + 1) <= 3
* ORDER BY (
* SELECT COUNT(*)
* FROM (
* SELECT [f2].[Name], SUM(CASE
* WHEN [b2].[MemId] = 0 THEN CAST([b2].[Slots] AS decimal(18,6)) * [f2].[GuestCost]
* ELSE CAST([b2].[Slots] AS decimal(18,6)) * [f2].[MemberCost]
* END) AS [c]
* FROM [Bookings] AS [b2]
* INNER JOIN [Facilities] AS [f2] ON [b2].[FacId] = [f2].[FacId]
* GROUP BY [f2].[Name]
* ) AS [t1]
* WHERE [t1].[c] > SUM(CASE
* WHEN [b0].[MemId] = 0 THEN CAST([b0].[Slots] AS decimal(18,6)) * [f0].[GuestCost]
* ELSE CAST([b0].[Slots] AS decimal(18,6)) * [f0].[MemberCost]
* END)) + 1
*/
var expectedResult = new[]
{
new { Name = "Massage Room 1", TotalRevenue = 72540.000000000000M, Rank = 1 },
new { Name = "Massage Room 2", TotalRevenue = 15810.000000000000M, Rank = 2 },
new { Name = "Tennis Court 2", TotalRevenue = 14310.000000000000M, Rank = 3 }
};
rankedFacilities.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/recursive/getdownward.html
// Find the downward recommendation chain for member ID 1: that is,
// the members they recommended, the members those members recommended,
// and so on. Return member ID and name, and order by ascending member id.
//
//with recursive recommendeds(memid) as (
// select memid from cd.members where recommendedby = 1
// union all
// select mems.memid
// from recommendeds recs
// inner join cd.members mems
// on mems.recommendedby = recs.memid
//)
//select recs.memid, mems.firstname, mems.surname
// from recommendeds recs
// inner join cd.members mems
// on recs.memid = mems.memid
//order by memid
EFServiceProvider.RunInContext(context =>
{
var id = 1;
var entity1WithAllOfItsDescendants =
context.Members
.Include(member => member.Children)
.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],
* [m0].[MemId], [m0].[Address], [m0].[FirstName], [m0].[JoinDate], [m0].[RecommendedBy], [m0].[Surname], [m0].[Telephone], [m0].[ZipCode]
* FROM [Members] AS [m]
* LEFT JOIN [Members] AS [m0] ON [m].[MemId] = [m0].[RecommendedBy]
* WHERE ([m].[MemId] = @__id_0) OR EXISTS (
* SELECT 1
* FROM [Members] AS [m1]
* WHERE ([m].[MemId] = [m1].[RecommendedBy]) AND ([m].[MemId] = [m1].[RecommendedBy]))
* ORDER BY [m].[MemId], [m0].[MemId]
*/
var expectedResult = new[]
{
new { MemId = 4, FirstName = "Janice", Surname = "Joplette" },
new { MemId = 5, FirstName = "Gerald", Surname = "Butters" },
new { MemId = 7, FirstName = "Nancy", Surname = "Dare" },
new { MemId = 10, FirstName = "Charles", Surname = "Owen" },
new { MemId = 11, FirstName = "David", Surname = "Jones" },
new { MemId = 14, FirstName = "Jack", Surname = "Smith" },
new { MemId = 20, FirstName = "Matthew", Surname = "Genting" },
new { MemId = 21, FirstName = "Anna", Surname = "Mackenzie" },
new { MemId = 26, FirstName = "Douglas", Surname = "Jones" },
new { MemId = 27, FirstName = "Henrietta", Surname = "Rumney" }
};
var actualResult = new List <dynamic>();
RecursiveUtils.FindChildren(entity1WithAllOfItsDescendants, actualResult);
var orderedActualResult = actualResult.OrderBy(x => x.MemId);
orderedActualResult.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/string/reg.html
// You've noticed that the club's member table has telephone numbers with very inconsistent
// formatting. You'd like to find all the telephone numbers that contain parentheses,
// returning the member ID and telephone number sorted by member ID.
//
// select memid, telephone from cd.members where telephone ~ '[()]';
//
// select memid, telephone from cd.members where telephone similar to '%[()]%';
EFServiceProvider.RunInContext(context =>
{
// Method 1
var members = context.Members
.Select(member => new { member.MemId, member.Telephone })
.Where(member => member.Telephone.Contains("(") &&
member.Telephone.Contains(")"))
.ToList();
/*
* SELECT [m].[MemId], [m].[Telephone]
* FROM [Members] AS [m]
* WHERE (CHARINDEX(N'(', [m].[Telephone]) > 0) AND (CHARINDEX(N')', [m].[Telephone]) > 0)
*/
var expectedResult = new[]
{
new { MemId = 0, Telephone = "(000) 000-0000" },
new { MemId = 3, Telephone = "(844) 693-0723" },
new { MemId = 4, Telephone = "(833) 942-4710" },
new { MemId = 5, Telephone = "(844) 078-4130" },
new { MemId = 6, Telephone = "(822) 354-9973" },
new { MemId = 7, Telephone = "(833) 776-4001" },
new { MemId = 8, Telephone = "(811) 433-2547" },
new { MemId = 9, Telephone = "(833) 160-3900" },
new { MemId = 10, Telephone = "(855) 542-5251" },
new { MemId = 11, Telephone = "(844) 536-8036" },
new { MemId = 13, Telephone = "(855) 016-0163" },
new { MemId = 14, Telephone = "(822) 163-3254" },
new { MemId = 15, Telephone = "(833) 499-3527" },
new { MemId = 20, Telephone = "(811) 972-1377" },
new { MemId = 21, Telephone = "(822) 661-2898" },
new { MemId = 22, Telephone = "(822) 499-2232" },
new { MemId = 24, Telephone = "(822) 413-1470" },
new { MemId = 27, Telephone = "(822) 989-8876" },
new { MemId = 28, Telephone = "(855) 755-9876" },
new { MemId = 29, Telephone = "(855) 894-3758" },
new { MemId = 30, Telephone = "(855) 941-9786" },
new { MemId = 33, Telephone = "(822) 665-5327" },
new { MemId = 35, Telephone = "(899) 720-6978" },
new { MemId = 36, Telephone = "(811) 732-4816" },
new { MemId = 37, Telephone = "(822) 577-3541" }
};
members.Should().BeEquivalentTo(expectedResult);
// Method 2
// `Like` query supports wildcard characters and hence very useful compared to the string extension methods in some scenarios.
members = context.Members
.Select(member => new { member.MemId, member.Telephone })
.Where(member => EF.Functions.Like(member.Telephone, "%[()]%"))
.ToList();
/*
* SELECT [m].[MemId], [m].[Telephone]
* FROM [Members] AS [m]
* WHERE [m].[Telephone] LIKE N'%[()]%'
*/
members.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
EFServiceProvider.RunInContext(context =>
{
var model = new UIModel
{
PersianYear = 1391,
SelectedPersianMonths = new[] { 4, 5 }
};
var itemsQuery = context.Members.AsQueryable();
// Linq chaining where clauses as an `Or` instead of `And`
var predicate = PredicateBuilder.False <Member>();
foreach (var month in model.SelectedPersianMonths)
{
var start = new DateTime(model.PersianYear, month, 1, new PersianCalendar());
var end = new DateTime(model.PersianYear, month, month <= 6 ? 31 : 30, new PersianCalendar());
// We can chain `IQueryable`s.
// itemsQuery = itemsQuery.Where(x => x.JoinDate.Date >= start && x.JoinDate.Date <= end);
// But it will be translated as an `AND`, not `OR`
predicate = predicate.Or(x => x.JoinDate.Date >= start && x.JoinDate.Date <= end);
}
itemsQuery = itemsQuery.Where(predicate);
var items = itemsQuery.Select(x => new { x.FirstName, x.Surname }).ToList();
/*
* SELECT [m].[FirstName],
* [m].[Surname]
* FROM [Members] AS [m]
* WHERE ((CONVERT (DATE, [m].[JoinDate]) >= '2012-06-21T00:00:00')
* AND (CONVERT (DATE, [m].[JoinDate]) <= '2012-07-21T00:00:00'))
* OR ((CONVERT (DATE, [m].[JoinDate]) >= '2012-07-22T00:00:00')
* AND (CONVERT (DATE, [m].[JoinDate]) <= '2012-08-21T00:00:00'));
*/
var expectedResult = new[]
{
new { FirstName = "GUEST", Surname = "GUEST" },
new { FirstName = "Darren", Surname = "Smith" },
new { FirstName = "Tracy", Surname = "Smith" },
new { FirstName = "Tim", Surname = "Rownam" },
new { FirstName = "Janice", Surname = "Joplette" },
new { FirstName = "Gerald", Surname = "Butters" },
new { FirstName = "Burton", Surname = "Tracy" },
new { FirstName = "Nancy", Surname = "Dare" },
new { FirstName = "Tim", Surname = "Boothe" },
new { FirstName = "Ponder", Surname = "Stibbons" },
new { FirstName = "Charles", Surname = "Owen" },
new { FirstName = "David", Surname = "Jones" },
new { FirstName = "Anne", Surname = "Baker" },
new { FirstName = "Jemima", Surname = "Farrell" },
new { FirstName = "Jack", Surname = "Smith" },
new { FirstName = "Florence", Surname = "Bader" },
new { FirstName = "Timothy", Surname = "Baker" },
new { FirstName = "David", Surname = "Pinker" },
new { FirstName = "Matthew", Surname = "Genting" }
};
items.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/joins/self2.html
// How can you output a list of all members, including the individual
// who recommended them (if any)? Ensure that results are ordered by (surname, firstname).
//select mems.firstname as memfname, mems.surname as memsname, recs.firstname as recfname, recs.surname as recsname
// from
// cd.members mems
// left outer join cd.members recs
// on recs.memid = mems.recommendedby
//order by memsname, memfname;
EFServiceProvider.RunInContext(context =>
{
var members = context.Members
.Select(member => new
{
memFName = member.FirstName,
memSName = member.Surname,
recFName = member.Recommender.FirstName ?? "",
recSName = member.Recommender.Surname ?? ""
})
.OrderBy(member => member.memSName).ThenBy(member => member.memFName)
.ToList();
/*
* SELECT [m].[FirstName] AS [memFName], [m].[Surname] AS [memSName],
* COALESCE([m0].[FirstName], N'') AS [recFName], COALESCE([m0].[Surname], N'') AS [recSName]
* FROM [Members] AS [m]
* LEFT JOIN [Members] AS [m0] ON [m].[RecommendedBy] = [m0].[MemId]
* ORDER BY [m].[Surname], [m].[FirstName]
*/
var expectedResult = new[]
{
new { memFName = "Florence", memSName = "Bader", recFName = "Ponder", recSName = "Stibbons" },
new { memFName = "Anne", memSName = "Baker", recFName = "Ponder", recSName = "Stibbons" },
new { memFName = "Timothy", memSName = "Baker", recFName = "Jemima", recSName = "Farrell" },
new { memFName = "Tim", memSName = "Boothe", recFName = "Tim", recSName = "Rownam" },
new { memFName = "Gerald", memSName = "Butters", recFName = "Darren", recSName = "Smith" },
new { memFName = "Joan", memSName = "Coplin", recFName = "Timothy", recSName = "Baker" },
new { memFName = "Erica", memSName = "Crumpet", recFName = "Tracy", recSName = "Smith" },
new { memFName = "Nancy", memSName = "Dare", recFName = "Janice", recSName = "Joplette" },
new { memFName = "David", memSName = "Farrell", recFName = "", recSName = "" },
new { memFName = "Jemima", memSName = "Farrell", recFName = "", recSName = "" },
new { memFName = "GUEST", memSName = "GUEST", recFName = "", recSName = "" },
new { memFName = "Matthew", memSName = "Genting", recFName = "Gerald", recSName = "Butters" },
new { memFName = "John", memSName = "Hunt", recFName = "Millicent", recSName = "Purview" },
new { memFName = "David", memSName = "Jones", recFName = "Janice", recSName = "Joplette" },
new { memFName = "Douglas", memSName = "Jones", recFName = "David", recSName = "Jones" },
new { memFName = "Janice", memSName = "Joplette", recFName = "Darren", recSName = "Smith" },
new { memFName = "Anna", memSName = "Mackenzie", recFName = "Darren", recSName = "Smith" },
new { memFName = "Charles", memSName = "Owen", recFName = "Darren", recSName = "Smith" },
new { memFName = "David", memSName = "Pinker", recFName = "Jemima", recSName = "Farrell" },
new { memFName = "Millicent", memSName = "Purview", recFName = "Tracy", recSName = "Smith" },
new { memFName = "Tim", memSName = "Rownam", recFName = "", recSName = "" },
new { memFName = "Henrietta", memSName = "Rumney", recFName = "Matthew", recSName = "Genting" },
new { memFName = "Ramnaresh", memSName = "Sarwin", recFName = "Florence", recSName = "Bader" },
new { memFName = "Darren", memSName = "Smith", recFName = "", recSName = "" },
new { memFName = "Darren", memSName = "Smith", recFName = "", recSName = "" },
new { memFName = "Jack", memSName = "Smith", recFName = "Darren", recSName = "Smith" },
new { memFName = "Tracy", memSName = "Smith", recFName = "", recSName = "" },
new { memFName = "Ponder", memSName = "Stibbons", recFName = "Burton", recSName = "Tracy" },
new { memFName = "Burton", memSName = "Tracy", recFName = "", recSName = "" },
new { memFName = "Hyacinth", memSName = "Tupperware", recFName = "", recSName = "" },
new { memFName = "Henry", memSName = "Worthington-Smyth", recFName = "Tracy", recSName = "Smith" },
};
members.Should().BeEquivalentTo(expectedResult);
});
}
public void Test_Method2()
{
// 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.
// Using
// https://github.com/linq2db/linq2db.EntityFrameworkCore
// https://linq2db.github.io/articles/sql/CTE.html
EFServiceProvider.RunInContext(context =>
{
var memberHierarchyCte =
context.CreateLinqToDbContext().GetCte <MemberHierarchyCTE>(memberHierarchy =>
{
return
((
from member in context.Members
select new MemberHierarchyCTE
{
ChildId = member.MemId,
ParentId = member.RecommendedBy
}
)
.Concat
(
from member in context.Members
from hierarchy in memberHierarchy
.InnerJoin(hierarchy => member.MemId == hierarchy.ParentId)
select new MemberHierarchyCTE
{
ChildId = hierarchy.ChildId,
ParentId = member.RecommendedBy
}
));
});
var parentIdsQuery = memberHierarchyCte.Where(mh => mh.ChildId == 27 && mh.ParentId != null)
.Select(mh => mh.ParentId);
var parents = context.Members.Where(member => parentIdsQuery.Contains(member.MemId))
.Select(member => new
{
Recommender = member.MemId,
member.FirstName,
member.Surname
})
.OrderByDescending(result => result.Recommender)
.ToLinqToDB()
.ToList();
/*
* WITH [memberHierarchy] ([ChildId], [ParentId])
* AS
* (
* SELECT
* [member_1].[MemId],
* [member_1].[RecommendedBy]
* FROM
* [Members] [member_1]
* UNION ALL
* SELECT
* [hierarchy_1].[ChildId],
* [member_2].[RecommendedBy]
* FROM
* [Members] [member_2]
* INNER JOIN [memberHierarchy] [hierarchy_1] ON [member_2].[MemId] = [hierarchy_1].[ParentId]
* )
* SELECT
* [member_3].[MemId],
* [member_3].[FirstName],
* [member_3].[Surname]
* FROM
* [Members] [member_3]
* WHERE
* EXISTS(
* SELECT
*
* FROM
* [memberHierarchy] [mh]
* WHERE
* [mh].[ChildId] = 27 AND [mh].[ParentId] IS NOT NULL AND
* [mh].[ParentId] = [member_3].[MemId]
* )
* ORDER BY
* [member_3].[MemId] DESC
*/
var expectedResult = new[]
{
new { Recommender = 20, FirstName = "Matthew", Surname = "Genting" },
new { Recommender = 5, FirstName = "Gerald", Surname = "Butters" },
new { Recommender = 1, FirstName = "Darren", Surname = "Smith" }
};
parents.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/aggregates/rankmembers.html
// Produce a list of members, along with the number of hours they've booked in facilities,
// rounded to the nearest ten hours. Rank them by this rounded figure, producing output of
// first name, surname, rounded hours, rank. Sort by rank, surname, and first name.
//
//
//select firstname, surname,
// ((sum(bks.slots)+10)/20)*10 as hours,
// rank() over (order by ((sum(bks.slots)+10)/20)*10 desc) as rank
//
// from cd.bookings bks
// inner join cd.members mems
// on bks.memid = mems.memid
// group by mems.memid
//order by rank, surname, firstname;
//
//
//select firstname, surname, hours, rank() over (order by hours desc) from
// (select firstname, surname,
// ((sum(bks.slots)+10)/20)*10 as hours
//
// from cd.bookings bks
// inner join cd.members mems
// on bks.memid = mems.memid
// group by mems.memid
// ) as subq
//order by rank, surname, firstname;
EFServiceProvider.RunInContext(context =>
{
var itemsQuery = context.Bookings
.GroupBy(booking => new
{
booking.Member.FirstName,
booking.Member.Surname
})
.Select(group => new
{
group.Key.FirstName,
group.Key.Surname,
Hours = (group.Sum(booking => booking.Slots) + 10) / 20 * 10
})
.OrderByDescending(result => result.Hours)
.ThenBy(result => result.Surname)
.ThenBy(result => result.FirstName);
var rankedItems = itemsQuery.Select(thisItem => new
{
thisItem.FirstName,
thisItem.Surname,
thisItem.Hours,
Rank = itemsQuery.Count(mainItem => mainItem.Hours > thisItem.Hours) + 1
})
.ToList();
/*
* SELECT [m0].[FirstName], [m0].[Surname], ((SUM([b0].[Slots]) + 10) / 20) * 10 AS [Hours], (
* SELECT COUNT(*)
* FROM (
* SELECT [m].[FirstName], [m].[Surname], ((SUM([b].[Slots]) + 10) / 20) * 10 AS [c]
* FROM [Bookings] AS [b]
* INNER JOIN [Members] AS [m] ON [b].[MemId] = [m].[MemId]
* GROUP BY [m].[FirstName], [m].[Surname]
* ) AS [t]
* WHERE [t].[c] > (((SUM([b0].[Slots]) + 10) / 20) * 10)) + 1 AS [Rank]
* FROM [Bookings] AS [b0]
* INNER JOIN [Members] AS [m0] ON [b0].[MemId] = [m0].[MemId]
* GROUP BY [m0].[FirstName], [m0].[Surname]
* ORDER BY ((SUM([b0].[Slots]) + 10) / 20) * 10 DESC, [m0].[Surname], [m0].[FirstName]
*/
var expectedResult = new[]
{
new { FirstName = "GUEST", Surname = "GUEST", Hours = 1200, Rank = 1 },
new { FirstName = "Darren", Surname = "Smith", Hours = 340, Rank = 2 },
new { FirstName = "Tim", Surname = "Rownam", Hours = 330, Rank = 3 },
new { FirstName = "Tim", Surname = "Boothe", Hours = 220, Rank = 4 },
new { FirstName = "Tracy", Surname = "Smith", Hours = 220, Rank = 4 },
new { FirstName = "Gerald", Surname = "Butters", Hours = 210, Rank = 6 },
new { FirstName = "Burton", Surname = "Tracy", Hours = 180, Rank = 7 },
new { FirstName = "Charles", Surname = "Owen", Hours = 170, Rank = 8 },
new { FirstName = "Janice", Surname = "Joplette", Hours = 160, Rank = 9 },
new { FirstName = "Anne", Surname = "Baker", Hours = 150, Rank = 10 },
new { FirstName = "Timothy", Surname = "Baker", Hours = 150, Rank = 10 },
new { FirstName = "David", Surname = "Jones", Hours = 150, Rank = 10 },
new { FirstName = "Nancy", Surname = "Dare", Hours = 130, Rank = 13 },
new { FirstName = "Florence", Surname = "Bader", Hours = 120, Rank = 14 },
new { FirstName = "Anna", Surname = "Mackenzie", Hours = 120, Rank = 14 },
new { FirstName = "Ponder", Surname = "Stibbons", Hours = 120, Rank = 14 },
new { FirstName = "Jack", Surname = "Smith", Hours = 110, Rank = 17 },
new { FirstName = "Jemima", Surname = "Farrell", Hours = 90, Rank = 18 },
new { FirstName = "David", Surname = "Pinker", Hours = 80, Rank = 19 },
new { FirstName = "Ramnaresh", Surname = "Sarwin", Hours = 80, Rank = 19 },
new { FirstName = "Matthew", Surname = "Genting", Hours = 70, Rank = 21 },
new { FirstName = "Joan", Surname = "Coplin", Hours = 50, Rank = 22 },
new { FirstName = "David", Surname = "Farrell", Hours = 30, Rank = 23 },
new { FirstName = "Henry", Surname = "Worthington-Smyth", Hours = 30, Rank = 23 },
new { FirstName = "John", Surname = "Hunt", Hours = 20, Rank = 25 },
new { FirstName = "Douglas", Surname = "Jones", Hours = 20, Rank = 25 },
new { FirstName = "Millicent", Surname = "Purview", Hours = 20, Rank = 25 },
new { FirstName = "Henrietta", Surname = "Rumney", Hours = 20, Rank = 25 },
new { FirstName = "Erica", Surname = "Crumpet", Hours = 10, Rank = 29 },
new { FirstName = "Hyacinth", Surname = "Tupperware", Hours = 10, Rank = 29 },
};
rankedItems.Should().BeEquivalentTo(expectedResult);
});
}
public void Test()
{
// https://pgexercises.com/questions/recursive/getupwardall.html
// Produce a CTE that can return the upward recommendation chain for any member.
// You should be able to select recommender from recommenders where member=x.
// Demonstrate it by getting the chains for members 12 and 22. Results table should have
// member and recommender, ordered by member ascending, recommender descending.
//
//with recursive recommenders(recommender, member) as (
// select recommendedby, memid
// from cd.members
// union all
// select mems.recommendedby, recs.member
// from recommenders recs
// inner join cd.members mems
// on mems.memid = recs.recommender
//)
//select recs.member member, recs.recommender, mems.firstname, mems.surname
// from recommenders recs
// inner join cd.members mems
// on recs.recommender = mems.memid
// where recs.member = 22 or recs.member = 12
//order by recs.member asc, recs.recommender desc
EFServiceProvider.RunInContext(context =>
{
var id1 = 12;
var id2 = 22;
var entityWithAllOfItsParents =
context.Members
.Where(member => member.MemId == id1 || member.MemId == id2 ||
member.Children.Any(m => member.MemId == m.RecommendedBy))
.ToList(); //It's a MUST - get all children from the database
/*
* SELECT [m].[MemId], [m].[Address], [m].[FirstName], [m].[JoinDate], [m].[RecommendedBy], [m].[Surname], [m].[Telephone], [m].[ZipCode]
* FROM [Members] AS [m]
* WHERE (([m].[MemId] = @__id1_0) OR ([m].[MemId] = @__id2_1)) OR EXISTS (
* SELECT 1
* FROM [Members] AS [m0]
* WHERE ([m].[MemId] = [m0].[RecommendedBy]) AND ([m].[MemId] = [m0].[RecommendedBy]))
*/
var expectedResultFor12 = new[]
{
new { Recommender = 9, FirstName = "Ponder", Surname = "Stibbons" },
new { Recommender = 6, FirstName = "Burton", Surname = "Tracy" }
};
var actualResultFor12 = new List <dynamic>();
RecursiveUtils.FindParents(entityWithAllOfItsParents.FirstOrDefault(x => x.MemId == id1), actualResultFor12);
actualResultFor12.Should().BeEquivalentTo(expectedResultFor12);
var expectedResultFor22 = new[]
{
new { Recommender = 16, FirstName = "Timothy", Surname = "Baker" },
new { Recommender = 13, FirstName = "Jemima", Surname = "Farrell" }
};
var actualResultFor22 = new List <dynamic>();
RecursiveUtils.FindParents(entityWithAllOfItsParents.FirstOrDefault(x => x.MemId == id2), actualResultFor22);
actualResultFor22.Should().BeEquivalentTo(expectedResultFor22);
});
}
public void Test()
{
// https://pgexercises.com/questions/joins/threejoin2.html
// How can you produce a list of bookings on the day of 2012-09-14 which will cost the member (or guest) more than $30?
// Remember that guests have different costs to members (the listed costs are per half-hour 'slot'), and the guest user is
// always ID 0. Include in your output the name of the facility, the name of the member formatted as a single column, and
// the cost. Order by descending cost, and do not use any subqueries.
//select mems.firstname || ' ' || mems.surname as member,
// facs.name as facility,
// case
// when mems.memid = 0 then
// bks.slots*facs.guestcost
// else
// bks.slots*facs.membercost
// end as cost
// from
// cd.members mems
// inner join cd.bookings bks
// on mems.memid = bks.memid
// inner join cd.facilities facs
// on bks.facid = facs.facid
// where
// bks.starttime >= '2012-09-14' and
// bks.starttime < '2012-09-15' and (
// (mems.memid = 0 and bks.slots*facs.guestcost > 30) or
// (mems.memid != 0 and bks.slots*facs.membercost > 30)
// )
//order by cost desc;
EFServiceProvider.RunInContext(context =>
{
var date1 = new DateTime(2012, 09, 14);
var date2 = new DateTime(2012, 09, 15);
var items = context.Members
.SelectMany(x => x.Bookings)
.Where(booking => booking.StartTime >= date1 && booking.StartTime < date2 &&
(
(((booking.Slots * booking.Facility.GuestCost) > 30) && (booking.MemId == 0)) ||
(((booking.Slots * booking.Facility.MemberCost) > 30) && (booking.MemId != 0))
))
.Select(booking => new
{
Member = booking.Member.FirstName + " " + booking.Member.Surname,
Facility = booking.Facility.Name,
Cost = booking.MemId == 0 ?
booking.Slots * booking.Facility.GuestCost
: booking.Slots * booking.Facility.MemberCost
})
.Distinct()
.OrderByDescending(x => x.Cost)
.ToList();
/*
* SELECT [t].[c] AS [Member], [t].[Name] AS [Facility], [t].[c0] AS [Cost]
* FROM (
* SELECT DISTINCT ([m0].[FirstName] + N' ') + [m0].[Surname] AS [c], [f].[Name], CASE
* WHEN [b].[MemId] = 0 THEN CAST([b].[Slots] AS decimal(18,6)) * [f].[GuestCost]
* ELSE CAST([b].[Slots] AS decimal(18,6)) * [f].[MemberCost]
* END AS [c0]
* FROM [Members] AS [m]
* INNER JOIN [Bookings] AS [b] ON [m].[MemId] = [b].[MemId]
* INNER JOIN [Facilities] AS [f] ON [b].[FacId] = [f].[FacId]
* INNER JOIN [Members] AS [m0] ON [b].[MemId] = [m0].[MemId]
* WHERE (([b].[StartTime] >= @__date1_0) AND ([b].[StartTime] < @__date2_1)) AND
* ((((CAST([b].[Slots] AS decimal(18,6)) * [f].[GuestCost]) > 30.0) AND ([b].[MemId] = 0))
* OR (((CAST([b].[Slots] AS decimal(18,6)) * [f].[MemberCost]) > 30.0) AND ([b].[MemId] <> 0)))
* ) AS [t]
* ORDER BY [t].[c0] DESC
*/
var expectedResult = new[]
{
new { Member = "GUEST GUEST", Facility = "Massage Room 2", Cost = 320M },
new { Member = "GUEST GUEST", Facility = "Massage Room 1", Cost = 160M },
new { Member = "GUEST GUEST", Facility = "Tennis Court 2", Cost = 150M },
new { Member = "Jemima Farrell", Facility = "Massage Room 1", Cost = 140M },
new { Member = "GUEST GUEST", Facility = "Tennis Court 1", Cost = 75M },
new { Member = "GUEST GUEST", Facility = "Tennis Court 2", Cost = 75M },
new { Member = "Matthew Genting", Facility = "Massage Room 1", Cost = 70M },
new { Member = "Florence Bader", Facility = "Massage Room 2", Cost = 70M },
new { Member = "GUEST GUEST", Facility = "Squash Court", Cost = 70.0M },
new { Member = "Jemima Farrell", Facility = "Massage Room 1", Cost = 70M },
new { Member = "Ponder Stibbons", Facility = "Massage Room 1", Cost = 70M },
new { Member = "Burton Tracy", Facility = "Massage Room 1", Cost = 70M },
new { Member = "Jack Smith", Facility = "Massage Room 1", Cost = 70M },
new { Member = "GUEST GUEST", Facility = "Squash Court", Cost = 35.0M }
};
items.Should().BeEquivalentTo(expectedResult);
});
}
