public void ProduceCorrectSQLFromOuterJoinedSelectQueryOrderWithTableAliasAndParameterizedClause() { var clause = Clause.New() .AddClause("@customer", 2, x => $"C.Id = {x}") .AddClause("@category", 5, x => $"O.CategoryId = {x}") ; var orderColumns = new string[] { "Product" }; var customerColumns = new string[] { "Name" }; var primary = new SelectQueryOrder("Order", "O", orderColumns); var secondary = new SelectQueryOrder("Customer", "C", customerColumns, "Id", new KeyValuePair <string, string>("O", "CustomerId")); var dataOrder = SelectQuery.New(primary).AddFullOuterJoin(secondary).Build(clause); string expectedSQL = "SELECT\r\n O.Product AS OProduct,\r\n C.Name AS CName\r\nFROM Order O\r\nFULL OUTER JOIN Customer C ON C.Id = O.CustomerId\r\nWHERE C.Id = @customer\r\nAND O.CategoryId = @category"; Assert.AreEqual(expectedSQL, dataOrder.Query); Assert.AreEqual(2, dataOrder.Parameters.Count()); Assert.AreEqual("@customer", dataOrder.Parameters.ElementAt(0).Key); Assert.AreEqual(2, dataOrder.Parameters.ElementAt(0).Value); Assert.AreEqual("@category", dataOrder.Parameters.ElementAt(1).Key); Assert.AreEqual(5, dataOrder.Parameters.ElementAt(1).Value); Assert.AreEqual(2, dataOrder.Aliases.Count()); Assert.AreEqual("O", dataOrder.Aliases.ElementAt(0).Name); Assert.AreEqual("Product", dataOrder.Aliases.ElementAt(0).Data.Item1); Assert.AreEqual("OProduct", dataOrder.Aliases.ElementAt(0).Data.Item2); Assert.AreEqual("C", dataOrder.Aliases.ElementAt(1).Name); Assert.AreEqual("Name", dataOrder.Aliases.ElementAt(1).Data.Item1); Assert.AreEqual("CName", dataOrder.Aliases.ElementAt(1).Data.Item2); }
public void ProduceCorrectSQLFromOuterJoinedSelectQueryOrderWithUnparameterizedClause() { var orderColumns = new string[] { "Product" }; var customerColumns = new string[] { "Name" }; var primary = new SelectQueryOrder("Order", "", orderColumns); var secondary = new SelectQueryOrder("Customer", "", customerColumns, "Id", new KeyValuePair <string, string>("Order", "CustomerId")); var dataOrder = SelectQuery.New(primary).AddFullOuterJoin(secondary).Build(Clause.New().AddClause("Customer.Id = 2").AddClause("Order.CategoryId = 5")); string expectedSQL = "SELECT\r\n Order.Product AS OrderProduct,\r\n Customer.Name AS CustomerName\r\nFROM Order\r\nFULL OUTER JOIN Customer ON Customer.Id = Order.CustomerId\r\nWHERE Customer.Id = 2\r\nAND Order.CategoryId = 5"; Assert.AreEqual(expectedSQL, dataOrder.Query); Assert.IsFalse(dataOrder.Parameters.Any()); Assert.AreEqual(2, dataOrder.Aliases.Count()); Assert.AreEqual("Order", dataOrder.Aliases.ElementAt(0).Name); Assert.AreEqual("Product", dataOrder.Aliases.ElementAt(0).Data.Item1); Assert.AreEqual("OrderProduct", dataOrder.Aliases.ElementAt(0).Data.Item2); Assert.AreEqual("Customer", dataOrder.Aliases.ElementAt(1).Name); Assert.AreEqual("Name", dataOrder.Aliases.ElementAt(1).Data.Item1); Assert.AreEqual("CustomerName", dataOrder.Aliases.ElementAt(1).Data.Item2); }
public void ProduceCorrectSQLFromOuterJoinedSelectQueryOrderWithTableAlias() { var orderColumns = new string[] { "Product" }; var customerColumns = new string[] { "Name" }; var primary = new SelectQueryOrder("Order", "O", orderColumns); var secondary = new SelectQueryOrder("Customer", "C", customerColumns, "Id", new KeyValuePair <string, string>("O", "CustomerId")); var dataOrder = SelectQuery.New(primary).AddFullOuterJoin(secondary).Build(); string expectedSQL = "SELECT\r\n O.Product AS OProduct,\r\n C.Name AS CName\r\nFROM Order O\r\nFULL OUTER JOIN Customer C ON C.Id = O.CustomerId"; Assert.AreEqual(expectedSQL, dataOrder.Query); Assert.IsFalse(dataOrder.Parameters.Any()); Assert.AreEqual(2, dataOrder.Aliases.Count()); Assert.AreEqual("O", dataOrder.Aliases.ElementAt(0).Name); Assert.AreEqual("Product", dataOrder.Aliases.ElementAt(0).Data.Item1); Assert.AreEqual("OProduct", dataOrder.Aliases.ElementAt(0).Data.Item2); Assert.AreEqual("C", dataOrder.Aliases.ElementAt(1).Name); Assert.AreEqual("Name", dataOrder.Aliases.ElementAt(1).Data.Item1); Assert.AreEqual("CName", dataOrder.Aliases.ElementAt(1).Data.Item2); }