public void Insert_ShouldInclude_AllColumns_ByDefault()
{
using (var db = CreateSqlServerCeDB())
{
db.SqlMode = SqlModes.Text;
db.BeginTransaction();
OrderItem item = new OrderItem { ID = 1, OrderID = 500, ItemDescription = "desc", Price = 5.5m };
string sql = db.Insert<OrderItem>()
.Entity(item)
.BuildQuery();
Assert.IsTrue(sql.Contains("[Price]"));
Assert.IsTrue(sql.Contains("[OrderID]"));
Assert.IsTrue(sql.Contains("[ItemDescription]"));
db.RollBack();
}
}
public void Update_IncludeColumns_ShouldFilterColumns()
{
using (var db = CreateSqlServerCeDB())
{
db.SqlMode = SqlModes.Text;
db.BeginTransaction();
OrderItem item = new OrderItem { ID = 1, OrderID = 500, ItemDescription = "desc", Price = 5.5m };
string sql = db.Update<OrderItem>()
.Entity(item)
.ColumnsIncluding(oi => oi.Price)
.BuildQuery();
Assert.IsTrue(sql.Contains("[Price]"));
Assert.IsFalse(sql.Contains("[OrderID]"));
Assert.IsFalse(sql.Contains("[ItemDescription]"));
db.RollBack();
}
}
public void TestJoin()
{
using (var db = CreateSqlServerCeDB())
{
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);
db.Commit();
}
catch
{
db.RollBack();
throw;
}
}
}
