Christoc.Modules.DnnChat.Components.ConnectionRecordRoomController.GetConnectionRecordRoomsByUserId C# (CSharp) Method

        public IEnumerable<Room> GetConnectionRecordRoomsByUserId(int userId)
        {
            IEnumerable<Room> t;
            using (IDataContext ctx = DataContext.Instance())
            {
                //TODO: update this SQL to only choose the LATEST connectionrecord, not all
                var connectionRecordRooms = ctx.ExecuteQuery<Room>(CommandType.Text,
                                                       string.Format(
                                                           "select distinct r.* from {0}{1}DnnChat_ConnectionRecordRooms crr join {0}{1}DnnChat_ConnectionRecords cr on (cr.ConnectionRecordId = crr.ConnectionRecordId)" +
                                                           " join {0}{1}DnnChat_Rooms r on (r.RoomId = crr.RoomId)" +
                                                           " where cr.UserId = '{2}' and crr.DepartedDate is null " +
                                                           " and r.enabled=1 and r.private = 0" +
                                                           " and cr.ConnectionRecordId in (select top 1 ConnectionRecordId from {0}{1}DnnChat_ConnectionRecords where  {1}DnnChat_ConnectionRecords.UserId ='{2}' order by {1}DnnChat_ConnectionRecords.ConnectionRecordId desc) " +
                                                           " order by r.roomName asc ",
                                                           _databaseOwner,
                                                           _objectQualifier,
                                                          userId)).ToList();

                if (connectionRecordRooms.Any())
                {
                    //get all rooms from the list of ConnectionRecords
                    t = connectionRecordRooms;
                }
                else
                    return null;
            }
            return t;
        }

        //TODO: on connection, reload rooms for user?
        public Task Join()
        {
            int moduleId = Convert.ToInt32(Clients.Caller.moduleid);

            var settingsDefault = new Guid(Clients.Caller.defaultRoomId);

            if (DefaultRoomId != settingsDefault)
            {
                DefaultRoomId = settingsDefault;
            }

            //get list of previously connected (not departed) rooms
            var crrc = new ConnectionRecordRoomController();
            var rc   = new RoomController();

            IEnumerable <Room> myRooms = null;

            //don't do this if we've got a private room loaded.
            if (Convert.ToInt32(Clients.Caller.userid) > 0)
            {
                myRooms = crrc.GetConnectionRecordRoomsByUserId((int)Clients.Caller.userid);
            }

            //TODO: the default room doesn't have a moduleid associated with it
            //if myRooms is empty, what to do (pass default room)
            if (myRooms == null)
            {
                //load the default room
                var r = rc.GetRoom(DefaultRoomId, moduleId);
                myRooms = new List <Room>();
                myRooms = myRooms.Concat(new[] { r });
            }
            else
            {
                //load the current default room to see if it is in the queue
                var r = rc.GetRoom(DefaultRoomId, moduleId);
                if (!myRooms.Contains(r))
                {
                    myRooms = myRooms.Concat(new[] { r });
                }
            }

            //get all the active rooms and send it back for the Lobby
            var allRooms = rc.GetRooms(moduleId);

            //we are passing in a list of All rooms, and the current user's rooms
            Clients.Caller.PopulateUser(allRooms, myRooms);
            return(base.OnConnected());
        }