Create a LINQ query comparing arrays

Question

I'm very new to LINQ and I'm searching to filter an SQL view which has a column called 'Readers' containing several group names separated by '#' (es. "Administrators#HR Group#Employees Group"). Having a list of user's groups, I need to extract all the records where Readers contains at least one of the user's groups. In other words, the user must see only those records belonging to him.

I found this solution, but I think it's extremely inefficient:

private List<vwFax>getmyFaxes(List<string> myGroups)
{
    var myFax = db.vwFax.AsQueryable();
    var res = db.vwFax.AsQueryable();
    List<vwFax> outRes= new List<vwFax>();

    foreach (string elem in myGroups)
    {
        res = (from a in myFax
                where a.Readers.Contains(elem)
                select a); 
        if(res.Count() > 0)
        {
            outRes.AddRange(res);
        }               
    }
    return outRes.ToList();
}

Any help please?

Maybe (from a in myFax where a.Readers.Any(r => myGroups.Contains(r)) select a).ToList() — haim770
– haim770, Commented Aug 3, 2016 at 11:04
db.vwFax.AsQueryable().Where(x=> myGroups.Any(y=> x.Contains(y)).ToList(); you can also create a stored proc instead of LINQ. — Valentin
– Valentin, Commented Aug 3, 2016 at 11:06
Looks like the problem is with if(res.Count() > 0){outRes.AddRange(res);} because you are executing the SQL query myGroups.Count time, instead of executing one big query. Using LINQ queries above you will execute only one query. — Valentin
– Valentin, Commented Aug 3, 2016 at 11:10

Gilad Green · Accepted Answer · 2016-08-03 11:06:20Z

2

Basically what you are saying in the following query is: For each item in myFax take it only if that item.Readers contains Any (at least 1) of the items in myGroups

outRes = db.myFax.Where(item => myGroups.Any(grp => item.Readers.Contains(grp)));

and in query-syntax:

outRes = from item in db.myFax
         where myGroups.Any(grp => item.Readers.Contains(grp))
         select item;

answered Aug 3, 2016 at 11:06

Gilad Green

37.3k7 gold badges67 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create a LINQ query comparing arrays

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related