Malfunction linq - the newest entry in the database

Question

decimal tmpPreviousSellingRate = db.DataServiceCurrenciesRates
                                   .Where(x => x.CurrencyId == tmpCurrencyId)
                                   .OrderByDescending(x => x.PublicationDate)
                                   .Select(x => x.SellingRate)
                                   .DefaultIfEmpty()
                                   .First();

In db i have:

CurrencyId: 1 PublicationDate: '2014-08-19' SellingRate: 3,4530
CurrencyId: 2 PublicationDate: '2014-08-19' SellingRate: 0,6117
CurrencyId: 3 PublicationDate: '2014-08-19' SellingRate: 1,3570

CurrencyId: 1 PublicationDate: '2014-08-20' SellingRate: 3,3753
CurrencyId: 2 PublicationDate: '2014-08-20' SellingRate: 0,5442
CurrencyId: 3 PublicationDate: '2014-08-20' SellingRate: 1,5478

CurrencyId: 1 PublicationDate: '2014-08-21' SellingRate: 3,38263
CurrencyId: 2 PublicationDate: '2014-08-21' SellingRate: 0,5837
CurrencyId: 3 PublicationDate: '2014-08-21' SellingRate: 1,4635

When CurrentId is 2, I want to get 0,5837, but in tmpPreviousSellingRate is 0,6117 (from 2014-08-19)

I used OrderByDescending and OrderBy - the result is the same, allways 0,6117.

With .Last() instead of .First() I got a error

"Additional information: LINQ to Entities does not recognize the method 'System.Decimal Last[Decimal](System.Linq.IQueryable`1[System.Decimal])' method, and this method cannot be translated into a store expression."

Did you check if the Where clause returns the three entries related to CurrencyId == 2? Try to remove DefaultIfEmpty() and First(), you should retrieve the ordered list. — Francesco De Lisi, Aug 21 '14 at 08:40
`PublicationDate` is `date`. No, I did not check I'll do it now. — Bogusz Michałowski, Aug 21 '14 at 08:44
`Last()` [can't be mapped to a Sql query](http://stackoverflow.com/a/7293652/314291). If the amount of data after the `Where` predicate is small, you might materialize the data with `.ToList()` before applying the rest of the chain. Also, you should be able to replace the last 2 functions with a single `FirstOrDefault()` — StuartLC, Aug 21 '14 at 08:57
When I removed `.DefaultIfEmpty().First()` at all - Yes, there were 3 entries in correct order. But some bad happend in `.DefaultIfEmpty` and after it, I had only 1 entries `0,6117` - the wrong one. — Bogusz Michałowski, Aug 21 '14 at 09:18

Jason Evans · Accepted Answer · 2014-08-21T10:06:00.197

I've tried to replicate your problem, but I managed to get the expected result from this code:

public class DBData
{
    public int CurrencyId { get; set; }
    public DateTime PublicationDate { get; set; }
    public decimal SellingRate { get; set; }
}

public class Program
{
    private static void Main(string[] args)
    {
        var items = new List<DBData>();

        items.Add(new DBData { CurrencyId = 1, PublicationDate = DateTime.Parse("2014-08-19"), SellingRate = 34530m });
        items.Add(new DBData { CurrencyId = 2, PublicationDate = DateTime.Parse("2014-08-19"), SellingRate = 6117m });
        items.Add(new DBData { CurrencyId = 3, PublicationDate = DateTime.Parse("2014-08-19"), SellingRate = 13570m });
        items.Add(new DBData { CurrencyId = 1, PublicationDate = DateTime.Parse("2014-08-21"), SellingRate = 338263m });

        decimal result = items
            .Where(x => x.CurrencyId == 1)
            .OrderByDescending(x => x.PublicationDate)
            .Select(x => x.SellingRate)
            .FirstOrDefault();
    }
}

The result I see is 338263m which, I believe, is similar to the result you expected.

Not really an answer, but I will leave this code here for now, in case it proves useful. If not, I will delete my answer to clean up.

EDIT:

I changed the LINQ code to use FirstOrDefault() as that is the more consistent method of getting an item instance, or a default (i.e. NULL for reference types) object, rather then calling DefaultIfEmpty() and First() separately.

I think it's different to run a LINQ generated query against the DB. In fact `DefaultIfEmpty()` and `First()` usually mix up the result. — Francesco De Lisi, Aug 21 '14 at 09:12
@JasonEvans Please, see my answer. Your original question contained exactly the same code that failed in the question. Now you've changed it to something that will work, but with no explanation. — JotaBe, Aug 21 '14 at 09:54

score 1 · Answer 2 · answered Aug 21 '14 at 09:05

1

It's strange, but the change that text

.DefaultIfEmpty()
.First();

to this

.FirstOrDefault();

worked.

answered Aug 21 '14 at 09:05

Bogusz Michałowski

342
4
21

Doh, I missed that in my code. But yes, that makes much more sense to use `FirstOrDefault()`. – Jason Evans Aug 21 '14 at 09:10

score 1 · Answer 3 · answered Aug 21 '14 at 09:53

You must be very careful with the methods invoked after OrderBy, because they can make it lose the order. This has to do with the lazy execution of the query: in LINQ to EF the LINQ query isn't converted into SQL until it's necessary, i.e. until you materialize it.

When you invoke OrderBy it returns an IOrderedQueryable<T>, which has information on the ordering. At this point, you can do three different things:

materialize the query, using any method like ToList or First. When doing so, the ordering information is available, and used
call any method (or extension method) of IOrederedQueryable which returns a new IOrderedQueryable. When you do so, the ordering information still exists. For example you can call ThenBy, which will add ordering information and return a new IOrderedQueryable. When you materialize this query, as explained in 1, the ordering information is used
and finally, you can call any other extension method that loses the ordering information. This is possible because an IOrderedQueryable is also IQueryable. So, you can call any extension method that gets an IOrderedQueryable, but returns a simple IQueryable. If you do so, the ordering information is lost, and, when you materialize the query, like in 1, the order is lost.

To avoid this problem you should execute the OrderBy, in the last possible moment, to guarantee that the ordering information is kept until the query materialization.

In your case, the DefaultIfEmpty extension method returns an IQueryable, so the ordering information is not available when you materialize it with First.

However, when you use FirstOrDefault you're materializing an IOrderedQueryable, and that makes it work. I.e. when you call this method, the SQL query is created, and the ordering information is still available.

When you use Linq to Objects, it works in a different way. It uses IOrderedEnumerable which is quite a different animal. So, you can not assure that all that works with LINQ to Objects also works with LINQ to EF.

Malfunction linq - the newest entry in the database

3 Answers3