public void TestSql_Insert_Query()
{
using (var db = CreateSqlServerDB())
{
try
{
db.SqlMode = SqlModes.Text;
db.BeginTransaction();
var orders = db.Query<Order>().Where(o => o.ID > 0).ToList();
int count = orders.Count;
db.Delete<Order>(o => o.ID > 0);
Order order1 = new Order { OrderName = "Test1" };
db.Insert(order1);
Order order2 = new Order { OrderName = "Test2" };
db.Insert(order2);
Order order3 = new Order { OrderName = "Prod3" };
db.Insert(order3);
var orderItems = new List<OrderItem>();
var results = (from o in db.Query<Order>()
where o.OrderName == "Test1"
orderby o.OrderName
select o).ToList();
Assert.IsTrue(results.Count == 1);
Assert.AreEqual(results[0].OrderName, "Test1");
var results2 = (from o in db.Query<Order>()
where o.OrderName.StartsWith("Test")
select o).ToList();
Assert.IsTrue(results2.Count == 2);
Assert.AreEqual(results2[0].OrderName, "Test1");
Assert.AreEqual(results2[1].OrderName, "Test2");
}
catch
{
throw;
}
finally
{
db.RollBack();
}
}
}
public void IQueryableShouldFilterAndSortDesc()
{
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);
Order order2 = new Order { OrderName = "Test2" };
db.Insert(order2);
Order order3 = new Order { OrderName = "Test3" };
db.Insert(order3);
var orders = db.Queryable<Order>()
.Where(o => o.OrderName == "Test1" || o.OrderName == "Test2")
.OrderByDescending(o => o.OrderName)
.ToArray();
Assert.AreEqual(2, orders.Length);
var o1 = orders[0];
var o2 = orders[1];
Assert.AreEqual(o1.OrderName, "Test2");
Assert.AreEqual(o2.OrderName, "Test1");
}
catch
{
throw;
}
finally
{
db.RollBack();
}
}
}
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();
}
}
}
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();
}
}
}
public void Test_Simple_Paging_WithNoJoins_WithMultipleOrderByClauses()
{
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);
}
// Get page 1 with up to 2 records
var page1 = db.Query<Order>()
.Where(o => o.OrderName == "Order09")
.OrderBy(o => o.OrderName)
.ThenByDescending(o => o.ID)
.Page(1, 2)
.ToList();
Assert.AreEqual(1, page1.Count, "Page should only have one record.");
Assert.AreEqual("Order09", page1[0].OrderName);
}
catch
{
throw;
}
finally
{
db.RollBack();
}
}
}
public void Test_Simple_Paging_WithNoJoins()
{
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);
}
// Get page 1 with 2 records
var page1 = db.Query<Order>()
.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);
// Get page 1 with 2 records
var page2 = db.Query<Order>()
.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);
}
catch (Exception ex)
{
throw;
}
finally
{
db.RollBack();
}
}
}
public void IQueryableShouldHandlePaging()
{
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);
Order order2 = new Order { OrderName = "Test2" };
db.Insert(order2);
Order order3 = new Order { OrderName = "Test3" };
db.Insert(order3);
var orders = db.Queryable<Order>()
.Where(o => o.OrderName.StartsWith("Test"))
.Skip(1)
.Take(1)
.OrderBy(o => o.OrderName)
.ToArray();
Assert.AreEqual(1, orders.Length);
var o1 = orders[0];
Assert.AreEqual(o1.OrderName, "Test2");
}
catch
{
throw;
}
finally
{
db.RollBack();
}
}
}
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();
}
}
}
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();
}
}
}
public void IQueryable_ShouldThrowUnsupportedExceptionIfTheyIncludeAFunctionInTheWhereClause()
{
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);
// Bury the order within another object to ensure that the expression visitor can parse out the OrderName
var nestedPropertyObj = new NestedPropertyObject
{
Order = new Order { OrderName = "Test1" }
};
var order = db.Queryable<Order>()
.Where(o => o.OrderName == nestedPropertyObj.GetOrder().OrderName)
.FirstOrDefault();
Assert.IsNotNull(order);
Assert.AreEqual("Test1", order.OrderName);
}
catch
{
throw;
}
finally
{
db.RollBack();
}
}
}
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();
}
}
}
public void IQueryable_FirstOrDefault_ShouldReturnOneResult()
{
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);
Order order2 = new Order { OrderName = "Test2" };
db.Insert(order2);
Order order3 = new Order { OrderName = "Test3" };
db.Insert(order3);
var order = db.Queryable<Order>()
.Where(o => o.OrderName == "Test2")
.FirstOrDefault();
Assert.IsNotNull(order);
Assert.AreEqual("Test2", order.OrderName);
}
catch
{
throw;
}
finally
{
db.RollBack();
}
}
}
public void IQueryable_First_WithNoResult_ShouldThrowException()
{
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);
Order order2 = new Order { OrderName = "Test2" };
db.Insert(order2);
Order order3 = new Order { OrderName = "Test3" };
db.Insert(order3);
var order = db.Queryable<Order>()
.Where(o => o.OrderName == "does not exist")
.First();
}
catch
{
throw;
}
finally
{
db.RollBack();
}
}
}
public void IQueryableShouldHandlePagingWithNoWhereAndNonConstantValues()
{
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);
Order order2 = new Order { OrderName = "Test2" };
db.Insert(order2);
Order order3 = new Order { OrderName = "Test3" };
db.Insert(order3);
var kvp = new KeyValuePair<int, int>(1, 1);
Func<int> getOne = () => 1 * 1;
var orders = db.Queryable<Order>()
.OrderBy(o => o.OrderName)
.Skip(kvp.Value)
.Take(getOne())
.ToArray();
Assert.AreEqual(1, orders.Length);
var o1 = orders[0];
Assert.AreEqual(o1.OrderName, "Test2");
}
catch
{
throw;
}
finally
{
db.RollBack();
}
}
}
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();
}
}
}
public void Test_GetRowCount()
{
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);
}
// Row count without a where statement
var orders = db.Query<Order>().ToList();
Assert.IsTrue(orders.Count == 10);
int count = db.Query<Order>().GetRowCount();
Assert.AreEqual(orders.Count, count);
// Row count with a where statement
var filteredOrders = db.Query<Order>().Where(o => o.OrderName == "Order03").ToList();
int filteredCount = db.Query<Order>().Where(o => o.OrderName == "Order03").GetRowCount();
Assert.AreEqual(filteredOrders.Count, filteredCount);
// Row count with paged results
var pagedOrders = db.Query<Order>()
.Page(1, 2)
.OrderBy(o => o.OrderName)
.ToList();
Assert.AreEqual(2, pagedOrders.Count);
int totalCount = db.Query<Order>().GetRowCount();
Assert.IsTrue(totalCount > pagedOrders.Count);
}
catch
{
throw;
}
finally
{
db.RollBack();
}
}
}
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();
}
}
}
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();
}
}
}
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();
}
}
}
public void IQueryableShouldHandleFilterWithTwoSorts()
{
using (var db = CreateSqlServerDB())
{
try
{
db.SqlMode = SqlModes.Text;
db.BeginTransaction();
// Clear out any existing records
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 = "GroupA" };
db.Insert<Order>(order1);
Order order2 = new Order { OrderName = "GroupB" };
db.Insert<Order>(order2);
Order order3 = new Order { OrderName = "GroupA" };
db.Insert<Order>(order3);
Order order4 = new Order { OrderName = "Test" };
db.Insert<Order>(order4);
var orders = db.Queryable<Order>()
.Where(o => o.OrderName.StartsWith("Group"))
.OrderBy(o => o.OrderName)
.ThenByDescending(o => o.ID)
.ToArray();
// Test where clause
Assert.AreEqual(3, orders.Length);
var r1 = orders[0];
var r2 = orders[1];
var r3 = orders[2];
// Test primary sort
Assert.AreEqual(r1.OrderName, "GroupA");
Assert.AreEqual(r2.OrderName, "GroupA");
Assert.AreEqual(r3.OrderName, "GroupB");
// Test secondary sort
Assert.AreEqual(order3.ID, r1.ID);
Assert.AreEqual(order1.ID, r2.ID);
Assert.AreEqual(order2.ID, r3.ID);
}
catch
{
throw;
}
finally
{
db.RollBack();
}
}
}