Help with LINQ SELECT Statements

4 posts, 0 answers
  1. Scott
    Scott avatar
    3 posts
    Member since:
    Sep 2012

    Posted 04 Oct 2011 Link to this post

    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!
  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 05 Oct 2011 Link to this post

    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

    Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>

  3. DevCraft banner
  4. Scott
    Scott avatar
    3 posts
    Member since:
    Sep 2012

    Posted 05 Oct 2011 Link to this post

    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.
  5. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 06 Oct 2011 Link to this post

    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

    Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's SQL Server Community Awards. We are competing in TWO categories and every vote counts! VOTE for Telerik NOW >>

Back to Top