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