Entity Framework - Select * from Entities where Id = (select max(Id) from Entities)

Question

I have an entity set called Entities which has a field Name and a field Version. I wish to return the object having the highest version for the selected Name.

SQL wise I'd go

Select * 
from table 
where name = 'name' and version = (select max(version)  
                                   from table 
                                   where name = 'name')

Or something similar. Not sure how to achieve that with EF. I'm trying to use CreateQuery<> with a textual representation of the query if that helps.

Thanks

EDIT: Here's a working version using two queries. Not what I want, seems very inefficient.

var container = new TheModelContainer();
var query = container.CreateQuery<SimpleEntity>(
    "SELECT VALUE i FROM SimpleEntities AS i WHERE i.Name = 'Test' ORDER BY i.Version desc");
var entity = query.Execute(MergeOption.OverwriteChanges).FirstOrDefault();
query =
    container.CreateQuery<SimpleEntity>(
        "SELECT VALUE i FROM SimpleEntities AS i WHERE i.Name = 'Test' AND i.Version =" + entity.Version);
var entity2 = query.Execute(MergeOption.OverwriteChanges);
Console.WriteLine(entity2.GetType().ToString());

Answer 1

Can you try something like this?

using(var container = new TheModelContainer())
{
    string maxEntityName = container.Entities.Max(e => e.Name);
    Entity maxEntity = container.Entities
                           .Where(e => e.Name == maxEntityName)
                           .FirstOrDefault();
}

That would select the maximum value for Name from the Entities set first, and then grab the entity from the entity set that matches that name.