Dynamic Where in Linq with Entity Framework

Question

i wrote function

    private Func<CategorizedPosts, bool> CompileExpression(IEnumerable<Category> categories)
    {
        Expression predicateBody;
        ParameterExpression pe = Expression.Parameter(typeof(CategorizedPosts), "post");
        Expression left = Expression.Property(pe, typeof(CategorizedPosts).GetProperty("CATEGORY_ID"));
        Expression right = Expression.Constant(categories.ElementAt(0).ID);
        Expression equal = Expression.Equal(left, right);
        predicateBody = equal;
        for (int i = 1, j = categories.Count() - 1; i < categories.Count(); ++i )
        {
            var category = categories.ElementAt(i);
            //y => y.CATEGORY_ID == 1 || y.CATEGORY_ID == 2)
            left = Expression.Property(pe, typeof(CategorizedPosts).GetProperty("CATEGORY_ID"));
            right = Expression.Constant(category.ID);
            equal = Expression.Equal(left, right);

            predicateBody = Expression.OrElse(predicateBody, equal);
        }

        var lll = Expression.Lambda<Func<CategorizedPosts, bool>>(predicateBody, pe);
        var compiled = lll.Compile();
        return compiled;
    }

it compiles OK, but when I try to run this query

            var ctx = db.Posts.Where(x => true);
            if(predicate != null)
            {
                ctx = ctx.Where(x => x.CategorizedPosts.Where(**predicate**).Count() > 0);
            }
            IList<Post> posts = ctx.OrderByDescending(x => x.CREATION_DATE).Skip((page - 1) * perPage).Take(perPage).Select(x => new Post 
            {
                POST_ID = x.ID,
                TYPE = new Type { ID = x.TYPE_ID, NAME = x.Types.NAME },
                AUTHOR = new Author()
                {
                    ID = x.AUTHOR_ID,
                    NAME = x.Authors.NAME,

                },
                CATEGORIES = x.CategorizedPosts.Select(y => new Category() { ID = y.CATEGORY_ID, NAME = y.Categories.NAME }),
                CREATION_DATE = x.CREATION_DATE,
            }).ToList();

EF throws exception about internal error 1025 for Entity Data Provider. How can I perform this query with dynamic where?

The last thing you want to do to an EF expression is Compile(). — Henk Holterman
– Henk Holterman, Commented Sep 13, 2014 at 12:40
As Henk says, returning Func<...> won't let you apply it. Rather, return Expression<Func<...>> — Wiktor Zychla
– Wiktor Zychla, Commented Sep 13, 2014 at 19:45

Felipe Oriani · Accepted Answer · 2014-09-13 19:31:40Z

2

You could use the Contains of a collection of Ids (int) and apply it on a where, for sample:

int[] categorieIds = categories.Select(x => x.Id).ToArray();

ctx = ctx.Where(x => x.CategorizedPosts.Any(c => categorieIds .Contains(c.Id));

Some Tips

Remember the Entity Framework works with Expression<Func<T, bool>> in the Where method, not only Func<T, bool>.

You also could try to apply PredicateBuilder class which provides some extensions methods like Or, And, Not, so, you could try this:

  var predicate = PredicateBuilder.False<Product>();

  foreach (string keyword in keywords)
  {
    string temp = keyword;
    predicate = predicate.Or (p => p.Description.Contains (temp));
  }

  return dataContext.Products.Where(predicate).ToList();

edited Sep 13, 2014 at 19:31

answered Sep 13, 2014 at 12:39

Felipe Oriani

38.7k20 gold badges141 silver badges203 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic Where in Linq with Entity Framework

1 Answer 1

Some Tips

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Some Tips

Comments

Your Answer

Sign up or log in

Post as a guest

Related