public void CanAggregate() { // First create some test data for the queries. IRepository <Product> repoCrud = GetRepo(); var products = GetSampleProducts(); repoCrud.DeleteAll(); products.ForEach(p => repoCrud.Create(p)); // NOTES: // 1. The Repository<T>'s base class is RepositoryQueryable. // 2. We are only using a IRepository<T> here initially for adding in-memory test data. // 3. But this could be initialized via new RepositoryQuerable(...) constructors against a real Database. // 4. Notice that IRepositoryQueryable does not know anything about the entity(Product). IRepositoryQueryable repo = (IRepositoryQueryable)repoCrud; // 0. Named filters first, to show their example in each section min, max, group, etc. repo.AddNamedFilter("InStock Filter", Query.New().Where("IsInStock").Is(true)); repo.AddNamedFilter("Expensive", Query.New().Where("Cost").MoreThan(20)); // 1. GROUPING var groups = repo.Group <int>("Cost"); var groups2 = repo.Group <int>("Cost", Query.New().Where("Make").Not("Honda")); var groups3 = repo.Group <int>("Cost", "Expensive"); // same as groups2. // 2. DISTINCT var names = repo.Distinct <string>("Make"); var names2 = repo.Distinct <string>("Make", Query.New().Where("Cost").MoreThan(30).OrderByDescending("Make")); var names3 = repo.Distinct <string>("Make", "Expensive"); // 3. COUNT var count1 = repo.Count(); var count2 = repo.Count(Query <object> .New().Where("Make").Is("Toyota").And("IsInStock").Is(true)); var count3 = repo.Count("InStock Filter"); // 4. MIN, MAX, AVG, SUM int count = repo.Count(); double min = repo.Min("Cost"); double min2 = repo.Min("Cost", Query.New().Where("Make").In <string>("Bmw", "Lexus")); double min3 = repo.Min("Cost", "InStock Filter"); double max = repo.Max("Cost"); double max2 = repo.Max("Cost", Query.New().Where("Make").NotIn <string>("Bmw", "Mercedes", "Lexus")); double max3 = repo.Max("Cost", "InStock Filter"); double sum = repo.Sum("Cost"); double sum2 = repo.Sum("Cost", Query.New().Where("Make").In <string>("Honda", "Toyota").And("IsInStock").Is(true)); double sum3 = repo.Sum("Cost", "InStock Filter"); double avg = repo.Avg("Cost"); double avg2 = repo.Avg("Cost", Query.New().Where("Make").Like("Nissan")); double avg3 = repo.Avg("Cost", "InStock Filter"); // 5. To Table DataTable all = repo.ToTable(); DataTable all2 = repo.ToTable(Query.New().Where("Make").Is("Honda")); DataTable all3 = repo.ToTable("InStock Filter"); // 6. Exists bool any1 = repo.Any(Query.New().Where("IsInStock").Is(true)); bool any2 = repo.Any("InStock Filter"); // 7. Limit number of records. repo.ToTable(Query.New().Limit(2)); var table1 = repo.ToTable(Query.New().Select("Make", "Model").Where("Cost").MoreThan(30).Limit(2)); // Check min double minLinq = products.Min(p => p.Cost); double min2Linq = products.Where(p => (p.Make == "Bmw" || p.Make == "Lexus")).Min(p => p.Cost); double min3Linq = products.Where(p => p.IsInStock).Min(p => p.Cost); double maxLinq = products.Max(p => p.Cost); double max2Linq = products.Where(p => (p.Make != "Bmw" && p.Make != "Mercedes" && p.Make != "Lexus")).Max(p => p.Cost); double max3Linq = products.Where(p => p.IsInStock).Max(p => p.Cost); double sumLinq = products.Sum(p => p.Cost); double sum2Linq = products.Where(p => (p.Make == "Honda" || p.Make == "Toyota") && p.IsInStock == true).Sum(p => p.Cost); double sum3Linq = products.Where(p => p.IsInStock).Sum(p => p.Cost); double avgLinq = products.Average(p => p.Cost); double avg2Linq = products.Where(p => p.Make == "Nissan").Average(p => p.Cost); double avg3Linq = products.Where(p => p.IsInStock).Average(p => p.Cost); int countLinq = products.Count; int count2Linq = products.Where(p => p.Make == "Toyota" && p.IsInStock).Count(); int count3Linq = products.Where(p => p.IsInStock).Count(); // Check count Assert.AreEqual(count1, countLinq); Assert.AreEqual(count2, count2Linq); Assert.AreEqual(count3, count3Linq); // Check min Assert.AreEqual(min, minLinq); Assert.AreEqual(min2, min2Linq); Assert.AreEqual(min3, min3Linq); // Check max Assert.AreEqual(max, maxLinq); Assert.AreEqual(max2, max2Linq); Assert.AreEqual(max3, max3Linq); // Check sum Assert.AreEqual(sum, sumLinq); Assert.AreEqual(sum2, sum2Linq); Assert.AreEqual(sum3, sum3Linq); // Check avg Assert.AreEqual(avg.ToString().Substring(0, 7), avgLinq.ToString().Substring(0, 7)); Assert.AreEqual(avg2.ToString().Substring(0, 7), avg2Linq.ToString().Substring(0, 7)); Assert.AreEqual(avg3.ToString().Substring(0, 7), avg3Linq.ToString().Substring(0, 7)); }
/// <summary> /// Run the application. /// </summary> public override BoolMessageItem Execute() { // First create some test data for the queries. IRepository <Product> repoCrud = new RepositoryInMemory <Product>(); var products = GetSampleProducts(); products.ForEach(p => repoCrud.Create(p)); // NOTES: // 1. The Repository<T>'s base class is RepositoryQueryable. // 2. We are only using a IRepository<T> here initially for adding in-memory test data. // 3. But this could be initialized via new RepositoryQuerable(...) constructors against a real Database. // 4. Notice that IRepositoryQueryable does not know anything about the entity(Product). IRepositoryQueryable repo = (IRepositoryQueryable)repoCrud; // 0. Named filters first, to show their example in each section min, max, group, etc. repo.AddNamedFilter("InStock Filter", Query <object> .New().Where("IsInStock").Is(true)); repo.AddNamedFilter("Expensive", Query <object> .New().Where("Cost").MoreThan(20)); // 1. GROUPING var groups = repo.Group <int>("Cost"); var groups2 = repo.Group <int>("Cost", Query <object> .New().Where("Make").Not("Honda")); var groups3 = repo.Group <int>("Cost", "Expensive"); // same as groups2. // 2. DISTINCT var names = repo.Distinct <string>("Make"); var names2 = repo.Distinct <string>("Make", Query <object> .New().Where("Cost").MoreThan(30).OrderByDescending("Make")); var names3 = repo.Distinct <string>("Make", "Expensive"); // 3. COUNT var count1 = repo.Count(); var count2 = repo.Count(Query <object> .New().Where("IsInStock").Is(true)); var count3 = repo.Count("InStock Filter"); // 4. MIN, MAX, AVG, SUM int count = repo.Count(); double min = repo.Min("Cost"); double min2 = repo.Min("Cost", Query <object> .New().Where("Make").In <string>("Bmw", "Lexus")); double min3 = repo.Min("Cost", "InStock Filter"); double max = repo.Max("Cost"); double max2 = repo.Max("Cost", Query <object> .New().Where("Make").NotIn <string>("Bmw", "Lexus")); double max3 = repo.Max("Cost", "InStock Filter"); double sum = repo.Sum("Cost"); double sum2 = repo.Sum("Cost", Query <object> .New().Where("Make").In <string>("Honda", "Toyota").And("IsInStock").Is(true)); double sum3 = repo.Sum("Cost", "InStock Filter"); double avg = repo.Avg("Cost"); double avg2 = repo.Avg("Cost", Query <object> .New().Where("Make").Like("Nissan")); double avg3 = repo.Avg("Cost", "InStock Filter"); // 5. To Table DataTable all = repo.ToTable(); DataTable all2 = repo.ToTable(Query <object> .New().Where("Make").Is("Honda")); DataTable all3 = repo.ToTable("InStock Filter"); // 6. Exists bool any1 = repo.Any(Query <object> .New().Where("IsInStock").Is(true)); bool any2 = repo.Any("InStock Filter"); // 7. Limit number of records. repo.ToTable(Query <object> .New().Limit(2)); var table1 = repo.ToTable(Query <object> .New().Select("Make", "Model").Where("Cost").MoreThan(30).Limit(2)); return(BoolMessageItem.True); }