/// <summary>
/// Applies Dynamic Linq Filtering to an IQueryable overriding the Request querystring values with a passed QuerySettings instance
/// </summary>
/// <returns>IQueryable</returns>
/// <param name="request">Request.</param>
/// <param name="query">Query.</param>
/// <param name="settings">Settings.</param>
/// <typeparam name="TEntity">The 1st type parameter.</typeparam>
public static IQueryable <TEntity> ApplyTo <TEntity>(this HttpRequest request, IQueryable <TEntity> query, QuerySettings settings)
{
if (settings.Filter.Count() < 1)
{
settings.Filter = getFilterOptions(request.Query);
}
//settings = checkSettings(request, settings);
var queryCount = getItems(query, settings);
return(queryCount.Items);
}
/// <summary>
/// Checks the settings.
/// </summary>
/// <returns>The settings.</returns>
/// <param name="request">Request.</param>
/// <param name="settings">Settings.</param>
private static QuerySettings checkSettings(HttpRequest request, QuerySettings settings)
{
return(settings);
}
/// <summary>
/// Gets the items.
/// </summary>
/// <returns>The items.</returns>
/// <param name="query">Query.</param>
/// <param name="settings">Settings.</param>
/// <typeparam name="TEntity">The 1st type parameter.</typeparam>
private static QueryCount <TEntity> getItems <TEntity>(IQueryable <TEntity> query, QuerySettings settings)
{
var queryCount = new QueryCount <TEntity>();
var filters = settings.Filter;
int skip;
int top;
//store orignal qeryable for any union operations
var origQuery = query;
/////////////// FILTERING ////////////////////////////////////
//filter query by list of QueryFilterOptions
if (filters.Any())
{
foreach (var filter in filters)
{
var val = filter.Value;
//insert direct sql expression
if (filter.Operation == FilterOperation.Sql)
{
string strSql = filter.Sql;
query = query.Where(strSql, val);
}
//insert direct sql expression w/ union
else if (filter.Operation == FilterOperation.SqlUnion)
{
string strSql = filter.Sql;
query = query.Union(origQuery.Where(strSql, val));
}
//insert a dynamically generated paramterized sql statement from QueryFilterOptions
else
{
string sql = filter.toSqlExpression();
if (!string.IsNullOrEmpty(sql))
{
if (filter.Transform == FilterTransform.Lower)
{
val = val.ToString().ToLower();
}
else if (filter.Transform == FilterTransform.Upper)
{
val = val.ToString().ToUpper();
}
else if (filter.Transform == FilterTransform.Trim)
{
val = val.ToString().Trim();
}
query = query.Where(sql, val);
}
}
}
}
//else directly insert a passed sql where clause
else if (!string.IsNullOrEmpty(settings.Where))
{
query = query.Where(settings.Where, settings.SqlParams);
}
//////// ASSIGN TOTAL COUNT ///////////////////////////////////////
queryCount.Count = query.Count();
////////////// ORDERING /////////////////////////////////////////
if (!string.IsNullOrEmpty(settings.OrderBy))
{
query = query.OrderBy(settings.OrderBy);
}
/////////////
if (settings.Top != null && settings.Skip != null)
{
skip = Convert.ToInt32(settings.Skip);
top = Convert.ToInt32(settings.Top);
query = query.Skip(skip).Take(top);
}
else if (settings.Top != null)
{
top = Convert.ToInt32(settings.Top);
query = query.Take(top);
}
queryCount.Items = query;
return(queryCount);
}
/// <summary>
/// Applies Dynamic Linq Filtering to an IQueryable overriding the Request querystring values with a passed QuerySettings instance
/// </summary>
/// <returns>PageResult</returns>
/// <param name="request">Request.</param>
/// <param name="query">Query.</param>
/// <param name="settings">Settings.</param>
/// <typeparam name="TEntity">The 1st type parameter.</typeparam>
public static QueryResult <TEntity> PageResult <TEntity>(this HttpRequest request, IQueryable <TEntity> query, QuerySettings settings, Uri nextPageLink)
{
var result = new QueryResult <TEntity>();
//settings = checkSettings(request, settings);
if (settings.Filter.Count() < 1)
{
settings.Filter = getFilterOptions(request.Query);
}
var queryCount = getItems(query, settings);
result.Items = queryCount.Items;
result.Count = queryCount.Count;
result.NextPageLink = nextPageLink;
return(result);
}