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);
}
