CODEWITHSUNDEEP
c#linqentity-framework
jt000
jt000

Reputation: 3236

Linq to Entities doesn't return same value as foreach

Note: I am using EF 6.1.2

I have a database with Transactions that will be available on a particular date. I have a method that is supposed to return the currently available balance, but when run in EF it returns a different value than if run normally...

My Entity class looks like this:

public class Transaction
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public int AmountInCents { get; set; }
    public DateTimeOffset DateAvailable { get; set; }
}

This method returns the correct available amount:

double GetAvailableBalance(DbProxy dbProxy, DateTimeOffset atTime)
{
    var amount = 0d;
    var transactions = dbProxy.Transactions.ToList();
    foreach (var transaction in transactions)
    {
        if (transaction.DateAvailable <= atTime)
        {
            amount += transaction.AmountInCents/100d;
        }
    }
}

This version returns 0:

double GetAvailableBalance(DbProxy dbProxy, DateTimeOffset atTime)
{
    return await dbProxy.Transactions
        .Where(t => t.DateAvailable <= atTime)
        .Select(t => t.AmountInCents / 100d)
        .Union(new[] { 0d })
        .SumAsync();
}

I'm pretty stumped why this isn't working... Can anyone see what the issue might be?

Upvotes: 0

Views: 191

Answers (1)

DLeh
DLeh

Reputation: 24395

The union with 0 is what's causing the issue. You should change this to use DefaultIfEmpty() or you could do Concat(new[] { 0 }) instead.

Again, I'd like to stress that you should probably be using decimal if you're dealing with dollar amounts, otherwise you may encounter wonky math operations that could result in real money being unaccounted for or lost.

double GetAvailableBalance(DbProxy dbProxy, DateTimeOffset atTime)
{
    return await dbProxy.Transactions
        .Where(t => t.DateAvailable <= atTime)
        .Select(t => t.AmountInCents / 100d)
        .DefaultIfEmpty()
        .SumAsync();
}

double GetAvailableBalance(DbProxy dbProxy, DateTimeOffset atTime)
{
    return await dbProxy.Transactions
        .Where(t => t.DateAvailable <= atTime)
        .Select(t => t.AmountInCents / 100d)
        .Concat(new[] { 0 })
        .SumAsync();
}

If you must use doubles, you should at least convert to a double as the last step:

    return await dbProxy.Transactions
        .Where(t => t.DateAvailable <= atTime)
        .Select(t => t.AmountInCents)
        .DefaultIfEmpty()
        .SumAsync() / 100d;

Upvotes: 1

Related Questions