Fairly complex LINQ to Entities query

I have two entities, assume they are called Container and Record. They have a master-child relationship: a 'container' can hold many records.

The Records table in the database has the following columns:

• Id
• Date
• Container_Id
• RecordType_Id

The Record entity does not have any navigation properties that back reference the Container.

I am writing a LINQ query for my repository that will retrieve ONLY the records for a container that have the most recent date for each RecordType_Id. All older records should be ignored.

So if a container has say 5 records, one for each RecordType_Id, with the date 24/May/2011. But also has another 5 r开发者_开发知识库ecords for each RecordType_Id but with the date 20/May/2011. Then only the first 5 with the 24/May date will be retrieved and added to the collection in the container.

I came up with an SQL query that does what I need (but maybe there is some more efficient way?):

select t.*
from Records t
    inner join (
        select Container_Id, RecordType_Id, max(Date) AS MaxDate
        from Records
        group by Container_Id, RecordType_Id ) g
    on t.Date = g.MaxDate
        and t.Container_Id = g.Container_Id
        and t.RecordType_Id = g.RecordType_Id 
order by t.Container_Id 
    , t.RecordType_Id 
    , t.Date

However I am struggling to translate this into a proper LINQ query. EF is already generating a fairly large query all by itself just to load the entities, which makes me unsure of how much of this SQL query is actually relevant to the LINQ query.

Off the top of my head:

var q = from c in Container
        from r in c.Records
        group r by r.RecordType.RecordType_Id into g
        select new
        {
            Container = c,
            RecordType_Id = g.Key,
            Records = from gr in g
                      let maxDate = g.Max(d => d.Date)
                      where gr.Date == maxDate
                      select gr
        };

Try using LinqPad, it helps you test linq queries easily. Even against an existing EF model (which is in your project). Visit http://www.linqpad.net/