none
Issue with contains clause in LINQ query, not giving expected search result. RRS feed

  • Question

  • Hi All,<o:p></o:p>

    We are working with entity framework 6.0 object repository pattern using code first approach. We have used contains clause of LINQ to get expected search result, our assumption was it will behave as like operator in SQL statement but it did exact match.<o:p></o:p>

    We have this SAMPLETABLE data where column “CONSTITUENCIES” has data separated with pipes. <o:p></o:p>

    CONSTITUENCIES

    BVALE|CONS4

    BVALE|CONS1|CONS3|CONS4

    BVALE

    BVALE|CONS1

    BVALE|CONS4|RANAC

    BVALE|CONS1|CONS2

    BVALE|CONS1|RANAC

    CONS5

    CONS2

    RANAC

    BVALE|CONS1|CONS2|RANAC

    CONS1

    BVALE|ENGC|CONS1

    ENGC


    Now as per my requirement I have to pass multiple values to match this data. Here is my code:<o:p></o:p>


    var query = esRepository.Context.SAMPLETABLE.AsExpandable();<o:p></o:p>

    var constituencyList = new List<string>() { "CONS1", "BVALE","RANAC" };<o:p></o:p>

    query = query.Where(x => constituencyList.Contains(x.CONSTITUENCIES));<o:p></o:p>

    This results into below query:<o:p></o:p>

     SELECT  "Extent1"."CONSTITUENCIES" AS "CONSTITUENCIES"FROM "LAND"."SAMPLETABLE" "Extent1"WHERE (('CONS1' = "Extent1"."CONSTITUENCIES") OR ('BVALE' = "Extent1"."CONSTITUENCIES") OR ('RANAC' = "Extent1"."CONSTITUENCIES")) AND ("Extent1"."CONSTITUENCIES" IS NOT NULL)

    The output of this statement gives <o:p></o:p>

    CONSTITUENCIES           

    BVALE  

    RANAC 

    CONS1


    <o:p> </o:p>

    Expected query:<o:p></o:p>

    SELECT  "Extent1"."CONSTITUENCIES" AS "CONSTITUENCIES"FROM "LAND"."SAMPLETABLE" "Extent1"WHERE (("Extent1"."CONSTITUENCIES" LIKE '%CONS1%') OR ("Extent1"."CONSTITUENCIES" LIKE '%BVALE%') OR ("Extent1"."CONSTITUENCIES" LIKE '%RANAC%')) AND ("Extent1"."CONSTITUENCIES" IS NOT NULL)

    Expected Result <o:p></o:p>

    CONSTITUENCIES

    BVALE

    BVALE|CONS1

    BVALE|CONS1|CONS2

    BVALE|CONS1|CONS2|RANAC

    BVALE|CONS1|CONS3|CONS4

    BVALE|CONS1|RANAC

    BVALE|CONS4

    BVALE|CONS4|RANAC

    BVALE|ENGC|CONS1

    CONS1

    RANAC


    Could someone help me to change the below statement to get above result<o:p></o:p>

    query = query.Where(x => constituenc yList.Contains(x.CONSTITUENCIES));<o:p></o:p>


    • Moved by CoolDadTx Tuesday, February 6, 2018 3:31 PM EF related
    Tuesday, February 6, 2018 11:37 AM

