How to join with LinQ to (typed) dataset?

Question

i recently upgraded VS 2005 to 2010 and am fairly new to LinQ. Maybe somebody can put me in the right way.

Background: I have a typed dataset and have the standard SQLMembershipProvider extended with a Table AccessRule. So a role can have infinitely AccessRules(f.e. "Administrator" has "DeleteCustomer"). I use a custom membership provider that inherits from SqlMemberShipProvider and has an overloaded function hasAccess(one with a memory-dataset as parameter and the other uses the database directly).

This is the complete Model:

Now i need to know f.e. if a User with UserID='89f9ea8d-8ae1-460b-a430-aa433261feec' has AccessRule "DeleteCustomer".

In SQL it would be something like this:

SELECT     CASE WHEN aspnet_AccessRule.idAccessRule IS NULL THEN 0 ELSE 1 END AS Access
FROM         aspnet_RoleAccessRule INNER JOIN
                      aspnet_AccessRule ON aspnet_RoleAccessRule.fiAccessRule = aspnet_AccessRule.idAccessRule INNER JOIN
                      aspnet_Roles ON aspnet_RoleAccessRule.fiRole = aspnet_Roles.RoleId INNER JOIN
                      aspnet_UsersInRoles ON aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId
WHERE (aspnet_UsersInRoles.UserId = @UserID) AND    (aspnet_AccessRule.RuleName =@RuleName) 

In short:

how do i get from aspnet_UsersInRoles toaspnet_AccessRule with LinQ?

Thank you in advance...

Edit:

Although C# is also welcome, i'm preferring VB.Net.

This is what i have, but it doesn't work.

Dim query = From accRule In dsAuth.aspnet_AccessRule _
                        From roleAccRule In dsAuth.aspnet_RoleAccessRule _
                        From role In dsAuth.aspnet_Roles _
                        From userRole In dsAuth.aspnet_UsersInRoles _
                        Where roleAccRule.fiAccessRule = accRule.idAccessRule _
                        And roleAccRule.fiRole = role.RoleId _
                        And userRole.RoleId = role.RoleId _
                        And userRole.UserId = userID And accRule.RuleName = accessRule
            Select accRule.idAccessRule 
Return query.Any

I get a "Definition of method SelectMany is not accessible in this context" compiler warning and the secondFrom is highlighted. I assume it has something to do with the composite-keys in aspnet_RoleAccessRule and aspnet_UsersInRoles. Any suggestions?

This gives the same exception on the first comma:

Dim query = From accRule In dsAuth.aspnet_AccessRule, _
           roleAccRule In dsAuth.aspnet_RoleAccessRule, _
           role In dsAuth.aspnet_Roles, _
           userRole In dsAuth.aspnet_UsersInRoles _
           Where accRule.idAccessRule = roleAccRule.fiAccessRule _
           And roleAccRule.fiRole = role.RoleId _
           And userRole.RoleId = role.RoleId _
           And userRole.UserId = userID And accRule.RuleName = accessRule
     Select accRule.idAccessRule
Return query.Any

This is the join syntax but with similar error(Join is not accessible in context..):

Dim userID As Guid = DirectCast(Membership.GetUser.ProviderUserKey, Guid)
        Dim query = From accRule In dsAuth.aspnet_AccessRule _
                       Join roleAccRule In dsAuth.aspnet_RoleAccessRule _
                       On accRule.idAccessRule Equals roleAccRule.fiAccessRule _
                       Join role In dsAuth.aspnet_Roles _
                       On role.RoleId Equals roleAccRule.fiRole _
                       Join userRole In dsAuth.aspnet_UsersInRoles _
                       On userRole.RoleId Equals role.RoleId _
                       Where userRole.UserId = userID And accRule.RuleName = accessRule
                 Select accRule.idAccessRule
        Return query.Any

---

Solution: I've forgotten to import the Namespace System.LinQ. All these queries are working.

Side note: the Join-Syntax is by far the fastest query. Have a look at my followup-question: Why is LINQ JOIN so much faster than linking with WHERE?

Squamulose

Answer 1

I am afraid that someone will come and start throwing rocks at me but I will take my chances. I would probably start with this:

var AccessRules = from ar in aspnet_AccessRule
            from rar in aspnet_Role
            from r in aspnet_Roles
            from uir in aspnet_UsersInRoles
            where ar.idaccessrule == rar.fiAccessRule
            where rar.fiRole == r.RoleId
            where r.RoleId == uir.RoleId
            select ar;

Hmmm.. I actuall don't know how your dataset looks, but I think you get the point from this pseudocode.

Edit: here is the link to tool that might help you: Linqer

Jemena

Thank you. But i get a "Definition of method Join is not accessible in this context" compiler error. I'm assuming that it has something to do with the composite-keys in UserInRoles and RoleAccessRule Tables. Any ideas? – Squamulose

Sorry, I haven't got this error before. So I don't know how to deal with it :x You could try going step by step (relation by relation) - making several individual queries.. it might help locate and deal with problem. It might be because of those m:n helper tables, but I don't see any reason why you wouldn't be able to query them. I have also added link to SQL->LINQ tool to my answer. – Jemena

i simply forgot to import the System.LinQ-Namespace. – Squamulose

@Tim Even this may happen... :d – Jemena