CODEWITHSUNDEEP
c#linqlinq-to-entities
Cybercop
Cybercop

Reputation: 8678

Linq: Order by date and Find the first element

I want to query a datatable where i select list of rows based on some data and order the rows by date and get the row with latest date. This is what I did

var propertyValueId = _dbSis.Set<PropertyValue>()
                            .Where(m => m.PropertyInstanceId == id)
                            .OrderBy(z => z.TimeStamp);
var pvalueId = propertyValueId.ElementAtOrDefault(0);

but I get error on propertyValueId.ElementAtOrDetault(0);

LINQ to Entities does not recognize the method 'Sorama.DataModel.SIS.Configuration.PropertyValue ElementAtOrDefault[PropertyValue](System.Linq.IQueryable`1[Sorama.DataModel.SIS.Configuration.PropertyValue], Int32)' method, and this method cannot be translated into a store expression.

How can I achieve what I just expected, and how can i solve the error?

Upvotes: 0

Views: 3760

Answers (3)

Anatolii Gabuza
Anatolii Gabuza

Reputation: 6260

The reason why this error occurs is that Linq to Entities does not support lot of Linq to Objects methods. It is because Linq to Entities converts your query to expression tree that then will be executed on SQL.
Full list of supported and unsupported methods you can find here.

And as @Gusdor mentioned: The unrecognized method in the expression (which your lambdas are resolving to) is being treated as a Stored Procedure which in turn does not exist. Easy way to check what will happen is to look at the parameter list for the LINQ method.

So to fix your problem you should use allowed method, like FirstOrDefault:

var pvalueId = propertyValueId.FirstOrDefault();

Upvotes: 1

AlexB
AlexB

Reputation: 7416

Could you try :

var propertyValueId = _dbSis.Set<PropertyValue()
                            .Where(m=>m.PropertyInstanceId==id)
                            .OrderBy(z=>z.TimeStamp).FirstOrDefault();

Upvotes: 0

user7116
user7116

Reputation: 64068

Seems strange that FirstOrDefault() does not work.

var pvalueId = propertyValueId.FirstOrDefault();

Should that not be supported:

var pvalueId = propertyValueId.Take(1).SingleOrDefault();

Or, if for some bizarre reason your L2EF provider cannot handle FirstOrDefault, Take, or SingleOrDefault, you can brute force it:

PropertyValue pvalueId = null;
foreach (var pv in propertyValueId)
{
    pvalueId = pv;
    break;
}

Upvotes: 0

Related Questions