// Query using only standard operations:
private IDataList QueryOrdersCount()
{
// get OrderDetails table
dynamic od = workspace.table(Utils.OrderDetails.Name);
// aggregate OrderDetails table
dynamic qp = workspace.query("query_ordersCount", new
{
Product = od.ProductID, // group by order
Orders = Op.Count(od.OrderID) // how many orders are made for each product
});
return(qp.Query.Execute());
}
// Query using only standard operations:
// discount information for all products
private IDataList QueryProductDiscount()
{
// get OrderDetails table
dynamic od = workspace.table(Utils.OrderDetails.Name);
// aggregate OrderDetails table
dynamic qp = workspace.query("query_productDiscount", new
{
ds = Op.Mul(od.UnitPrice, od.Discount), // calculate discount for a product in an order
Product = od.ProductID, // group by product
OrderCount = Op.Count(od.OrderID), // order count for a product
DiscountSum = Op.Sum("ds") // total discount for a product in all orders
});
return(qp.Query.Execute());
}
// discount information for the specified product
private IDataList QueryProduct()
{
int productID = cbProduct.SelectedIndex;
// get OrderDetails table
dynamic od = workspace.table(Utils.OrderDetails.Name);
// aggregate OrderDetails table
dynamic qp = workspace.query("query_product", new
{
_range = od.ProductID.Eq(productID), // narrow data to the specified product
ds = Op.Mul(od.UnitPrice, od.Discount), // calculate the discount for a product in an order
Product = od.ProductID, // group by product
Discount = od.Discount, // and group by discount
OrdersCount = Op.Count(od.OrderID), // order count for products with the same discount in all orders
DiscountSum = Op.Sum("ds") // total discount for products with the same discount in all orders
});
return(qp.Query.Execute());
}
// products within specified unit price range
private IDataList QueryUnitPriceRange()
{
double from = (double)nudUnitPriceFrom.Value;
double to = (double)nudUnitPriceTo.Value;
// get OrderDetails table
dynamic od = workspace.table(Utils.OrderDetails.Name);
// aggregate OrderDetails table
dynamic qr = workspace.query("query_range", new
{
_range = od.UnitPrice.Gte(from).Lte(to), // narrow data to the specified unit price range
ds = Op.Mul(od.UnitPrice, od.Discount), // calculate the discount for a product in an order
Price = od.UnitPrice, // group by UnitPrice
OrdersCount = Op.Count(od.OrderID), // how many times products with the same price were ordered
MaxDiscount = Op.Max("ds") // what is the maximum discount for products with the same price
});
return(qr.Query.Execute());
}
// order information by month
private IDataList QueryMonth()
{
// get Orders table
dynamic orders = workspace.table(Utils.Orders.Name);
// create a query from Orders table with additional OrdersMonth column
dynamic qm = workspace.query("query_date", new
{
_base = "*", // all columns from the Orders table
OrderMonth = Op.DtPart(orders.OrderDate, DateTimeParts.Month) // month from Orders.OrderDate column
});
// aggregate query_date data
dynamic qdt = workspace.query("query_month", new
{
Month = qm.OrderMonth, // group by OrderMonth
FirstOrderDate = Op.Min(qm.OrderDate), // first order date in that month
OrderCount = Op.Count(qm.OrderID) // order count in that month
});
return(qdt.Query.Execute());
}
// products that were ordered more than specified number of times
private IDataList QueryGroupRange()
{
// get OrderDetails table
dynamic od = workspace.table(Utils.OrderDetails.Name);
// aggregate OrderDetails
dynamic qb = workspace.query("query_group", new
{
Product = od.ProductID, // group by ProducID
OrdersCount = Op.Count(od.OrderID), // order count of that product
SumPrice = Op.Sum(od.UnitPrice) // price sum of that product
});
// filter products by order count
long from = (long)nudGroupFilter.Value;
dynamic qg = workspace.query("query_group_range", new
{
_base = "*", // include all columns from the aggregated OrderDetails
_range = qb.OrdersCount.Gte(from).Lte(long.MaxValue) // filter products by order count
});
return(qg.Query.Execute());
}
// product information: how many times it was ordered (from Order Details) and how many items are left in stock (from Products table)
private IDataList QueryJoin()
{
// get tables
dynamic od = workspace.table(Utils.OrderDetails.Name);
dynamic pr = workspace.table(Utils.Products.Name);
// join OrderDetails and Products tables
dynamic jq = workspace.join("query_join", od, new
{
od = od.ProductID + od.OrderID, // get ProductID and OrderID from the OrderDetails table
pr = pr.UnitsInStock | od.ProductID == pr.ProductID // get UnitsInStock from the Products table and join OrderDetails and Products table on ProductID field
});
// aggregate joined data
dynamic qjf = workspace.query("query_join_result", new
{
Product = jq.ProductID, // group by ProductID
OrdersCount = Op.Count(jq.OrderID), // order count of a product
UnitsOnStock = Op.First(jq.UnitsInStock), // how many units of a product left in stock
});
return(qjf.Query.Execute());
}
static void Main(string[] args)
{
// Create and initialize a new workspace folder relative to the project root
Workspace workspace = new Workspace();
workspace.KeepFiles = KeepFileType.Results;
workspace.Init("workspace");
// Uncomment the following line to clear the workspace before each run
workspace.Clear();
// Extract JSON data from a zip file, if not already done
if (!File.Exists("seattle-pet-licenses.json") || !File.Exists("washington-zip-codes.json"))
{
ZipFile.ExtractToDirectory("data.zip", ".");
}
// Import the main license table
if (!workspace.TableExists("PetLicenses"))
{
List <PetLicense> collection = JsonConvert.DeserializeObject <List <PetLicense> >(File.ReadAllText("seattle-pet-licenses.json"));
ObjectConnector <PetLicense> connector = new ObjectConnector <PetLicense>(workspace, collection);
connector.GetData("PetLicenses");
workspace.Save();
Console.WriteLine("{0:d} licenses imported.", collection.Count);
}
// Import the secondary location table
if (!workspace.TableExists("Locations"))
{
List <Location> collection = JsonConvert.DeserializeObject <List <Location> >(File.ReadAllText("washington-zip-codes.json"));
ObjectConnector <Location> connector = new ObjectConnector <Location>(workspace, collection);
connector.GetData("Locations");
workspace.Save();
Console.WriteLine("{0:d} locations imported.", collection.Count);
}
// Retrieve the main table for use in constructing queries
dynamic licenses = workspace.table("PetLicenses");
// Number of licenses, by species
if (!workspace.QueryExists("BySpecies"))
{
dynamic query = workspace.query("BySpecies", new {
licenses.Species,
Count = Op.Count(licenses.Species)
});
query.Query.Execute();
}
// Number of licenses, by calendar year
if (!workspace.QueryExists("ByYear"))
{
// Create a query with all base table columns and add a Year field, extracted from the DateTime value
dynamic parent = workspace.query(new {
_base = "*",
Year = Op.DtPart(licenses.IssueDate, DateTimeParts.Year)
});
// Derive another query from the unnamed query above and perform the aggregation
dynamic query = workspace.query("ByYear", new {
parent.Year,
Count = Op.Count(parent.Year)
});
query.Query.Execute();
}
// Most popular dog names (sort criteria and row limits are applied later)
if (!workspace.QueryExists("DogNames"))
{
// Use the _range attribute to limit the results to dogs only
dynamic query = workspace.query("DogNames", new {
_range = licenses.Species.Eq("Dog"),
Species = licenses.Species,
DogName = licenses.AnimalName,
Count = Op.Count(licenses.AnimalName)
});
query.Query.Execute();
}
// List names for all species except dogs and cats
if (!workspace.QueryExists("OtherAnimals"))
{
// Create a query with specified base table columns and use the _filter attribute to limit the results
// (_filter is used instead of _range because the latter does not support the Ne operator)
dynamic parent = workspace.query(new {
_filter = licenses.Species.Ne("Dog").And().Ne("Cat"),
licenses.LicenseNumber,
licenses.Species,
licenses.AnimalName
});
// Derive another query from the unnamed query above and use First as the aggregation operator
// (otherwise the query will not yield any results)
dynamic query = workspace.query("OtherAnimals", new {
parent.LicenseNumber,
parent.Species,
AnimalName = Op.First(parent.AnimalName)
});
query.Query.Execute();
}
// Retrieve the secondary table for use in a join query
dynamic locations = workspace.table("Locations");
// Number of licenses in King County, by city
if (!workspace.QueryExists("KingCounty"))
{
// Create a join from the main table (licenses) to the secondary table (locations)
// * The addition expression to the left of the vertical bar denotes which secondary fields to include
// * The equality expression to the right of the vertical bar specifies the join condition
// * The assignment statement specifies a property of the anonymous type (its name is not significant)
dynamic join = workspace.join(licenses, new {
locale = locations.County + locations.City | licenses.ZipCode == locations.Zip
});
// Derive another query from the join query above, specifying both a _range and an aggregation
dynamic query = workspace.query("KingCounty", new {
_range = join.County.Eq("King"),
join.County,
join.City,
Count = Op.Count(join.LicenseNumber)
});
query.Query.Execute();
}
// Output query results in CSV format to the console
IDataList species = workspace.GetQueryData("BySpecies");
Console.WriteLine();
Console.WriteLine("// Licenses issued, by species");
DataList.Write(species, Console.Out);
IDataList years = workspace.GetQueryData("ByYear");
Console.WriteLine();
Console.WriteLine("// Licenses issued, by year");
DataList.Write(years, Console.Out);
IDataList names = workspace.GetQueryData("DogNames", 10); // Limit results to 10 rows
Console.WriteLine();
Console.WriteLine("// Most popular dog names");
DataList.Sort(names, "Count", false); // Sort by Count in descending order
DataList.Write(names, Console.Out);
IDataList others = workspace.GetQueryData("OtherAnimals");
Console.WriteLine();
Console.WriteLine("// Excluding dogs and cats");
DataList.Write(others, Console.Out);
IDataList cities = workspace.GetQueryData("KingCounty");
Console.WriteLine();
Console.WriteLine("// Licenses issued in King County, by city");
DataList.Write(cities, Console.Out);
}
