ef and linq extension method

Question

Hi

I have this sql that i want to have written in linq extension method returning an entity from my edm:

SELECT p.[Id],p.[Firstname],p.[Lastname],prt.[AddressId],prt.[Street],prt.[City] 
FROM [Person] p 
CROSS APPLY ( 
    SELECT TOP(1) pa.[AddressId],a.[ValidFrom],a.[Street],a.[City]
    FROM [Person_Addresses] pa 
    LEFT OUTER JOIN [Addresses] AS a
    ON a.[Id] = pa.[AddressId]
    WHERE p.[Id] = pa.[PersonId] 
    ORDER BY a.[ValidFrom] DESC ) prt 

Also could this be re-written in linq extension method using 3 joins?

Answer 1

Assuming you have set the Person_Addresses table up as a pure relation table (i.e., with no data besides the foreign keys) this should do the trick:

    var persons = model.People
      .Select(p => new { p = p, a = p.Addresses.OrderByDescending(a=>a.ValidFrom).First() })
      .Select(p => new { p.p.Id, p.p.Firstname, p.p.LastName, AddressId = p.a.Id, p.a.Street, p.a.City });

The first Select() orders the addresses and picks the latest one, and the second one returns an anonymous type with the properties specified in your query.

If you have more data in your relation table you're gonna have to use joins but this way you're free from them. In my opinion, this is more easy to read.

NOTE: You might get an exception if any entry in Persons have no addresses connected to them, although I haven't tried it out.