private static void LogLastQuery(IEntitySession session)
{
//Debug.WriteLine("---------------------------------------------------------------------------------------");
var command = session.GetLastCommand();
//if(command != null)
// Debug.WriteLine("SQL:" + command.CommandText);
}
private void PrintLastSql(IEntitySession session)
{
//Printout SQL query that was executed
var cmd = session.GetLastCommand();
Debug.WriteLine(cmd.ToLogString());
}
private string GetLastWhere(IEntitySession session)
{
var cmd = session.GetLastCommand();
var sql = cmd.CommandText;
var wpos = sql.IndexOf("WHERE") + "Where".Length;
var where = sql.Substring(wpos + 1);
var result = where.Replace("\"", string.Empty)
.Replace(" ", string.Empty)
.Replace(Environment.NewLine, string.Empty)
.Replace(";", string.Empty)
.ToUpper();
return(result);
}
/// <summary>Executes a search query for a given WHERE condition, and returns results object which contains the resulting rows (page)
/// and total count of rows for a search condition.</summary>
/// <typeparam name="TEntity">Root entity type.</typeparam>
/// <param name="session">Entity session.</param>
/// <param name="where">Combined WHERE condition for a query.</param>
/// <param name="searchParams">Search parameters object containing extra options for the query: order by, skip, take.</param>
/// <param name="include">Include expression.</param>
/// <param name="nameMapping">A name mapping dictionary, to map names in order-by expression to actual properties of the entity.</param>
/// <param name="options">Optional, search query options.</param>
/// <returns>An instance of the <c>SearchResults</c> class, with selected rows and total count for the query condition.</returns>
public static SearchResults <TEntity> ExecuteSearch <TEntity>(
this IEntitySession session,
Expression <Func <TEntity, bool> > where,
SearchParams searchParams,
Expression <Func <TEntity, object> > include = null,
Dictionary <string, string> nameMapping = null,
QueryOptions options = QueryOptions.ForceIgnoreCase) where TEntity : class
{
var baseQuery = session.EntitySet <TEntity>().Where(where).WithOptions(options); //this will be reused by Count query
//add include, order by, skip, take
var incQuery = include == null ? baseQuery : baseQuery.Include <TEntity>(include);
var orderedQuery = string.IsNullOrEmpty(searchParams.OrderBy) ? incQuery : incQuery.OrderBy(searchParams.OrderBy, nameMapping);
// Add Skip, Take; we return '+1' rows, to check if there are any more; if not, we do not need to query total
var takePlusOne = searchParams.Take + 1;
var pageQuery = orderedQuery.Skip(searchParams.Skip).Take(takePlusOne);
var rows = pageQuery.ToList();
if (rows.Count < takePlusOne)
{
//We see the last row, we do not need to run total query
return new SearchResults <TEntity>()
{
Results = rows, TotalCount = searchParams.Skip + rows.Count
}
}
;
// we received more than Take number of rows; it means we need to run TotalCount
//save main query command, and restore it after total query; in debugging main query is more interesting than total query
var queryCmd = session.GetLastCommand();
var totalCount = baseQuery.Count(); //use baseQuery here, without OrderBy, Skip, Take
session.SetLastCommand(queryCmd); //restore main query command
rows.RemoveAt(rows.Count - 1); //remove last extra row
var results = new SearchResults <TEntity>()
{
Results = rows, TotalCount = totalCount
};
return(results);
}
private void PrintLastSql(IEntitySession session)
{
//Printout SQL query that was executed
var cmd = session.GetLastCommand();
Debug.WriteLine(cmd.ToLogString());
}