CODEWITHSUNDEEP
c#linqentity-framework-core
Franky
Franky

Reputation: 1594

SQL LINQ Evaluate Comma-Separated String Contains Value

I am using EFCore Linq and run into a problem. I have a table that stores a comma-separated string and I need to use it in where clause to filter only those containing certain value. Here is my Linq:

var volunteers = context.Volunteers
               .Where(x => x.StatusId == 1
                     && x.RoleIds.Split(',', StringSplitOptions.None).Contains("1")
                ).ToList();

The column is RoleIds. The error I received is Linq query cannot be translated. What do I need to do here? Thanks!

Upvotes: 1

Views: 1220

Answers (1)

NetMage
NetMage

Reputation: 26926

If you are okay with using LINQKit (or rolling your own focused version), you can create extensions to handle the testing for you:

public static class IQueryableExt { // using LINQKit
    // string fieldExpr(T row) - function returning multiple value string field to test
    // delimiter - string separator between values in test field
    // value - string value to find in values of test field
    // dbq.Where(r => fieldExpr(r).Split(delimiter).Contains(value))
    public static IQueryable<T> WhereSplitContains<T>(this IQueryable<T> dbq, 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 dbq.Where((Expression<Func<T, bool>>)pred.Expand());
    }

    // values - string values, one of which to find in values of test field
    // string fieldExpr(T row) - function returning multiple value 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) {
        var pred = PredicateBuilder.New<T>();
        foreach (var value in values) {
            pred = pred.Or(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 dbq.Where((Expression<Func<T, bool>>)pred.Expand());
    }
    public static IQueryable<T> WhereSplitContainsAny<T>(this IQueryable<T> dbq, Expression<Func<T, string>> fieldExpr, string delimiter, IEnumerable<string> values) =>
        dbq.WhereAnySplitContains(values, fieldExpr, delimiter);
    public static IQueryable<T> WhereSplitContainsAny<T>(this IQueryable<T> dbq, Expression<Func<T, string>> fieldExpr, string delimiter, params string[] values) =>
        dbq.WhereAnySplitContains(values, fieldExpr, delimiter);

    // values - string values, all of which to find in values of test field
    // string fieldExpr(T row) - function returning multiple value string field to test
    // delimiter - string separator between values in test field
    // dbq.Where(r => values.All(value => fieldExpr(r).Split(delimiter).Contains(value)))
    public static IQueryable<T> WhereAllSplitContains<T>(this IQueryable<T> dbq, IEnumerable<string> values, Expression<Func<T, string>> fieldExpr, string delimiter) {
        var pred = PredicateBuilder.New<T>();
        foreach (var value in values) {
            var subPred = PredicateBuilder.New<T>(r => fieldExpr.Invoke(r) == value);
            subPred = subPred.Or(r => fieldExpr.Invoke(r).StartsWith(value + delimiter));
            subPred = subPred.Or(r => fieldExpr.Invoke(r).EndsWith(delimiter + value));
            subPred = subPred.Or(r => fieldExpr.Invoke(r).Contains(delimiter + value + delimiter));
            pred = pred.And(subPred);
        }

        return dbq.Where((Expression<Func<T, bool>>)pred.Expand());
    }
    public static IQueryable<T> WhereSplitContainsAll<T>(this IQueryable<T> dbq, Expression<Func<T, string>> fieldExpr, string delimiter, IEnumerable<string> values) =>
        dbq.WhereAllSplitContains(values, fieldExpr, delimiter);
    public static IQueryable<T> WhereSplitContainsAll<T>(this IQueryable<T> dbq, Expression<Func<T, string>> fieldExpr, string delimiter, params string[] values) =>
        dbq.WhereAllSplitContains(values, fieldExpr, delimiter);
}

With these extensions, your query would be:

var volunteers = context.Volunteers.Where(x => x.StatusId == 1)
                                   .WhereSplitContains(r => r.RoleIds, ",", "1")
                                   .ToList();

If you had multiple values, you could use the other variations:

var volunteers = context.Volunteers.Where(x => x.StatusId == 1)
                                   .WhereSplitContainsAny(r => r.RoleIds, ",", "1", "2")
                                   .ToList();

Upvotes: 2

Related Questions