public void EqualsNull3([IncludeDataContexts("Northwind")] string context)
{
using (var db = new NorthwindDB())
AreEqual(
from employee in Employee where employee.ReportsToEmployee != null select new { employee.ReportsToEmployee, employee },
from employee in db.Employee where employee.ReportsToEmployee != null select new { employee.ReportsToEmployee, employee });
}
public void EqualsNull2(string context)
{
using (var db = new NorthwindDB())
AreEqual(
from employee in Employee where employee.ReportsToEmployee != null select employee,
from employee in db.Employee where employee.ReportsToEmployee != null select employee);
}
public void FirstOrDefaultEntitySet([IncludeDataContexts("Northwind")] string context)
{
using (var db = new NorthwindDB())
{
AreEqual(
Customer.Select(c => c.Orders.FirstOrDefault()),
db.Customer.Select(c => c.Orders.FirstOrDefault()));
}
}
static void CountTest()
{
using (var db = new NorthwindDB())
{
int count = db.Employee.Count();
Console.WriteLine(count);
}
}
public void SearchCondition2()
{
using (var db = new NorthwindDB())
{
AreEqual(
from cust in Customer
where cust.Orders.Count > 0 && cust.CompanyName.StartsWith("H")
select cust.CustomerID,
VisualBasicCommon.SearchCondition2(db));
}
}
public void AllNestedTest(string context)
{
using (var db = new NorthwindDB())
AreEqual(
from c in Customer
where Order.Where(o => o.Customer == c).All(o => Employee.Where(e => o.Employee == e).Any(e => e.FirstName.StartsWith("A")))
select c,
from c in db.Customer
where db.Order.Where(o => o.Customer == c).All(o => db.Employee.Where(e => o.Employee == e).Any(e => e.FirstName.StartsWith("A")))
select c);
}
public void MultipleQuery([IncludeDataContexts("Northwind")] string context)
{
using (var db = new NorthwindDB())
{
var q =
from p in db.Product
select db.Category.Select(zrp => zrp.CategoryName).FirstOrDefault();
q.ToList();
}
}
public void SearchCondition2(string context)
{
using (var db = new NorthwindDB(context))
{
var dd = GetNorthwindAsList(context);
AreEqual(
from cust in dd.Customer
where cust.Orders.Count > 0 && cust.CompanyName.StartsWith("H")
select cust.CustomerID,
VisualBasicCommon.SearchCondition2(db));
}
}
static void FirstTest()
{
using (var db = new NorthwindDB())
{
var query = db.Employee;
foreach (var employee in query)
{
Console.WriteLine("{0} {1}", employee.EmployeeID, employee.FirstName);
}
}
}
public void FreeText1()
{
using (var db = new NorthwindDB())
{
var q =
from c in db.Category
join t in db.FreeTextTable<Northwind.Category,int>("[Description]", "sweetest candy bread and dry meat")
on c.CategoryID equals t.Key
select c;
q.ToList();
}
}
public void FreeText3([IncludeDataContexts("Northwind")] string context)
{
using (var db = new NorthwindDB())
{
var q =
from t in db.FreeTextTable<Northwind.Category,int>(c => c.Description, "sweetest candy bread and dry meat")
join c in db.Category
on t.Key equals c.CategoryID
select c;
q.ToList();
}
}
public void InnerJoinOnSingleColumn(string context)
{
using (var db = new NorthwindDB(context))
{
var query =
from c in db.Category
join p in db.Product on c.CategoryID equals p.CategoryID
where !p.Discontinued
select c;
foreach (var category in query)
Console.WriteLine(category.CategoryID);
}
}
public void FreeText2(string context)
{
using (var db = new NorthwindDB())
{
var q =
from t in db.Category
where Sql.FreeText(Sql.AllColumns(), "sweet")
select t;
var list = q.ToList();
Assert.That(list.Count, Is.GreaterThan(0));
}
}
public void Test(string context)
{
using (var db = new NorthwindDB(context))
{
var zz =
from e in db.Employee
select e;
var lst = zz.ToList();
var item1 = lst.Take(1).Single();
var item2 = zz.Take(1).Single();
Assert.AreEqual(item1.EmployeeID, item2.EmployeeID);
}
}
static void SingleTableTest()
{
using (var db = new NorthwindDB())
{
var query =
from e in db.Employee
where e.EmployeeID > 5
orderby e.LastName, e.FirstName
select e;
foreach (var employee in query)
{
Console.WriteLine("{0} {1}, {2}", employee.EmployeeID, employee.LastName, employee.FirstName);
}
}
}
public void SelftJoinOptimized(string context)
{
using (var db = new NorthwindDB(context))
{
var q1 = from od in db.Order
join od2 in db.Order on od.OrderID equals od2.OrderID
select od;
Assert.AreEqual(0, q1.GetTableSource().Joins.Count);
var q2 = from od in db.Order
join od2 in db.Order on new { od.OrderID, od.EmployeeID } equals new { od2.OrderID, od2.EmployeeID }
select od;
Assert.AreEqual(0, q2.GetTableSource().Joins.Count);
}
}
public void Contains12(string context)
{
using (var db = new NorthwindDB())
{
var q =
from e in db.EmployeeTerritory
group e by e.Employee into g
where g.Key.EmployeeTerritories.Count() > 1 && g.Count() > 2
select new
{
g.Key.LastName,
//cnt = g.Where(t => t.Employee.FirstName.Contains("an")).Count(),
};
q.ToList();
}
}
public void Contains12(string context)
{
using (var db = new NorthwindDB())
{
var q =
from e in db.EmployeeTerritory
group e by e.Employee into g
where g.Key.EmployeeTerritories.Count() > 1 && g.Count() > 2
select new
{
g.Key.LastName,
//cnt = g.Where(t => t.Employee.FirstName.Contains("an")).Count(),
};
q.ToList();
}
}
public void ExpressionTest1([IncludeDataContexts("Northwind")] string context)
{
Expression <Func <Northwind.Customer, bool> > pred1 = cust => cust.Country == "UK";
Expression <Func <Northwind.Customer, bool> > pred2 = cust => cust.Country == "France";
var param = Expression.Parameter(typeof(Northwind.Customer), "x");
var final = Expression.Lambda <Func <Northwind.Customer, bool> >(
Expression.OrElse(
Expression.Invoke(pred1, param),
Expression.Invoke(pred2, param)
), param);
using (var db = new NorthwindDB())
{
var count = db.Customer.Count(final);
}
}
public void InnerJoinToSelf([NorthwindDataContext] string context)
{
using (var db = new NorthwindDB(context))
{
var dd = GetNorthwindAsList(context);
//Configuration.Linq.OptimizeJoins = false;
var q = from od in db.OrderDetail
join o1 in db.Order on od.OrderID equals o1.OrderID
join o2 in db.Order on o1.OrderID equals o2.OrderID
join o3 in db.Order on new { o1.OrderID, ID2 = o2.OrderID } equals new { o3.OrderID, ID2 = o3.OrderID }
join od1 in db.OrderDetail on new { o1.OrderID, od.ProductID } equals new { od1.OrderID, od1.ProductID }
join od2 in db.OrderDetail on new { od1.OrderID, od.ProductID } equals new { od2.OrderID, od2.ProductID }
join od3 in db.OrderDetail on new { od1.OrderID, od2.ProductID } equals new { od3.OrderID, od3.ProductID }
orderby od.OrderID, od.ProductID
select new
{
OrderID = od.OrderID,
ProductID = od.ProductID,
OrderID1 = od3.OrderID,
OrderID2 = od2.OrderID,
};
var q2 = from od in dd.OrderDetail
join o1 in dd.Order on od.OrderID equals o1.OrderID
join o2 in dd.Order on o1.OrderID equals o2.OrderID
join o3 in dd.Order on new { o1.OrderID, ID2 = o2.OrderID } equals new { o3.OrderID, ID2 = o3.OrderID }
join od1 in dd.OrderDetail on new { o1.OrderID, od.ProductID } equals new { od1.OrderID, od1.ProductID }
join od2 in dd.OrderDetail on new { od1.OrderID, od.ProductID } equals new { od2.OrderID, od2.ProductID }
join od3 in dd.OrderDetail on new { od1.OrderID, od2.ProductID } equals new { od3.OrderID, od3.ProductID }
orderby od.OrderID, od.ProductID
select new
{
OrderID = od.OrderID,
ProductID = od.ProductID,
OrderID1 = od3.OrderID,
OrderID2 = od2.OrderID,
};
Assert.AreEqual(q, q2);
var ts = q.GetTableSource();
Assert.AreEqual(1, ts.Joins.Count);
}
}
static void AssociationCountTest()
{
using (var db = new NorthwindDB())
{
var query =
from p in db.Product
select new
{
p.OrderDetails.Count,
p.ProductName
};
foreach (var item in query)
{
Console.WriteLine(item);
}
}
}
static void AssociationInnerJoinTest()
{
using (var db = new NorthwindDB())
{
var query =
from p in db.Product
select new
{
p.Category.CategoryName,
p.ProductName
};
foreach (var item in query)
{
Console.WriteLine(item);
}
}
}
public static IQueryable <QuarterlyOrder> VwQuarterlyOrders(this NorthwindDB db, int year)
{
var result =
(from o in db.Orders
join c in db.Customers on o.CustomerID equals c.CustomerID into customersJoin
from c2 in customersJoin.DefaultIfEmpty()
where o.OrderDate.Value.Year == year
select new QuarterlyOrder
{
CustomerID = c2.CustomerID,
CompanyName = c2.CompanyName,
City = c2.City,
Country = c2.Country
})
.Distinct();
return(result);
}
public static IQueryable <SalesTotalsByAmount> VwSalesTotalsByAmount(this NorthwindDB db, int year, decimal amount)
{
var result =
from c in db.Customers
join o in (from o2 in db.Orders
join os in VwOrdersSubtotals(db) on o2.OrderID equals os.OrderID
select new { Order = o2, OrderSubtotal = os }) on c.CustomerID equals o.Order.CustomerID
where o.OrderSubtotal.Subtotal > amount && o.Order.ShippedDate.Value.Year == year
select new SalesTotalsByAmount
{
SaleAmount = o.OrderSubtotal.Subtotal,
OrderID = o.Order.OrderID,
CompanyName = c.CompanyName,
ShippedDate = o.Order.ShippedDate
};
return(result);
}
public static IQueryable <OrderSubtotal> VwOrdersSubtotals(this NorthwindDB db)
{
var result =
from od in db.OrderDetails
group od by new
{
od.OrderID
}
into g
select new OrderSubtotal
{
OrderID = g.Key.OrderID,
Subtotal = g.Sum(
p => Convert.ToDecimal(p.UnitPrice * p.Quantity * (1 - (decimal)p.Discount) / 100) * 100)
};
return(result);
}
static void AssociationObjectTest()
{
using (var db = new NorthwindDB())
{
var query =
from o in db.Order
select new Order
{
OrderID = o.OrderID,
Customer = o.Customer
};
foreach (var item in query)
{
Console.WriteLine(item);
}
}
}
static void NorthwindTest()
{
using (var db = new NorthwindDB())
{
var q =
from order in db.Orders
select new
{
order.Customer.CompanyName,
Count = order.OrderDetails.Count(),
};
foreach (var item in q)
{
WriteLine($"Company Name: {item.CompanyName}, Count: {item.Count}");
}
}
}
public async Task TestProperty_IsNull_ServerSideOnly(
[IncludeDataSources(TestProvName.Northwind)] string context)
{
string defaultCategory = "test";
string statement = "ISNULL([c_1].[CategoryName], @defaultCategory)";
using (var db = new NorthwindDB(context))
{
var query = (from c in db.Category
select Sql.Ext.SqlServer().IsNull(c.CategoryName, defaultCategory));
Assert.That(query.ToString() !.Contains(statement));
var results = await query.ToListAsync();
Assert.IsTrue(results.Any());
Assert.That(db.LastQuery !.Contains(statement));
}
}
public void ComplexAllTest(string context)
{
using (var db = new NorthwindDB(context))
{
var dd = GetNorthwindAsList(context);
AreEqual(
from o in dd.Order
where
dd.Customer.Where(c => c == o.Customer).All(c => c.CompanyName.StartsWith("A")) ||
dd.Employee.Where(e => e == o.Employee).All(e => e.FirstName.EndsWith("t"))
select o,
from o in db.Order
where
db.Customer.Where(c => c == o.Customer).All(c => c.CompanyName.StartsWith("A")) ||
db.Employee.Where(e => e == o.Employee).All(e => e.FirstName.EndsWith("t"))
select o);
}
}
static void MethodExpressionTest()
{
using (var db = new NorthwindDB())
{
var query =
from c in db.Customer
select new
{
sum1 = OrderCount(c, "SP"),
sum2 = OrderCount(c, "NM")
};
foreach (var item in query)
{
Console.WriteLine(item);
}
}
}
static void InsertWithIdentityTest2()
{
using (var db = new NorthwindDB())
{
var value =
db
.Into(db.Employee)
.Value(e => e.FirstName, "John")
.Value(e => e.LastName, "Shepard")
.Value(e => e.Title, () => "Spectre")
.Value(e => e.HireDate, () => Sql.CurrentTimestamp)
.InsertWithIdentity();
Console.WriteLine(value);
db.Employee.Delete(e => e.EmployeeID == Convert.ToInt32(value));
}
}
static void NorthwindTest()
{
using (var db = new NorthwindDB())
{
var q =
from order in db.Orders
select new
{
order.Customer.CompanyName,
Count = order.OrderDetails.Count(),
};
foreach (var item in q)
{
WriteLine($"Company Name: {item.CompanyName}, Count: {item.Count}");
}
}
}
static void MultipleInsertTest1()
{
using (var db = new NorthwindDB())
{
var value =
db.Region
.Where(r => r.RegionID > 2)
.Insert(db.Region, r => new Region()
{
RegionID = r.RegionID + 100,
RegionDescription = "Copy Of " + r.RegionDescription
});
Console.WriteLine(value);
db.Region.Delete(r => r.RegionDescription.StartsWith("Copy Of "));
}
}
public void NestedSingleOrDefaultTest(string context)
{
try
{
LinqToDB.Common.Configuration.Linq.AllowMultipleQuery = true;
using (var db = new NorthwindDB(context))
{
var dd = GetNorthwindAsList(context);
AreEqual(
dd.Customer.Select(c => c.Orders.Take(1).SingleOrDefault()),
db.Customer.Select(c => c.Orders.Take(1).SingleOrDefault()));
}
}
finally
{
LinqToDB.Common.Configuration.Linq.AllowMultipleQuery = false;
}
}
public void SearchCondition4([NorthwindDataContext] string context)
{
using (var db = new NorthwindDB(context))
{
var cQuery =
from order in db.Order
where order.OrderDate == new DateTime(1997, 11, 14)
select order.OrderID;
var cSharpResults = cQuery.ToList();
var vbResults = (VisualBasicCommon.SearchCondition4(db)).ToList();
AreEqual(
cSharpResults,
vbResults);
}
}
public void InnerJoinOnSingleColumn(string context)
{
using (var db = new NorthwindDB(context))
{
var query =
from c in db.Category
join p in db.Product on c.CategoryID equals p.CategoryID
where !p.Discontinued
select c;
foreach (var category in query)
{
#if !APPVEYOR
Console.WriteLine(category.CategoryID);
#endif
}
}
}
public void SearchCondition3(string context)
{
using (var db = new NorthwindDB())
{
var cQuery = from order in db.Order
where order.OrderDate == new DateTime(1997, 11, 14)
select order.OrderID;
var cSharpResults = cQuery.ToList();
var vbResults = (VisualBasicCommon.SearchCondition3(db)).ToList();
AreEqual(
cSharpResults,
vbResults);
}
}
public void EmployeeHierarchy(string context)
{
using (var db = new NorthwindDB(context))
{
var employeeHierarchyCte = db.GetCte <EmployeeHierarchyCTE>(employeeHierarchy =>
{
return
((
from e in db.Employee
where e.ReportsTo == null
select new EmployeeHierarchyCTE
{
EmployeeID = e.EmployeeID,
LastName = e.LastName,
FirstName = e.FirstName,
ReportsTo = e.ReportsTo,
HierarchyLevel = 1
}
)
.Concat
(
from e in db.Employee
from eh in employeeHierarchy.InnerJoin(eh => e.ReportsTo == eh.EmployeeID)
select new EmployeeHierarchyCTE
{
EmployeeID = e.EmployeeID,
LastName = e.LastName,
FirstName = e.FirstName,
ReportsTo = e.ReportsTo,
HierarchyLevel = eh.HierarchyLevel + 1
}
));
});
var result =
from eh in employeeHierarchyCte
orderby eh.HierarchyLevel, eh.LastName, eh.FirstName
select eh;
var resultdStr = result.ToString();
var data = result.ToArray();
}
}
public void Query_Task_2_1()
{
using (var connection = new NorthwindDB(_connectionString))
{
var result = connection.Products
.Select(product => new
{
Product = product.ProductName,
Category = product.Category.CategoryName,
Supplier = product.Supplier.CompanyName
});
foreach (var product in result)
{
Console.WriteLine("{0} | {1} | {2}", product.Product,
product.Category, product.Supplier);
}
}
}
public void Query_Task_2_3()
{
using (var connection = new NorthwindDB(_connectionString))
{
var result = connection.Regions
.Join(connection.Territories,
region => region.RegionID,
territory => territory.RegionID,
(region, territory) => new
{
Region = region.RegionDescription,
TerritoryId = territory.TerritoryID
})
.Join(connection.EmployeeTerritories,
region => region.TerritoryId,
employeeTerritories => employeeTerritories.TerritoryID,
(region, employeeTerritory) => new
{
Region = region.Region,
EmployeeId = employeeTerritory.EmployeeID
})
.Join(connection.Employees,
region => region.EmployeeId,
employee => employee.EmployeeID,
(region, employee) => new
{
Region = region.Region,
EmployeeId = employee.EmployeeID
})
.Distinct()
.GroupBy(region => region.Region,
(region, group) => new
{
Region = region,
CountEmployees = group.Count(employee => employee.EmployeeId != 0)
});
foreach (var regionInfo in result)
{
Console.WriteLine("{0} | {1}", regionInfo.Region, regionInfo.CountEmployees);
}
}
}
static void InnerJoinTest()
{
using (var db = new NorthwindDB())
{
var query =
from p in db.Product
join c in db.Category on p.CategoryID equals c.CategoryID
select new
{
c.CategoryName,
p.ProductName
};
foreach (var item in query)
{
Console.WriteLine(item);
}
}
}
public void Query_Task_2_4()
{
using (var connection = new NorthwindDB(_connectionString))
{
var result = connection.Employees
.Join(connection.Orders,
employee => employee.EmployeeID,
order => order.EmployeeID,
(employee, order) => new
{
FirstName = employee.FirstName,
LastName = employee.LastName,
ShipperId = order.ShipVia
})
.Join(connection.Shippers,
order => order.ShipperId,
shipper => shipper.ShipperID,
(order, shipper) => new
{
FirstName = order.FirstName,
LastName = order.LastName,
Shipper = shipper.CompanyName
})
.Distinct()
.OrderBy(employee => employee.FirstName)
.GroupBy(employee => new { employee.FirstName, employee.LastName },
(employee, group) => new
{
FirstName = employee.FirstName,
LastName = employee.LastName,
Shippers = group.OrderBy(shipper => shipper.Shipper)
});
foreach (var employeeInfo in result)
{
Console.WriteLine($"{employeeInfo.FirstName} {employeeInfo.LastName}");
foreach (var shipper in employeeInfo.Shippers)
{
Console.WriteLine($"\t {shipper.Shipper}");
}
}
}
}
static void MultiLevelAssociationTest()
{
using (var db = new NorthwindDB())
{
var query =
from o in db.OrderDetail
select new
{
o.Product.ProductName,
o.Order.OrderID,
o.Order.Employee.ReportsToEmployee.Region
};
foreach (var item in query)
{
Console.WriteLine(item);
}
}
}
public static void EntityFrameworkConsole()
{
using (var con = new NorthwindDB())
{
var needCategoryID = con.Categories.Select(c => c.CategoryID).Min();
var orders = (from p in con.Products
join od in con.Order_Details on p.ProductID equals od.ProductID
join odd in con.Order_Details on od.OrderID equals odd.OrderID
where p.CategoryID == needCategoryID
select new
{
od.Order.OrderID,
od.Order.OrderDate,
od.Order.RequiredDate,
od.Order.ShippedDate,
od.Order.Customer.CompanyName,
odd.Product.ProductName,
odd.Product.Category.CategoryName,
odd.Quantity,
odd.UnitPrice,
odd.Discount
}
).Distinct();
int lastOrderId = 0;
foreach (var o in orders)
{
if (o.OrderID != lastOrderId)
{
Console.WriteLine("-------------------");
Console.WriteLine("Заказ №{0} от {1}", o.OrderID, o.OrderDate);
Console.WriteLine("Дата подтверждения {0}", o.RequiredDate);
Console.WriteLine("Дата доставки {0}", o.ShippedDate);
Console.WriteLine("Заказчик \"{0}\"", o.CompanyName);
Console.WriteLine("Включает следующие продукты:");
lastOrderId = o.OrderID;
}
Console.WriteLine(" - {0} : {1} | {2} | {3} | {4}", o.ProductName, o.CategoryName, o.Quantity, o.UnitPrice, o.Discount);
}
}
Console.ReadKey();
}
// Список сотрудников с указанием региона, за который они отвечают
static void EmployeesWithRegion()
{
using (var db = new NorthwindDB())
{
var employees = db.Employees
.LoadWith(e => e.EmployeeTerritories);
var employeeTerritotries = employees.SelectMany(e => e.EmployeeTerritories);
var result = employeeTerritotries
.InnerJoin(db.Territories.LoadWith(t => t.Region),
(t1, t2) => t1.TerritoryID == t2.TerritoryID,
(t1, t2) => new { t1.EmployeeID, t2.Region })
.Distinct()
.InnerJoin(employees,
(region, employee) => region.EmployeeID == employee.EmployeeID,
(region, employee) => new { employee, region.Region })
.ToList();
}
}
public void ConvertDataToDecimal(string context)
{
using (var db = new NorthwindDB(context))
{
var actual = (from od in db.OrderDetail
select
Sql.AsSql(od.UnitPrice * od.Quantity * (decimal)(1 - od.Discount))).ToArray();
var expected = (from od in db.OrderDetail
select
od.UnitPrice * od.Quantity * (decimal)(1 - od.Discount)).ToArray();
Assert.AreEqual(actual.Length, expected.Length);
for (var i = 0; i < actual.Length; i++)
{
Assert.GreaterOrEqual(0.01m, Math.Abs(actual[i] - expected[i]));
}
}
}
public void InnerJoinOnMultipleColumns(string context)
{
using (var db = new NorthwindDB(context))
{
var query =
from p in db.Product
from o in db.Order
join d in db.OrderDetail
on new { p.ProductID, o.OrderID }
equals new { d.ProductID, d.OrderID }
select new
{
p.ProductID,
o.OrderID,
};
var data = query.ToArray();
Assert.IsNotEmpty(data);
}
}
public void InnerJoinOnMultipleColumns(string context)
{
using (var db = new NorthwindDB(context))
{
var query =
from p in db.Product
from o in db.Order
join d in db.OrderDetail
on new { p.ProductID, o.OrderID }
equals new { d.ProductID, d.OrderID }
select new
{
p.ProductID,
o.OrderID,
};
foreach (var item in query)
Console.WriteLine(item);
}
}
public void LeftJoin2([NorthwindDataContext] string context)
{
using (var db = new NorthwindDB(context))
{
var q = from od in db.OrderDetail
from o1 in db.Order.Where(o => o.OrderID == od.OrderID).DefaultIfEmpty()
from o2 in db.Order.Where(o => o.OrderID == od.OrderID && od.ProductID == 100).DefaultIfEmpty()
orderby od.OrderID
select new
{
od.OrderID,
o1.OrderDate,
OrderID1 = o1.OrderID,
OrderID2 = o2.OrderID,
};
var ts = q.GetTableSource();
Assert.AreEqual(2, ts.Joins.Count(j => j.JoinType == JoinType.Left));
}
}
protected override void Setup()
{
//using (new DbManager("PerformanceTest"))
//{}
db = new NorthwindDB();
Customers = db.Customers.ToList();
Employees = db.Employees.ToList();
Orders = db.Orders. ToList();
Products = db.Products. ToList();
foreach (var o in Orders)
{
o.Customer = Customers.SingleOrDefault(c => c.Id == o.CustomerID);
o.Employee = Employees.SingleOrDefault(e => e.EmployeeID == o.EmployeeID);
}
foreach (var c in Customers)
c.Orders = Orders.Where(o => c.Id == o.CustomerID).ToList();
}
static void Main(string[] args)
{
#if DEBUG
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (s, s1) => Debug.WriteLine(s, s1);
#endif
using (var db = new NorthwindDB())
{
var q =
from c in db.Customers
select new
{
c.CompanyName,
OrderCount = c.Orders.Count()
};
foreach (var c in q)
Console.WriteLine(c);
}
}
protected override void Setup()
{
db = new NorthwindDB();
Customers = db.Customers.ToList();
Employees = db.Employees.ToList();
Orders = db.Orders. ToList();
Products = db.Products. ToList();
foreach (var o in Orders)
{
o.Customer = Customers.SingleOrDefault(c => c.Id == o.CustomerID);
o.Employee = Employees.SingleOrDefault(e => e.EmployeeID == o.EmployeeID);
}
foreach (var c in Customers)
c.Orders = Orders.Where(o => c.Id == o.CustomerID).ToList();
DbManager.TurnTraceSwitchOn();
DbManager.WriteTraceLine = (message, displayName) => Debug.WriteLine(message, displayName); // any logging function
}
protected override void Setup()
{
db = new NorthwindDB();
Customers = db.Customers.ToList();
Employees = db.Employees.ToList();
Orders = db.Orders. ToList();
Products = db.Products. ToList();
foreach (var o in Orders)
{
o.Customer = Customers.SingleOrDefault(c => c.Id == o.CustomerID);
o.Employee = Employees.SingleOrDefault(e => e.EmployeeID == o.EmployeeID);
}
foreach (var c in Customers)
c.Orders = Orders.Where(o => c.Id == o.CustomerID).ToList();
DataConnection.TurnTraceSwitchOn();
DataConnection.WriteTraceLine = (s, s1) => Debug.WriteLine(s, s1);
}
public void Test(string context)
{
using (var db = new NorthwindDB(context))
{
var zz =
from e in db.Employee
from et in db.EmployeeTerritory
where et.EmployeeID == e.EmployeeID
group e by new { e.EmployeeID }
into g
select new
{
g.Key.EmployeeID,
//g.FirstOrDefault().FirstName,
db.Employee.FirstOrDefault(em => em.EmployeeID == g.Key.EmployeeID).FirstName,
};
// zz = zz.OrderBy(a => a.FirstName);
var res = zz.ToList();
}
}
public void Contains10(string context)
{
using (var db = new NorthwindDB())
{
var arr = new[]
{
new Northwind.Order { OrderID = 11000 },
new Northwind.Order { OrderID = 11001 },
new Northwind.Order { OrderID = 11002 }
};
var q =
from e in db.Employee
from o in e.Orders
where arr.Contains(o)
select new
{
e.FirstName,
o.OrderID,
};
q.ToList();
}
}
public void GroupByAggregate2()
{
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));
}
public void GrooupByAssociation4()
{
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 ObjectUnion()
{
using (var db = new NorthwindDB())
{
var q1 =
from p in db.Product
join c in db.Category on p.CategoryID equals c.CategoryID into g
from c in g.DefaultIfEmpty()
select new
{
p,
c.CategoryName,
p.ProductName
};
var q2 =
from p in db.Product
join c in db.Category on p.CategoryID equals c.CategoryID into g
from c in g.DefaultIfEmpty()
select new
{
p,
c.CategoryName,
p.ProductName
};
var q = q1.Union(q2).Take(5);
foreach (var item in q)
{
Console.WriteLine(item);
}
}
}
public void Concat7()
{
using (var db = new NorthwindDB())
AreEqual(
Customer.Where(c => c.Orders.Count <= 1).Concat( Customer.Where(c => c.Orders.Count > 1)),
db.Customer.Where(c => c.Orders.Count <= 1).Concat(db.Customer.Where(c => c.Orders.Count > 1)));
}
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);
}
}