Getting all Data from SQL using LINQ with comma-separated IDs

Question

I do have a string of Empids separated by comma like:

EMpID:"2007,2008,2002,1992,1000,2108,1085

and I need to retrieve the records of all those specified employees using LINQ query. I tried it with looping but I need to get that in efficient and faster way.

Here goes what i did using looping.

string[] EMpID_str = LeaveDictionary["EMpID"].ToString().Split(',');

for (int i = 0; i < EMpID_str.Length; i++)
            {
                EMpID = Convert.ToInt32(EMpID_str[i]);

               //Linq to get data for each Empid goes here
             }

But What I need is to use single LINQ or Lambda query to retrieve the same.Without looping

What have you tried so far? Show some example code you already have - this can help streamline other members of the site to answer you fast. — Asunez, Jul 28 '20 at 06:20
First convert your ,(comma) separated empId to string array like `var empArr = EmpId.split(',');` `var employeesResult = emplyeeList.Where(x => empArr.contains(x.EmpId.ToString()));` — Rajeev Kumar, Jul 28 '20 at 06:25
@MichaelSchönbauer isn't there a Linq extension for arrays which allows us to use `Contains` then? The accepted solution [here](https://stackoverflow.com/questions/857973/linq-to-entities-sql-in-clause) seems to use `Contains` on arrays — Rafalon, Jul 28 '20 at 06:28
@MichaelSchönbauer, I have used Contains on an array with LINQ many times in my code. You can check again. — Rajeev Kumar, Jul 28 '20 at 06:30
@Ne2, You are most welcome! Please vote for my comment as useful. — Rajeev Kumar, Jul 28 '20 at 15:14

score 1 · Answer 1 · answered Jul 28 '20 at 06:33

If the Ids that you want to fetch are numbers, not strings, then you should not convert the string to an array of strings, but to a sequence of numbers:

IEnumerable<int> employeeIdsToFetch = LeaveDictionary["EMpID"].ToString()
    .Split(',')
    .Select(splitText => Int32.Parse(splitText));

To fetch all employees with thees Ids:

var fetchedEmployees = dbContext.Employees
    .Where(employee => employeeIdsToFetch.Contains(employee.Id))
    .Select(employee => new
    {
         // Select only the employee properties that you plan to use:
         Id = employee.Id,
         Name = employee.Name,
         ...
    });

Thank you @Harald for your help and suggestion. – Ne2 Jul 28 '20 at 09:26 — Ne2, Jul 28 '20 at 09:26

score 1 · Accepted Answer · answered Aug 12 '20 at 17:26

1

First convert your ,(comma) separated empId to string array like below:

var empArr = EmpId.split(','); 
var employeesResult = emplyeeList.Where(x => empArr.contains(x.EmpId.ToString()));

I hope, it will help someone.

answered Aug 12 '20 at 17:26

Rajeev Kumar

371
2
9

Ackdari · Answer 3 · 2020-07-28T06:55:10.993

You can use the Expression class to build a Func<int, bool> from your string and use it with the Where methode:

var str = "2,5,8,9,4,6,7";

var para = Expression.Parameter(typeof(int));

var body = str.Split(",")
    .Select(s => int.Parse(s))
    .Select(i => Expression.Constant(i))
    .Select(c => Expression.Equal(para, c))
    .Aggregate((a, b) => Expression.Or(a, b));

Func<int, bool> func = Expression.Lambda<Func<int, bool>>(body, para).Compile();

and if you this solution to work with linq to SQL just dont compile the expression at the end and let the linq to SQL engine compile it to an efficent SQL expression.

Instead of the Aggregate Method (which will produce an expression with linear complexity) one could use an divide and conquer approach to fold the values into one value.

For example with this class:

public static class Helper
{
    public static T EfficientFold<T>(this List<T> list, Func<T, T, T> func)
    {
        return EfficientFold(list, 0, list.Count, func);
    }

    private static T EfficientFold<T>(List<T> list, int lowerbound, int upperbound, Func<T, T, T> func)
    {
        int diff = upperbound - lowerbound;
        var mid = lowerbound + diff / 2;

        if (diff < 1)
        {
            throw new Exception();
        }
        else if (diff == 1)
        {
            return list[lowerbound];
        }
        else
        {
            var left = EfficientFold(list, lowerbound, mid, func);
            var right = EfficientFold(list, mid, upperbound, func);

            return func(left, right);
        }
    }
}

and then we can do

var body = str.Split(",")
    .Select(s => int.Parse(s))
    .Select(i => Expression.Constant(i))
    .Select(c => Expression.Equal(para, c))
    .ToList()
    .EfficientFold((a, b) => Expression.Or(a, b));

which gives the evaluation a complexity of log(n).

Getting all Data from SQL using LINQ with comma-separated IDs

3 Answers3