How do I write this cross apply query in LINQ-to-SQL?

Question

I have the following tables:

create table TableA (     Id int primary key identity,     Key int not null )  create table TableB (     Id int primary key identity,     TableA_Id int not null foreign key references TableA(Id),     Value varchar(80) not null ) 

I would like to write the following query in LINQ-to-SQL using lambda notation:

select TableA.Key, b.Value from TableA cross apply (     select top 10 TableB.Value     from TableB     where TableA.Id = TableB.TableA_Id     order by TableB.Value ) b where TableA.Key between 0 and 999 

How would I do this?

Answer 1

This should do the trick

var query = from a in context.TableA             from b in context.TableB                              .Where(x => x.TableA_Id == a.Id)                              .OrderBy(x => x.Value)                              .Take(10)             where a.Key >= 0 && a.Key <= 999             select new             {               a.Key,               b.Value,             }; 

Answer 2

//get latest activity info for every user

 var query = ActivityRepository.Where(p => p.iAction > -1 &&      userIds.Contains(p.iSellerId)).GroupBy(c => c.iSellerId).Select(c => c.OrderByDescending(cc => cc.dBeginTime).First()).Select(a => new ActivityInfo          {             ActivityId = a.iActivityId,             StartTime = a.dBeginTime,             SellerId = a.iSellerId,             EndTime = a.dEndTime,             ActivityName = a.sName,         }); 

this code will generate outer apply sytax.