All replies

  • Hi All,<o:p></o:p>

    We are working with entity framework 6.0 object repository pattern using code first approach. We have used contains clause of LINQ to get expected search result, our assumption was it will behave as like operator in SQL statement but it did exact match.<o:p></o:p>



    Yea that is a misunderstanding.

    Linq is much more consistent in its way it uses its expression. SQL is based on USA streetwise style plain English, it was originally meant for end users but completely failed in that.

    Linq is based on that what developers  do in a less coded way. 

    Try to focus yourself on Linq and not on SQL, that words are Select, Join and Like are used is because that are the words in English all over the word for the actions and operators.


    Success Cor


    Tuesday, February 6, 2018 11:53 AM
  • You would have to use dynamic Linq, dynamic t-sql using EF, a Sproc using EF or dynamic  Entity-SQL using EF, IMO.
    Tuesday, February 6, 2018 10:28 PM
  • Hi NKamble,

    We could build a predicate incrementally to achieve it.

    var constituencyList = new List<string>() { "CONS1", "BVALE", "RANAC" };
    
    Func<SAMPLETABLE, bool> predicate = p => false;
    foreach (var item in constituencyList)
    {
        var oldPredicate = predicate;
        predicate = p => oldPredicate(p) || p.CONSTITUENCIES.Contains(item);
    }
    
    query = query.Where(predicate);

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 7, 2018 2:45 AM
    Moderator
  • Hi Zhanglong,

    I tried but it gave me null exception at

    predicate = p => oldPredicate(p) || p.CONSTITUENCIES.Contains(item)

    becasue p is assigned as false and doesn't have any reference for CONSTITUENCIES


    • Edited by NKamble Wednesday, February 7, 2018 9:41 AM
    Wednesday, February 7, 2018 9:40 AM
  • Hi NKamble,

    Please check the following line of code, p is SAMPLETABLE, please modify SAMPLETABLE as your model class name.

    Func<SAMPLETABLE, bool> predicate = p => false;

    And the following simple console sample for your reference.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    
    namespace ConsoleApp1
    {
        class Program
        {
            static void Main(string[] args)
            {
                List<SAMPLETABLE> query = new List<SAMPLETABLE>() {
                   new SAMPLETABLE() { CONSTITUENCIES = "BVALE|CONS4" },
                   new SAMPLETABLE() { CONSTITUENCIES =  "BVALE|CONS1|CONS3|CONS4"},
                   new SAMPLETABLE() { CONSTITUENCIES =  "BVALE" },
                   new SAMPLETABLE() { CONSTITUENCIES =  "BVALE|CONS1"},
                   new SAMPLETABLE() { CONSTITUENCIES =  "BVALE|CONS4|RANAC"},
                   new SAMPLETABLE() { CONSTITUENCIES =  "BVALE|CONS1|CONS2"},
                   new SAMPLETABLE() { CONSTITUENCIES =  "BVALE|CONS1|RANAC" },
                   new SAMPLETABLE() { CONSTITUENCIES =  "CONS5" },
                   new SAMPLETABLE() { CONSTITUENCIES =  "CONS2" },
                   new SAMPLETABLE() { CONSTITUENCIES =  "RANAC" },
                   new SAMPLETABLE() { CONSTITUENCIES = "BVALE|CONS1|CONS2|RANAC" },
                   new SAMPLETABLE() { CONSTITUENCIES =  "CONS1" },
                   new SAMPLETABLE() { CONSTITUENCIES =  "BVALE|ENGC|CONS1" },
                   new SAMPLETABLE() { CONSTITUENCIES =  "ENGC"}
                };
                var constituencyList = new List<string>() { "CONS1", "BVALE", "RANAC" };
    
                Func<SAMPLETABLE, bool> predicate = p => false;
                foreach (var item in constituencyList)
                {
                    var oldPredicate = predicate;
                    predicate = p => oldPredicate(p) || p.CONSTITUENCIES.Contains(item);
                }
    
                query = query.Where(predicate).ToList();
           
                foreach (var item in query)
                {
                    Console.WriteLine(item.CONSTITUENCIES);
                }
                Console.ReadLine();
            }
        }
    
        public class SAMPLETABLE
        {
            public string CONSTITUENCIES { get; set; }
        }
        
    }
    

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, February 7, 2018 9:46 AM
    Moderator
  • Thanks Zhanglong. The latest solution helped to get the desired output; but this fails when we play around with iqueryable.

    The predicate code deals with ienumerable object which means the query is executed and further appying the filter and we got other filter criteria added in iqueryable already.

    So this criteria needs to be part of this iqueryable to fetch data in one hit.

    Friday, February 9, 2018 10:55 AM
  • Hi NKamble,

    >>So this criteria needs to be part of this iqueryable to fetch data in one hit.

    If you want to fetch data in one hit, you could use Raw SQL Queries, like this:

    using (var db = new EFDemoContext())
    {
        var query = db.Database.SqlQuery<string>(@"yoursqlstatement");
    }
    
    For more information, please refer to:

    https://msdn.microsoft.com/en-us/library/jj592907%28v=vs.113%29.aspx?f=255&MSPPError=-2147217396

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 12, 2018 7:23 AM
    Moderator
  • Hi,

    Can you please suggest a solution if  <g class="gr_ gr_68 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling ins-del multiReplace" data-gr-id="68" id="68"><g class="gr_ gr_90 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="90" id="90">SAMPLETABLE</g></g> object is not List type object but it will System.Linq.IQueryable <g class="gr_ gr_157 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style multiReplace" data-gr-id="157" id="157">object ?</g> Because CONSTITUENCIES is part IQueryable object and not a List type object.

    Monday, March 26, 2018 10:16 AM
  • Can you please suggest a solution if  <g class="gr_ gr_8 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="8" id="8"><g class="gr_ gr_9 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="9" id="9">SAMPLETABLE</g></g> object is not List type object but it will System.Linq.IQueryable object? Because CONSTITUENCIES is part IQueryable object and not a List type object.
    Monday, March 26, 2018 10:18 AM