CODEWITHSUNDEEP
c#entity-frameworklinqlinq-to-sql
Cosmin
Cosmin

Reputation: 585

Linq Query to sum only values above specific threshold

I am having a hard time isolating values above a certain threshold from a column. I need to calculate only the miles that exceed 100 and multiply that with a rate. For example, if I have 101 miles on one row, 102 miles on the next one and 103 on another day, my desired sum would be 1*myrate + 2*myrate + 3*myrate 

private void SetMiles()
    {
    int.TryParse(MDate.ToString("MM"), out int month);
    int.TryParse(MDate.ToString("yyyy"), out int year);

    TotalMiles = Decimal.Round(Convert.ToDecimal(MyTable
        .Where(a => a.Date <= MDate & a.Date.Month == MDate.Month & (a.Date.Year) == year & a.Miles > 100)
        .Select(a => a.Miles).Sum() * myrate), 2, MidpointRounding.AwayFromZero); 
    }

The above does not work properly but that is my starting point. How can I accomplish my goal and keep it as simple as possible?

Upvotes: 0

Views: 176

Answers (2)

Bakaji
Bakaji

Reputation: 88

you could make property inside your class 

public int Value {
        get {
            if (Miles > 100)
            {
                return (Miles - 100);
            }
            else
            {
                return 0;
            }

        }
    }

then use this

select(a=>a.Value).Sum()*rate;

since those which doesn't exceed 100 will return 0 so they don't effect your result the final code will be

TotalMiles = Decimal.Round(Convert.ToDecimal(MyTable
.Where(a => a.Date <= MDate && a.Date.Month == MDate.Month && (a.Date.Year) == year)
.select(a=>a.Value).Sum() * myrate), 2, MidpointRounding.AwayFromZero);

Upvotes: 1

mmathis
mmathis

Reputation: 1610

Change your Select to take the difference:

TotalMiles = Decimal.Round(Convert.ToDecimal(MyTable
    .Where(a => a.Date <= MDate && a.Date.Month == MDate.Month && (a.Date.Year) == year && a.Miles > 100)
    .Select(a => a.Miles - 100).Sum() * myrate), 2, MidpointRounding.AwayFromZero);

Also note that you need && as the logical AND operator

Upvotes: 1

Related Questions