Exemple #1
0
 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 });
 }
Exemple #2
0
 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()));
     }
 }
Exemple #4
0
		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));
			}
		}
Exemple #6
0
 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();
            }
        }
Exemple #8
0
		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));
			}
		}
Exemple #9
0
		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();
            }
        }
Exemple #12
0
		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);
			}
		}
Exemple #13
0
        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));
            }
        }
Exemple #14
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);
			}
		}
Exemple #15
0
		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);
				}
			}
		}
Exemple #16
0
        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);
            }
        }
Exemple #17
0
        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();
            }
        }
Exemple #18
0
        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();
            }
        }
Exemple #19
0
        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);
            }
        }
Exemple #20
0
        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);
            }
        }
Exemple #21
0
        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);
                }
            }
        }
Exemple #22
0
        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);
                }
            }
        }
Exemple #23
0
        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);
        }
Exemple #24
0
        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);
        }
Exemple #25
0
        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);
        }
Exemple #26
0
        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);
                }
            }
        }
Exemple #27
0
		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}");
				}
			}
		}
Exemple #28
0
        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));
            }
        }
Exemple #29
0
 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);
     }
 }
Exemple #30
0
        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);
                }
            }
        }
Exemple #31
0
        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));
            }
        }
Exemple #32
0
        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}");
                }
            }
        }
Exemple #33
0
        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;
     }
 }
Exemple #35
0
        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);
            }
        }
Exemple #36
0
        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
                }
            }
        }
Exemple #37
0
        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);
            }
        }
Exemple #38
0
        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();
            }
        }
Exemple #39
0
        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);
                }
            }
        }
Exemple #40
0
        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);
                }
            }
        }
Exemple #41
0
        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);
                }
            }
        }
Exemple #42
0
        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}");
                    }
                }
            }
        }
Exemple #43
0
        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();
        }
Exemple #45
0
        // Список сотрудников с указанием региона, за который они отвечают
        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();
            }
        }
Exemple #46
0
        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]));
                }
            }
        }
Exemple #47
0
        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);
            }
        }
Exemple #48
0
		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);
			}
		}
Exemple #49
0
        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();
    }
Exemple #51
0
		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);
        }
Exemple #54
0
		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();
			}
		}
Exemple #55
0
        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();
            }
        }
Exemple #56
0
		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));
		}
Exemple #57
0
		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);
			}
		}
Exemple #58
0
		////////[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);
				}
			}
		}
Exemple #59
0
		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)));
		}
Exemple #60
0
		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);
			}
		}