Exemplo n.º 1
0
		public void Test_Filtering_ChildrenToload_With_Graph()
		{
			using (var db = CreateSqlServerDB())
			{
				try
				{
					db.SqlMode = SqlModes.Text;
					db.BeginTransaction();

					// Insert 10 orders
					for (int i = 1; i < 11; i++)
					{
						Order order = new Order { OrderName = "Order" + (i.ToString().PadLeft(2, '0')) };
						db.Insert<Order>()
							.Entity(order)
							.GetIdentity()
							.Execute();

						OrderItem orderItem1 = new OrderItem { OrderID = order.ID, ItemDescription = "Desc1", Price = 5.5m };
						OrderItem orderItem2 = new OrderItem { OrderID = order.ID, ItemDescription = "Desc2", Price = 6.6m };
						db.Insert<OrderItem>(orderItem1);
						db.Insert<OrderItem>(orderItem2);

						Receipt receipt1 = new Receipt { OrderItemID = orderItem1.ID, AmountPaid = orderItem1.Price };
						Receipt receipt2 = new Receipt { OrderItemID = orderItem2.ID, AmountPaid = orderItem2.Price };
						db.Insert<Receipt>(receipt1);
						db.Insert<Receipt>(receipt2);
					}
					
					// Try to get entire graph, including orderitems and receipts
					var orders = db.Query<Order>().FromView("V_OrdersReceipts").Graph(o => o.OrderItems, o => o.OrderItems.First().ItemReceipt).ToList();
					Assert.IsTrue(orders.Count > 0);
					Assert.IsNotNull(orders[0].OrderItems);
					Assert.IsNotNull(orders[0].OrderItems[0].ItemReceipt);

					// Try to get filtered graph without receipt
					var orders2 = db.Query<Order>().FromView("V_OrdersReceipts").Graph(o => o.OrderItems).ToList();
					Assert.IsTrue(orders2.Count > 0);
					Assert.IsNotNull(orders2[0].OrderItems);
					Assert.IsNull(orders2[0].OrderItems[0].ItemReceipt);
				}
				catch
				{
					throw;
				}
				finally
				{
					db.RollBack();
				}
			}
		}
Exemplo n.º 2
0
		public void TestJoin()
		{
			using (var db = CreateSqlServerDB())
			{
				try
				{
					db.SqlMode = SqlModes.Text;
					db.BeginTransaction();

					// Insert a new order
					Order newOrder = new Order();
					newOrder.OrderName = "new order";
					int orderID = Convert.ToInt32(db.Insert<Order>().Entity(newOrder).GetIdentity().Execute());
					Assert.IsTrue(orderID > 0);

					// Update order name to use the generated ID autoincremented value
					newOrder.OrderName = string.Concat(newOrder.OrderName, " ", newOrder.ID);
					db.Update<Order>(newOrder, o => o.ID == newOrder.ID);

					// Add an order item associated to the newly added order
					OrderItem orderItem = new OrderItem { OrderID = newOrder.ID, ItemDescription = "Test item", Price = 5.5m };
					int orderItemID = Convert.ToInt32(db.Insert<OrderItem>().Entity(orderItem).GetIdentity().Execute());
					Assert.IsTrue(orderItemID > 0);

					// Add a receipt associated to the new ordeer / order item
					Receipt receipt = new Receipt { OrderItemID = orderItem.ID, AmountPaid = 5.5m };
					db.Insert<Receipt>(receipt);

					// Query the newly added order with its order item (do not query receipt)
					var orderWithItem = db.Query<Order>()
						.Join<Order, OrderItem>(JoinType.Left, o => o.OrderItems, (o, oi) => o.ID == oi.OrderID)
						.Where(o => o.ID == newOrder.ID)
						.FirstOrDefault();

					// Query the newly added order with associated order item and receipt
					var orderWithItemAndReceipt = db.Query<Order>()
						.Join<Order, OrderItem>(JoinType.Left, o => o.OrderItems, (o, oi) => o.ID == oi.OrderID)
						.Join<OrderItem, Receipt>(JoinType.Left, oi => oi.ItemReceipt, (oi, r) => oi.ID == r.OrderItemID)
						.Where(o => o.ID == newOrder.ID).FirstOrDefault();

					Assert.IsNotNull(orderWithItem);
					Assert.IsTrue(orderWithItem.OrderItems.Count == 1);
					Assert.IsNull(orderWithItem.OrderItems[0].ItemReceipt);

					Assert.IsNotNull(orderWithItemAndReceipt.OrderItems[0].ItemReceipt);

					// Delete all added items
					db.Delete<Order>(o => o.ID == orderID);
					db.Delete<OrderItem>(oi => oi.ID == orderItemID);
					db.Delete<Receipt>(r => r.OrderItemID == orderItemID);

					// Verify items are deleted
					var receipts = db.Query<Receipt>().Where(r => r.OrderItemID == orderItemID).ToList();
					Assert.IsTrue(receipts.Count == 0);

					var orderItems = db.Query<OrderItem>().Where(oi => oi.ID == orderItemID).ToList();
					Assert.IsTrue(orderItems.Count == 0);

					var orders = db.Query<Order>().Where(o => o.ID == orderID).ToList();
					Assert.IsTrue(orders.Count == 0);
				}
				catch
				{
					throw;
				}
				finally
				{
					db.RollBack();
				}
			}
		}
