Creating a dynamic query using IQueryable

Question

I'm trying to iterate for over an string array and dynamically create a IQueryable query. Its pretty straightforward but here's where I'm stuck

var query = context.QuestionsMetaDatas.AsQueryable();

var keywords=new List<string>(){ "Test1","Test2" };

foreach(var key in keywords)
{
   query=query.Where(a=>a.Text.Contains(key));
}

Now the problem is that when the query gets generated its compiles to

select * from QuestionsMetaDatas where Text Like "Test1" AND Text Like "Test2"

Instead of AND I wanted the query to generate OR...Now how can I achieve this?

Tommy Grovnes · Accepted Answer · 2013-09-12 20:32:16Z

6

I have used predicate builder like Raphael suggested, it's just one file to include in your project, then your example becomes:

var keywords=new List<string>(){ "Test1","Test2" };

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

foreach (var key in keywords)
{
  predicate = predicate.Or (a => a.Text.Contains (key));
}

var query = context.QuestionsMetaDatas.AsQueryable().Where(predicate);

Generating the OR query your are looking for.

answered Sep 12, 2013 at 20:32

Tommy Grovnes

4,1562 gold badges28 silver badges40 bronze badges

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

Comments

Nilesh · Accepted Answer · 2013-09-12 12:23:50Z

5

Have you tried contains the other way?

var keywords=new List<int>(){ "Test1","Test2" };
query=query.Where(a=>keywords.Contains(a));

this is like an IN clause

answered Sep 12, 2013 at 12:23

Nilesh

2,7015 gold badges23 silver badges35 bronze badges

2 Comments

Brian_Noronha Over a year ago

I was looking to generate a query like this "select * from QuestionsMetaDatas where Text Like 'Test1' OR Text Like 'Test2'". Since i was doing a string comparison. I wanted the OR operator instead of the AND

Nilesh Over a year ago

Try using LINQkit; you can build dynamic query expressions. You can get it through Nuget package manager, follow this link

Raphaël Althaus · Accepted Answer · 2013-09-12 15:27:56Z

you could look at predicate builder, or build your own expression (here a possble solution with a static extension method on IQueryable<QuestionsMetadatas> )

public static IQueryable<QuestionsMetaDatas> FilterText(this IQueryable<QuestionsMetaDatas> queryable, IEnumerable<string> keywords)
        {
            var entityType = typeof(QuestionsMetaDatas);
            var parameter = Expression.Parameter(entityType, "a");
            var containsMethod = typeof(string).GetMethod("Contains"
                                                           , new[] { typeof(string) });
            var propertyExpression = Expression.Property(parameter, "Text");
            Expression body = Expression.Constant(false);
            foreach (var keyword in keywords)
            {
                var innerExpression = Expression.Call(propertyExpression, containsMethod, Expression.Constant(keyword));
                body = Expression.OrElse(body, innerExpression);
            }
            var lambda = Expression.Lambda<Func<QuestionsMetaDatas, bool>>(body, new[] { parameter });
            return queryable.Where(lambda);

        }

where lambda will look like that :

a => ((False OrElse a.Text.Contains("firstKeyWord")) OrElse a.Text.Contains("secondKeyWord"))

and usage is

var query = context.QuestionsMetaDatas.AsQueryable();

var keywords=new List<string>(){ "Test1","Test2" };

query = query.FilterText(keywords);

or shorter

var query = context.QuestionsMetaDatas.AsQueryable().FilterText(new[]{"Test1", "Test2"});

Collectives™ on Stack Overflow

Creating a dynamic query using IQueryable

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related