CODEWITHSUNDEEP
c#linq
mig_08
mig_08

Reputation: 181

Linq contains of an array

I have an array of values and trying to filter my linq query to see if any majorchains contain any of the values from the array. Majorchains is a string delimited string.

Filter by

var array = new string[]{"Checkers", "Usave"};

Query

var query = _dbContext.Promotions.Where(e => array.Contains(e.MajorChain));

Example of how Majorchains is stored: "Checkers, OK Franchise, Shop Check Butchery, Shoprite, Usave" enter image description here

What is currently happening, if there is just 1 item in there array filter it returns all data fine. Once there is more than 1 it returns an empty list.

Upvotes: 0

Views: 1350

Answers (2)

NetMage
NetMage

Reputation: 26907

Using LINQKit, or a dedicated simplified internal version, you can create a method to expand the filter expression to something that can be translated to SQL.

// string fieldExpr(T row) - function returning multiple value delimited string field to test
// delimiter - string separator between values in test field
// value - string value to find in splits of test field
// r => fieldExpr(r).Split(delimiter).Contains(value)
public static Expression<Func<T, bool>> SplitContains<T>(this Expression<Func<T, string>> fieldExpr, string delimiter, string value) {
    var pred = PredicateBuilder.New<T>(r => fieldExpr.Invoke(r) == value);
    pred = pred.Or(r => fieldExpr.Invoke(r).StartsWith(value + delimiter));
    pred = pred.Or(r => fieldExpr.Invoke(r).EndsWith(delimiter + value));
    pred = pred.Or(r => fieldExpr.Invoke(r).Contains(delimiter + value + delimiter));

    return pred;
}

// values - string values, one of which to find in splits of test field
// string fieldExpr(T row) - function returning multiple value delimited string field to test
// delimiter - string separator between values in test field
// r => values.Any(value => fieldExpr(r).Split(delimiter).Contains(value))
public static Expression<Func<T, bool>> AnySplitContains<T>(this IEnumerable<string> values, Expression<Func<T, string>> fieldExpr, string delimiter) {
    var pred = PredicateBuilder.New<T>();
    foreach (var value in values)
        pred = pred.Or(fieldExpr.SplitContains(delimiter, value));

    return pred;
}
public static Expression<Func<T, bool>> SplitContainsAny<T>(this Expression<Func<T, string>> fieldExpr, string delimiter, IEnumerable<string> values) =>
    values.AnySplitContains(fieldExpr, delimiter);
public static Expression<Func<T, bool>> SplitContainsAny<T>(this Expression<Func<T, string>> fieldExpr, string delimiter, params string[] values) =>
    values.AnySplitContains(fieldExpr, delimiter);

// values - string values, one of which to find in splits of test field
// string fieldExpr(T row) - function returning multiple value delimited string field to test
// delimiter - string separator between values in test field
// dbq.Where(r => values.Any(value => fieldExpr(r).Split(delimiter).Contains(value)))
public static IQueryable<T> WhereAnySplitContains<T>(this IQueryable<T> dbq, IEnumerable<string> values, Expression<Func<T, string>> fieldExpr, string delimiter) =>
    dbq.AsExpandable().Where(values.AnySplitContains(fieldExpr, delimiter));
public static IQueryable<T> WhereSplitContainsAny<T>(this IQueryable<T> dbq, Expression<Func<T, string>> fieldExpr, string delimiter, IEnumerable<string> values) =>
    dbq.AsExpandable().Where(values.AnySplitContains(fieldExpr, delimiter));
public static IQueryable<T> WhereSplitContainsAny<T>(this IQueryable<T> dbq, Expression<Func<T, string>> fieldExpr, string delimiter, params string[] values) =>
    dbq.AsExpandable().Where(values.AnySplitContains(fieldExpr, delimiter));

Basically, every value is tested for belonging in the database field by testing for being exactly equal to the value, starting with the value+delimiter, ending with delimiter+value, or containing delimiter+value+delimiter. Each of these tests translate to SQL using LIKE.

Using this method, you can write your query:

var query = _dbContext.Promotions.WhereAnySplitContains(array, e => e.MajorChain, ", ");

NOTE: The delimiter is assumed to be consistent - you must have either "," or ", " between every value in the field.

Upvotes: 1

Qudus
Qudus

Reputation: 1520

That's because irrespective of the number of words you have for any MajorColumn, it would all still be treated as a string. You could split each MajorChain column value by the comma separating the words into an array of strings.

var query = _dbContext.Promotions.Where(e =>
  e.MajorChain.Split(',').Any(a=>Array.Contains(a)));

Upvotes: 0

Related Questions