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

Question

I have a collection of objects called Entities that has a Name field and a Version field. I want to return the object that has the highest version for the selected Name .

SQL wise i would 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 this with EF. I am trying to use CreateQuery<> with a textual representation of the query if this helps.

thanks

EDIT: Here's a working version with 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(); } 

This will first select the maximum value for Name from the Entities set, and then take the object from the set of objects corresponding to this name.

Answer 2

this is the easiest way to get max

 using (MyDBEntities db = new MyDBEntities()) { var maxReservationID = _db .LD_Customer.Select(r => r.CustomerID).Max(); } 

Answer 3

I think that from the point of view of simplicity this should be the same result, but faster, since it does not require two rounds through EF to the sql server, you always want to execute the query as little time as possible for the delay, since the Id field is the primary key and is indexed must be performed

 using(var db = new DataContext()) { var maxEntity = db.Entities.OrderByDecending(x=>x.Id).FirstOrDefault() } 

There should be an equivalent sql query

 SELECT TOP 1 * FROM Entities Order By id desc 

to enable search query

 string predicate = "name"; using(var db = new DataContext()) { var maxEntity = db.Entities .Where(x=>x.Name == predicate) .OrderByDecending(x=>x.Id) .FirstOrDefault() } 

Answer 4

I think something like this.?

  var maxVersion = (from t in table where t.name == "name" orderby t.version descending select t.version).FirstOrDefault(); var star = from t in table where t.name == "name" && t.version == maxVersion select t; 

Or, as one statement:

  var star = from t in table let maxVersion = ( from v in table where v.name == "name" orderby v.version descending select v.version).FirstOrDefault() where t.name == "name" && t.version == maxVersion select t;