Entity, Contains or intersect, is this query possible?

Question

I have a list of string retreived this way :

List<string> keyWords = db.MotCleRecherche.Select(t => t.MotClé).ToList();

I also have a query that takes many parameters to be executed :

object = db.DAapp.Where(t => t.CODE_ART.StartsWith(s) && t.DATE_CREAT >= debut && t.DATE_CREAT < fin).ToList()

now... I want to add this kind of condition :

  db.DAapp.Where(t => t.DESC_ART.ToLower().Contains(keywords.ToLower()))

or

  db.DAapp.Where(t => t.DESC_ART.ToLower().Intersect(keywords.ToLower()))

I guess you could see it comming... I can't figure how to really make this work... all i know is considering a list X filed and Y list filled:

X.Intersect(Y).Any()

will return true if there is something equal... but DESC_ART is just ONE long string and i want to know if some of my keywords are in there

You probably can just do this: `db.DAapp.Where(t => keyWords.Contains(t.DESC_ART.ToLower())` ... and you'll want to `ToLower()` your entire keywords first `db.MotCleRechercher.Select(t => t.MotCle.ToLower()).ToList()` — stephen.vakil, Sep 28 '16 at 20:46
@stephen.vakil Keywords list put to lower from the go is a nice thing, thanks, but there no way that keyword would contains desc_art, desc_art is like 500 car long and most of my keywords have 5 or 6 letters... — Antoine Pelletier, Sep 28 '16 at 20:51
I see - you want to search for any `DESC_ART` that contains any of the specified keywords? So something like `t => keyWords.Any(k => t.DESC_ART.Contains(k))`? — stephen.vakil, Sep 28 '16 at 20:54

score 1 · Accepted Answer · answered Sep 28 '16 at 20:57

1

I agree with Stephen that you should cast the keyWords to lower first before comparing. But if you really need to do this with linq you can do something like this.

var result =  db.DAapp.Where(t => keywords.Any(keyword=> string.Equals(keyword,t.DESC_ART, StringComparison.InvariantCultureIgnoreCase )));

This will cause a to lower to get called on each string every iteration of your linq loop so its expensive.

answered Sep 28 '16 at 20:57

ILikeCamelCase

61
2

Will that work when it hits the DB? I thought that it wouldn't work as expected based on some [really old](http://stackoverflow.com/questions/5080727/string-equals-not-working-as-intended) SO answers. But maybe something changed in 5 years? :) – stephen.vakil Sep 28 '16 at 21:00
1

Yea I forgot about that. And no it was not fixed. Just get admin access to your db and edit the table! See, easy! – ILikeCamelCase Sep 28 '16 at 21:07

score -1 · Answer 2 · edited May 23 '17 at 12:33

First add this to your project (for example to your controller):

      static Expression<Func<T, bool>> AnyOf<T>(
      params Expression<Func<T, bool>>[] expressions)
    {
        if (expressions == null || expressions.Length == 0) return x => false;
        if (expressions.Length == 1) return expressions[0];

        var body = expressions[0].Body;
        var param = expressions[0].Parameters.Single();
        for (int i = 1; i < expressions.Length; i++)
        {
            var expr = expressions[i];
            var swappedParam = new SwapVisitor(expr.Parameters.Single(), param)
                                .Visit(expr.Body);
            body = Expression.OrElse(body, swappedParam);
        }
        return Expression.Lambda<Func<T, bool>>(body, param);
    }
    class SwapVisitor : ExpressionVisitor
    {
        private readonly Expression from, to;
        public SwapVisitor(Expression from, Expression to)
        {
            this.from = from;
            this.to = to;
        }
        public override Expression Visit(Expression node)
        {
            return node == from ? to : base.Visit(node);
        }
    }

I find this from stackoverflow. now you can create desired query as below :

        var filters = new List<Expression<Func<Models.DAapp, bool>>>();
        foreach (var st in keyWords)
            filters.Add(d => d.DESC_ART.ToLower().Contains(st.ToLower()));

        var lambda = AnyOf(filters.ToArray());

        var q = db.DAapp.Where(t =>
            t.CODE_ART.StartsWith(s)
            && t.DATE_CREAT >= debut
                && t.DATE_CREAT < fin
                           );

        q = q.Where(lambda);

        var res = q.ToList();

Please be noticed that, this solution creates only one select query with multiple where expressions. which is more efficient that other solutions like below that contains multiple select queries inside where clause :

var q = db.DAapp.Where(t =>
            t.CODE_ART.StartsWith(s)
            && t.DATE_CREAT >= debut
                && t.DATE_CREAT < fin
                && keyWords.Any(k => t.DESC_ART.ToLower().Contains(k.ToLower()))
                           );

Entity, Contains or intersect, is this query possible?

2 Answers2