I need help finding a record in an Entity Framework context using a lambda expression

Question

I have an Entity Framework context with an object called "LengthDatas" and a property/column called "AbsoluteCounter" that is a string value. The string value represents a decimal number. I would like to use a lambda expression to find the largest one. How would I modify my expression to achieve this ?

context.LengthDatas.Where(c => c.AbsoluteCounter.Last())

(I know the column should be decimal and I will convert this over to a decimal value at a later time)

Thanks

score 2 · Answer 1 · edited May 23 '17 at 11:49

2

Use OrderByDescending and First(OrDefault) combination:

context.LengthDatas.OrderByDescending(c => c.AbsoluteCounter).FirstOrDefault();

But to really get decimal comparison (not string) you have to define your own method, as described in an answer to Convert string to decimal in group join linq query question.

edited May 23 '17 at 11:49

Community

1
1

answered Apr 12 '13 at 12:38

MarcinJuraszek

124,003
15
196
263

It's important the conversion to decimal is done first. Ordering strings doesn't ensure the largest number(or even string) will be the first one. – Giorgio Minardi Apr 12 '13 at 13:46
How did you get Decimal.Parse(c.AbsoluteCounter) to work with Linq to Entities? – Ackroydd Apr 12 '13 at 14:59
I'm getting this error message: LINQ to Entities does not recognize the method 'System.Decimal Parse(System.String)' method, and this method cannot be translated into a store expression – Ackroydd Apr 12 '13 at 15:04
Check out that answer: [Convert string to decimal in group join linq query](http://stackoverflow.com/questions/12216043/convert-string-to-decimal-in-group-join-linq-query) – MarcinJuraszek Apr 12 '13 at 15:07
Very nice, I'll give that a try. You should have referenced it in you answer above. – Ackroydd Apr 12 '13 at 15:10
Is there an equivalent when using code first? I don't actually have an EDMX. – Ackroydd Apr 12 '13 at 15:13

Giorgio Minardi · Answer 2 · 2013-04-12T12:47:36.573

2

You should do it first (switch to decimal) , then you can easily change your query to be :

context.LengthDatas.OrderByDescending(c => c.AbsoluteCounter))

If absolutecounter is decimal then the query will order it and the first element will be the largest

edited Apr 12 '13 at 12:47

answered Apr 12 '13 at 12:39

Giorgio Minardi

2,765
1
15
11

Ackroydd · Answer 3 · 2013-04-12T17:37:19.113

Sorting by string can fail if the data is malformed (leading spaces, alphas). If you can afford to bring the data into memory, this should cut out the loopholes:

        var result = db.LengthDatas
            .ToList()
            .Select(c =>
            {
                decimal d;
                Decimal.TryParse(c.AbsoluteCounter, out d);
                return d;
            })
            .Max();

But if you're confident about the data quality, just use

        var result1 = db.LengthDatas.Max(c => c.AbsoluteCounter);

ADDENDUM

To round out the discussion about converting the string to decimal on the server instead of in memory, I tried out the technique @MarcinJuraszek references above.

I'm testing in a Code First app, but added an EDMX by adding an ADO.Net Entity Data Model to the project. There's a few things to tidy up, but essentially the following type of query will work

var result = db.LengthDatas.Max(c => EdmxExtensions.DecimalParse(c.AbsoluteCounter));

The SQL generated by Linq to Entities is

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    MAX( CAST( [Extent1].[AbsoluteCounter] AS decimal(12,2))) AS [A1]
    FROM [dbo].[LengthDatas] AS [Extent1]
)  AS [GroupBy1]

However, should note that this is not a robust conversion as you get with c# Decimal.TryParse. It will throw an exception with malformed strings.

I need help finding a record in an Entity Framework context using a lambda expression

3 Answers3