public Join(FromTerm leftTable, FromTerm rightTable, WhereClause conditions, JoinType type)
{
this.leftTable = leftTable;
this.rightTable = rightTable;
// this.leftField = leftField;
// this.rightField = rightField;
this.conditions = conditions;
this.type = type;
}
/// <summary>
/// Creates a SqlExpression which represents a field in a database table.
/// </summary>
/// <param name="fieldName">Name of a field</param>
/// <param name="table">The table this field belongs to</param>
/// <returns></returns>
public static SqlExpression Field(string fieldName, FromTerm table)
{
SqlExpression expr = new SqlExpression();
expr.val = fieldName;
expr.table = table;
expr.type = SqlExpressionType.Field;
return(expr);
}
/// <summary>
/// Joins two tables using on an arbitrary join condition
/// </summary>
/// <param name="type">The type of join to be created.</param>
/// <param name="leftTable">The left table</param>
/// <param name="rightTable">The right table</param>
/// <param name="conditions">Specifies how the join should be performed</param>
/// <remarks>
/// Use this overload to create complex join conditions.
/// Note that not all <see cref="WhereClause"/> operators and expressions are supported in joins.
/// </remarks>
/// <example>
/// WhereClause condition = new WhereClause(WhereClauseRelationship.Or);
/// condition.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("productId", tOrders), SqlExpression.Field("productId", tProducts), CompareOperator.Equal));
/// condition.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("productName", tOrders), SqlExpression.Field("productName", tProducts), CompareOperator.Equal));
/// query.FromClause.Join(JoinType.Left, tOrders, tProducts, condition);
/// </example>
public void Join(JoinType type, FromTerm leftTable, FromTerm rightTable, WhereClause conditions)
{
if (conditions.IsEmpty && type != JoinType.Cross)
{
throw new InvalidQueryException("A join must have at least one condition.");
}
joins.Add(new Join(leftTable, rightTable, conditions, type));
}
/// <summary>
/// Joins two tables using on an array join condition
/// </summary>
/// <param name="type">The type of join to be created.</param>
/// <param name="leftTable">The left table</param>
/// <param name="rightTable">The right table</param>
/// <param name="conditions">An array of equality condition to be applied on the join</param>
/// <remarks>
/// A logical AND will be applied on the conditions.
/// Schematically, the resulting SQL will be ... x join y on (cond1 and cond2 and cond3 and ... and condN) ...
/// </remarks>
public void Join(JoinType type, FromTerm leftTable, FromTerm rightTable, JoinCondition[] conditions)
{
WhereClause clause = new WhereClause(WhereClauseRelationship.And);
foreach (JoinCondition cond in conditions)
{
clause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(cond.LeftField, leftTable), SqlExpression.Field(cond.RightField, rightTable), CompareOperator.Equal));
}
Join(type, leftTable, rightTable, clause);
}
/// <summary>
/// Joins two tables using on a triple join condition
/// </summary>
/// <param name="type">The type of join to be created.</param>
/// <param name="leftTable">The left table</param>
/// <param name="rightTable">The right table</param>
/// <param name="cond1">First equality condition to be applied on the join</param>
/// <param name="cond2">First equality condition to be applied on the join</param>
/// <param name="cond3">First equality condition to be applied on the join</param>
/// <remarks>
/// A logical AND will be applied on all conditions.
/// Schematically, the resulting SQL will be ... x join y on (cond1 and cond2 and cond3) ...
/// </remarks>
public void Join(JoinType type, FromTerm leftTable, FromTerm rightTable, JoinCondition cond1, JoinCondition cond2, JoinCondition cond3)
{
Join(type, leftTable, rightTable, new JoinCondition[] { cond1, cond2, cond3 });
}
/// <summary>
/// Creates an uncoditional join
/// </summary>
/// <param name="type">Must be JoinType.CrossJoin</param>
/// <param name="leftTable"></param>
/// <param name="rightTable"></param>
public void Join(JoinType type, FromTerm leftTable, FromTerm rightTable)
{
Join(type, leftTable, rightTable, new JoinCondition[] {});
}
/// <overloads>Use the following methods to define a join between two FromTerms.</overloads>
/// <summary>
/// Joins two tables using on a single join condition
/// </summary>
/// <param name="type">The type of join to be created.</param>
/// <param name="leftTable">The left table</param>
/// <param name="rightTable">The right table</param>
/// <param name="leftField">Name of the field in the left table to join on</param>
/// <param name="rightField">Name of the field in the right table to join on</param>
/// <example>
/// <code>
/// query.FromClause.Join(JoinType.Left, tCustomers, tOrders, "customerId", "customerId");
/// </code>
/// </example>
public void Join(JoinType type, FromTerm leftTable, FromTerm rightTable, string leftField, string rightField)
{
Join(type, leftTable, rightTable, new JoinCondition(leftField, rightField));
}