I am trying to extract data from a column and apply filter for the same. Below is the scenario.

As shown in below screenshot, a serviceId can have multiple userRoleId assigned to it. 0 or more userRoleId are stored in a single column userRoleIds as string value. I am trying to write a lambda expression to filter information when I select one or more userRoleId.

Let's say, if I pass user id 1,4,11,9 as a list, then it should return the following rows:

Could you please share code snippet to achieve this in a single query without duplication records. Thanks in advance.

Tried to implement the logic with SQL UDFs. Was able to achieve it. But required to do it from LINQ lambda expression.

CodePudding user response：

Try following :

            int[] values = { 1, 4, 11, 9 };
            DataTable dt = new DataTable();
            dt.Columns.Add("serviceId", typeof(int));
            dt.Columns.Add("userRoleIds", typeof(int[]));

            dt.Rows.Add(new object[] { 1, new int[] { 1,5,11}});
            dt.Rows.Add(new object[] { 2, new int[] {4} });
            dt.Rows.Add(new object[] { 3, null });
            dt.Rows.Add(new object[] { 4, new int[] { 1, 4, 6, 9, 19 } });

            var results = dt.AsEnumerable().Where(x => x.Field<object>("userRoleIds") != null && x.Field<int[]>("userRoleIds").Any(y => values.Contains(y))).ToList();
 

 

CodePudding user response：

Using LINQKit, you can create helpers to allow taking a List<T> of possible members of a delimited string and searching it in an EF/EF Core compatible way by adding a predicate for each term that is like v == term || v.StartsWith(term delimiter) || v.EndsWith(delimiter term) || v.Contains(delimiter term delimiter).

You can also avoid LINQKit by rolling your own Expression tree helpers.

Here is code I use with a LINQKit dependency:

public static class LinqKitExt { // using LINQKit
    // 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>> SplitContainsAny<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;
    }
    // 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> WhereSplitContainsAny<T>(this IQueryable<T> dbq, Expression<Func<T, string>> fieldExpr, string delimiter, IEnumerable<string> values) =>
        dbq.AsExpandable().Where(values.SplitContainsAny(fieldExpr, delimiter));
}

With this available, you can do:

var ans = src.WhereSplitContainsAny(s => s.userRoleIds.Substring(1, s.userRoleIds.Length-2),
                                    ",",
                                    filter.Select(n => n.ToString()))
             .ToList();