/// <summary>
/// Generated SQL code and query parameters from the specified query details.
/// </summary>
/// <param name="existingParameterCount"></param>
/// <returns></returns>
public ISQLinqResult ToSQL(int existingParameterCount = 0, string parameterNamePrefix = SqlExpressionCompiler.DefaultParameterNamePrefix)
{
var parameterCount = existingParameterCount;
var result = new SQLinqSelectResult();
result.Parameters = new Dictionary <string, object>();
result.Take = this.TakeCount;
result.Skip = this.SkipCount;
result.Distinct = this.DistinctValue;
// ****************************************************
// **** FROM ******************************************
result.Table = this.TableName;
// ****************************************************
// **** SELECT ****************************************
if (this.SelectFields.Count == 0)
{
result.Select = new string[] { "*" };
}
else
{
result.Select = this.SelectFields.ToArray();
}
// ****************************************************
// **** JOIN ******************************************
var joins = new List <string>();
foreach (var jc in this.JoinClauses)
{
var r = jc.ToSQL(parameterCount, parameterNamePrefix);
foreach (var j in r.Join)
{
joins.Add(j);
}
foreach (var i in r.Parameters)
{
result.Parameters.Add(i.Key, i.Value);
}
}
result.Join = joins.ToArray();
parameterCount = existingParameterCount + result.Parameters.Count;
// ****************************************************
// **** WHERE *****************************************
var compiledWhere = this.WhereClauses.Compile(parameterCount, parameterNamePrefix);
result.Where = compiledWhere.SQL;
foreach (var p in compiledWhere.Parameters)
{
result.Parameters.Add(p.Key, p.Value);
}
parameterCount = existingParameterCount + result.Parameters.Count;
// ****************************************************
// **** GROUP BY **************************************
if (this.GroupByFields.Count > 0)
{
result.GroupBy = this.GroupByFields.ToArray();
}
// ****************************************************
// **** HAVING ****************************************
var compiledHaving = this.HavingClauses.Compile(parameterCount, parameterNamePrefix);
result.Having = compiledHaving.SQL;
foreach (var p in compiledHaving.Parameters)
{
result.Parameters.Add(p.Key, p.Value);
}
// ****************************************************
// **** ORDER BY **************************************
if (this.OrderByFields.Count > 0)
{
result.OrderBy = this.OrderByFields.ToArray();
}
return(result);
}
public string ToQuery(SQLinqSelectResult selectResult)
{
var orderby = DialectProvider.ConcatFieldArray(selectResult.OrderBy);
var groupby = DialectProvider.ConcatFieldArray(selectResult.GroupBy);
var sb = new StringBuilder();
if (selectResult.Distinct == true)
{
sb.Append("DISTINCT ");
}
// SELECT
sb.Append(DialectProvider.ConcatFieldArray(selectResult.Select));
sb.Append(" FROM ");
sb.Append(selectResult.Table);
if (selectResult.Join != null)
{
foreach (var j in selectResult.Join)
{
sb.Append(_Space);
sb.Append(j);
}
}
if (!string.IsNullOrEmpty(selectResult.Where))
{
sb.Append(" WHERE ");
sb.Append(selectResult.Where);
}
if (!string.IsNullOrEmpty(groupby))
{
sb.Append(" GROUP BY ");
sb.Append(groupby);
}
if (!string.IsNullOrEmpty(selectResult.Having))
{
sb.Append(" HAVING ");
sb.Append(selectResult.Having);
}
var sqlOrderBy = string.Empty;
if (orderby.Length > 0)
{
sqlOrderBy = " ORDER BY " + orderby;
sb.Append(sqlOrderBy);
}
if (selectResult.Take != null)
{
if (selectResult.Skip != null)
{
sb.Append($" LIMIT {selectResult.Take} OFFSET {selectResult.Skip}");
}
else
{
sb.Append($" LIMIT {selectResult.Take}");
}
}
else
{
if (selectResult.Skip != null)
{
sb.Append($" LIMIT {long.MaxValue} OFFSET {selectResult.Skip}");
}
}
return $"SELECT {sb}";
}
