CODEWITHSUNDEEP
c#linqlinq-to-entitiesef-code-first
Erre Efe
Erre Efe

Reputation: 15577

LINQ Query to mimic IS IN

So I have this linq query clause.

var ctx = new Context();
IQueryable<Users> consulta = ctx.Users;

if ( filters.Count > 0 )
   query = query.Where( p => filters.Contains(p.Name) || 
                             filters.Contains(p.LastName) );

Filters

Is an string list that contains unordered names and lastnames (not necessary complete). For example: Filter {Mary, Joseph Ken} but DB {Mary Katie, Joseph Kendall}.

Expected results

For the previous filters I want the query to return the list of users no matter if within the filters its names and lastnames are incomplete but correct. So if the filter has "Mary" it must found the db record with "Mary Katie" and so on.

Upvotes: 0

Views: 489

Answers (3)

richk
richk

Reputation: 416

Maybe something along these lines? I haven't actually tested it out so buyer beware. 

public static bool IsIn(this string value, IEnumerable<string> compareList)
{
   foreach (string compareValue in compareList)
   {
        if (value.Contains(compareValue))
                return true;
   }
        return false;
}

Upvotes: 0

Saeed Amiri
Saeed Amiri

Reputation: 22565

You can use mix of String.Contains and Any to solve your problem in linq2entity without fetching extra data from DB:

var ctx = new Context();
IQueryable<Users> consulta = ctx.Users;

if ( filters.Count > 0 )
   query = query.Where( p => filters.Any(x=>x.Contains(p.Name)) || 
                             filters.Any(x=>x.Contains(p.LastName)) ||
                             filters.Any(x=>p.Name.Contains(x)) || 
                             filters.Any(x=>p.LastName.Contains(x)));

Upvotes: 2

devshorts
devshorts

Reputation: 8872

I think what you want is given a filter 

Bob
Alice
Jane

and items in your query such as

Bob Nob
Alice Howzer
Jane Bob
Tim

You would get

Bob Nob
Alice Howzer
Jane Bob

Because the filter matched any part of the first or last name right?

Why not add an extension method to your collection that does something like

public static bool IsInAny(this IEnumerable<String> source, string name, string delim = " ")
{
    return source.Any(item =>
                          {
                             var splits = name.Split(new[] { delim }, StringSplitOptions.RemoveEmptyEntries);
                             return splits.Contains(item);
                          });
}

Here is a test you can use to test it

[Test]
public void TestInAny()
{
    var filters = new[] {"Bob", "Alice"};
    var items = new[] {"Bob Knob", "Alice Jane", "Tim"};

    var found = items.Where(i => filters.IsInAny(i)).ToList();
}

Obviously this is just a quick and dirty way and there are cleaner ways of doing it, but at least now you've encapsulated the logic.

Upvotes: 3

Related Questions