How do I do this in LINQ

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 == Ma开发者_Python百科pping.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

---

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.

---

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
    }
}

---

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