public void SelectAnonymObject() { var expected = "SELECT Orders.OrdersID, ProductID, UnitPrice, Quantity, Discount FROM Orders JOIN OrderDetails ON (OrderDetails.OrdersID = Orders.OrdersID)"; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor(() => new { ProductID = 0, Quantity = 0 }) .BeforeExecute(s => Assert.AreEqual(s.QueryString.Flatten(), expected)); using (var context = provider.Open()) { var items = context.From<Orders>() .Map(o => o.OrdersID) .Join<OrderDetails>((od, o) => od.OrdersID == o.OrdersID) .Select(od => new { ProductID = od.ProductID, Quantity = od.Quantity }); items.Any(); } }
public void ExecuteSelectStatement() { var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor<Orders>().BeforeExecute(s => Assert.AreEqual(s.QueryString.Flatten(), "SELECT * FROM Orders")); using (var context = provider.Open()) { // select with string select statement var orders = context.Execute<Orders>("SELECT * FROM Orders"); } }
public void ExecuteSqlStatement() { var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor<Orders>().BeforeExecute(s => Assert.AreEqual(s.QueryString.Flatten(), "UPDATE Orders SET Freight = 20 WHERE OrdersID = 10000000")); using (var context = provider.Open()) { // select with string select statement context.Execute("UPDATE Orders SET Freight = 20 WHERE OrdersID = 10000000"); } }
public void UpdateTestWithKeyExpression() { var sql = ""; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor<Warrior>().BeforeExecute(s => sql = s.QueryString.Flatten()); using (var context = provider.Open()) { context.Update(() => new Warrior { ID = 1, Race = "Elf", WeaponID = 2 }, e => e.ID); context.Commit(); } }
public void UpdateTestWithKeyExpression_Fail() { var sql = ""; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor<Warrior>().BeforeExecute(s => sql = s.QueryString.Flatten()); using (var context = provider.Open()) { ((Mock.ConnectionProvider)provider.ConnectionProvider).CheckCallbackCall = false; Assert.Throws<ArgumentException>(() => context.Update(() => new Warrior {ID = 1, Race = "Elf", WeaponID = 2}, e => e.ID == 1)); context.Commit(); } }
public void InsertWithIgnoreTest() { var sql = ""; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor<Warrior>().BeforeExecute(s => sql = s.QueryString.Flatten()); using (var context = provider.Open()) { // insert all except ignored elements used in the reference expression context.Insert(() => new Warrior { ID = 1, Race = "Dwarf" }).Ignore(w => w.ID).Ignore(w => w.WeaponID); context.Commit(); Assert.AreEqual(sql, "INSERT INTO Warrior (Name, Race, SpecialSkill) VALUES (NULL, 'Dwarf', NULL)"); } }
public void InsertWithAnonymObjectTest() { var sql = ""; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor<Warrior>().BeforeExecute(s => sql = s.QueryString.Flatten()); using (var context = provider.Open()) { // insert all fields defined in the anonym object context.Insert<Warrior>(() => new { ID = 1, Race = "Dwarf" }); context.Commit(); Assert.AreEqual(sql, "INSERT INTO Warrior (ID, Race) VALUES (1, 'Dwarf')"); } }
public void UpdateTests() { var sql = ""; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor<Warrior>().BeforeExecute(s => sql = s.QueryString.Flatten()); using (var context = provider.Open()) { context.Update(() => new Warrior { ID = 1, Race = "Elf", WeaponID = 2 }); context.Commit(); Assert.AreEqual(sql, "UPDATE Warrior SET Name = NULL, WeaponID = 2, Race = 'Elf', SpecialSkill = NULL WHERE (Warrior.ID = 1)"); context.Update(() => new Warrior { ID = 1, Race = "Elf", WeaponID = 2 }, e => e.ID); context.Commit(); Assert.AreEqual(sql, "UPDATE Warrior SET Name = NULL, WeaponID = 2, Race = 'Elf', SpecialSkill = NULL WHERE (Warrior.ID = 1)"); context.Update<Warrior>(() => new { ID = 1, Race = "Elf" }); context.Commit(); Assert.AreEqual(sql, "UPDATE Warrior SET Race = 'Elf' WHERE (Warrior.ID = 1)"); context.Update<Warrior>(() => new { Race = "Elf" }, e => e.ID == 1); context.Commit(); Assert.AreEqual(sql, "UPDATE Warrior SET Race = 'Elf' WHERE (Warrior.ID = 1)"); context.Update<Warrior>(() => new { ID = 1, Race = "Elf" }, e => e.ID == 1); context.Commit(); Assert.AreEqual(sql, "UPDATE Warrior SET Race = 'Elf' WHERE (Warrior.ID = 1)"); context.Update<Warrior>(() => new { Race = "Elf" }, e => e.ID == 1 && e.SpecialSkill == null); context.Commit(); Assert.AreEqual(sql, "UPDATE Warrior SET Race = 'Elf' WHERE ((Warrior.ID = 1) AND (Warrior.SpecialSkill is null))"); context.Update<Warrior>(() => new Warrior { ID = 1, Race = "Elf" }).Ignore(w => w.SpecialSkill).Ignore(w => w.Name); context.Commit(); Assert.AreEqual(sql, "UPDATE Warrior SET WeaponID = 0, Race = 'Elf' WHERE (Warrior.ID = 1)"); var id = 1; context.Update<Warrior>(() => new { ID = 1, Race = "Elf" }, e => e.ID == id); context.Commit(); Assert.AreEqual(sql, "UPDATE Warrior SET Race = 'Elf' WHERE (Warrior.ID = 1)"); } }
public void ISelectQueryExpressionWithIgnoringFields() { var sql = ""; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor<WarriorWithName>().BeforeExecute(s => sql = s.QueryString.Flatten()); using (var context = provider.Open()) { // ignore a member in the select context.From<WarriorWithName>() .Ignore(w => w.ID) .Ignore(w => w.Name) .Ignore(w => w.SpecialSkill) .Select(); Assert.AreEqual(sql, "SELECT WeaponID, Race FROM WarriorWithName"); // ignore a member in the select context.From<WarriorWithName>() .Ignore(w => w.ID) .Ignore(w => w.Name) .Ignore(w => w.SpecialSkill) .Map(w => w.Name) .Select(); Assert.AreEqual(sql, "SELECT WarriorWithName.Name, WeaponID, Race FROM WarriorWithName"); // ignore a member in the select context.From<WarriorWithName>() .Ignore(w => w.ID) .Ignore(w => w.Name) .Map(w => w.WeaponID, "TestFieldName") .Ignore(w => w.SpecialSkill) .Select(); Assert.AreEqual(sql, "SELECT WarriorWithName.WeaponID AS TestFieldName, Race FROM WarriorWithName"); } }
public void SelectWithWhereTest() { var sql = ""; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor<Employee>().BeforeExecute(s => sql = s.QueryString.Flatten()); provider.Interceptor<Orders>().BeforeExecute(s => sql = s.QueryString.Flatten()); using (var context = provider.Open()) { // select statement with a where operation and a or operation that has two genereic parameters and alias for both types context.From<Customers>("cust") .Map(e => e.EmployeeID) .Map(e => e.Address) .Map(e => e.City) .Map(e => e.PostalCode) .Join<Orders>((o, c) => o.EmployeeID == c.EmployeeID, source: "cust") .Join<Employee>((e, o) => e.EmployeeID == o.EmployeeID, alias: "emp") .Where(e => e.FirstName.Contains("Davolio")) .Or<Customers, Employee>((c, e) => c.EmployeeID == e.EmployeeID, "cust", "emp") .Select(); var expected = "SELECT cust.EmployeeID, cust.Address, cust.City, cust.PostalCode, LastName, FirstName, Title, BirthDate, HireDate, ReportsTo FROM Customers cust JOIN Orders ON (Orders.EmployeeID = cust.EmployeeID) JOIN Employee emp ON (emp.EmployeeID = Orders.EmployeeID) WHERE emp.FirstName like '%Davolio%' OR (cust.EmployeeID = emp.EmployeeID)"; //"select cust.EmployeeID, OrdersID, CustomerID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Customers cust JOIN Orders ON (Orders.EmployeeID = cust.EmployeeID) JOIN Employee emp ON (emp.EmployeeID = Orders.EmployeeID) where emp.FirstName like '%Davolio%' or (cust.EmployeeID = emp.EmployeeID)"; Assert.AreEqual(sql, expected); // select statement with a where operation and a or operation that has two genereic parameters and a alias ON the source type context.From<Customers>() .Map(e => e.EmployeeID) .Map(e => e.Address) .Map(e => e.City) .Map(e => e.PostalCode) .Join<Orders>((o, c) => o.EmployeeID == c.EmployeeID) .Join<Employee>((e, o) => e.EmployeeID == o.EmployeeID, alias: "emp") .Where(e => e.FirstName.Contains("Davolio")) .Or<Customers, Employee>((c, e) => c.EmployeeID == e.EmployeeID, source: "emp") .Select(); expected = "SELECT Customers.EmployeeID, Customers.Address, Customers.City, Customers.PostalCode, LastName, FirstName, Title, BirthDate, HireDate, ReportsTo FROM Customers JOIN Orders ON (Orders.EmployeeID = Customers.EmployeeID) JOIN Employee emp ON (emp.EmployeeID = Orders.EmployeeID) WHERE emp.FirstName like '%Davolio%' OR (Customers.EmployeeID = emp.EmployeeID)"; //expected = "select Customers.EmployeeID, OrdersID, CustomerID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry from Customers JOIN Orders ON (Orders.EmployeeID = Customers.EmployeeID) JOIN Employee emp ON (emp.EmployeeID = Orders.EmployeeID) where emp.FirstName like '%Davolio%' or (Customers.EmployeeID = emp.EmployeeID)"; Assert.AreEqual(sql, expected); // select statement with a where operation and a or operation that has two genereic parameters and a alias ON the type context.From<Customers>("cust") .Map(e => e.EmployeeID) .Map(e => e.Address) .Map(e => e.City) .Map(e => e.PostalCode) .Join<Orders>((o, c) => o.EmployeeID == c.EmployeeID, source: "cust") .Join<Employee>((e, o) => e.EmployeeID == o.EmployeeID) .Where(e => e.FirstName.Contains("Davolio")) .Or<Customers, Employee>((c, e) => c.EmployeeID == e.EmployeeID, alias: "cust") .Select(); expected = "SELECT cust.EmployeeID, cust.Address, cust.City, cust.PostalCode, LastName, FirstName, Title, BirthDate, HireDate, ReportsTo FROM Customers cust JOIN Orders ON (Orders.EmployeeID = cust.EmployeeID) JOIN Employee ON (Employee.EmployeeID = Orders.EmployeeID) WHERE Employee.FirstName like '%Davolio%' OR (cust.EmployeeID = Employee.EmployeeID)"; //expected = "select cust.EmployeeID, OrdersID, CustomerID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Customers cust JOIN Orders ON (Orders.EmployeeID = cust.EmployeeID) JOIN Employee ON (Employee.EmployeeID = Orders.EmployeeID) where Employee.FirstName like '%Davolio%' or (cust.EmployeeID = Employee.EmployeeID)"; Assert.AreEqual(sql, expected); // Select statement with a simple where operation context.From<Orders>() .Map(o => o.OrdersID) .Join<OrderDetails>((d, o) => d.OrdersID == o.OrdersID) .Where(o => o.Discount > 0) .Rebase<OrderDetails, Orders>() .Select(); expected = "SELECT Orders.OrdersID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders JOIN OrderDetails ON (OrderDetails.OrdersID = Orders.OrdersID) WHERE (OrderDetails.Discount > '0')"; Assert.AreEqual(sql, expected); // select statement with a where and a simple or operation context.From<Orders>() .Where(p => p.CustomerID.StartsWith("P")) .Or<Orders>(o => o.ShipCity == "London") .Select(); expected = "SELECT OrdersID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders WHERE Orders.CustomerID like 'P%' OR (Orders.ShipCity = 'London')"; Assert.AreEqual(sql, expected); // select statement with a where and a simple or operation context.From<Orders>() .Where(p => p.CustomerID.StartsWith("P")) .Or<Orders>(o => o.ShipCity == "Paris") .Or<Orders>(o => o.ShipCity == "London") .Select(); expected = "SELECT OrdersID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders WHERE Orders.CustomerID like 'P%' OR (Orders.ShipCity = 'Paris') OR (Orders.ShipCity = 'London')"; Assert.AreEqual(sql, expected); // Select statement with a where and a generic OR operation context.From<Orders>("ord") .Where(p => p.CustomerID.StartsWith("P")) .Or<Orders>(o => o.ShipCity == "London", "ord") .Select(); expected = "SELECT OrdersID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders ord WHERE ord.CustomerID like 'P%' OR (ord.ShipCity = 'London')"; Assert.AreEqual(sql, expected); // select statement with a where and a simple and operation context.From<Orders>() .Where(p => p.CustomerID.StartsWith("se")) .And(o => o.ShipCity == "London") .Select(); expected = "SELECT OrdersID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders WHERE Orders.CustomerID like 'se%' AND (Orders.ShipCity = 'London')"; Assert.AreEqual(sql, expected); } }
public void SelectTestForOrders() { var sql = ""; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor<Orders>().BeforeExecute(s => sql = s.QueryString.Flatten()); using (var context = provider.Open()) { // select statement with a FOR expression and mapping members/fields to a specific table context.From<Orders>().Join<OrderDetails>((od, o) => od.OrdersID == o.OrdersID).For<Orders>().Map<Orders>(o => o.OrdersID).Select(); var expected = "SELECT CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry, Orders.OrdersID FROM Orders JOIN OrderDetails ON (OrderDetails.OrdersID = Orders.OrdersID)"; Assert.AreEqual(sql, expected); // select statement with a FOR expression and ignoring fields in the resultset context.From<Orders>() .Join<OrderDetails>((od, o) => od.OrdersID == o.OrdersID) .For<Orders>() .Ignore(o => o.OrdersID) .Ignore(o => o.OrderDate) .Ignore(o => o.RequiredDate) .Select(); expected = "SELECT CustomerID, EmployeeID, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders JOIN OrderDetails ON (OrderDetails.OrdersID = Orders.OrdersID)"; Assert.AreEqual(sql, expected); // select statement that compiles FROM a FOR operation with a anonym object defining the resultset entries and mapped to a defined type context.From<Orders>().Map(o => o.OrdersID).Join<OrderDetails>((od, o) => od.OrdersID == o.OrdersID).For<Orders>().Select(); expected = "SELECT Orders.OrdersID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders JOIN OrderDetails ON (OrderDetails.OrdersID = Orders.OrdersID)"; Assert.AreEqual(sql, expected); // simple select from statement context.From<Orders>().Select(); expected = "SELECT OrdersID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders"; Assert.AreEqual(sql, expected); } }
public void SelectWithPredicate() { var sql = string.Empty; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor<Warrior>().BeforeExecute(s => sql = s.QueryString.Flatten()); using (var context = provider.Open()) { // ignore a member in the select context.From<Warrior>(w => w.ID == 1) .Select(); Assert.AreEqual(sql, "SELECT ID, Name, WeaponID, Race, SpecialSkill FROM Warrior WHERE (Warrior.ID = 1)"); // ignore a member in the SELECT context.Select<Warrior>(w => w.ID == 1); Assert.AreEqual(sql, "SELECT ID, Name, WeaponID, Race, SpecialSkill FROM Warrior WHERE (Warrior.ID = 1)"); } }
public void SelectWithConstraintInBaseClass() { var sql = ""; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor(() => new { ID = 0 }).BeforeExecute(s => sql = s.QueryString.Flatten()); using (var context = provider.Open()) { // select the properties that are defined in the mapping context.From<WarriorDerivate>(a => a.ID == 5).Select(() => new { ID = 0 }); Assert.AreEqual(sql, "SELECT ID FROM WarriorDerivate WHERE (WarriorDerivate.ID = 5)"); } }
public void SelectCustomObjectWithDelegate() { var expected = "SELECT Orders.OrdersID, ProductID, UnitPrice, Quantity, Discount FROM Orders JOIN OrderDetails ON (OrderDetails.OrdersID = Orders.OrdersID)"; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor<OrderWithDetail>().BeforeExecute(s => Assert.AreEqual(s.QueryString.Flatten(), expected)); using (var context = provider.Open()) { // SELECT only the properties that are defined in the anony object var items = context.From<Orders>() .Map(o => o.OrdersID) .Join<OrderDetails>((od, o) => od.OrdersID == o.OrdersID) .Select(od => new OrderWithDetail { // only select the properties defined ProductID = od.ProductID, Quantity = od.Quantity }); items.Any(); } }
public void SelectTestWithForAndCustomMaps() { var sql = ""; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor<Orders>().BeforeExecute(s => sql = s.QueryString.Flatten()); provider.Interceptor(() => new { WarriorName = "", WeaponName = "" }) .BeforeExecute(s => sql = s.QueryString.Flatten()); using (var context = provider.Open()) { // select statement with a FOR expression and mapping members/fields to a specific table context.From<Orders>().Join<OrderDetails>((od, o) => od.OrdersID == o.OrdersID).For<Orders>().Map<Orders>(o => o.OrdersID).Select(); var expected = "SELECT CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry, Orders.OrdersID FROM Orders JOIN OrderDetails ON (OrderDetails.OrdersID = Orders.OrdersID)"; Assert.AreEqual(sql, expected); // select statement with a FOR expression and ignoring fields in the resultset context.From<Warrior>().Join<Weapon>((wpn, wrir) => wpn.ID == wrir.WeaponID) .For(() => new { WarriorName = "", WeaponName = "" }) .Map<Warrior>(wrir => wrir.Name, a => a.WarriorName) .Map<Weapon>(wpn => wpn.Name, a => a.WeaponName) .Select(); expected = "SELECT Warrior.Name AS WarriorName, Weapon.Name AS WeaponName FROM Warrior JOIN Weapon ON (Weapon.ID = Warrior.WeaponID)"; Assert.AreEqual(expected, sql); } }
public void SelectWithINExpression() { var expected = "SELECT ID, Name, WeaponID, Race, SpecialSkill FROM Warrior WHERE Warrior.Race In ('Elf','Dwarf')"; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor<Warrior>().BeforeExecute(s => Assert.AreEqual(s.QueryString.Flatten(), expected)); using (var context = provider.Open()) { IEnumerable<string> races = new List<string> { "Elf", "Dwarf" }; context.From<Warrior>() .Where(w => races.Contains(w.Race)) .Select(); } }
public void SelectWithGroupBy() { var sql = ""; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor<Warrior>().BeforeExecute(s => sql = s.QueryString.Flatten()); provider.Interceptor(() => new { Race = "" }).BeforeExecute(s => sql = s.QueryString.Flatten()); provider.Interceptor(() => new { Race = "", WeaponID = 0 }).BeforeExecute(s => sql = s.QueryString.Flatten()); provider.Interceptor(() => new { ID = 0, Race = "" }).BeforeExecute(s => sql = s.QueryString.Flatten()); using (var context = provider.Open()) { context.From<Warrior>().Ignore(w => w.ID).Ignore(w => w.SpecialSkill).Ignore(w => w.WeaponID).Ignore(w => w.Name).GroupBy(w => w.Race).Select(); Assert.AreEqual(sql, "SELECT Race FROM Warrior GROUP BY Race"); context.From<Warrior>().GroupBy(w => w.Race).ThenBy(w => w.WeaponID).For<Warrior>().Ignore(w => w.ID).Ignore(w => w.SpecialSkill).Ignore(w => w.Name).Select(); Assert.AreEqual(sql, "SELECT WeaponID, Race FROM Warrior GROUP BY Race, WeaponID"); context.From<Warrior>().For(() => new { ID = 0, Race = "" }).GroupBy(w => w.Race).ThenBy(w => w.ID).Select(); Assert.AreEqual(sql, "SELECT ID, Race FROM Warrior GROUP BY Race, ID"); context.From<Warrior>().Join<Weapon>((wep, war) => wep.ID == war.WeaponID).Where(w => w.Damage > 20).GroupBy<Warrior>(w => w.Race).For(() => new { Race = "" }).Select(); Assert.AreEqual(sql, "SELECT Race FROM Warrior JOIN Weapon ON (Weapon.ID = Warrior.WeaponID) WHERE (Weapon.Damage > 20) GROUP BY Race"); context.From<Warrior>().Join<Weapon>((wep, war) => wep.ID == war.WeaponID).Where(w => w.Damage > 20).GroupBy<Warrior>(w => w.Race).ThenBy<Warrior>(w => w.WeaponID).For(() => new { Race = "", WeaponID = 0 }).Select(); Assert.AreEqual(sql, "SELECT Race, WeaponID FROM Warrior JOIN Weapon ON (Weapon.ID = Warrior.WeaponID) WHERE (Weapon.Damage > 20) GROUP BY Race, WeaponID"); context.From<Warrior>().Join<Weapon>((wep, war) => wep.ID == war.WeaponID).Where(w => w.Damage > 20).For(() => new { Race = "" }).GroupBy(w => w.Race).Select(); Assert.AreEqual(sql, "SELECT Race FROM Warrior JOIN Weapon ON (Weapon.ID = Warrior.WeaponID) WHERE (Weapon.Damage > 20) GROUP BY Race"); context.From<Warrior>().Join<Weapon>((wep, war) => wep.ID == war.WeaponID).Where(w => w.Damage > 20).For(() => new { Race = "", WeaponID = 0 }).GroupBy(w => w.Race).ThenBy(w => w.WeaponID).Select(); Assert.AreEqual(sql, "SELECT Race, WeaponID FROM Warrior JOIN Weapon ON (Weapon.ID = Warrior.WeaponID) WHERE (Weapon.Damage > 20) GROUP BY Race, WeaponID"); } }
public void SelectWithExtendedMapping() { var expected = "SELECT Orders.Freight AS SpecialFreight, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry, ProductID, UnitPrice, Quantity, Discount FROM Orders JOIN OrderDetails ON (OrderDetails.OrdersID = Orders.OrdersID)"; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor<OrderWithDetailExtended>().BeforeExecute(s => Assert.AreEqual(s.QueryString.Flatten(), expected)); using (var context = provider.Open()) { // Map => To context.From<Orders>() .Join<OrderDetails>((detail, order) => detail.OrdersID == order.OrdersID) .Map(i => i.OrdersID) // map a property from a joni to a property in the result type .Map<Orders, OrderWithDetailExtended>(source => source.Freight, alias => alias.SpecialFreight) .Select<OrderWithDetailExtended>(); } }
public void SelectWithDifferenctCasesInMappedPropertyNamesTest() { var sql = ""; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor<ArbeitsPlanHeader>().BeforeExecute(s => sql = s.QueryString.Flatten()); using (var context = provider.Open()) { context.From<ArbeitsPlan>() .Map(ap => ap.PlanID) .Map<int>(ap => ap.Status, converter: value => value == 1 ? Status.Active : Status.Inactive) .Map<ArbeitsPlanHeader>(ap => ap.PlanID, aph => aph.ID) .Map<ArbeitsPlanHeader>(ap => ap.ATID, aph => aph.ArbeitsTageId) .Map(ap => ap.Name) .Join<Schemas>((s, ap) => s.SchemaID == ap.SchemaID) .Map(s => s.SchemaID) .Where(ap => ap.Status == 1) .For<ArbeitsPlanHeader>() .Ignore(aph => aph.Layout) .Select(); Assert.AreEqual(sql, "SELECT ArbeitsPlan.PlanID, ArbeitsPlan.Status, ArbeitsPlan.PlanID AS ID, ArbeitsPlan.ATID AS ArbeitsTageId, Schemas.SchemaID, JahrId, Von, Bis FROM ArbeitsPlan JOIN Schemas ON (Schemas.SchemaID = ArbeitsPlan.SchemaID) WHERE (Schemas.Status = 1)"); } }
public void SelectForAnonymObjectType() { var expected = "SELECT ProductID, Quantity FROM Orders JOIN OrderDetails ON (OrderDetails.OrdersID = Orders.OrdersID)"; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor(() => new { ProductID = 0, Quantity = 0 }) .BeforeExecute(s => Assert.AreEqual(s.QueryString.Flatten(), expected)); using (var context = provider.Open()) { // select only the properties that are defined in the anony object context.From<Orders>() .Join<OrderDetails>((od, o) => od.OrdersID == o.OrdersID) .For(() => new { ProductID = 0, Quantity = 0 }) .Select(); } }
public void SelectWithMin() { var sql = ""; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor<Warrior>().BeforeExecute(s => sql = s.QueryString.Flatten()); provider.Interceptor(() => new { MinID = 0 }).BeforeExecute(s => sql = s.QueryString.Flatten()); using (var context = provider.Open()) { // select the min id context.From<Warrior>().Min(w => w.ID).Select(); Assert.AreEqual(sql, "SELECT MIN(ID) AS ID FROM Warrior"); // select the min id with grouping context.From<Warrior>().Min(w => w.ID).Map(w => w.Race).GroupBy(w => w.Race).Select(); Assert.AreEqual(sql, "SELECT MIN(ID) AS ID, Warrior.Race FROM Warrior GROUP BY Race"); // select the min id with grouping context.From<Warrior>().Min(w => w.ID).Map(w => w.Race).GroupBy(w => w.Race).For<Warrior>().Select(); Assert.AreEqual(sql, "SELECT MIN(ID) AS ID, Warrior.Race FROM Warrior GROUP BY Race"); // select the min id context.From<Warrior>().Min(w => w.ID, "MinID").For(() => new { MinID = 0 }).Select(); Assert.AreEqual(sql, "SELECT MIN(ID) AS MinID FROM Warrior"); } }
public void SelectWithOrderTest() { var sql = ""; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor<Orders>().BeforeExecute(s => sql = s.QueryString.Flatten()); using (var context = provider.Open()) { // join with simple order by context.From<Orders>().OrderBy(o => o.OrderDate).Select(); string expected = "SELECT OrdersID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders ORDER BY Orders.OrderDate ASC"; Assert.AreEqual(sql, expected); // join with generic order by context.From<Orders>().Join<Customers>((c, o) => c.CustomerID == o.CustomerID).Map(c => c.CustomerID).Map(c => c.EmployeeID).OrderBy<Orders>(o => o.OrderDate).Select(); expected = "SELECT Customers.CustomerID, Customers.EmployeeID, OrdersID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders JOIN Customers ON (Customers.CustomerID = Orders.CustomerID) ORDER BY Orders.OrderDate ASC"; Assert.AreEqual(sql, expected); // join with simple order by desc context.From<Orders>().OrderByDesc(o => o.OrderDate).Select(); expected = "SELECT OrdersID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders ORDER BY Orders.OrderDate DESC"; Assert.AreEqual(sql, expected); // join with generic order by desc context.From<Orders>().Join<Customers>((c, o) => c.CustomerID == o.CustomerID).Map(c => c.CustomerID).Map(c => c.EmployeeID).OrderByDesc<Orders>(o => o.OrderDate).Select(); expected = "SELECT Customers.CustomerID, Customers.EmployeeID, OrdersID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders JOIN Customers ON (Customers.CustomerID = Orders.CustomerID) ORDER BY Orders.OrderDate DESC"; Assert.AreEqual(sql, expected); // join with simple order by with simple then by context.From<Orders>().OrderBy(o => o.OrderDate).ThenBy(o => o.RequiredDate).Select(); expected = "SELECT OrdersID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders ORDER BY Orders.OrderDate ASC, Orders.RequiredDate ASC"; Assert.AreEqual(sql, expected); // join with generic order by with simple then by context.From<Orders>() .Join<Customers>((c, o) => c.CustomerID == o.CustomerID) .Map(c => c.CustomerID) .Map(c => c.EmployeeID) .OrderBy<Orders>(o => o.OrderDate) .ThenBy(o => o.RequiredDate) .Select(); expected = "SELECT Customers.CustomerID, Customers.EmployeeID, OrdersID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders JOIN Customers ON (Customers.CustomerID = Orders.CustomerID) ORDER BY Orders.OrderDate ASC, Orders.RequiredDate ASC"; Assert.AreEqual(sql, expected); // join with simple order by desc with simple then by context.From<Orders>().OrderByDesc(o => o.OrderDate).ThenBy(o => o.RequiredDate).Select(); expected = "SELECT OrdersID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders ORDER BY Orders.OrderDate DESC, Orders.RequiredDate ASC"; Assert.AreEqual(sql, expected); // join with generic order by desc with simple then by context.From<Orders>() .Join<Customers>((c, o) => c.CustomerID == o.CustomerID) .Map(c => c.CustomerID) .Map(c => c.EmployeeID) .OrderByDesc<Orders>(o => o.OrderDate) .ThenBy(o => o.RequiredDate) .Select(); expected = "SELECT Customers.CustomerID, Customers.EmployeeID, OrdersID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders JOIN Customers ON (Customers.CustomerID = Orders.CustomerID) ORDER BY Orders.OrderDate DESC, Orders.RequiredDate ASC"; Assert.AreEqual(sql, expected); // join with simple order by with generic then by context.From<Orders>().OrderBy(o => o.OrderDate).ThenBy<Orders>(o => o.RequiredDate).Select(); expected = "SELECT OrdersID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders ORDER BY Orders.OrderDate ASC, Orders.RequiredDate ASC"; Assert.AreEqual(sql, expected); // join with generic order by with generic then by context.From<Orders>() .Join<Customers>((c, o) => c.CustomerID == o.CustomerID) .Map(c => c.CustomerID) .Map(c => c.EmployeeID) .OrderBy<Orders>(o => o.OrderDate) .ThenBy<Customers>(c => c.CompanyName) .Select(); expected = "SELECT Customers.CustomerID, Customers.EmployeeID, OrdersID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders JOIN Customers ON (Customers.CustomerID = Orders.CustomerID) ORDER BY Orders.OrderDate ASC, Customers.CompanyName ASC"; Assert.AreEqual(sql, expected); // join with simple order by desc with generic then by context.From<Orders>().OrderByDesc(o => o.OrderDate).ThenBy<Orders>(o => o.RequiredDate).Select(); expected = "SELECT OrdersID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders ORDER BY Orders.OrderDate DESC, Orders.RequiredDate ASC"; Assert.AreEqual(sql, expected); // join with generic order by desc with generic then by context.From<Orders>() .Join<Customers>((c, o) => c.CustomerID == o.CustomerID) .Map(c => c.CustomerID) .Map(c => c.EmployeeID) .OrderByDesc<Orders>(o => o.OrderDate) .ThenBy<Customers>(c => c.CompanyName) .Select(); expected = "SELECT Customers.CustomerID, Customers.EmployeeID, OrdersID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders JOIN Customers ON (Customers.CustomerID = Orders.CustomerID) ORDER BY Orders.OrderDate DESC, Customers.CompanyName ASC"; Assert.AreEqual(sql, expected); // join with simple order by with generic then by desc context.From<Orders>().OrderBy(o => o.OrderDate).ThenByDesc<Orders>(o => o.RequiredDate).Select(); expected = "SELECT OrdersID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders ORDER BY Orders.OrderDate ASC, Orders.RequiredDate DESC"; Assert.AreEqual(sql, expected); // join with generic order by with generic then by desc context.From<Orders>() .Join<Customers>((c, o) => c.CustomerID == o.CustomerID) .Map(c => c.CustomerID) .Map(c => c.EmployeeID) .OrderBy<Orders>(o => o.OrderDate) .ThenByDesc<Customers>(c => c.CompanyName) .Select(); expected = "SELECT Customers.CustomerID, Customers.EmployeeID, OrdersID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders JOIN Customers ON (Customers.CustomerID = Orders.CustomerID) ORDER BY Orders.OrderDate ASC, Customers.CompanyName DESC"; Assert.AreEqual(sql, expected); // join with simple order by desc with generic then by desc context.From<Orders>().OrderByDesc(o => o.OrderDate).ThenByDesc<Orders>(o => o.RequiredDate).Select(); expected = "SELECT OrdersID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders ORDER BY Orders.OrderDate DESC, Orders.RequiredDate DESC"; Assert.AreEqual(sql, expected); // join with generic order by desc with generic then by desc context.From<Orders>() .Join<Customers>((c, o) => c.CustomerID == o.CustomerID) .Map(c => c.CustomerID) .Map(c => c.EmployeeID) .OrderByDesc<Orders>(o => o.OrderDate) .ThenByDesc<Customers>(c => c.CompanyName) .Select(); expected = "SELECT Customers.CustomerID, Customers.EmployeeID, OrdersID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders JOIN Customers ON (Customers.CustomerID = Orders.CustomerID) ORDER BY Orders.OrderDate DESC, Customers.CompanyName DESC"; Assert.AreEqual(sql, expected); } }
public void SelectWithMultipleMapsToSameType() { var sql = ""; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor<WarriorWithName>().BeforeExecute(s => sql = s.QueryString.Flatten()); using (var context = provider.Open()) { // select the properties that are defined in the mapping context.From<WarriorWithName>() .Map(w => w.WeaponID, "ID") .Map(w => w.WeaponID) .Map(w => w.Race, "Name") .Map(w => w.Race) .Select(); Assert.AreEqual(sql, "SELECT WarriorWithName.WeaponID AS ID, WarriorWithName.WeaponID, WarriorWithName.Race AS Name, WarriorWithName.Race, SpecialSkill FROM WarriorWithName"); // map one property to a custom field context.From<WarriorWithName>() .Map(w => w.WeaponID, "ID") .Map(w => w.Race, "Name") .Map(w => w.Race) .Select(); Assert.AreEqual(sql, "SELECT WarriorWithName.WeaponID AS ID, WarriorWithName.Race AS Name, WarriorWithName.Race, SpecialSkill FROM WarriorWithName"); } }
public void SelectAnonymObjectTypeDefiniton() { var expected = "SELECT ProductID, Quantity FROM Orders JOIN OrderDetails ON (OrderDetails.OrdersID = Orders.OrdersID)"; var type = new { ProductID = 0, Quantity = 0.0 }; var provider = new ContextProvider(new Mock.ConnectionProvider()); provider.Interceptor(() => type).BeforeExecute(s => Assert.AreEqual(s.QueryString.Flatten(), expected)); using (var context = provider.Open()) { context.From<Orders>() .Join<OrderDetails>((od, o) => od.OrdersID == o.OrdersID) .Select(() => new { ProductID = 0, Quantity = 0.0 }); } }