public override async Task <ActionResult <PaginationModel <ReadViewModel> > > Get()
{
var searchPostData = new SearchPostQueryModel(Request, _lang);
var getData = await Helper.SearchPosts <ReadViewModel>(searchPostData);
return(GetResponse(getData));
}
public static IQueryable <MixPost> GetSortedPost <T>(
IEnumerable <T> postIds,
MixCmsContext context,
SearchPostQueryModel searchPostData)
{
string orderColName = searchPostData.PagingData.OrderBy.Split('.')[1];
var colType = context.MixDatabaseColumn.FirstOrDefault(c => c.Name == orderColName && c.MixDatabaseName == searchPostData.PostType).DataType;
string orderCol = GetColumn(colType);
string sql = $@"
SELECT p.* FROM mix_post p
INNER JOIN (
SELECT m.ParentId, m.Specificulture, t.{orderCol}
FROM mix_database_data_association AS m
LEFT JOIN (
SELECT m0.DataId, m0.{orderCol}
FROM mix_database_data_value AS m0
WHERE ((m0.Specificulture = '{searchPostData.Specificulture}') AND (m0.MixDatabaseName = '{searchPostData.PostType}'))
AND (m0.MixDatabaseColumnName = '{orderColName}')
) AS t ON m.DataId = t.DataId
WHERE ((m.ParentType = 'Post') AND (m.MixDatabaseName = '{searchPostData.PostType}'))
AND m.ParentId IN ({string.Join(",", postIds)})) AS n ON p.Id = n.ParentId AND p.Specificulture = n.Specificulture
ORDER BY n.{orderCol} {searchPostData.PagingData.Direction}
LIMIT {searchPostData.PagingData.PageSize} OFFSET {searchPostData.PagingData.PageIndex * searchPostData.PagingData.PageSize}";
return(context.MixPost.FromSqlRaw(sql));
}
private static RepositoryResponse <PaginationModel <TView> > GetSortedPostByValue <TView>(
Expression <Func <MixPost, bool> > predicate,
SearchPostQueryModel searchPostData,
MixCmsContext context,
IDbContextTransaction transaction)
where TView : ViewModelBase <MixCmsContext, MixPost, TView>
{
var total = context.MixPost.Count(predicate);
var allPostIds = context.MixPost.Where(predicate)
.AsEnumerable()
.Select(m => m.Id);
var posts = IQueryableHelper.GetSortedPost(allPostIds, context, searchPostData).ToList();
return(new RepositoryResponse <PaginationModel <TView> >()
{
IsSucceed = true,
Data = new PaginationModel <TView>()
{
Items = DefaultRepository <MixCmsContext, MixPost, TView> .Instance.GetCachedData(posts, context, transaction),
PageSize = searchPostData.PagingData.PageSize,
PageIndex = searchPostData.PagingData.PageIndex
}
});
}
private static List <int> GetSortedIdsByPage(
IQueryable <string> allPostIds, SearchPostQueryModel searchPostData, MixCmsContext context, IDbContextTransaction transaction)
{
return(context.MixPagePost.Where(p =>
p.Specificulture == searchPostData.Specificulture &&
p.PageId == searchPostData.PageId &&
allPostIds.Contains(p.PostId.ToString()))
.OrderBy(p => p.Priority)
.Skip(searchPostData.PagingData.PageIndex * searchPostData.PagingData.PageSize)
.Take(searchPostData.PagingData.PageSize)
.Select(p => p.PostId)
.ToList());
}
public override async Task <ActionResult <PaginationModel <PostViewModel> > > Get()
{
var searchPostData = new SearchPostQueryModel(Request, _lang);
var getData = await Helper.SearchPosts <PostViewModel>(searchPostData);
if (getData.IsSucceed)
{
return(getData.Data);
}
else
{
return(BadRequest(getData.Errors));
}
}
private static Expression <Func <MixPost, bool> > BuildPostExpression(SearchPostQueryModel searchPostData)
{
Expression <Func <MixPost, bool> > predicate = model => model.Specificulture == searchPostData.Specificulture;
predicate = predicate.AndAlsoIf(searchPostData.Status.HasValue, model => model.Status == searchPostData.Status.Value);
predicate = predicate.AndAlsoIf(searchPostData.FromDate.HasValue, model => model.CreatedDateTime >= searchPostData.FromDate.Value);
predicate = predicate.AndAlsoIf(searchPostData.ToDate.HasValue, model => model.CreatedDateTime <= searchPostData.ToDate.Value);
predicate = predicate.AndAlsoIf(!string.IsNullOrEmpty(searchPostData.PostType), model => model.Type == searchPostData.PostType);
predicate = predicate.AndAlsoIf(!string.IsNullOrEmpty(searchPostData.Keyword), model =>
(EF.Functions.Like(model.Title, $"%{searchPostData.Keyword}%")) ||
(EF.Functions.Like(model.Excerpt, $"%{searchPostData.Keyword}%")) ||
(EF.Functions.Like(model.Content, $"%{searchPostData.Keyword}%")));
return(predicate);
}
private static async Task <RepositoryResponse <PaginationModel <TView> > > SearchPostByValue <TView>(
Expression <Func <MixDatabaseDataValue, bool> > valPredicate,
SearchPostQueryModel searchPostData,
MixCmsContext context,
IDbContextTransaction transaction)
where TView : ViewModelBase <MixCmsContext, MixPost, TView>
{
var allPostIds = IQueryableHelper.GetPostIdsByValue(
valPredicate,
context,
searchPostData.Specificulture,
searchPostData.PostType);
var resultIds = searchPostData.PagingData.OrderBy.StartsWith("additionalData.")
? GetSortedIdsByValue(allPostIds, context, searchPostData.PagingData, searchPostData.Specificulture, searchPostData.PostType)
: searchPostData.PageId.HasValue
? GetSortedIdsByPage(allPostIds, searchPostData, context, transaction)
: allPostIds.Skip(searchPostData.PagingData.PageIndex * searchPostData.PagingData.PageSize)
.Take(searchPostData.PagingData.PageSize)
.Select(p => int.Parse(p)).ToList();
var getPosts = (await DefaultRepository <MixCmsContext, MixPost, TView> .Instance.GetModelListByAsync(
m => resultIds.Any(p => p == m.Id) && m.Specificulture == searchPostData.Specificulture,
context,
transaction));
var items = getPosts.Data.OrderBy(
m => resultIds.IndexOf((int)ReflectionHelper.GetPropertyValue(m, "Id"))).ToList();
var total = allPostIds.Count();
var result = new RepositoryResponse <PaginationModel <TView> >()
{
IsSucceed = true,
Data = new PaginationModel <TView>()
{
Items = items,
PageIndex = searchPostData.PagingData.PageIndex,
PageSize = searchPostData.PagingData.PageSize,
TotalItems = total,
TotalPage = (int)Math.Ceiling((double)total / searchPostData.PagingData.PageSize)
}
};
return(result);
}
private static Expression <Func <MixDatabaseDataValue, bool> > GetCategoryPredicate(
SearchPostQueryModel searchPostData)
{
Expression <Func <MixDatabaseDataValue, bool> > catePredicate = null;
if (searchPostData.Categories.Count > 0)
{
foreach (var cate in searchPostData.Categories)
{
catePredicate = catePredicate.OrIf(
!string.IsNullOrEmpty(cate),
Expressions.GetMetaExpression(MixDatabaseNames.SYSTEM_CATEGORY, cate, searchPostData.Specificulture));
}
}
catePredicate = catePredicate.OrIf(
!string.IsNullOrEmpty(searchPostData.Category),
Expressions.GetMetaExpression(MixDatabaseNames.SYSTEM_CATEGORY, searchPostData.Category, searchPostData.Specificulture));
return(catePredicate);
}
public static async Task <RepositoryResponse <PaginationModel <TView> > > GetModelistByMeta <TView>(
string metaName,
string metaValue,
string postType,
PagingRequest pagingData,
string culture = null,
MixCmsContext _context = null,
IDbContextTransaction _transaction = null)
where TView : ViewModelBase <MixCmsContext, MixPost, TView>
{
UnitOfWorkHelper <MixCmsContext> .InitTransaction(_context, _transaction, out MixCmsContext context, out IDbContextTransaction transaction, out bool isRoot);
try
{
culture ??= MixService.GetAppSetting <string>(MixAppSettingKeywords.DefaultCulture);
postType ??= MixDatabaseNames.ADDITIONAL_COLUMN_POST;
var valExp = Expressions.GetMetaExpression(metaName, metaValue, culture);
var searchPostData = new SearchPostQueryModel()
{
PagingData = pagingData,
PostType = postType,
Specificulture = culture
};
return(await SearchPostByValue <TView>(valExp, searchPostData, context, transaction));
}
catch (Exception ex)
{
return(UnitOfWorkHelper <MixCmsContext> .HandleException <PaginationModel <TView> >(ex, isRoot, transaction));
}
finally
{
if (isRoot)
{
//if current Context is Root
UnitOfWorkHelper <MixCmsContext> .CloseDbContext(ref context, ref transaction);
}
}
}
public static async Task <RepositoryResponse <PaginationModel <TView> > > SearchPosts <TView>(
SearchPostQueryModel searchPostData,
MixCmsContext _context = null,
IDbContextTransaction _transaction = null)
where TView : ViewModelBase <MixCmsContext, MixPost, TView>
{
UnitOfWorkHelper <MixCmsContext> .InitTransaction(_context, _transaction, out MixCmsContext context, out IDbContextTransaction transaction, out bool isRoot);
try
{
Expression <Func <MixDatabaseDataValue, bool> > valPredicate = null;
valPredicate = valPredicate.AndAlsoIf(
!string.IsNullOrEmpty(searchPostData.Category),
Expressions.GetMetaExpression(MixDatabaseNames.SYSTEM_CATEGORY, searchPostData.Category, searchPostData.Specificulture));
valPredicate = valPredicate.AndAlsoIf(
!string.IsNullOrEmpty(searchPostData.Tag),
Expressions.GetMetaExpression(MixDatabaseNames.SYSTEM_TAG, searchPostData.Tag, searchPostData.Specificulture));
if (valPredicate != null)
{
return(await SearchPostByValue <TView>(valPredicate, searchPostData.PagingData, searchPostData.PostType, searchPostData.Specificulture, context, transaction));
}
else
{
Expression <Func <MixPost, bool> > predicate = BuildPostExpression(searchPostData);
if (searchPostData.PagingData.OrderBy.StartsWith("additionalData."))
{
var total = context.MixPost.Count(predicate);
var allPostIds = context.MixPost.Where(predicate)
.AsEnumerable()
.Select(m => m.Id);
var posts = IQueryableHelper.GetSortedPost(allPostIds, context, searchPostData).ToList();
return(new RepositoryResponse <PaginationModel <TView> >()
{
IsSucceed = true,
Data = new PaginationModel <TView>()
{
Items = DefaultRepository <MixCmsContext, MixPost, TView> .Instance.GetCachedData(posts, context, transaction),
PageSize = searchPostData.PagingData.PageSize,
PageIndex = searchPostData.PagingData.PageIndex
}
});
}
return(await DefaultRepository <MixCmsContext, MixPost, TView> .Instance.GetModelListByAsync(
predicate,
searchPostData.PagingData.OrderBy,
searchPostData.PagingData.Direction,
searchPostData.PagingData.PageSize,
searchPostData.PagingData.PageIndex,
null, null,
context,
transaction));
}
}
catch (Exception ex)
{
return(UnitOfWorkHelper <MixCmsContext> .HandleException <PaginationModel <TView> >(ex, isRoot, transaction));
}
finally
{
if (isRoot)
{
//if current Context is Root
UnitOfWorkHelper <MixCmsContext> .CloseDbContext(ref context, ref transaction);
}
}
}
private static RepositoryResponse <PaginationModel <TView> > GetSortedPostByPage <TView>(Expression <Func <MixPost, bool> > predicate, SearchPostQueryModel searchPostData, MixCmsContext context, IDbContextTransaction transaction) where TView : ViewModelBase <MixCmsContext, MixPost, TView>
{
var allPostIds = context.MixPost.Where(predicate)
.AsEnumerable()
.Select(m => m.Id);
var allPosts =
context.MixPagePost.Where(p =>
p.Specificulture == searchPostData.Specificulture &&
p.PageId == searchPostData.PageId &&
allPostIds.Contains(p.PostId))
.OrderBy(p => p.Priority);
var posts = allPosts
.Skip(searchPostData.PagingData.PageIndex * searchPostData.PagingData.PageSize)
.Take(searchPostData.PagingData.PageSize)
.Include(m => m.MixPost)
.Select(m => m.MixPost)
.ToList();
var total = allPosts.Count();
var totalPage = (int)Math.Ceiling((double)total / searchPostData.PagingData.PageSize);
return(new RepositoryResponse <PaginationModel <TView> >()
{
IsSucceed = true,
Data = new PaginationModel <TView>()
{
Items = DefaultRepository <MixCmsContext, MixPost, TView> .Instance.GetCachedData(posts, context, transaction),
PageSize = searchPostData.PagingData.PageSize,
PageIndex = searchPostData.PagingData.PageIndex,
TotalItems = total,
TotalPage = totalPage
}
});
}
public static async Task <RepositoryResponse <PaginationModel <TView> > > SearchPosts <TView>(
SearchPostQueryModel searchPostData,
MixCmsContext _context = null,
IDbContextTransaction _transaction = null)
where TView : ViewModelBase <MixCmsContext, MixPost, TView>
{
UnitOfWorkHelper <MixCmsContext> .InitTransaction(_context, _transaction, out MixCmsContext context, out IDbContextTransaction transaction, out bool isRoot);
try
{
Expression <Func <MixDatabaseDataValue, bool> > valPredicate = null;
Expression <Func <MixDatabaseDataValue, bool> > catePredicate = GetCategoryPredicate(searchPostData);
Expression <Func <MixDatabaseDataValue, bool> > tagPredicate = GetTagPredicate(searchPostData);
Expression <Func <MixDatabaseDataValue, bool> > queryPredicate = GetQueryPredicate(searchPostData);
valPredicate = valPredicate
.AndAlsoIf(queryPredicate != null, queryPredicate)
.AndAlsoIf(catePredicate != null, catePredicate)
.AndAlsoIf(tagPredicate != null, tagPredicate);
if (valPredicate != null)
{
return(await SearchPostByValue <TView>(valPredicate, searchPostData, context, transaction));
}
else
{
Expression <Func <MixPost, bool> > predicate = BuildPostExpression(searchPostData);
if (searchPostData.PagingData.OrderBy.StartsWith("additionalData."))
{
return(GetSortedPostByValue <TView>(predicate, searchPostData, context, transaction));
}
if (searchPostData.PageId.HasValue)
{
return(GetSortedPostByPage <TView>(predicate, searchPostData, context, transaction));
}
return(await DefaultRepository <MixCmsContext, MixPost, TView> .Instance.GetModelListByAsync(
predicate,
searchPostData.PagingData.OrderBy,
searchPostData.PagingData.Direction,
searchPostData.PagingData.PageSize,
searchPostData.PagingData.PageIndex,
null, null,
context,
transaction));
}
}
catch (Exception ex)
{
return(UnitOfWorkHelper <MixCmsContext> .HandleException <PaginationModel <TView> >(ex, isRoot, transaction));
}
finally
{
if (isRoot)
{
//if current Context is Root
UnitOfWorkHelper <MixCmsContext> .CloseDbContext(ref context, ref transaction);
}
}
}
private static Expression <Func <MixDatabaseDataValue, bool> > GetTagPredicate(SearchPostQueryModel searchPostData)
{
Expression <Func <MixDatabaseDataValue, bool> > tagPredicate = null;
tagPredicate = tagPredicate.AndAlsoIf(
!string.IsNullOrEmpty(searchPostData.Tag),
Expressions.GetMetaExpression(MixDatabaseNames.SYSTEM_TAG, searchPostData.Tag, searchPostData.Specificulture));
return(tagPredicate);
}
private static Expression <Func <MixDatabaseDataValue, bool> > GetQueryPredicate(SearchPostQueryModel searchPostData)
{
Expression <Func <MixDatabaseDataValue, bool> > queryPredicate = null;
if (searchPostData.Query != null)
{
foreach (JProperty prop in searchPostData.Query.Properties())
{
string val = searchPostData.Query.Value <string>(prop.Name);
string name = prop.Name;
queryPredicate = queryPredicate.AndAlsoIf(
!string.IsNullOrEmpty(val),
Expressions.GetMetaExpression(searchPostData.PostType, val, searchPostData.Specificulture, name));
}
}
return(queryPredicate);
}
