/// <summary>
/// Builds a select query across the related tables
/// </summary>
/// <typeparam name="T1">the primary table class</typeparam>
/// <typeparam name="T2">the foreign table class</typeparam>
/// <param name="database">the database the method extends</param>
/// <param name="primary">the primary table properties</param>
/// <param name="foreign">the foreign table properties</param>
/// <param name="join">the type of join</param>
/// <param name="where">the where clause</param>
/// <param name="order">an optional order by clause</param>
/// <returns>A formatted SQL query to select data from the specified tables</returns>
public static string BuildRelatedSelectQuery <T1, T2>(this Database database, TableProperties primary, TableProperties foreign, Join join, string where, string order = "")
{
if (string.IsNullOrEmpty(where))
{
throw new ArgumentException("WHERE clause not specified");
}
if (where.ToLowerInvariant().StartsWith("where ") == false)
{
throw new ArgumentException("WHERE clause not well formed");
}
if (string.IsNullOrEmpty(order) == false && order.ToLowerInvariant().StartsWith("order by ") == false)
{
throw new ArgumentException("ORDER BY clause not well formed");
}
var primaryFactory = database.PocoDataFactory.ForType(typeof(T1));
var primaryTable = primaryFactory.TableInfo.TableName;
var primaryKey = primaryFactory.Columns.FirstOrDefault(c => c.Value.MemberInfoData.Name == primary.Id).Key;
var foreignFactory = database.PocoDataFactory.ForType(typeof(T2));
var foreignTable = foreignFactory.TableInfo.TableName;
var foreignKey = foreignFactory.Columns.FirstOrDefault(c => c.Value.MemberInfoData.Name == foreign.Id).Key;
return(string.Format("SELECT {0}{1} FROM {2} AS {3} {4} {5} AS {6} ON {3}.{7} = {6}.{8} {9}", primary.GetColumns(), foreign.GetColumns(), primaryTable, primary.Alias, join.GetDescription(), foreignTable, foreign.Alias, primaryKey, foreignKey, where));
}