CODEWITHSUNDEEP
entity-framework
Ian
Ian

Reputation: 4909

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

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());

Upvotes: 5

Views: 25752

Answers (4)

Gerard
Gerard

Reputation: 483

I think from a simplicity point of view, this should be same result but faster as does not require two round trips through EF to sql server, you always want to execute query as few times as possible for latency, as the Id field is primary key and indexed, should be performant

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

Should be equivalent of sql query

SELECT TOP 1 * FROM Entities Order By id desc

so to include search term

string predicate = "name";

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

Upvotes: 2

user2408551
user2408551

Reputation: 19

this is the easiest way to get max

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

Upvotes: 1

qxn
qxn

Reputation: 17584

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;

Upvotes: 1

marc_s
marc_s

Reputation: 754468

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.

Upvotes: 7

Related Questions