CODEWITHSUNDEEP
linqlinq-to-sql
Alan
Alan

Reputation: 21

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

Upvotes: 2

Views: 117

Answers (3)

dahlbyk
dahlbyk

Reputation: 77590

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

Upvotes: 1

Neil
Neil

Reputation: 1922

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

Upvotes: 0

Rex M
Rex M

Reputation: 144172

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.

Upvotes: 1

Related Questions