Exemplo n.º 3
0
		public void Test_Paging_With_View()
		{
			using (var db = CreateSqlServerDB())
			{
				try
				{
					db.BeginTransaction();

					// Insert 10 orders
					for (int i = 1; i < 11; i++)
					{
						Order order = new Order { OrderName = "Order" + (i.ToString().PadLeft(2, '0')) };
						db.Insert<Order>()
							.Entity(order)
							.GetIdentity()
							.Execute();

						Assert.IsTrue(order.ID > 0, "Identity value should have been returned.");

						OrderItem orderItem1 = new OrderItem { OrderID = order.ID, ItemDescription = "Desc1", Price = 5.5m };
						OrderItem orderItem2 = new OrderItem { OrderID = order.ID, ItemDescription = "Desc2", Price = 6.6m };
						db.Insert(orderItem1);
						db.Insert(orderItem2);
					}

					db.SqlMode = SqlModes.Text;

					// Get page 1 with up to 2 records
					var page1 = db.Query<Order>()
						.FromView("V_Orders")
						.Graph(o => o.OrderItems)
						.OrderBy(o => o.OrderName)
						.Page(1, 2)
						.ToList();

					Assert.AreEqual(2, page1.Count, "Page should have two records.");
					Assert.AreEqual(2, page1[0].OrderItems.Count);
					Assert.AreEqual(2, page1[1].OrderItems.Count);
				}
				catch
				{
					throw;
				}
				finally
				{
					db.RollBack();
				}
			}
		}
Exemplo n.º 4
0
		public void Test_Linq_Clauses_Mixed_With_FromView()
		{
			using (var db = CreateSqlServerDB())
			{
				try
				{
					db.BeginTransaction();

					// Insert 10 orders
					for (int i = 1; i < 11; i++)
					{
						Order order = new Order { OrderName = "Order" + (i.ToString().PadLeft(2, '0')) };
						db.Insert<Order>(order);

						Assert.IsTrue(order.ID > 0, "Identity value should have been returned.");

						OrderItem orderItem1 = new OrderItem { OrderID = order.ID, ItemDescription = "Desc1", Price = 5.5m };
						OrderItem orderItem2 = new OrderItem { OrderID = order.ID, ItemDescription = "Desc2", Price = 6.6m };
						db.Insert(orderItem1);
						db.Insert(orderItem2);
					}

					db.SqlMode = SqlModes.Text;

					var orders = db.Query<Order>()
						.Graph(o => o.OrderItems)
						.FromView("V_Orders")
						.Where<OrderItem>(oi => oi.Price > 6m)
						.ToList();

					Assert.IsTrue(orders.Count > 0);

					var orderItems = db.Query<OrderItem>()
						.FromView("V_Orders")
						.Where(oi => oi.Price > 6)
						.OrWhere(oi => oi.Price > 6.1m)     // Test using OrWhere method in the SortBuilder
						.ToList();

					Assert.IsTrue(orderItems.Count > 0);
				}
				catch
				{
					throw;
				}
				finally
				{
					db.RollBack();
				}
			}
		}
