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 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);
}