How do I do this in LINQ

Question

Ok I now have this

public IEnumerable<roomvu_User> GetLocationUsers(
   long LocationID, 
   DateTime StartDate, 
   DateTime EndDate, 
   int StartRows, 
   int MaximumRows)
{
    using ( DataClasses_RoomViewDataContext context = Context )
    {
        IEnumerable<roomvu_LocationMapping> Mappings = 
            ( from m in context.roomvu_LocationMappings
              where ( m.LocationID == LocationID 
                 && ( StartDate <= m.EndDate && m.StartDate <= EndDate ) )
              select m ).Skip( StartRows ).Take( MaximumRows );

        List<roomvu_User> Users = new List<roomvu_User>();

        foreach ( roomvu_LocationMapping Mapping in Mappings )
        {
            roomvu_User User = ( from u in context.roomvu_Users 
                                 where ( u.ID == Mapping.UserID ) 
                                 select u ).Single();
            Users.Add( User );
        }

        return Users;
    }
}

But I hate the foreach bit, there must be a way to do this in a single LINQ expression.....

Help

Answer 1

from m in context.roomvu_LocationMappings
join u in context.roomvu_Users
on m.UserID equals u.ID
where (m.LocationID == LocationID 
          && (StartDate <= m.EndDate && m.StartDate <= EndDate))
select new { Mapping = m, User = u }

Will return a sequence of a with two properties, .Mapping and .User.

Answer 2

return 
   Mappings.ConvertAll<roomvu_User>(new Converter<roomvu_LocationMapping, roomvu_User>(a =>
    {
       return (from u in context.roomvu_Users where (u.ID == Mapping.UserID) select u).Single();
    }));

Above is using the ConvertAll linq extension on the List<roomvu_LocationMapping> Mappings
or

return (from m in Mappings
        let u = context.roomvu_Users.Single(u => u.ID == m.UserID)
        select u).ToList();

not 100% sure about the second, make sure it works first

Edit: cos of your edit!

I dont know ur db structure but if romvu_Users is related with foreign keys you should just be able to change the select to

select m.roomvu_Users ).Skip( StartRows ).Take( MaximumRows );

in your Linq2Sql query

Answer 3

I believe this is the most expressive way to represent what you're asking for. However, I'm not sure if LINQ to SQL is smart enough to translate the Single() call on the results of the GroupJoin() (how the compiler translates join ... into j):

public IEnumerable<roomvu_User> GetLocationUsers(
   long LocationID, 
   DateTime StartDate, 
   DateTime EndDate, 
   int StartRows, 
   int MaximumRows)
{
    using ( DataClasses_RoomViewDataContext context = Context )
    {
        IQueryable<roomvu_LocationMapping> Mappings = 
            ( from m in context.roomvu_LocationMappings
              where ( m.LocationID == LocationID 
                 && ( StartDate <= m.EndDate && m.StartDate <= EndDate ) )
              select m ).Skip( StartRows ).Take( MaximumRows );

        IQueryable<roomvu_User> Users =
              from Mapping in Mappings
              join User in context.roomvu_Users
                on Mapping.UserID equals User.ID into j
              select j.Single();

        return Users.ToList(); // Force execution
    }
}