Exemplo n.º 5
0
		public void Test_Complex_Paging_WithJoins_WithWhereClause_WithMultipleOrderClauses()
		{
			using (var db = CreateSqlServerDB())
			{
				try
				{
					db.BeginTransaction();

					// Insert 10 orders
					for (int i = 1; i < 11; i++)
					{
						Order order = new Order { OrderName = "Order" + (i.ToString().PadLeft(2, '0')) };
						db.Insert<Order>()
							.Entity(order)
							.GetIdentity()
							.Execute();

						OrderItem orderItem1 = new OrderItem { OrderID = order.ID, ItemDescription = "Desc1", Price = 5.5m };
						OrderItem orderItem2 = new OrderItem { OrderID = order.ID, ItemDescription = "Desc2", Price = 6.6m };
						db.Insert(orderItem1);
						db.Insert(orderItem2);
					}

					// Get page 1 with 2 records
					var page1 = db.Query<Order>()
						.Join<Order, OrderItem>(JoinType.Left, o => o.OrderItems, (o, oi) => o.ID == oi.OrderID)
						.Where(o => o.OrderName == "Order09")
						.OrderBy(o => o.OrderName)
						.ThenByDescending(o => o.ID)
						.Page(1, 2)
						.ToList();

					Assert.AreEqual(1, page1.Count, "Page size should be 1.");
					Assert.AreEqual("Order09", page1[0].OrderName);
					Assert.AreEqual(2, page1[0].OrderItems.Count);
				}
				catch
				{
					throw;
				}
				finally
				{
					db.RollBack();
				}
			}
		}
Exemplo n.º 6
0
		public void Test_Complex_Paging_WithJoins()
		{
			using (var db = CreateSqlServerDB())
			{
				try
				{
					db.BeginTransaction();

					// Insert 10 orders
					for (int i = 1; i < 11; i++)
					{
						Order order = new Order { OrderName = "Order" + (i.ToString().PadLeft(2, '0')) };
						db.Insert<Order>()
							.Entity(order)
							.GetIdentity()
							.Execute();

						OrderItem orderItem1 = new OrderItem { OrderID = order.ID, ItemDescription = "Desc1", Price = 5.5m };
						OrderItem orderItem2 = new OrderItem { OrderID = order.ID, ItemDescription = "Desc2", Price = 6.6m };
						db.Insert(orderItem1);
						db.Insert(orderItem2);
					}

					string query1 = db.Query<Order>()
						.Join<Order, OrderItem>(JoinType.Left, o => o.OrderItems, (o, oi) => o.ID == oi.OrderID)
						.OrderBy(o => o.OrderName)
						.Page(1, 2)
						.BuildQuery();

					Assert.IsNotNull(query1);

					// Get page 1 with 2 records
					var page1 = db.Query<Order>()
						.Join<Order, OrderItem>(JoinType.Left, o => o.OrderItems, (o,oi) => o.ID == oi.OrderID)
						.OrderBy(o => o.OrderName)
						.Page(1, 2)
						.ToList();

					Assert.AreEqual(2, page1.Count, "Page size should be 2.");
					Assert.AreEqual("Order01", page1[0].OrderName);
					Assert.AreEqual("Order02", page1[1].OrderName);
					Assert.AreEqual(2, page1[0].OrderItems.Count);

					// Get page 1 with 2 records
					var page2 = db.Query<Order>()
						.Join<Order, OrderItem>(JoinType.Left, o => o.OrderItems, (o, oi) => o.ID == oi.OrderID)
						.OrderBy(o => o.OrderName)
						.Page(2, 2)
						.ToList();

					Assert.AreEqual(2, page2.Count, "Page size should be 2.");
					Assert.AreEqual("Order03", page2[0].OrderName);
					Assert.AreEqual("Order04", page2[1].OrderName);
					Assert.AreEqual(2, page2[0].OrderItems.Count);
				}
				catch (Exception ex)
				{
					throw;
				}
				finally
				{
					db.RollBack();
				}
			}
		}
