/// <summary>
/// Creates an TSQL Count(1) statement
/// </summary>
/// <typeparam name="TPoco">The type of the poco.</typeparam>
/// <typeparam name="TOut">The type of the out.</typeparam>
/// <param name="query">The query.</param>
/// <returns></returns>
private static ElementProducer <TOut> Sum <TPoco, TOut>(this IElementProducer <TPoco> query)
{
IQueryBuilder newQuery = new RootQuery(query.ContainerObject.AccessLayer);
//in case there is a grouping in the query, we must use a SubQuery
var ordering = query.ContainerObject.SearchLast <OrderByColumnQueryPart>();
var cteName = query.ContainerObject.CreateAlias(QueryIdentifier.QueryIdTypes.Cte);
var item = new CteDefinitionQueryPart.CteInfo()
{
Name = cteName
};
item.CteContentParts.AddRange(query.ContainerObject.Parts.Except(new IQueryPart[] { ordering }).ToArray());
var cteQueryPart = query.ContainerObject.SearchLast <CteDefinitionQueryPart>();
newQuery = newQuery.Add(cteQueryPart ?? (cteQueryPart = new CteDefinitionQueryPart()))
.Add(cteQueryPart.AddCte(item));
var subQueryId = query.ContainerObject.CreateAlias(QueryIdentifier.QueryIdTypes.SubQuery);
var countQueryPart = newQuery
.Add(new CountTargetQueryPart(cteName, subQueryId));
if (ordering != null)
{
var orderByColumnQueryPart = new OrderByColumnQueryPart();
orderByColumnQueryPart.Descending = ordering.Descending;
orderByColumnQueryPart.Columns = orderByColumnQueryPart.Columns
.Select(f => new ColumnInfo(f.ColumnName, f, subQueryId, query.ContainerObject)).ToList();
countQueryPart.Add(orderByColumnQueryPart);
}
return(new ElementProducer <TOut>(countQueryPart));
}
/// <summary>
/// Creates an closed sub select
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query">The query.</param>
/// <param name="subSelect">The sub select.</param>
/// <returns></returns>
public static ElementProducer <T> SubSelect <T>(this RootQuery query,
Func <ElementResultQuery <T> > subSelect)
{
var classInfo = query.ContainerObject.AccessLayer.Config.GetOrCreateClassInfoCache(typeof(T));
var queryIdentifier = query.ContainerObject.CreateTableAlias(classInfo.TableName);
var part = new SubSelectQueryPart(queryIdentifier, subSelect().ContainerObject.Parts, query.ContainerObject);
return(new ElementProducer <T>(query.Add(part)));
}
/// <summary>
/// Creates a Common Table Expression that selects a Specific type
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="TN">The type of the n.</typeparam>
/// <param name="query">The query.</param>
/// <param name="cteName">Name of the cte.</param>
/// <param name="subCte">if set to <c>true</c> [sub cte].</param>
/// <returns></returns>
public static ElementProducer <TN> AsCte <T, TN>(this ElementProducer <T> query,
string cteName,
bool subCte = false)
{
var cp = new RootQuery(query.ContainerObject.AccessLayer);
var prefix = string.Format("WITH {0} AS (", cteName);
cp = cp.QueryText(prefix);
foreach (var genericQueryPart in query.ContainerObject.Parts)
{
cp = cp.Add(genericQueryPart);
}
return(new ElementProducer <TN>(cp.Add(new CteQueryPart(")")).QueryText(string.Format("SELECT {1} FROM {0}", cteName,
query.ContainerObject.AccessLayer.Config.GetOrCreateClassInfoCache(typeof(TN))
.GetSchemaMapping()
.Aggregate((e, f) => e + ", " + f))), query.CurrentIdentifier));
}
public static RootQuery WithCteForType(this RootQuery query, Type target, string cteName,
bool useStarOperator = false)
{
var cteBuilder = new StringBuilder();
cteBuilder.Append("WITH ");
cteBuilder.Append(cteName);
cteBuilder.Append(" (");
cteBuilder.Append(!useStarOperator ? query.ContainerObject.AccessLayer.GetClassInfo(target).CreatePropertyCsv() : "*");
cteBuilder.Append(") AS (");
cteBuilder.Append(query.ContainerObject.AccessLayer.CreateSelect(target));
cteBuilder.Append(")");
return(query.Add(new CteQueryPart(cteBuilder.ToString())));
}
/// <summary>
/// Creates a Common Table Expression that selects a Specific type
/// </summary>
/// <param name="query">The query.</param>
/// <param name="cteName">Name of the cte.</param>
/// <param name="cteAction">The cte action.</param>
/// <param name="subCte">if set to <c>true</c> [sub cte].</param>
/// <returns></returns>
public static RootQuery WithCte <T>(this RootQuery query,
string cteName,
Func <RootQuery, ISelectQuery <T> > cteAction,
bool subCte = false)
{
var lod = query.ContainerObject.Parts.LastOrDefault();
var prefix = string.Empty;
if (lod is CteQueryPart || subCte)
{
prefix = string.Format(", {0} AS", cteName);
}
else
{
prefix = string.Format("WITH {0} AS ", cteName);
}
return(new RootQuery(query.Add(new GenericQueryPart(prefix)).InBracket(cteAction).Add(new CteQueryPart(""))));
}