Linq to Entites and ObjectQuery

Question

Hi

I have Person enity which has a 1 : N relationship with Person_Addresses (fields: PersonID, AddressID, ValidFrom). I want to get all Person records and associated Person_Addresses with only latest ValidFrom. How should i do this using ObjectQuery or IQueryable?

Edit: I mentioned ObjectQuery and IQueryable because i wanted to have a solution using extension methods (i think, that how it's called). Also i forgot to mention that i'm using EF where i have the entities generated. I want to get a person object which has it's person_adress member eagerly loaded.

Here are the entities structure: Person members: int id, string firstname, string lastname, Partner_Address partneradress

Person_Address members: int personid, int adressid, date validfrom

Answer 1

Try this

I have the following Entities

    //Person Entity
    public class Person
    {
        public int PersonID  { get; set; }
        public string PersonName { get; set; }
    }
    //PersonAddress Entity
    public class PersonAddress
    {
        public int PersonID { get; set; }
        public int AddressID { get; set; }
        public DateTime ValidFrom { get; set; }
    }

Then fire the below query

//Get the latest ValidFrom for each person from PersonAddress
var getLatestDateRecords =      
from p in lstPersonAddress
group p by p.PersonID into g
select new 
{ 
    Infos = 
        (from PA in g
        select new 
        {
         PersonId = PA.PersonID
        ,Date = g.Max(t=>t.ValidFrom)
        }).Distinct()
};

//Segregate the ValidFroms  and PersonId from the  
//previous record set(getLatestDateRecords)
var segRecords = 
    from x in getLatestDateRecords
    from y in x.Infos                     
    select new { Date = y.Date, PersonId = y.PersonId };

//Obtain all the relevant information from the PersonAddress
// for the latest ValidFrom dates
var allValidRecords = 
    from PA in lstPersonAddress
    join x in segRecords
    on PA.ValidFrom equals x.Date                      
    where PA.PersonID == x.PersonId
    select new { 
            PersonId = PA.PersonID
            , AddressId = PA.AddressID
            , Date = PA.ValidFrom 
        };

//Get the final result
var resultSet = 
    from p in lstPerson
    join x in allValidRecords
    on p.PersonID equals x.PersonId
   select new
   {
       PersonId = p.PersonID
       ,PersonName = p.PersonName
       ,AddressId = x.AddressId,
       Date = x.Date
   };

I found it to be working fine with some test data.

Let me know in case of any concern.