private static Expression <Func <Post, bool> > ExpressionBuilder(PostCriteria criteria) { // Start with our base expression - we want all posts //Expression<Func<Post, bool>> criteriaExpression = PredicateBuilder.True<Post>(); var criteriaExpression = PredicateBuilder.True <Post>(); if (criteria == null) { return(criteriaExpression); } // If a title has been specified in the filter, add an expression to include // any Post where the Title contains the value specified in the filter if (!String.IsNullOrEmpty(criteria.Title)) { criteriaExpression = criteriaExpression.And(post => post.Title.Contains(criteria.Title)); } // If a 'Before' date is specified, add an expression to include posts which have // a PublishedDate less than the date specified if (criteria.BeforeDate.HasValue) { criteriaExpression = criteriaExpression.And(post => post.PublishDate.CompareTo(criteria.BeforeDate.Value) < 0); } // If an 'After' date is specified, add an expression to include posts which have // a PublishedDate greater than the date specified if (criteria.AfterDate.HasValue) { criteriaExpression = criteriaExpression.And(post => post.PublishDate.CompareTo(criteria.AfterDate.Value) >= 0); } return(criteriaExpression); }
/// <summary> /// Override of Retrieve that builds an explicit SQL query to do the work instead of have EF do it /// </summary> /// <param name="criteria">The post criteria</param> /// <param name="orderBy">The criteria for ordering the results</param> /// <returns>All of the Posts in the database which satisfy the criteria</returns> /// <remarks> /// This is an example to demonstrate replacing an EF-based method with a custom method. /// Because the Order class and the custom criteria class provide enough information, /// EF's generated query can be completely replaced with something else. Here it's a SQL /// statement that we cobble together; it could just as easily be a stored procedure or a query /// against a NoSQL store of some kind. You're not locked into EF or SQL Server in any way. /// </remarks> public override IEnumerable <Post> Retrieve(PostCriteria criteria = null, params Order <Post>[] orderBy) { string whereClause = string.Empty; var parameters = new List <DbParameter>(); string orderByClause = string.Empty; if (criteria != null) { if (!string.IsNullOrEmpty(criteria.Title)) { if (whereClause.Length > 0) { whereClause += " and "; } whereClause += " title = @title"; var param = new SqlParameter("title", criteria.Title); parameters.Add(param); } if (criteria.AfterDate.HasValue) { if (whereClause.Length > 0) { whereClause += " and "; } whereClause += " publishdate >= @afterDate"; var param = new SqlParameter("afterDate", criteria.AfterDate.Value); parameters.Add(param); } if (criteria.BeforeDate.HasValue) { if (whereClause.Length > 0) { whereClause += " and "; } whereClause += " publishdate < @beforeDate"; var param = new SqlParameter("beforeDate", criteria.BeforeDate.Value); parameters.Add(param); } } foreach (var o in orderBy) { if (orderByClause.Length > 0) { orderByClause += ", "; } else { orderByClause += "order by "; } // For the love of all that's holy, if you implement something like this // in the real world make sure you sanitize this 'order by' clause that you're // building to avoid SQL injection orderByClause += o.PropertyName + (o.Descending ? " desc" : " asc"); } if (whereClause.Length > 0) { whereClause = "where " + whereClause; } string command = string.Format("select * from posts {0} {1}", whereClause, orderByClause); return(_context.Database.SqlQuery <Post>(command, parameters.Cast <object>().ToArray())); }
public IEnumerable <Post> Retrieve(int pageSize, int pageIndex, out int virtualCount, PostCriteria criteria = null, params Order <Post>[] orderBy) { return(_pagedRepository.Retrieve(pageSize, pageIndex, out virtualCount, criteria, orderBy)); }
public virtual IEnumerable <Post> Retrieve(PostCriteria criteria = null, params Order <Post>[] orderBy) { return(_matchingRepository.Retrieve(criteria, orderBy)); }