locked
Nested Query using DataLoadOptions

    Question

  • I need some help with a LINQ query to select/load nested data from 3 tables using this database schema.

    http://i392.photobucket.com/albums/pp9/tophog01/Misc/Clipboard01.png

    I am using a modified aspnet_Users & aspnet_Roles provider database tables to form relationships to the 'Requests' & 'RequestDetails' table.  What I am trying to do is load all Request and RequestDetail data associated with any user with a specific primary group where the RequestDetail 'VacationDate' value matches a specific year.  I am close as the query below loads the Request and RequestDetail records for all role members matching the correct primary role however all Requests are being returned without any regard for the 'VacationDate' column in the 'RequestDetails' table.  I know my problem is most like the way I am trying to reference records in the RequestDetails table ...however I'm relatively new to Linq and nested subqueries and not sure the correct method of acheiving what I am trying to do.   I need all columns in all tables as I'm binding the query to a 3-level nested ListView that displays:

    UserName (# of Requests)
        - <RequestNumber>
            - <RequestDetail records>

    The user can select a Year from a Dropdownlist ...and optionally a User where I need to be able to search by User instead of PrimaryRole however I think once I figure the first problem out I'll be able to tackle the User-specific query vice the role-specific query.

    The Requests-> RequestDetails is a one-many relationship.  Not sure at this point where to focus my attention.  Any help would be appreciated.  Thanks.

    Dim dlo As New DataLoadOptions()  
    dlo.LoadWith(Of aspnet_User)(Function(c As aspnet_User) c.aspnet_Role)  
    dlo.LoadWith(Of aspnet_User)(Function(c As aspnet_User) c.Requests)  
    dlo.LoadWith(Of Request)(Function(o As Request) o.RequestDetails)  
    dlo.LoadWith(Of Request)(Function(o As Request) o.StatusType)  
    Me.LoadOptions = dlo  
     
    Dim query = From u In aspnet_Users _  
       Where u.aspnet_Role.LoweredRoleName = primaryRole.ToLower _  
       And u.AccountHidden = False _  
       And (From rd In RequestDetails Where rd.VacationDate.Year = intYear).Count > 0 _  
       Order By u.LoweredUserName _  
       Select u  
     
    return query 
    Friday, February 27, 2009 2:45 AM

All replies

  • I have made some progress but not quite there yet.  The below code is successfully returning the correct RequestDetail records for a given year however it's returning ALL Request records ...even those with no RequestDetail records that match the specified year.

    Anyone know how to only return the Request entities that have RequestDetail records matching the specified year?

     Dim dlo As New DataLoadOptions()  
     
            dlo.LoadWith(Of aspnet_User)(Function(c As aspnet_User) c.aspnet_Role)  
            dlo.LoadWith(Of aspnet_User)(Function(c As aspnet_User) c.Requests)  
            dlo.LoadWith(Of Request)(Function(o As Request) o.RequestDetails)  
            dlo.LoadWith(Of Request)(Function(o As Request) o.StatusType)  
            'dlo.AssociateWith(Of Request)(Function(u As Request) u.RequestDetails.Contains(u.RequestId))  
     
            dlo.AssociateWith(Of Request)(Function(r As Request) r.RequestDetails.Where(Function(x) x.VacationDate.Year = intYear))  
     
     
            Me.LoadOptions = dlo  
     
            Dim query = From u In aspnet_Users _  
                      Where u.aspnet_Role.LoweredRoleName = primaryRole.ToLower _  
                      And u.AccountHidden = False _  
                      Order By u.LoweredUserName _  
                      Select u  
     
              
     
            Return query.ToList 

     

    Friday, February 27, 2009 4:49 PM