CODEWITHSUNDEEP
entity-frameworkentity-framework-4linq-to-entitiesentity-framework-4.1
nbevans
nbevans

Reputation: 7949

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:

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 records 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.

Upvotes: 1

Views: 848

Answers (2)

Numan
Numan

Reputation: 3948

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/

Upvotes: 1

Craig Stuntz
Craig Stuntz

Reputation: 126547

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

Upvotes: 1

Related Questions