Help with LINQ SELECT Statements

[Scott]

Hello:

I have a problem I am trying to solve, been pulling my hair out for quite some time. My issue is that I am trying to create a distinct list of users, based on their security permissions, as they relate to information provided on a web form.

A user enters some information into a form (basically a list of departments).

I then need to return a list of users that have access to each of the departments.

My code looks like this so far:

dim details = (Form p in db.Details select p.deptID Distinct)

dim singers = (from p in db.Users join ds in db.deptSecurities on p.UserID queals ds.UserID where details.contains(ds.deptID), select p.UserID Distinct)

This returns me a list users that have access to at least 1 of the departments in details, but I need to return a list of users that has permission to all departments in details.

Can this be done easily?

Thanks!

[Thomas]

Hello Scott,

Please turn the first query into a list first:

dim details = (Form p in db.Details select p.deptID Distinct).ToList();

dim singers = (from p in db.Users join ds in db.deptSecurities on p.UserID equals ds.UserID where details.Contains(ds.deptID) select p.UserID Distinct)

Kind regards,
Thomas
the Telerik team

[Scott]

Thanks Thomas, unfortunately this returns the same results as without the .ToList()

Here is an Example.

Let's say the list of departmentID is:  {10, 15, 35}

I have a table with the following columns:  UserID, deptID.

I need to get a list of users from the above table for only users that have all of the departmentID's in the first dataset. The current code returns all users, that have at least 1 of the departments.

[Thomas]

Hello Scott,

that is currently not possible, and my hair got more gray with this too; I still have it :-) .
I believe at the moment the only option is to generate the LINQ tree dynamically:

01.    [TestMethod]
02.    public void AllByParam()
03.    {
04.        IList<int> details = (new int[] { 81929, 81677, 81678 }).ToList();
05.        //var q = from x in Scope.Extent<City>() where details.All(z => x.Zips.Contains(z)) select x;
06.        //var r = q.ToList();
07.        //Assert.AreEqual(1, r.Count);
08.        var q2 = Scope.Extent<City>().AllPresent(j => j.Zips, details);
09.        Assert.AreEqual(1, q2.ToList().Count);
10.    }
11.}
12.public static class CollectionExtensions
13.{
14.    public static IQueryable<TSource> AllPresent<TSource, TKey, Z>(this IQueryable<TSource> source, Expression<Func<TSource, TKey>> keySelector, ICollection<Z> elems)
15.                                    where TKey : ICollection<Z>
16.    {
17.        Expression cond = null;
18.        foreach (var z in elems)
19.        {
20.            if (z != null)
21.            {
22.                Expression c = Expression.Call(keySelector.Body, typeof(ICollection<Z>).GetMethod("Contains"), Expression.Constant(z));
23.                if (cond == null)
24.                    cond = c;
25.                else
26.                    cond = Expression.AndAlso(cond, c);
27.            }
28.        }
29.        if (cond != null)
30.        {
31.            Expression<Func<TSource, bool>> lambda = Expression.Lambda<Func<TSource, bool>>(cond, keySelector.Parameters);
32.            source = source.Where(lambda);
33.        }
34.        return source;
35.    }
36.}

I think line 5-7 contain the right LINQ, but this is currently not implemented, and a manual generation of the test is needed as shown above.

All the best,
Thomas
the Telerik team