public void JoinAndGroup()
{
IEnumerable <Person> source = TestData.GetPeople();
IEnumerable <Family> families = TestData.GetFamilies();
var answer = from p in source
join f in families on p.Address equals f.Address
group f by f.Name into g
select new Tuple <string, int>(g.Key, g.Count());
var result = source.Query <Person, Family, Tuple <string, int> >("SELECT that.Name, COUNT(*) FROM this INNER JOIN that ON this.Address = that.Address GROUP BY that.Name", families);
Assert.IsTrue(result.Any());
Assert.IsTrue(answer.Any());
Assert.IsTrue(result.SequenceEqual(answer));
}
public void OrderedJoin()
{
IEnumerable <Person> source = TestData.GetPeople();
IEnumerable <Family> families = TestData.GetFamilies();
var answer = source.Join <Person, Family, string, FamilyMember>(families, p => p.Address, f => f.Address,
(p, f) => new FamilyMember {
Name = p.Name, LastName = f.Name, Location = f.Address
}).OrderByDescending(m => m.Location);
var result = source.Query <Person, Family, FamilyMember>("SELECT Name, that.Name AS LastName, Address AS Location FROM this INNER JOIN that ON this.Address = that.Address ORDER BY Address DESC", families);
Assert.IsTrue(result.Any());
Assert.IsTrue(answer.Any());
Assert.IsTrue(result.SequenceEqual(answer));
}
public void JoinWithInnerWhere2()
{
IEnumerable <Person> source = TestData.GetPeople();
IEnumerable <Family> families = TestData.GetFamilies();
var answer = from p in source
join f in families on p.Address equals f.Address
where f.Name == "Smith"
select new FamilyMember {
Name = p.Name, LastName = f.Name, Location = f.Address
};
var result = source.Query <Person, Family, FamilyMember>("SELECT Name, that.Name AS LastName, Address AS Location FROM this INNER JOIN that ON this.Address = that.Address WHERE that.Name = 'Smith'", families);
Assert.IsTrue(answer.Any());
Assert.IsTrue(result.Any());
Assert.IsTrue(result.SequenceEqual(answer));
}
public void JoinObjectsIntoDictionary()
{
IEnumerable <Person> source = TestData.GetPeople();
IEnumerable <Family> families = TestData.GetFamilies();
var answer = from p in source
join f in families on p.Address equals f.Address
select new Dictionary <string, object>(StringComparer.OrdinalIgnoreCase)
{
{ "Name", p.Name },
{ "LastName", f.Name },
{ "Location", p.Address }
};
var result = source.Query <Person, Family, IDictionary <string, object> >("SELECT Name, that.Name AS LastName, Address AS Location FROM this INNER JOIN that ON this.Address = that.Address", families);
Assert.IsTrue(result.Any());
Assert.IsTrue(answer.Any());
Assert.IsTrue(result.SequenceEqual(answer, new DictionaryComparer <string, object>()));
}
public void JoinObjectsToDictionary()
{
IEnumerable <Family> outer = TestData.GetFamilies();
IEnumerable <IDictionary <string, object> > inner = TestData.GetPeopleDictionary();
var answer = from f in outer
join p in inner on f.Name equals p["lastName"]
select new Dictionary <string, object>(StringComparer.OrdinalIgnoreCase)
{
{ "Name", p["name"] },
{ "lastName", p["lastName"] },
{ "Address", f.Address }
};
var result = outer.Query <Family, IDictionary <string, object>, IDictionary <string, object> >("SELECT that.Name, that.lastName, Address FROM this INNER JOIN that ON this.Name = that.lastName", inner);
Assert.IsTrue(result.Any());
Assert.IsTrue(answer.Any());
Assert.IsTrue(result.SequenceEqual(answer, new DictionaryComparer <string, object>()));
}
