--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/UnitTests/Linq/GroupByTest.cs	Thu Mar 27 21:46:09 2014 +0400
@@ -0,0 +1,1535 @@
+using System;
+using System.Linq;
+
+using NUnit.Framework;
+
+using BLToolkit.Data.DataProvider;
+
+namespace Data.Linq
+{
+	using Model;
+
+	[TestFixture]
+	public class GroupByTest : TestBase
+	{
+		[Test]
+		public void Simple1()
+		{
+			BLToolkit.Common.Configuration.Linq.PreloadGroups = true;
+
+			ForEachProvider(db =>
+			{
+				var q =
+					from ch in db.Child
+					group ch by ch.ParentID;
+
+				var list = q.ToList().Where(n => n.Key < 6).OrderBy(n => n.Key).ToList();
+
+				Assert.AreEqual(4, list.Count);
+
+				for (var i = 0; i < list.Count; i++)
+				{
+					var values = list[i].OrderBy(c => c.ChildID).ToList();
+
+					Assert.AreEqual(i + 1, list[i].Key);
+					Assert.AreEqual(i + 1, values.Count);
+
+					for (var j = 0; j < values.Count; j++)
+						Assert.AreEqual((i + 1) * 10 + j + 1, values[j].ChildID);
+				}
+			});
+		}
+
+		[Test]
+		public void Simple2()
+		{
+			BLToolkit.Common.Configuration.Linq.PreloadGroups = false;
+
+			ForEachProvider(db =>
+			{
+				var q =
+					from ch in db.GrandChild
+					group ch by new { ch.ParentID, ch.ChildID };
+
+				var list = q.ToList();
+
+				Assert.AreEqual   (8, list.Count);
+				Assert.AreNotEqual(0, list.OrderBy(c => c.Key.ParentID).First().ToList().Count);
+			});
+		}
+
+		[Test]
+		public void Simple3()
+		{
+			ForEachProvider(db =>
+			{
+				var q =
+					from ch in db.Child
+					group ch by ch.ParentID into g
+					select g.Key;
+
+				var list = q.ToList().Where(n => n < 6).OrderBy(n => n).ToList();
+
+				Assert.AreEqual(4, list.Count);
+				for (var i = 0; i < list.Count; i++) Assert.AreEqual(i + 1, list[i]);
+			});
+		}
+
+		[Test]
+		public void Simple4()
+		{
+			ForEachProvider(db =>
+			{
+				var q =
+					from ch in db.Child
+					group ch by ch.ParentID into g
+					orderby g.Key
+					select g.Key;
+
+				var list = q.ToList().Where(n => n < 6).ToList();
+
+				Assert.AreEqual(4, list.Count);
+				for (var i = 0; i < list.Count; i++) Assert.AreEqual(i + 1, list[i]);
+			});
+		}
+
+		[Test]
+		public void Simple5()
+		{
+			var expected =
+				from ch in GrandChild
+				group ch by new { ch.ParentID, ch.ChildID } into g
+				group g  by new { g.Key.ParentID }          into g
+				select g.Key;
+
+			ForEachProvider(db => AreEqual(expected,
+				from ch in db.GrandChild
+				group ch by new { ch.ParentID, ch.ChildID } into g
+				group g  by new { g.Key.ParentID }          into g
+				select g.Key));
+		}
+
+		[Test]
+		public void Simple6()
+		{
+			ForEachProvider(db =>
+			{
+				var q    = db.GrandChild.GroupBy(ch => new { ch.ParentID, ch.ChildID }, ch => ch.GrandChildID);
+				var list = q.ToList();
+
+				Assert.AreNotEqual(0, list[0].Count());
+				Assert.AreEqual   (8, list.Count);
+			});
+		}
+
+		[Test]
+		public void Simple7()
+		{
+			ForEachProvider(db =>
+			{
+				var q = db.GrandChild
+					.GroupBy(ch => new { ch.ParentID, ch.ChildID }, ch => ch.GrandChildID)
+					.Select (gr => new { gr.Key.ParentID, gr.Key.ChildID });
+
+				var list = q.ToList();
+				Assert.AreEqual(8, list.Count);
+			});
+		}
+
+		[Test]
+		public void Simple8()
+		{
+			ForEachProvider(db =>
+			{
+				var q = db.GrandChild.GroupBy(ch => new { ch.ParentID, ch.ChildID }, (g,ch) => g.ChildID);
+
+				var list = q.ToList();
+				Assert.AreEqual(8, list.Count);
+			});
+		}
+
+		[Test]
+		public void Simple9()
+		{
+			ForEachProvider(db =>
+			{
+				var q    = db.GrandChild.GroupBy(ch => new { ch.ParentID, ch.ChildID }, ch => ch.GrandChildID,  (g,ch) => g.ChildID);
+				var list = q.ToList();
+
+				Assert.AreEqual(8, list.Count);
+			});
+		}
+
+		[Test]
+		public void Simple10()
+		{
+			var expected = (from ch in Child group ch by ch.ParentID into g select g).ToList().OrderBy(p => p.Key).ToList();
+
+			ForEachProvider(db =>
+			{
+				var result = (from ch in db.Child group ch by ch.ParentID into g select g).ToList().OrderBy(p => p.Key).ToList();
+
+				AreEqual(expected[0], result[0]);
+				AreEqual(expected.Select(p => p.Key), result.Select(p => p.Key));
+				AreEqual(expected[0].ToList(), result[0].ToList());
+			});
+		}
+
+		[Test]
+		public void Simple11()
+		{
+			ForEachProvider(db =>
+			{
+				var q1 = GrandChild
+					.GroupBy(ch => new { ParentID = ch.ParentID + 1, ch.ChildID }, ch => ch.ChildID);
+
+				var q2 = db.GrandChild
+					.GroupBy(ch => new { ParentID = ch.ParentID + 1, ch.ChildID }, ch => ch.ChildID);
+
+				var list1 = q1.AsEnumerable().OrderBy(_ => _.Key.ChildID).ToList();
+				var list2 = q2.AsEnumerable().OrderBy(_ => _.Key.ChildID).ToList();
+
+				Assert.AreEqual(list1.Count,       list2.Count);
+				Assert.AreEqual(list1[0].ToList(), list2[0].ToList());
+			});
+		}
+
+		[Test]
+		public void Simple12()
+		{
+			ForEachProvider(db =>
+			{
+				var q = db.GrandChild
+					.GroupBy(ch => new { ParentID = ch.ParentID + 1, ch.ChildID }, (g,ch) => g.ChildID);
+
+				var list = q.ToList();
+				Assert.AreEqual(8, list.Count);
+			});
+		}
+
+		[Test]
+		public void Simple13()
+		{
+			ForEachProvider(db =>
+			{
+				var q = db.GrandChild
+					.GroupBy(ch => new { ParentID = ch.ParentID + 1, ch.ChildID }, ch => ch.ChildID, (g,ch) => g.ChildID);
+
+				var list = q.ToList();
+				Assert.AreEqual(8, list.Count);
+			});
+		}
+
+		//[Test]
+		public void Simple14()
+		{
+			ForEachProvider(db => AreEqual(
+				from p in    Parent
+				select
+					from c in p.Children
+					group c by c.ParentID into g
+					select g.Key,
+				from p in db.Parent
+				select
+					from c in p.Children
+					group c by c.ParentID into g
+					select g.Key));
+		}
+
+		[Test]
+		public void MemberInit1()
+		{
+			ForEachProvider(db => AreEqual(
+				from ch in Child
+				group ch by new Child { ParentID = ch.ParentID } into g
+				select g.Key,
+				from ch in db.Child
+				group ch by new Child { ParentID = ch.ParentID } into g
+				select g.Key));
+		}
+
+		class GroupByInfo
+		{
+			public GroupByInfo Prev;
+			public object      Field;
+
+			public override bool Equals(object obj)
+			{
+				return Equals(obj as GroupByInfo);
+			}
+
+			public bool Equals(GroupByInfo other)
+			{
+				if (ReferenceEquals(null, other)) return false;
+				if (ReferenceEquals(this, other)) return true;
+				return Equals(other.Prev, Prev) && Equals(other.Field, Field);
+			}
+
+			public override int GetHashCode()
+			{
+				unchecked
+				{
+					return ((Prev != null ? Prev.GetHashCode() : 0) * 397) ^ (Field != null ? Field.GetHashCode() : 0);
+				}
+			}
+		}
+
+		[Test]
+		public void MemberInit2()
+		{
+			ForEachProvider(db => AreEqual(
+				from ch in Child
+				group ch by new GroupByInfo { Prev = new GroupByInfo { Field = ch.ParentID }, Field = ch.ChildID } into g
+				select g.Key,
+				from ch in db.Child
+				group ch by new GroupByInfo { Prev = new GroupByInfo { Field = ch.ParentID }, Field = ch.ChildID } into g
+				select g.Key));
+		}
+
+		[Test]
+		public void MemberInit3()
+		{
+			ForEachProvider(db => AreEqual(
+				from ch in Child
+				group ch by new { Prev = new { Field = ch.ParentID }, Field = ch.ChildID } into g
+				select g.Key,
+				from ch in db.Child
+				group ch by new { Prev = new { Field = ch.ParentID }, Field = ch.ChildID } into g
+				select g.Key));
+		}
+
+		[Test]
+		public void SubQuery1()
+		{
+			var n = 1;
+
+			var expected =
+				from ch in
+					from ch in Child select ch.ParentID + 1
+				where ch + 1 > n
+				group ch by ch into g
+				select g.Key;
+
+			ForEachProvider(db => AreEqual(expected,
+				from ch in
+					from ch in db.Child select ch.ParentID + 1
+				where ch > n
+				group ch by ch into g
+				select g.Key));
+		}
+
+		[Test]
+		public void SubQuery2()
+		{
+			var n = 1;
+
+			var expected =
+				from ch in Child select new { ParentID = ch.ParentID + 1 } into ch
+				where ch.ParentID > n
+				group ch by ch into g
+				select g.Key;
+
+			ForEachProvider(db => AreEqual(expected,
+				from ch in db.Child select new { ParentID = ch.ParentID + 1 } into ch
+				where ch.ParentID > n
+				group ch by ch into g
+				select g.Key));
+		}
+
+		[Test]
+		public void SubQuery3()
+		{
+			ForEachProvider(db => AreEqual(
+				from ch in
+					from ch in Child
+					select new { ch, n = ch.ChildID + 1 }
+				group ch by ch.n into g
+				select new
+				{
+					g.Key,
+					Sum = g.Sum(_ => _.ch.ParentID)
+				},
+				from ch in
+					from ch in db.Child
+					select new { ch, n = ch.ChildID + 1 }
+				group ch by ch.n into g
+				select new
+				{
+					g.Key,
+					Sum = g.Sum(_ => _.ch.ParentID)
+				}));
+		}
+
+		[Test]
+		public void SubQuery31()
+		{
+			ForEachProvider(db => AreEqual(
+				from ch in
+					from ch in Child
+					select new { ch, n = ch.ChildID + 1 }
+				group ch.ch by ch.n into g
+				select new
+				{
+					g.Key,
+					Sum = g.Sum(_ => _.ParentID)
+				},
+				from ch in
+					from ch in db.Child
+					select new { ch, n = ch.ChildID + 1 }
+				group ch.ch by ch.n into g
+				select new
+				{
+					g.Key,
+					Sum = g.Sum(_ => _.ParentID)
+				}));
+		}
+
+		[Test]
+		public void SubQuery32()
+		{
+			ForEachProvider(db => AreEqual(
+				from ch in
+					from ch in Child
+					select new { ch, n = ch.ChildID + 1 }
+				group ch.ch.ParentID by ch.n into g
+				select new
+				{
+					g.Key,
+					Sum = g.Sum(_ => _)
+				},
+				from ch in
+					from ch in db.Child
+					select new { ch, n = ch.ChildID + 1 }
+				group ch.ch.ParentID by ch.n into g
+				select new
+				{
+					g.Key,
+					Sum = g.Sum(_ => _)
+				}));
+		}
+
+		[Test]
+		public void SubQuery4()
+		{
+			ForEachProvider(db => AreEqual(
+				from ch in Child
+				group ch by new { n = ch.ChildID + 1 } into g
+				select new
+				{
+					g.Key,
+					Sum = g.Sum(_ => _.ParentID)
+				},
+				from ch in db.Child
+				group ch by new { n = ch.ChildID + 1 } into g
+				select new
+				{
+					g.Key,
+					Sum = g.Sum(_ => _.ParentID)
+				}));
+		}
+
+		[Test]
+		public void SubQuery5()
+		{
+			ForEachProvider(db => AreEqual(
+				from ch in Child
+				join p in Parent on ch.ParentID equals p.ParentID into pg
+				from p in pg.DefaultIfEmpty()
+				group ch by ch.ChildID into g
+				select g.Sum(_ => _.ParentID),
+				from ch in db.Child
+				join p in db.Parent on ch.ParentID equals p.ParentID into pg
+				from p in pg.DefaultIfEmpty()
+				group ch by ch.ChildID into g
+				select g.Sum(_ => _.ParentID)));
+		}
+
+		[Test]
+		public void SubQuery6()
+		{
+			var expected =
+				from ch in Child select new { ParentID = ch.ParentID + 1 } into ch
+				group ch.ParentID by ch into g
+				select g.Key;
+
+			ForEachProvider(db => AreEqual(expected,
+				from ch in db.Child select new { ParentID = ch.ParentID + 1 } into ch
+				group ch.ParentID by ch into g
+				select g.Key));
+		}
+
+		[Test]
+		public void SubQuery7()
+		{
+			ForEachProvider(db => AreEqual(
+				from p in Parent
+				join c in 
+					from c in Child
+					where c.ParentID == 1
+					select c
+				on p.ParentID equals c.ParentID into g
+				from c in g.DefaultIfEmpty()
+				group p by c == null ? 0 : c.ChildID into gg
+				select new { gg.Key },
+				from p in db.Parent
+				join c in 
+					from c in db.Child
+					where c.ParentID == 1
+					select c
+				on p.ParentID equals c.ParentID into g
+				from c in g.DefaultIfEmpty()
+				group p by c.ChildID into gg
+				select new { gg.Key }));
+		}
+
+		[Test]
+		public void Calculated1()
+		{
+			var expected = 
+				(
+					from ch in Child
+					group ch by ch.ParentID > 2 ? ch.ParentID > 3 ? "1" : "2" : "3"
+					into g select g
+				).ToList().OrderBy(p => p.Key).ToList();
+
+			ForEachProvider(db =>
+			{
+				var result =
+					(
+						from ch in db.Child
+						group ch by ch.ParentID > 2 ? ch.ParentID > 3 ? "1" : "2" : "3"
+						into g select g
+					).ToList().OrderBy(p => p.Key).ToList();
+
+				AreEqual(expected[0], result[0]);
+				AreEqual(expected.Select(p => p.Key), result.Select(p => p.Key));
+			});
+		}
+
+		[Test]
+		public void Calculated2()
+		{
+			var expected =
+				from p in
+					from ch in
+						from ch in Child
+						group ch by ch.ParentID > 2 ? ch.ParentID > 3 ? "1" : "2" : "3"
+						into g select g
+					select ch.Key + "2"
+				where p == "22"
+				select p;
+
+			ForEachProvider(db => AreEqual(expected,
+				from p in
+					from ch in
+						from ch in db.Child
+						group ch by ch.ParentID > 2 ? ch.ParentID > 3 ? "1" : "2" : "3"
+						into g select g
+					select ch.Key + "2"
+				where p == "22"
+				select p));
+		}
+
+		[Test]
+		public void GroupBy1()
+		{
+			ForEachProvider(db => AreEqual(
+				   Child.GroupBy(ch => ch.ParentID).GroupBy(ch => ch).GroupBy(ch => ch).Select(p => p.Key.Key.Key),
+				db.Child.GroupBy(ch => ch.ParentID).GroupBy(ch => ch).GroupBy(ch => ch).Select(p => p.Key.Key.Key)));
+		}
+
+		[Test]
+		public void GroupBy2()
+		{
+			ForEachProvider(db => AreEqual(
+				from p in Parent
+				join c in Child on p.ParentID equals c.ParentID
+				group p by new
+				{
+					ID = p.Value1 ?? c.ChildID
+				} into gr
+				select new
+				{
+					gr.Key.ID,
+					ID1 = gr.Key.ID + 1,
+				},
+				from p in db.Parent
+				join c in db.Child on p.ParentID equals c.ParentID
+				group p by new
+				{
+					ID = p.Value1 ?? c.ChildID
+				} into gr
+				select new
+				{
+					gr.Key.ID,
+					ID1 = gr.Key.ID + 1,
+				}));
+		}
+
+		[Test]
+		public void GroupBy3()
+		{
+			ForEachProvider(db => AreEqual(
+				from p in Parent
+				join c in Child on p.ParentID equals c.ParentID
+				group p by p.Value1 ?? c.ChildID into gr
+				select new
+				{
+					gr.Key
+				},
+				from p in db.Parent
+				join c in db.Child on p.ParentID equals c.ParentID
+				group p by p.Value1 ?? c.ChildID into gr
+				select new
+				{
+					gr.Key
+				}));
+		}
+
+		[Test]
+		public void Sum1()
+		{
+			var expected =
+				from ch in Child
+				group ch by ch.ParentID into g
+				select g.Sum(p => p.ChildID);
+
+			ForEachProvider(db => AreEqual(expected,
+				from ch in db.Child
+				group ch by ch.ParentID into g
+				select g.Sum(p => p.ChildID)));
+		}
+
+		[Test]
+		public void Sum2()
+		{
+			var expected =
+				from ch in Child
+				group ch by ch.ParentID into g
+				select new { Sum = g.Sum(p => p.ChildID) };
+
+			ForEachProvider(db => AreEqual(expected,
+				from ch in db.Child
+				group ch by ch.ParentID into g
+				select new { Sum = g.Sum(p => p.ChildID) }));
+		}
+
+		[Test]
+		public void Sum3()
+		{
+			ForEachProvider(
+				new[] { ProviderName.SqlCe },
+				db => AreEqual(
+					from ch in Child
+					group ch by ch.Parent into g
+					select g.Key.Children.Sum(p => p.ChildID),
+					from ch in db.Child
+					group ch by ch.Parent into g
+					select g.Key.Children.Sum(p => p.ChildID)));
+		}
+
+		[Test]
+		public void SumSubQuery1()
+		{
+			var n = 1;
+
+			var expected =
+				from ch in
+					from ch in Child select new { ParentID = ch.ParentID + 1, ch.ChildID }
+				where ch.ParentID + 1 > n group ch by ch into g
+				select g.Sum(p => p.ParentID - 3);
+
+			ForEachProvider(db => AreEqual(expected,
+				from ch in
+					from ch in db.Child select new { ParentID = ch.ParentID + 1, ch.ChildID }
+				where ch.ParentID + 1 > n group ch by ch into g
+				select g.Sum(p => p.ParentID - 3)));
+		}
+
+		[Test]
+		public void GroupByMax()
+		{
+			ForEachProvider(db => AreEqual(
+				from ch in    Child group ch.ParentID by ch.ChildID into g select new { Max = g.Max() },
+				from ch in db.Child group ch.ParentID by ch.ChildID into g select new { Max = g.Max() }));
+		}
+
+		[Test]
+		public void Aggregates1()
+		{
+			ForEachProvider(db => AreEqual(
+				from  ch in Child
+				group ch by ch.ParentID into g
+				select new
+				{
+					Cnt = g.Count(),
+					Sum = g.Sum(c => c.ChildID),
+					Min = g.Min(c => c.ChildID),
+					Max = g.Max(c => c.ChildID),
+					Avg = (int)g.Average(c => c.ChildID),
+				},
+				from  ch in db.Child
+				group ch by ch.ParentID into g
+				select new
+				{
+					Cnt = g.Count(),
+					Sum = g.Sum(c => c.ChildID),
+					Min = g.Min(c => c.ChildID),
+					Max = g.Max(c => c.ChildID),
+					Avg = (int)g.Average(c => c.ChildID),
+				}));
+		}
+
+		[Test]
+		public void Aggregates2()
+		{
+			ForEachProvider(db => AreEqual(
+				from  ch in Child
+				group ch by ch.ParentID into g
+				select new
+				{
+					Sum = g.Select(c => c.ChildID).Sum(),
+					Min = g.Select(c => c.ChildID).Min(),
+					Max = g.Select(c => c.ChildID).Max(),
+					Avg = (int)g.Select(c => c.ChildID).Average(),
+					Cnt = g.Count()
+				},
+				from  ch in db.Child
+				group ch by ch.ParentID into g
+				select new
+				{
+					Sum = g.Select(c => c.ChildID).Sum(),
+					Min = g.Select(c => c.ChildID).Min(),
+					Max = g.Select(c => c.ChildID).Max(),
+					Avg = (int)g.Select(c => c.ChildID).Average(),
+					Cnt = g.Count()
+				}));
+		}
+
+		[Test]
+		public void Aggregates3()
+		{
+			ForEachProvider(
+				new[] { ProviderName.SqlCe },
+				db => AreEqual(
+					from  ch in Child
+					where ch.ChildID > 30
+					group ch by ch.ParentID into g
+					select new
+					{
+						Sum =      g.Select(c => c.ChildID).Where(_ => _ > 30).Sum(),
+						Min =      g.Select(c => c.ChildID).Where(_ => _ > 30).Min(),
+						Max =      g.Select(c => c.ChildID).Where(_ => _ > 30).Max(),
+						Avg = (int)g.Select(c => c.ChildID).Where(_ => _ > 30).Average(),
+					},
+					from  ch in db.Child
+					where ch.ChildID > 30
+					group ch by ch.ParentID into g
+					select new
+					{
+						Sum =      g.Select(c => c.ChildID).Where(_ => _ > 30).Sum(),
+						Min =      g.Select(c => c.ChildID).Where(_ => _ > 30).Min(),
+						Max =      g.Select(c => c.ChildID).Where(_ => _ > 30).Max(),
+						Avg = (int)g.Select(c => c.ChildID).Where(_ => _ > 30).Average(),
+					}));
+		}
+
+		[Test]
+		public void Aggregates4()
+		{
+			ForEachProvider(
+				new[] { ProviderName.SqlCe },
+				db => AreEqual(
+					from  ch in Child
+					group ch by ch.ParentID into g
+					select new
+					{
+						Count = g.Count(_ => _.ChildID > 30),
+						Sum   = g.Where(_ => _.ChildID > 30).Sum(c => c.ChildID),
+					},
+					from  ch in db.Child
+					group ch by ch.ParentID into g
+					select new
+					{
+						Count = g.Count(_ => _.ChildID > 30),
+						Sum   = g.Where(_ => _.ChildID > 30).Sum(c => c.ChildID),
+					}));
+		}
+
+		[Test]
+		public void SelectMax()
+		{
+			var expected =
+				from ch in Child
+				group ch by ch.ParentID into g
+				select g.Max(c => c.ChildID);
+
+			ForEachProvider(db => AreEqual(expected,
+				from ch in db.Child
+				group ch by ch.ParentID into g
+				select g.Max(c => c.ChildID)));
+		}
+
+		[Test]
+		public void JoinMax()
+		{
+			var expected =
+				from ch in Child
+					join max in
+						from ch in Child
+						group ch by ch.ParentID into g
+						select g.Max(c => c.ChildID)
+					on ch.ChildID equals max
+				select ch;
+
+			ForEachProvider(db => AreEqual(expected,
+				from ch in db.Child
+					join max in
+						from ch in db.Child
+						group ch by ch.ParentID into g
+						select g.Max(c => c.ChildID)
+					on ch.ChildID equals max
+				select ch));
+		}
+
+		[Test]
+		public void Min1()
+		{
+			var expected = Child.Min(c => c.ChildID);
+			ForEachProvider(db => Assert.AreEqual(expected, db.Child.Min(c => c.ChildID)));
+		}
+
+		[Test]
+		public void Min2()
+		{
+			var expected = Child.Select(c => c.ChildID).Min();
+			ForEachProvider(db => Assert.AreEqual(expected, db.Child.Select(c => c.ChildID).Min()));
+		}
+
+		[Test]
+		public void Max1()
+		{
+			var expected = Child.Max(c => c.ChildID);
+			Assert.AreNotEqual(0, expected);
+			ForEachProvider(db => Assert.AreEqual(expected, db.Child.Max(c => c.ChildID)));
+		}
+
+		[Test]
+		public void Max11()
+		{
+			ForEachProvider(db => Assert.AreEqual(
+				   Child.Max(c => c.ChildID > 20),
+				db.Child.Max(c => c.ChildID > 20)));
+		}
+
+		[Test]
+		public void Max12()
+		{
+			ForEachProvider(db => Assert.AreEqual(
+				   Child.Max(c => (bool?)(c.ChildID > 20)),
+				db.Child.Max(c => (bool?)(c.ChildID > 20))));
+		}
+
+		[Test]
+		public void Max2()
+		{
+			var expected =
+				from p in Parent
+					join c in Child on p.ParentID equals c.ParentID
+				where c.ChildID > 20
+				select p;
+
+			ForEachProvider(db =>
+			{
+				var result =
+					from p in db.Parent
+						join c in db.Child on p.ParentID equals c.ParentID
+					where c.ChildID > 20
+					select p;
+
+				Assert.AreEqual(expected.Max(p => p.ParentID), result.Max(p => p.ParentID));
+			});
+		}
+
+		[Test]
+		public void Max3()
+		{
+			ForEachProvider(db => Assert.AreEqual(
+				Child.Select(c => c.ChildID).Max(),
+				db.Child.Select(c => c.ChildID).Max()));
+		}
+
+		[Test]
+		public void Max4()
+		{
+			ForEachProvider(db => Assert.AreEqual(
+				from t1 in Types
+				join t2 in
+					from sub in Types
+					where
+						sub.ID == 1 &&
+						sub.DateTimeValue <= DateTime.Today
+					group sub by new
+					{
+						sub.ID
+					} into g
+					select new
+					{
+						g.Key.ID,
+						DateTimeValue = g.Max( p => p.DateTimeValue )
+					}
+				on new { t1.ID, t1.DateTimeValue } equals new { t2.ID, t2.DateTimeValue }
+				select t1.MoneyValue,
+				from t1 in db.Types
+				join t2 in
+					from sub in db.Types
+					where
+						sub.ID == 1 &&
+						sub.DateTimeValue <= DateTime.Today
+					group sub by new
+					{
+						sub.ID
+					} into g
+					select new
+					{
+						g.Key.ID,
+						DateTimeValue = g.Max( p => p.DateTimeValue )
+					}
+				on new { t1.ID, t1.DateTimeValue } equals new { t2.ID, t2.DateTimeValue }
+				select t1.MoneyValue
+				));
+		}
+
+		[Test]
+		public void Average1()
+		{
+			ForEachProvider(db => Assert.AreEqual(
+				(int)db.Child.Average(c => c.ChildID),
+				(int)   Child.Average(c => c.ChildID)));
+		}
+
+		[Test]
+		public void Average2()
+		{
+			var expected = Child.Select(c => c.ChildID).Average();
+			ForEachProvider(db => Assert.AreEqual((int)expected, (int)db.Child.Select(c => c.ChildID).Average()));
+		}
+
+		[Test]
+		public void GrooupByAssociation1()
+		{
+			ForEachProvider(db => AreEqual(
+				from ch in GrandChild1
+				group ch by ch.Parent into g
+				where g.Count() > 2
+				select g.Key.Value1
+				,
+				from ch in db.GrandChild1
+				group ch by ch.Parent into g
+				where g.Count() > 2
+				select g.Key.Value1));
+		}
+
+		[Test]
+		public void GrooupByAssociation101()
+		{
+			ForEachProvider(db => AreEqual(
+				from ch in GrandChild1
+				group ch by ch.Parent into g
+				where g.Max(_ => _.ParentID) > 2
+				select g.Key.Value1
+				,
+				from ch in db.GrandChild1
+				group ch by ch.Parent into g
+				where g.Max(_ => _.ParentID) > 2
+				select g.Key.Value1));
+		}
+
+		[Test]
+		public void GrooupByAssociation102()
+		{
+			ForEachProvider(
+				new[] { ProviderName.Informix },
+				db => AreEqual(
+					from ch in GrandChild1
+					group ch by ch.Parent into g
+					where g.Count(_ => _.ChildID >= 20) > 2
+					select g.Key.Value1
+					,
+					from ch in db.GrandChild1
+					group ch by ch.Parent into g
+					where g.Count(_ => _.ChildID >= 20) > 2
+					select g.Key.Value1));
+		}
+
+		[Test]
+		public void GrooupByAssociation1022()
+		{
+			ForEachProvider(
+				new[] { ProviderName.SqlCe, ProviderName.Access, ProviderName.Informix }, // Can be fixed.
+					db => AreEqual(
+					from ch in GrandChild1
+					group ch by ch.Parent into g
+					where g.Count(_ => _.ChildID >= 20) > 2 && g.Where(_ => _.ChildID >= 19).Sum(p => p.ParentID) > 0
+					select g.Key.Value1
+					,
+					from ch in db.GrandChild1
+					group ch by ch.Parent into g
+					where g.Count(_ => _.ChildID >= 20) > 2 && g.Where(_ => _.ChildID >= 19).Sum(p => p.ParentID) > 0
+					select g.Key.Value1));
+		}
+
+		[Test]
+		public void GrooupByAssociation1023()
+		{
+			ForEachProvider(
+				new[] { ProviderName.SqlCe, ProviderName.Access, ProviderName.Informix }, // Can be fixed.
+				db => AreEqual(
+					from ch in GrandChild1
+					group ch by ch.Parent into g
+					where
+						g.Count(_ => _.ChildID >= 20) > 2 &&
+						g.Where(_ => _.ChildID >= 19).Sum(p => p.ParentID) > 0 &&
+						g.Where(_ => _.ChildID >= 19).Max(p => p.ParentID) > 0
+					select g.Key.Value1
+					,
+					from ch in db.GrandChild1
+					group ch by ch.Parent into g
+					where
+						g.Count(_ => _.ChildID >= 20) > 2 &&
+						g.Where(_ => _.ChildID >= 19).Sum(p => p.ParentID) > 0 &&
+						g.Where(_ => _.ChildID >= 19).Max(p => p.ParentID) > 0
+					select g.Key.Value1));
+		}
+
+		[Test]
+		public void GrooupByAssociation1024()
+		{
+			ForEachProvider(
+				new[] { ProviderName.SqlCe, ProviderName.Access, ProviderName.Informix }, // Can be fixed.
+				db => AreEqual(
+					from ch in GrandChild1
+					group ch by ch.Parent into g
+					where
+						g.Count(_ => _.ChildID >= 20) > 2 &&
+						g.Where(_ => _.ChildID >= 19).Sum(p => p.ParentID) > 0 &&
+						g.Where(_ => _.ChildID >= 19).Max(p => p.ParentID) > 0 &&
+						g.Where(_ => _.ChildID >= 18).Max(p => p.ParentID) > 0
+					select g.Key.Value1
+					,
+					from ch in db.GrandChild1
+					group ch by ch.Parent into g
+					where
+						g.Count(_ => _.ChildID >= 20) > 2 &&
+						g.Where(_ => _.ChildID >= 19).Sum(p => p.ParentID) > 0 &&
+						g.Where(_ => _.ChildID >= 19).Max(p => p.ParentID) > 0 &&
+						g.Where(_ => _.ChildID >= 18).Max(p => p.ParentID) > 0
+					select g.Key.Value1));
+		}
+
+		[Test]
+		public void GrooupByAssociation2()
+		{
+			ForEachProvider(db => AreEqual(
+				from ch in GrandChild1
+				group ch by ch.Parent into g
+				where g.Count() > 2 && g.Key.ParentID != 1
+				select g.Key.Value1
+				,
+				from ch in db.GrandChild1
+				group ch by ch.Parent into g
+				where g.Count() > 2 && g.Key.ParentID != 1
+				select g.Key.Value1));
+		}
+
+		[Test]
+		public void GrooupByAssociation3([IncludeDataContexts("Northwind")] string context)
+		{
+			using (var db = new NorthwindDB())
+			{
+				var result = 
+					from p in db.Product
+					group p by p.Category into g
+					where g.Count() == 12
+					select g.Key.CategoryName;
+
+				var list = result.ToList();
+				Assert.AreEqual(3, list.Count);
+			}
+		}
+
+		[Test]
+		public void GrooupByAssociation4([IncludeDataContexts("Northwind")] string context)
+		{
+			using (var db = new NorthwindDB())
+			{
+				var result = 
+					from p in db.Product
+					group p by p.Category into g
+					where g.Count() == 12
+					select g.Key.CategoryID;
+
+				var list = result.ToList();
+				Assert.AreEqual(3, list.Count);
+			}
+		}
+
+		[Test]
+		public void GroupByAggregate1()
+		{
+			var expected =
+				from p in Parent
+				group p by p.Children.Count > 0 && p.Children.Average(c => c.ParentID) > 3 into g
+				select g.Key;
+
+			ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(expected,
+				from p in db.Parent
+				group p by p.Children.Average(c => c.ParentID) > 3 into g
+				select g.Key));
+		}
+
+		[Test]
+		public void GroupByAggregate11()
+		{
+			var expected =
+				from p in Parent
+				where p.Children.Count > 0
+				group p by p.Children.Average(c => c.ParentID) > 3 into g
+				select g.Key;
+
+			ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(expected,
+				from p in db.Parent
+				where p.Children.Count > 0
+				group p by p.Children.Average(c => c.ParentID) > 3 into g
+				select g.Key));
+		}
+
+		[Test]
+		public void GroupByAggregate12()
+		{
+			var expected =
+				from p in Parent
+				group p by p.Children.Count > 0 && p.Children.Average(c => c.ParentID) > 3 into g
+				select g.Key;
+
+			ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(expected,
+				from p in db.Parent
+				group p by p.Children.Count > 0 && p.Children.Average(c => c.ParentID) > 3 into g
+				select g.Key));
+		}
+
+		[Test]
+		public void GroupByAggregate2([IncludeDataContexts("Northwind")] string context)
+		{
+			using (var db = new NorthwindDB())
+				AreEqual(
+					(
+						from c in Customer
+						group c by c.Orders.Count > 0 && c.Orders.Average(o => o.Freight) >= 80
+					).ToList().Select(k => k.Key),
+					(
+						from c in db.Customer
+						group c by c.Orders.Average(o => o.Freight) >= 80
+					).ToList().Select(k => k.Key));
+		}
+
+		[Test]
+		public void GroupByAggregate3()
+		{
+			var expected =
+				(
+					from p in Parent
+					group p by p.Children.Count > 0 && p.Children.Average(c => c.ParentID) > 3
+				).ToList().First(g => !g.Key);
+
+			ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(expected,
+				(
+					from p in db.Parent
+					group p by p.Children.Average(c => c.ParentID) > 3
+				).ToList().First(g => !g.Key)));
+		}
+
+		[Test]
+		public void ByJoin()
+		{
+			ForEachProvider(db => AreEqual(
+				from c1 in Child
+				join c2 in Child on c1.ChildID equals c2.ChildID + 1
+				group c2 by c1.ParentID into g
+				select g.Sum(_ => _.ChildID),
+				from c1 in db.Child
+				join c2 in db.Child on c1.ChildID equals c2.ChildID + 1
+				group c2 by c1.ParentID into g
+				select g.Sum(_ => _.ChildID)));
+		}
+
+		[Test]
+		public void SelectMany()
+		{
+			ForEachProvider(db => AreEqual(
+				   Child.GroupBy(ch => ch.ParentID).SelectMany(g => g),
+				db.Child.GroupBy(ch => ch.ParentID).SelectMany(g => g)));
+		}
+
+		[Test]
+		public void Scalar1()
+		{
+			ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(
+				(from ch in Child
+				 group ch by ch.ParentID into g
+				 select g.Select(ch => ch.ChildID).Max()),
+				(from ch in db.Child
+				 group ch by ch.ParentID into g
+				 select g.Select(ch => ch.ChildID).Max())));
+		}
+
+		[Test]
+		public void Scalar101()
+		{
+			ForEachProvider(db => AreEqual(
+				(from ch in Child
+				 select ch.ChildID into id
+				 group id by id into g
+				 select g.Max()),
+				(from ch in db.Child
+				 select ch.ChildID into id
+				 group id by id into g
+				 select g.Max())));
+		}
+
+		[Test]
+		public void Scalar2()
+		{
+			ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(
+				(from ch in Child
+				 group ch by ch.ParentID into g
+				 select new
+					 {
+						 Max1 = g.Select(ch => ch.ChildID              ).Max(),
+						 Max2 = g.Select(ch => ch.ChildID + ch.ParentID).Max()
+					 }),
+				(from ch in db.Child
+				 group ch by ch.ParentID into g
+				 select new
+					 {
+						 Max1 = g.Select(ch => ch.ChildID              ).Max(),
+						 Max2 = g.Select(ch => ch.ChildID + ch.ParentID).Max()
+					 })));
+		}
+
+		[Test]
+		public void Scalar3()
+		{
+			ForEachProvider(
+				new[] { ProviderName.SqlCe },
+				db => AreEqual(
+					(from ch in Child
+					 group ch by ch.ParentID into g
+					 select g.Select(ch => ch.ChildID).Where(id => id > 0).Max()),
+					(from ch in db.Child
+					 group ch by ch.ParentID into g
+					 select g.Select(ch => ch.ChildID).Where(id => id > 0).Max())));
+		}
+
+		[Test]
+		public void Scalar4()
+		{
+			ForEachProvider(
+				new[] { ProviderName.SqlCe, ProviderName.Access, ProviderName.Informix },
+				db => AreEqual(
+					from ch in Child
+					group ch by ch.ParentID into g
+					where g.Where(ch => ch.ParentID > 2).Select(ch => (int?)ch.ChildID).Min() != null
+					select g.Where(ch => ch.ParentID > 2).Select(ch => ch.ChildID).Min()
+					,
+					from ch in db.Child
+					group ch by ch.ParentID into g
+					where g.Where(ch => ch.ParentID > 2).Select(ch => (int?)ch.ChildID).Min() != null
+					select g.Where(ch => ch.ParentID > 2).Select(ch => ch.ChildID).Min()));
+		}
+
+		[Test]
+		public void Scalar41()
+		{
+			ForEachProvider(
+				new[] { ProviderName.SqlCe, ProviderName.Access, ProviderName.Informix },
+				db => AreEqual(
+					from ch in Child
+					group ch by ch.ParentID into g
+					select new { g } into g
+					where g.g.Where(ch => ch.ParentID > 2).Select(ch => (int?)ch.ChildID).Min() != null
+					select g.g.Where(ch => ch.ParentID > 2).Select(ch => ch.ChildID).Min()
+					,
+					from ch in db.Child
+					group ch by ch.ParentID into g
+					select new { g } into g
+					where g.g.Where(ch => ch.ParentID > 2).Select(ch => (int?)ch.ChildID).Min() != null
+					select g.g.Where(ch => ch.ParentID > 2).Select(ch => ch.ChildID).Min()));
+		}
+
+		[Test]
+		public void Scalar5()
+		{
+			ForEachProvider(db => AreEqual(
+				from ch in Child
+				select ch.ParentID into id
+				group id by id into g
+				select g.Max()
+				,
+				from ch in db.Child
+				select ch.ParentID into id
+				group id by id into g
+				select g.Max()));
+		}
+
+		//[Test]
+		public void Scalar51()
+		{
+			ForEachProvider(db => AreEqual(
+				from ch in Child
+				group ch by ch.ParentID into g
+				select g.Max()
+				,
+				from ch in db.Child
+				group ch by ch.ParentID into g
+				select g.Max()));
+		}
+
+		[Test]
+		public void Scalar6()
+		{
+			ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(
+				(from ch in Child
+				 where ch.ParentID < 3
+				 group ch by ch.ParentID into g
+				 select g.Where(ch => ch.ParentID < 3).Max(ch => ch.ChildID)),
+				(from ch in db.Child
+				 where ch.ParentID < 3
+				 group ch by ch.ParentID into g
+				 select g.Where(ch => ch.ParentID < 3).Max(ch => ch.ChildID))));
+		}
+
+		[Test]
+		public void Scalar7()
+		{
+			ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(
+				(from ch in Child
+				 group ch by ch.ParentID into g
+				 select new { max = g.Select(ch => ch.ChildID).Max()}).Select(id => id.max),
+				(from ch in db.Child
+				 group ch by ch.ParentID into g
+				 select new { max = g.Select(ch => ch.ChildID).Max()}).Select(id => id.max)));
+		}
+
+		[Test]
+		public void Scalar8()
+		{
+			ForEachProvider(db => AreEqual(
+				(from ch in Child
+				group ch by ch.ParentID into g
+				select new { max = g.Max(ch => ch.ChildID)}).Select(id => id.max),
+				(from ch in db.Child
+				group ch by ch.ParentID into g
+				select new { max = g.Max(ch => ch.ChildID)}).Select(id => id.max)));
+		}
+
+		[Test]
+		public void Scalar9()
+		{
+			ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(
+				(from ch in Child
+				 group ch by ch.ParentID into g
+				 select g.Select(ch => ch.ChildID).Where(id => id < 30).Count()),
+				(from ch in db.Child
+				 group ch by ch.ParentID into g
+				 select g.Select(ch => ch.ChildID).Where(id => id < 30).Count())));
+		}
+
+		[Test]
+		public void Scalar10()
+		{
+			ForEachProvider(new[] { ProviderName.SqlCe }, db => AreEqual(
+				(from ch in Child
+				 group ch by ch.ParentID into g
+				 select g.Select(ch => ch.ChildID).Where(id => id < 30).Count(id => id >= 20)),
+				(from ch in db.Child
+				 group ch by ch.ParentID into g
+				 select g.Select(ch => ch.ChildID).Where(id => id < 30).Count(id => id >= 20))));
+		}
+
+		[Test, Category("MySql")]
+		public void GroupByExtraFieldBugTest([IncludeDataContexts(ProviderName.MySql)] string context)
+		{
+			// https://github.com/igor-tkachev/bltoolkit/issues/42
+			// extra field is generated in the GROUP BY clause, for example:
+			// GROUP BY p.LastName, p.LastName <--- the second one is redundant
+
+			using (var db = new TestDbManager(context))
+			{
+				var q =
+					from d in db.Doctor
+					join p in db.Person on d.PersonID equals p.ID
+					group d by p.LastName into g
+					select g.Key;
+
+				q.ToList();
+
+				const string fieldName = "LastName";
+
+				var lastQuery  = db.LastQuery;
+				var groupByPos = lastQuery.IndexOf("GROUP BY");
+				var fieldPos   = lastQuery.IndexOf(fieldName, groupByPos);
+				
+				// check that our field does not present in the GROUP BY clause second time
+				Assert.AreEqual(-1, lastQuery.IndexOf(fieldName, fieldPos + 1));
+			}
+		}
+
+		[Test]
+		public void DoubleGroupBy1()
+		{
+			ForEachProvider(
+				db => AreEqual(
+					from t in
+						from p in Parent
+						where p.Value1 != null
+						group p by p.ParentID into g
+						select new
+						{
+							ID  = g.Key,
+							Max = g.Max(t => t.Value1)
+						}
+					group t by t.ID into g
+					select new
+					{
+						g.Key,
+						Sum = g.Sum(t => t.Max)
+					},
+					from t in
+						from p in db.Parent
+						where p.Value1 != null
+						group p by p.ParentID into g
+						select new
+						{
+							ID  = g.Key,
+							Max = g.Max(t => t.Value1)
+						}
+					group t by t.ID into g
+					select new
+					{
+						g.Key,
+						Sum = g.Sum(t => t.Max)
+					}));
+			
+		}
+
+		[Test]
+		public void DoubleGroupBy2()
+		{
+			ForEachProvider(
+				db => AreEqual(
+					from p in Parent
+					where p.Value1 != null
+					group p by p.ParentID into g
+					select new
+					{
+						ID  = g.Key,
+						Max = g.Max(t => t.Value1)
+					} into t
+					group t by t.ID into g
+					select new
+					{
+						g.Key,
+						Sum = g.Sum(t => t.Max)
+					},
+					from p in db.Parent
+					where p.Value1 != null
+					group p by p.ParentID into g
+					select new
+					{
+						ID  = g.Key,
+						Max = g.Max(t => t.Value1)
+					} into t
+					group t by t.ID into g
+					select new
+					{
+						g.Key,
+						Sum = g.Sum(t => t.Max)
+					}));
+			
+		}
+
+		[Test]
+		public void InnerQuery([DataContexts(ProviderName.SqlCe)] string context)
+		{
+			using (var db = GetDataContext(context))
+			{
+				AreEqual(
+					   Doctor.GroupBy(s => s.PersonID).Select(s => s.Select(d => d.Taxonomy).First()),
+					db.Doctor.GroupBy(s => s.PersonID).Select(s => s.Select(d => d.Taxonomy).First()));
+			}
+		}
+
+		[Test]
+		public void CalcMember([DataContexts] string context)
+		{
+			using (var db = GetDataContext(context))
+			{
+				AreEqual(
+					from parent in Parent
+					from child  in Person
+					where child.ID == parent.ParentID
+					let data = new
+					{
+						parent.Value1,
+						Value = child.FirstName == "John" ? child.FirstName : "a"
+					}
+					group data by data.Value into groupedData
+					select new
+					{
+						groupedData.Key,
+						Count = groupedData.Count()
+					},
+					from parent in db.Parent
+					from child  in db.Person
+					where child.ID == parent.ParentID
+					let data = new
+					{
+						parent.Value1,
+						Value = child.FirstName == "John" ? child.FirstName : "a"
+					}
+					group data by data.Value into groupedData
+					select new
+					{
+						groupedData.Key,
+						Count = groupedData.Count()
+					});
+			}
+		}
+
+		[Test]
+		public void GroupByDate([DataContexts] string context)
+		{
+			using (var db = GetDataContext(context))
+			{
+				AreEqual(
+					from t in Types2
+					group t by new { t.DateTimeValue.Value.Month, t.DateTimeValue.Value.Year } into grp
+					select new
+					{
+						Total = grp.Sum(_ => _.MoneyValue),
+						year  = grp.Key.Year,
+						month = grp.Key.Month
+					},
+					from t in db.Types2
+					group t by new { t.DateTimeValue.Value.Month, t.DateTimeValue.Value.Year } into grp
+					select new
+					{
+						Total = grp.Sum(_ => _.MoneyValue),
+						year  = grp.Key.Year,
+						month = grp.Key.Month
+					});
+			}
+		}
+	}
+}