private void button5_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <Order>("SELECT VALUE o FROM OrderITEntities.Orders AS o WHERE COUNT(SELECT VALUE DISTINCT(d.product.productid) FROM o.OrderDetails AS d) > 1");
Dump(result);
}
}
private void button2_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <Order>("SELECT VALUE o FROM OrderITEntities.Orders AS o WHERE o.Customer.BillingAddress.City = 'New York'");
Dump(result);
}
}
private void button16_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <DbDataRecord>("SELECT o.OrderId, o.OrderDate, SUM(SELECT VALUE (d.UnitPrice - d.Discount) * d.Quantity FROM o.OrderDetails As d) AS Total FROM OrderITEntities.Orders AS o");
Dump(result);
}
}
private void button7_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <Order>("SELECT VALUE o FROM OrderITEntities.Orders AS o ORDER BY o.orderid SKIP 5 LIMIT 5");
Dump(result);
}
}
private void button15_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <DbDataRecord>("SELECT o.OrderId, o.OrderDate, (SELECT d.OrderDetailId, d.Product.ProductId, d.Quantity FROM o.OrderDetails As d) as Details FROM OrderITEntities.Orders AS o");
Dump(result);
}
}
private void button13_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <DbDataRecord>("SELECT o.OrderId, o.OrderDate, o.Customer.CompanyId, o.Customer.Name FROM OrderITEntities.Orders AS o");
Dump(result);
}
}
private void button8_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <DbDataRecord>("SELECT c.CompanyId, c.Name FROM OrderITEntities.Companies AS c");
Dump(result);
}
}
private void button10_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <DbDataRecord>("SELECT (o.ShippingAddress.Address + '-' + o.ShippingAddress.City + '-' + o.ShippingAddress.Country + '-' + o.ShippingAddress.ZipCode) AS ShippingAddress FROM OrderITEntities.Orders AS o");
Dump(result);
}
}
private void button3_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <Order>("SELECT VALUE o FROM OrderITEntities.Orders as o WHERE EXISTS (SELECT d FROM o.OrderDetails as d WHERE d.Product.Brand = 'MyBrand')");
Dump(result);
}
}
private void button20_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <DbDataRecord>("SELECT o.ShippingAddress.City, (SELECT VALUE o2 FROM OrderITEntities.Orders AS o2 WHERE o.ShippingAddress.City = o2.ShippingAddress.City) AS details FROM OrderITEntities.Orders AS o GROUP BY o.ShippingAddress.City HAVING COUNT(o.ShippingAddress.City) > 2");
Dump(result);
}
}
private void button23_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <Order>("SELECT VALUE o FROM OrderITEntities.Orders AS o ORDER BY Sum(SELECT VALUE (d.UnitPrice - d.Discount) * d.Quantity FROM o.OrderDetails AS d)");
Dump(result);
}
}
private void button4_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <Order>("SELECT VALUE o FROM OrderITEntities.Orders as o WHERE SUM(SELECT value d.discount FROM o.OrderDetails as d) = 0 ");
Dump(result);
}
}
private void button26_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <DbDataRecord>("SELECT o.OrderId, o.OrderDate FROM OrderITEntities.Orders AS o JOIN OrderITEntities.Companies AS c ON o.Customer.CompanyId = c.CompanyId AND o.ShippingAddress.City = c.ShippingCity");
Dump(result);
}
}
private void button27_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <Customer>("SELECT VALUE c FROM OFTYPE(OrderITEntities.Companies, OrderIT.Model.Customer) AS c");
Dump(result);
}
}
private void button25_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <Order>("SELECT VALUE o FROM OrderITEntities.Orders AS o ORDER BY o.Customer.ShippingAddress.City");
Dump(result);
}
}
private void button24_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <DbDataRecord>("SELECT o.OrderId, o.OrderDate, (Select VALUE d FROM o.OrderDetails AS d ORDER BY d.Quantity) AS Details FROM OrderITEntities.Orders AS o");
Dump(result);
}
}
private void button22_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <DbDataRecord>("SELECT VALUE o FROM OrderITEntities.Orders AS o ORDER BY o.ShippingAddress.City, o.ShippingAddress.ZipCode");
Dump(result);
}
}
private void button5_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities())
{
var result = ctx.CreateQuery <DbDataRecord>("SELECT o.OrderId, SUM(SELECT VALUE OrderITModel.GetUDFTotalAmountWithObjectParameter(d) FROM o.OrderDetails As d) FROM OrderITEntities.Orders AS o");
Dump(result);
}
}
private void button2_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities())
{
var result = ctx.CreateQuery <DbDataRecord>("SELECT o.OrderId, SUM(SELECT VALUE OrderITModel.GetUDFTotalAmount(d.UnitPrice, d.Quantity, d.Discount) FROM o.OrderDetails As d) FROM OrderITEntities.Orders AS o");
Dump(result);
}
}
private void button7_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities())
{
var result = ctx.CreateQuery <DbDataRecord>("SELECT OrderITModel.GetUDFAddresses(c) FROM OFTYPE(OrderITEntities.Companies, OrderIT.Model.Customer) As c");
Dump(result);
}
}
private void button8_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities())
{
var result = ctx.CreateQuery <CustomerProjection>("SELECT VALUE OrderIT.Model.CustomerProjection(detail.Name, detail.BillingAddress.Address, detail.BillingAddress.City, detail.ShippingAddress.Address, detail.ShippingAddress.City) FROM OFTYPE(OrderITEntities.Companies, OrderIT.Model.Customer) As detail").ToList();
Dump(result);
}
}
public List <KeyValue <int, string> > GetCompaniesProjection()
{
using (var ctx = new OrderITEntities()) {
var q = ctx.CreateQuery <DbDataRecord>("SELECT c.companyid, c.Name " +
"FROM OrderITEntities.Companies AS c").ToList();
return(q.Select(i => new KeyValue <int, string>(
(int)i["CompanyId"], i["Name"].ToString())).ToList());
}
}
private void button11_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <DbDataRecord>("SELECT o.OrderId, o.OrderDate, ROW(o.ShippingAddress.City, o.ShippingAddress.Address) AS ShippingAddress FROM OrderITEntities.Orders AS o");
Dump(result);
textConsole.Text = String.Empty;
foreach (var item in result)
{
textConsole.Text += ((DbDataRecord)item["ShippingAddress"])["Address"].ToString() + "\r\n";
}
}
}
private void button1_Click(object sender, EventArgs e)
{
var name = "New York";
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <Order>("SELECT VALUE c FROM " +
"OrderITEntities.Orders as c " +
"WHERE c.ShippingAddress.City = @city");
result.Parameters.Add(new ObjectParameter("city", name));
Dump(result);
}
}
private void button14_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <DbDataRecord>("SELECT o.OrderId, o.OrderDate, o.OrderDetails FROM OrderITEntities.Orders AS o");
Dump(result);
textConsole.Text = String.Empty;
foreach (var item in result)
{
textConsole.Text += item["OrderId"].ToString() + "\r\n";
foreach (var detail in (List <OrderDetail>)item["OrderDetails"])
{
textConsole.Text = " " + detail.OrderDetailId.ToString() + "\r\n";
}
}
}
}
private void SelectCustomersViaObjectServices(string testType, bool enableTracking, bool useCompiledQuery, bool enablePlanCaching, bool useEntitySQL)
{
_watch.Start(testType);
for (int i = 0; i < 50; i++)
{
using (OrderITEntities ctx = new OrderITEntities()) {
List <Customer> items;
if (!enableTracking)
{
ctx.Companies.MergeOption = MergeOption.NoTracking;
}
if (!useEntitySQL)
{
if (useCompiledQuery)
{
var it = compQuery.Invoke(ctx, "C").ToList();
foreach (var item in it)
{
object o = item;
}
}
else
{
string name = "C";
items = ctx.Companies.OfType <Customer>().Where(c => c.Name.StartsWith(name)).ToList();
}
}
else
{
var oq = ctx.CreateQuery <Customer>("SELECT VALUE c FROM OFTYPE(OrderITEntities.Companies, OrderIT.Model.Customer) AS c WHERE c.name LIKE @name");
if (!enableTracking)
{
oq.MergeOption = MergeOption.NoTracking;
}
if (!enablePlanCaching)
{
oq.EnablePlanCaching = false;
}
oq.Parameters.Add(new ObjectParameter("name", "C%"));
items = oq.ToList();
}
}
_watch.SaveLap();
}
_watch.Stop();
}