public void FilterTSE4()
{
var db = new AdventureWorksDB();
var orders1 = db.SalesOrderHeaders.Where(c => c.ShipMethodId == ShipMethodEnum.CARGO_TRANSPORT_5.Key).ToList();
var orders2 = db.SalesOrderHeaders.FilterByShipMethod(ShipMethodEnum.CARGO_TRANSPORT_5).ToList();
Assert.AreEqual(orders1.Count, orders2.Count);
var orders2Query = db.SalesOrderHeaders.FilterByShipMethod(ShipMethodEnum.CARGO_TRANSPORT_5).ToString();
}
public void TestInExpression1()
{
var db = new AdventureWorksDB();
var customQuery = db.SalesOrderHeaders.Where
(
QueryHelper.CreateInExpression <SalesOrderHeader, int>(order => order.ShipMethodId, new int[] { 1 })
);
var customQuery2 = customQuery.ToList();
}
public void TestDapper_DBFilter()
{
var db = new AdventureWorksDB();
var contacts = db.Query <Entities.BusinessEntityContact>("SELECT * FROM person.BusinessEntityContact WHERE ContactTypeID=@ContactType",
new { ContactType = ContactTypeEnum.ASSISTANT_SALES_AGENT }).ToList();
foreach (var contact in contacts)
{
ContactTypeEnum type = contact.ContactType;
Assert.That(type.Key == ContactTypeEnum.ASSISTANT_SALES_AGENT.Key);
Assert.That(contact.ContactTypeId == 2);
}
}
public void TestEF_FromDB()
{
var db = new AdventureWorksDB();
var contacts = db.BusinessEntityContacts.Take(10).ToList();
foreach (var contact in contacts)
{
ContactTypeEnum type = contact.ContactType;
Assert.NotNull(type);
Assert.NotNull(type.Key);
Assert.AreEqual(type.Key, contact.ContactTypeId);
}
}
public void FilterTSE3()
{
var db = new AdventureWorksDB();
// explicit types and inferred types
var orders = db.SalesOrderHeaders.WhereEnum <SalesOrderHeader, ShipMethodEnum, int>(order => order.ShipMethod, shipMethod => shipMethod.CanCancelShipment);
Assert.NotNull(orders);
Assert.IsNotEmpty(orders);
orders = db.SalesOrderHeaders.WhereEnum(order => order.ShipMethod, shipMethod => shipMethod.CanCancelShipment);
Assert.NotNull(orders);
Assert.IsNotEmpty(orders);
}
public void FilterTSE2()
{
var db = new AdventureWorksDB();
// explicit types and inferred types
var orders = db.SalesOrderHeaders.WhereEnum <SalesOrderHeader, ShipMethodEnum, int>(order => order.ShipMethod, shipMethod => shipMethod == ShipMethodEnum.CARGO_TRANSPORT_5 || shipMethod == ShipMethodEnum.OVERNIGHT_J_FAST);
Assert.NotNull(orders);
Assert.IsNotEmpty(orders);
orders = db.SalesOrderHeaders.WhereEnum(order => order.ShipMethod, shipMethod => shipMethod == ShipMethodEnum.CARGO_TRANSPORT_5 || shipMethod == ShipMethodEnum.OVERNIGHT_J_FAST);
Assert.NotNull(orders);
Assert.IsNotEmpty(orders);
}
public void TestCombinedFilters()
{
var db = new AdventureWorksDB();
// Combining (AND/OR) Expressions on Type-Safe-Enums (created using TypeSafeEnumsQuery.CreateExpression) and Expressions on primitive properties
var filter1 = TypeSafeEnumsQueryHelper.CreateExpression <SalesOrderHeader, ShipMethodEnum, int>(order => order.ShipMethod, shipMethod => shipMethod == ShipMethodEnum.OVERNIGHT_J_FAST);
System.Linq.Expressions.Expression <Func <SalesOrderHeader, bool> > filter3 = (x) => x.SalesOrderNumber.StartsWith("SO4");
var filter2 = TypeSafeEnumsQueryHelper.CreateExpression <SalesOrderHeader, ShipMethodEnum, int>(order => order.ShipMethod, shipMethod => !shipMethod.CanCancelShipment);
var filter1OrFilterAnd3 = filter1.OrElse(filter2.AndAlso(filter3));
var s = db.SalesOrderHeaders.Where(filter1OrFilterAnd3).Select(x => new { x.SalesOrderId, x.SalesOrderNumber, x.SalesPersonId });
System.Diagnostics.Debug.WriteLine(s.ToString());
var anonOrders = s.ToList();
// testing combining multiple filters (AND filters all in LINQ)
var andsQuery = db.SalesOrderHeaders
//.FilterByShipMethod(ShipMethodEnum.CARGO_TRANSPORT_5)
//.Where(x => x.OrderDate >= new DateTime(2014, 1, 1) && x.OrderDate < new DateTime(2014 + 1, 1, 1))
.FilterByFiscalYear(2014)
.WhereEnum(order => order.ShipMethod, shipMethod => shipMethod.CanCancelShipment)
;
Assert.That(andsQuery.ToString().Contains("WHERE "));
Assert.That(andsQuery.Count() > 0);
Assert.That(andsQuery.ToList().Where(x => !x.ShipMethod.CanCancelShipment).Count() == 0);
// bring all cancellable orders, plus(or) all orders which OrderNumber starts with "SO4" (OR filters all in LINQ)
var cancellableMethods = ShipMethodEnum.Values.Where(x => x.CanCancelShipment).Select(x => x.Key).ToArray();
var orsQuery = db.SalesOrderHeaders.Where(order =>
cancellableMethods.Contains(order.ShipMethodId)
||
order.SalesOrderNumber.StartsWith("SO4")
);
Assert.That(orsQuery.ToString().Contains("WHERE "));
Assert.That(orsQuery.Count() > 0);
Assert.That(andsQuery.ToList().Where(x => !x.ShipMethod.CanCancelShipment).Count() >= 0);
Assert.That(andsQuery.ToList().Where(x => x.SalesOrderNumber.StartsWith("SO4")).Count() >= 0);
Assert.That(andsQuery.ToList().Where(x => !x.ShipMethod.CanCancelShipment).All(y => y.SalesOrderNumber.StartsWith("SO4")));
var andsQuery2 = andsQuery.ToList();
// OR filters, using unsupported LINQ properties
var orsQuery2 = db.SalesOrderHeaders.ToList().Where(order =>
order.SalesOrderNumber.ToLower().StartsWith("so4") || // my SQL Server instance is case insensitive!
order.ShipMethod.CanCancelShipment
);
Assert.That(orsQuery.Count() == orsQuery2.Count());
}
public void TestWhereEnumIn()
{
var db = new AdventureWorksDB();
var orders1 = db.SalesOrderHeaders.WhereEnumNotIn <SalesOrderHeader, ShipMethodEnum, int>(x => x.ShipMethod, new ShipMethodEnum[] { ShipMethodEnum.OVERNIGHT_J_FAST }).ToList();
orders1 = db.SalesOrderHeaders.WhereEnumIn <SalesOrderHeader, ShipMethodEnum, int>(x => x.ShipMethod, new ShipMethodEnum[] { ShipMethodEnum.OVERNIGHT_J_FAST, ShipMethodEnum.OVERSEAS_DELUXE }).ToList();
var orders3 = db.Database.SqlQuery <SalesOrderHeader>("SELECT * FROM sales.SalesOrderHeader WHERE ShipMethodId=1").ToList();
var allOrders = db.SalesOrderHeaders;
var cancellableOrders = db.SalesOrderHeaders.WhereEnumIn <SalesOrderHeader, ShipMethodEnum, int>(o => o.ShipMethod, new ShipMethodEnum[] { ShipMethodEnum.CARGO_TRANSPORT_5, ShipMethodEnum.OVERNIGHT_J_FAST, ShipMethodEnum.OVERSEAS_DELUXE, ShipMethodEnum.ZY_EXPRESS });
var nonCancellableOrders = db.SalesOrderHeaders.WhereEnumNotIn <SalesOrderHeader, ShipMethodEnum, int>(o => o.ShipMethod, new ShipMethodEnum[] { ShipMethodEnum.CARGO_TRANSPORT_5, ShipMethodEnum.OVERNIGHT_J_FAST, ShipMethodEnum.OVERSEAS_DELUXE, ShipMethodEnum.ZY_EXPRESS });
Assert.That(allOrders.Count() == cancellableOrders.Count() + nonCancellableOrders.Count());
}
public void FilterTSE0()
{
var db = new AdventureWorksDB();
// Filtering by a not-mapped property is not supported (can't be passed to SQL)
Assert.Throws <NotSupportedException>(() =>
{
var orders = db.SalesOrderHeaders.Where(order => order.ShipMethod == ShipMethodEnum.CARGO_TRANSPORT_5).ToList();
});
// But filtering with WhereEnum we can find the underlying property (int) and build a expression which can be passed to SQL
Assert.DoesNotThrow(() =>
{
var orders = db.SalesOrderHeaders.WhereEnum <SalesOrderHeader, ShipMethodEnum, int>(order => order.ShipMethod, shipMethod => shipMethod == ShipMethodEnum.CARGO_TRANSPORT_5).ToList();
});
}
public void TestIQueryableFilterExtensions1()
{
var db = new AdventureWorksDB();
var orders1 = db.SalesOrderHeaders.WherePropertyIn(x => x.ShipMethodId, new List <int>()
{
1, 2
}).ToList();
var qry = db.SalesOrderHeaders.WherePropertyIn(x => x.ShipMethodId, new List <int>()
{
1, 2
}).ToString();
System.Diagnostics.Debug.WriteLine(qry);
Assert.IsTrue(qry.Contains("IN (1, 2)"));
Assert.That(orders1.Count > 0);
}
public void TestEnumUpdate()
{
var db = new AdventureWorksDB();
// get order with shipMethod XRQ_TRUCK_GROUND, check enum
var order = db.SalesOrderHeaders.Where(x => x.ShipMethodId == 1).First();
Assert.AreEqual(order.ShipMethod, ShipMethodEnum.XRQ_TRUCK_GROUND);
Assert.AreNotEqual(order.ShipMethod, ShipMethodEnum.OVERSEAS_DELUXE);
// update ShipMethodEnum to OVERNIGHT_J_FAST using EF dynamic SQL (syntax 1)
int ret = db.Database.ExecuteSqlCommand("UPDATE sales.SalesOrderHeader SET ShipMethodID=@ShipMethodID WHERE SalesOrderID=@ID",
new SqlParameter("ShipMethodID", ShipMethodEnum.OVERNIGHT_J_FAST.Key),
new SqlParameter("ID", order.SalesOrderId));
Assert.That(ret == 1);
// update ShipMethodEnum to OVERNIGHT_J_FAST using EF dynamic SQL (syntax 2)
ret = db.Database.ExecuteSqlCommand("UPDATE sales.SalesOrderHeader SET ShipMethodID={0} WHERE SalesOrderID={1}",
ShipMethodEnum.OVERNIGHT_J_FAST.Key,
order.SalesOrderId);
Assert.That(ret == 1);
// check ShipMethod was updated
var newShipMethod = ShipMethodEnum.FromKey(db.Database.SqlQuery <int>("SELECT ShipMethodID FROM sales.SalesOrderHeader WHERE SalesOrderID=@ID",
new SqlParameter("ID", order.SalesOrderId)).SingleOrDefault());
Assert.That(newShipMethod == ShipMethodEnum.OVERNIGHT_J_FAST);
Assert.That(newShipMethod != ShipMethodEnum.XRQ_TRUCK_GROUND);
Assert.That(ShipMethodEnum.OVERNIGHT_J_FAST.Key == 4);
// update back to XRQ_TRUCK_GROUND
db.Database.ExecuteSqlCommand("UPDATE sales.SalesOrderHeader SET ShipMethodID=@ShipMethodID WHERE SalesOrderID=@ID",
new SqlParameter("ShipMethodID", ShipMethodEnum.XRQ_TRUCK_GROUND.Key),
new SqlParameter("ID", order.SalesOrderId));
newShipMethod = ShipMethodEnum.FromKey(db.Database.SqlQuery <int>("SELECT ShipMethodID FROM sales.SalesOrderHeader WHERE SalesOrderID=@ID",
new SqlParameter("ID", order.SalesOrderId)).SingleOrDefault());
Assert.That(newShipMethod == ShipMethodEnum.XRQ_TRUCK_GROUND);
Assert.That(newShipMethod != ShipMethodEnum.OVERNIGHT_J_FAST);
Assert.That(newShipMethod.Key == 1);
}
public void TestQuerySyntax()
{
var db = new AdventureWorksDB();
// EF dynamic (inline) SQL
// then parametrized EF syntax 1
// then parametrized EF syntax 2
// then parametrized Dapper syntax 1 (passing primitive type)
// then parametrized Dapper syntax 2 (passing primitive type)
// then parametrized Dapper syntax 3 (passing TSE itself, which is correctly converted into underlying type)
var orders1 = db.Database.SqlQuery <SalesOrderHeader>("SELECT * FROM sales.SalesOrderHeader WHERE ShipMethodID=1").ToList();
var orders2 = db.Database.SqlQuery <SalesOrderHeader>("SELECT * FROM sales.SalesOrderHeader WHERE ShipMethodID=@ShipMethodID", new SqlParameter("ShipMethodID", ShipMethodEnum.XRQ_TRUCK_GROUND.Key)).ToList();
var orders3 = db.Database.SqlQuery <SalesOrderHeader>("SELECT * FROM sales.SalesOrderHeader WHERE ShipMethodID={0}", ShipMethodEnum.XRQ_TRUCK_GROUND.Key).ToList();
var orders4 = db.Query <SalesOrderHeader>("SELECT * FROM sales.SalesOrderHeader WHERE ShipMethodID=@Status", new { Status = ShipMethodEnum.XRQ_TRUCK_GROUND.Key }).ToList();
var orders5 = db.Query <SalesOrderHeader>("SELECT * FROM sales.SalesOrderHeader WHERE ShipMethodID=@XRQ_TRUCK_GROUND", new { ShipMethodEnum.XRQ_TRUCK_GROUND }).ToList();
// check all equal
Assert.That(orders1.Count > 0);
Assert.AreEqual(orders1.Count, orders2.Count);
Assert.AreEqual(orders1.Count, orders3.Count);
Assert.AreEqual(orders1.Count, orders4.Count);
Assert.AreEqual(orders1.Count, orders5.Count);
}
public void TestEF_UpdateEnum()
{
var db = new AdventureWorksDB();
var order = db.SalesOrderHeaders.Where(c => c.ShipMethodId != ShipMethodEnum.CARGO_TRANSPORT_5.Key).First();
ShipMethodEnum previousType = order.ShipMethod;
Assert.NotNull(order);
Assert.NotNull(previousType);
Assert.AreNotEqual(previousType, ShipMethodEnum.CARGO_TRANSPORT_5);
order.ShipMethod = ShipMethodEnum.CARGO_TRANSPORT_5;
int saved = db.SaveChanges();
Assert.AreEqual(1, saved);
db = new AdventureWorksDB();
order = db.SalesOrderHeaders.Single(c => c.SalesOrderId == order.SalesOrderId);
Assert.NotNull(order);
Assert.NotNull(order.ShipMethod);
Assert.AreEqual(order.ShipMethod, ShipMethodEnum.CARGO_TRANSPORT_5);
order.ShipMethod = previousType;
saved = db.SaveChanges();
Assert.AreEqual(1, saved);
}