More efficient method for querying twice-separated-or-more objects in a relation chain?

Question

Not sure if I am using the correct term, but it's not child/parent since they aren't nested objects. In my application I've got a User, UserUserGroup, and UserGroup objects which are pretty standard, UserUserGroup is a linking object with corresponding IDs. I am using Linq-to-SQL so unforunately it doesn't used nested objects, but it still knows the relation.

Consider the following class and query where I am trying to return a list of Users that are associated with a specific UserGroup:

    Public Class UserDao
    Inherits EntityDao(Of User)

    Public Function getListOfUsersByUserGroupName(ByVal userGroupName As String) As IList(Of User)

        Dim userList As New List(Of User)

        If Not userGroupName Is Nothing Then
            Dim userGroupDao As New UserGroupDao()
            Dim userUserGroupDao As New UserUserGroupDao()

            Dim userGroup As New UserGroup()
            userGroup = userGroupDao.getOneByValueOfProperty("Name", userGroupName)

            If Not userGroup Is Nothing Then

                Dim userUserGroup As IQueryable(Of UserUserGroup) = userUserGroupDao.getListByValueOfProperty("UserGroupId", userGroup.Id)

                If Not userUserGroup Is Nothing Then
                    Dim userDao As New UserDao()
                    Dim user As New User()

                    For Each entry As UserUserGroup In userUserGroup
                        Dim result As UserUserGroup = entry
                        user = userDao.getOneByValueOfProperty("Id", result.Id)
                        userList.Add(user)
                    Next

                End If
            End If

        End If

        Return userList

    End Function

End Class

Is there anyway to make this better with less lines and more efficient? It seems incredibly inefficient for me to first get the ID of the supplied usergroup, then get the results in UserUserGroup where UserGroupId equals that, then get all the User Ids from that query and then for each of those Ids, query the User table to grab the object corresponding to that ID. It just seems bad for one 'result' I am running at least 3 queries, with each of them creating and disposings dataContexts. It seems like it would be taxing, especially on the for each loop if there was a ton of results returned.

Answer 1

I think what you're looking for can be done relatively easily in LINQ to SQL, but the structure of your data layer is unexpected from the perspective of LINQ. I would expect, if you use the designer and have the relationships defined in the database, that you'd have entity sets and could use those. A more natural way using the data context, in say the Repository pattern, as the base for the query would probably work better. Essentially, what you want are any users who have any associations in which the set of groups that they are associated with contains the group with the name in question.

EDIT: I think what I'm actually saying is let LINQ be LINQ. LINQ is a data context-centric ORM (lightweight), and you need to think about using LINQ from a data context perspective to make it really work for you. If you stick with your previous, data-object centered perspective, I think you'll be disappointed in LINQ.

Example (in C#) -- this is not a full example on how to implement a Repository, just for illustrative purposes.

public class UserRepository : IDisposable
{
   private DataContext Context { get; set; }
   private bool DisposeContext { get; set; }

   public UserRepository() : this( null ) { }

   public UserRepository( DataContext context ) // manual constructor injection
   {
      this.Context = context ?? new MyDataContext();
      this.DisposeContext = context == null;
   }

   public IQueryable<User> GetListOfUsersByUserGroupName( string userGroupName )
   {     
      return Context.Users
                    .Where( u => u.UserUserGroups
                                  .Any( uug => uug.Groups
                                                  .Any( ug => ug.GroupName == userGroupName ) );
   }

   ...

   public void SaveChanges()
   {
        Context.SubmitChanges();
   }

   // insert canonical IDisposable pattern here and dispose of
   // the data context if needed, don't dispose if injected
}

Used as:

 using (var repository = new UserRepository())
 {
     var users = repository.GetListOfUsersByUserGroupName( "admin" );
     foreach (var admin in users)
     {
         SendMessage( admin, notification );
         admin.LastNotified = DateTime.Now;
     }
     repository.SaveChanges();
 }