public IList <DataHistoryEntry> GetEntityHistory(IEntitySession session, Type entityType, object primaryKey,
DateTime?fromDate = null, DateTime?tillDate = null, int skip = 0, int take = 100, Guid?userId = null)
{
var entInfo = session.Context.App.Model.GetEntityInfo(entityType);
Util.Check(entInfo != null, "Type {0} is not registered as an entity.", entityType);
var entName = entInfo.FullName;
var entNameHash = Util.StableHash(entName);
var where = session.NewPredicate <IDataHistory>()
.And(h => h.EntityNameHash == entNameHash && h.EntityName == entName)
.AndIfNotEmpty(fromDate, h => h.CreatedOn > fromDate.Value)
.AndIfNotEmpty(tillDate, h => h.CreatedOn < tillDate.Value);
if (primaryKey != null)
{
var pkStr = PrimaryKeyAsString(primaryKey);
var pkHash = Util.StableHash(pkStr);
where = where.And(h => h.EntityPrimaryKeyHash == pkHash && h.EntityPrimaryKey == pkStr);
}
var query = session.EntitySet <IDataHistory>().Where(where).OrderByDescending(h => h.CreatedOn).Skip(skip).Take(take);
var histEntList = query.ToList();
var result = histEntList.Select(h => h.ToHistoryEntry(entInfo)).ToList();
return(result);
}
public ITextTemplate GetTemplate(IEntitySession session, string templateName, string culture = "EN-US", Guid? ownerId = null)
{
var where = session.NewPredicate<ITextTemplate>()
.And(t => t.Name == templateName)
.AndIfNotEmpty(culture, t => t.Culture == culture)
.AndIfNotEmpty(ownerId, t => t.OwnerId == ownerId.Value);
var template = session.EntitySet<ITextTemplate>().Where(where).FirstOrDefault();
return template;
}
public ITextTemplate GetTemplate(IEntitySession session, string templateName, string culture = "EN-US", Guid?ownerId = null)
{
var where = session.NewPredicate <ITextTemplate>()
.And(t => t.Name == templateName)
.AndIfNotEmpty(culture, t => t.Culture == culture)
.AndIfNotEmpty(ownerId, t => t.OwnerId == ownerId.Value);
var template = session.EntitySet <ITextTemplate>().Where(where).FirstOrDefault();
return(template);
}
public IList<DataHistoryEntry> GetEntityHistory(IEntitySession session, Type entityType, object primaryKey,
DateTime? fromDate = null, DateTime? tillDate = null, int skip = 0, int take = 100, Guid? userId = null)
{
var entInfo = session.Context.App.Model.GetEntityInfo(entityType);
Util.Check(entInfo != null, "Type {0} is not registered as an entity.", entityType);
var entName = entInfo.FullName;
var entNameHash = Util.StableHash(entName);
var where = session.NewPredicate<IDataHistory>()
.And(h => h.EntityNameHash == entNameHash && h.EntityName == entName)
.AndIfNotEmpty(fromDate, h => h.CreatedOn > fromDate.Value)
.AndIfNotEmpty(tillDate, h => h.CreatedOn < tillDate.Value);
if (primaryKey != null) {
var pkStr = PrimaryKeyAsString(primaryKey);
var pkHash = Util.StableHash(pkStr);
where = where.And(h => h.EntityPrimaryKeyHash == pkHash && h.EntityPrimaryKey == pkStr);
}
var query = session.EntitySet<IDataHistory>().Where(where).OrderByDescending(h => h.CreatedOn).Skip(skip).Take(take);
var histEntList = query.ToList();
var result = histEntList.Select(h => h.ToHistoryEntry(entInfo)).ToList();
return result;
}
public static SearchResults <Book> SearchBooks(this IEntitySession session, BookSearch searchParams)
{
// Warning about substring match (LIKE): Be careful using it in real apps, against big tables
// Match by fragment results in LIKE operator which NEVER works on real volumes.
// For MS SQL, it is OK to do LIKE with pattern that does not start with % (so it is StartsWith(smth) operator).
// AND column must be indexed - so server will use index. For match inside the string, LIKE is useless on big tables.
// In our case, Title is indexed and we use StartsWith, so it's OK
// An interesting article about speeding up string-match search in MS SQL:
// http://aboutsqlserver.com/2015/01/20/optimizing-substring-search-performance-in-sql-server/
var categories = ConvertHelper.ParseEnumArray <BookCategory>(searchParams.Categories);
var where = session.NewPredicate <IBook>()
.AndIfNotEmpty(searchParams.Title, b => b.Title.StartsWith(searchParams.Title))
.AndIfNotEmpty(searchParams.MaxPrice, b => b.Price <= (Decimal)searchParams.MaxPrice.Value)
.AndIfNotEmpty(searchParams.Publisher, b => b.Publisher.Name.StartsWith(searchParams.Publisher))
.AndIfNotEmpty(searchParams.PublishedAfter, b => b.PublishedOn.Value >= searchParams.PublishedAfter.Value)
.AndIfNotEmpty(searchParams.PublishedBefore, b => b.PublishedOn.Value <= searchParams.PublishedBefore.Value)
.AndIf(categories != null && categories.Length > 0, b => categories.Contains(b.Category));
// A bit more complex clause for Author - it is many2many, results in subquery
if (!string.IsNullOrEmpty(searchParams.AuthorLastName))
{
var qAuthBookIds = session.EntitySet <IBookAuthor>()
.Where(ba => ba.Author.LastName.StartsWith(searchParams.AuthorLastName))
.Select(ba => ba.Book.Id);
where = where.And(b => qAuthBookIds.Contains(b.Id));
}
// Alternative method for author name - results in inefficient query (with subquery for every row)
// if(!string.IsNullOrEmpty(authorLastName))
// where = where.And(b => b.Authors.Any(a => a.LastName == authorLastName));
//Use VITA-defined helper method ExecuteSearch - to build query from where predicate, get total count,
// add clauses for OrderBy, Take, Skip, run query and convert to list of model objects with TotalCount
var results = session.ExecuteSearch(where, searchParams, ibook => ibook.ToModel(), b => b.Publisher,
nameMapping: _orderByMapping);
return(results);
}