Linq to SQL using IQueryable API for 1 to many relationship

Assume A is a parent table with many B records. Essentially I need LINQ to S开发者_运维问答QL to generate this query:

Select * from A
Join B n on B.Id = A.Id
where A.OtherId in (0,1,2,3)
and B.DateTime >= '2011-02-03 00:30:00.000'
and A.TypeId = 1
order by B.DateTime

The LINQ I have looks like this:

List<string> programIds = new List<string>("0", "1", "2", "3");  
IQueryable<A> query = db.As;
  query = query.Where(a => programIds.Contains(a.ProgramId));
  query = query.Where(a => a.B.Any(b => b.DateTime >= ('2011-02-03 00:30:00.000')));

The problem begins on this last statement, the generated query then looks like this:

?query

{SELECT *
FROM [dbo].[A] AS [A]
WHERE (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[B] AS [B]
    WHERE ([B].[AirsOnStartDateTime] >= @p0) AND ([B].[Id] = [A].[Id])
    )) AND ((CONVERT(BigInt,[A].[OtherId])) IN (@p1, @p2, @p3, @p4))
}

Any ideas?

Sorry, I'm typing this up in notepad and can't verify the syntax at the moment, but I think something like this is what you are looking for:

List<string> programIds = new List<string>("0", "1", "2", "3");
var query = from a in db.As
            from b in db.Bs
        where programIds.Contains(a.ProgramID)
        &&    a.TypeID == 1
        &&    a.ID == b.ID
        &&    b.DateTime >= ('2011-02-03 00:30:00.000')
        select new 
        {
            a....
            b....
            ....                
        }

Just fill in the fields you want to return in the anonymous type select section.

Also, I made an assumption on the db.Bs being the way to get the B values out of your table... Fix that as appropriate.