Exemplo n.º 7
0
		public void Test_GetRowCount_WithTableJoins()
		{
			using (var db = CreateSqlServerDB())
			{
				try
				{
					db.BeginTransaction();

					// Insert 10 orders
					for (int i = 1; i < 11; i++)
					{
						Order order = new Order { OrderName = "Order" + (i.ToString().PadLeft(2, '0')) };
						db.Insert<Order>(order);

						Assert.IsTrue(order.ID > 0, "Identity value should have been returned.");

						OrderItem orderItem1 = new OrderItem { OrderID = order.ID, ItemDescription = "Desc1", Price = 5.5m };
						OrderItem orderItem2 = new OrderItem { OrderID = order.ID, ItemDescription = "Desc2", Price = 6.6m };
						db.Insert(orderItem1);
						db.Insert(orderItem2);
					}

					// First verify that there are 20 order item records
					var orderItems = db.Query<OrderItem>()
						.FromView("V_Orders")
						.ToList();

					Assert.AreEqual(20, orderItems.Count);

					// Row count with a join
					var orders = db.Query<Order>()
						.Join<Order, OrderItem>(JoinType.Left, o => o.OrderItems, (o, oi) => o.ID == oi.OrderID)
						.ToList();
					Assert.IsTrue(orders.Count == 10);

					int count = db.Query<Order>()
						.Join<Order, OrderItem>(JoinType.Left, o => o.OrderItems, (o, oi) => o.ID == oi.OrderID)
						.GetRowCount();
					Assert.AreEqual(orders.Count, count);

					// Rowcount from a view
					var orders2 = db.Query<Order>()
						.FromView("V_Orders")
						.Graph(o => o.OrderItems)
						.ToList();
					Assert.IsTrue(orders2.Count == 10);

					int count2 = db.Query<Order>()
						.FromView("V_Orders")
						.Graph(o => o.OrderItems)
						.GetRowCount();
					Assert.AreEqual(orders2.Count, count2);
				}
				catch
				{
					throw;
				}
				finally
				{
					db.RollBack();
				}
			}
		}
Exemplo n.º 8
0
		public void IQueryable_ShouldReturnTwoLevelsIfBaseQueryIsProvided()
		{
			using (var db = CreateSqlServerDB())
			{
				try
				{
					db.SqlMode = SqlModes.Text;
					db.BeginTransaction();

					var existingOrders = db.Query<Order>().Where(o => o.ID > 0).ToList();
					int count = existingOrders.Count;
					db.Delete<Order>(o => o.ID > 0);

					Order order1 = new Order { OrderName = "Test1" };
					db.Insert(order1);

					OrderItem orderItem1 = new OrderItem { ItemDescription = "Desc", OrderID = order1.ID, Price = 5.5m };
					db.Insert(orderItem1);

					var baseQuery = db.Query<Order>();
					baseQuery.Join<Order, OrderItem>(QGen.JoinType.Inner, o => o.OrderItems, (o, oi) => o.ID == oi.OrderID);

					IQueryable<Order> ctx = db.Queryable<Order>(baseQuery);
					var order = ctx
						.Where(o => o.OrderName == "Test1")
						.FirstOrDefault();

					Assert.IsNotNull(order);
					Assert.AreEqual(1, order.OrderItems.Count);
				}
				catch
				{
					throw;
				}
				finally
				{
					db.RollBack();
				}
			}
		}
Exemplo n.º 9
0
		public void IQueryable_ShouldOnlyReturnRootLevelByDefault()
		{
			using (var db = CreateSqlServerDB())
			{
				try
				{
					db.SqlMode = SqlModes.Text;
					db.BeginTransaction();

					var existingOrders = db.Query<Order>().Where(o => o.ID > 0).ToList();
					int count = existingOrders.Count;
					db.Delete<Order>(o => o.ID > 0);

					Order order1 = new Order { OrderName = "Test1" };
					db.Insert(order1);

					OrderItem orderItem1 = new OrderItem { ItemDescription = "Desc", OrderID = order1.ID, Price = 5.5m };
					db.Insert(orderItem1);

					IQueryable<Order> ctx = db.Queryable<Order>();
					var order = ctx
						.Where(o => o.OrderName == "Test1")
						.FirstOrDefault();

					Assert.IsNotNull(order);
					Assert.IsNull(order.OrderItems, "No order items should have been queried because no Graph settings were specified via a base query.");
				}
				catch
				{
					throw;
				}
				finally
				{
					db.RollBack();
				}
			}
		}