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()
{
// 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/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);
});
}