LINQ to Entities - Rounding to the nearest dollar, nearest half-dollar, or not at all

Question

I need to round FinalPrice the following ways based on an identifier:

0 = Not at all
1 = Up to the next Dollar
2 = Up to the next Half-dollar

Example of the expected rounding:

ID 0: 133.15 => 133.15
ID 1: 133.15 => 134.00
ID 2: 133.15 => 133.50

 public IQueryable<MyObj> GetPrices()
    {
        int roundingId = 0; //0 = no rounding. 1 = dollar rounding. 2 = half-dollar rounding.

        var prices = from ps in ObjectContext.Products
        select new MyObj
        {
             FinalPrice = (ps.IsCustomPrice ? ps.CustomPrice : ps.Retail),
        }

        return prices;
    }

I wish I could use a custom function to do this rounding in LINQ to Entities...

What's your question here? If you want to make it look fluent, you could create an extension method to round decimals (or are they floats? some other type?) the way you're looking to have them rounded here. But yes - Math.Round() doesn't do this out of the box for you. — 48klocs, Feb 16 '12 at 16:56
I don't believe you can create extension methods within a LINQ query. The CustomPrice and Retail properties are decimals. Yeah, I wish .Round() would have some more options for me. — Greg Snider, Feb 16 '12 at 17:17
You can do whatever you want inside of a Linq query; if you're talking Linq to Entities/Linq to SQL then yes, you're quite a bit more restricted in what you can do inside of the query. If you reify that anonymous type into a defined type, you could fluently perform the rounding after the data's been retrieved. — 48klocs, Feb 16 '12 at 17:27
"If you reify that anonymous type into a defined type, you could fluently perform the rounding after the data's been retrieved" - How would I go about doing this? I can create a defined type for it. How would i perform the rounding after the data has been received? I would like to have this contained within this one function because a lot of different areas and websites will be using it. — Greg Snider, Feb 16 '12 at 17:48

score 2 · Accepted Answer · answered Feb 16 '12 at 17:53

This will work for you. You can clean it up for your own use, but basically you are just using the % modulo operator to get the cents and the ? : ternary operator to select the correct value

var prices = from ps in ObjectContext.Products
             let cents = ps.Price % 1
             let upToDollar = cents > 0 ? (ps.Price + 1 - cents) : ps.Price
             let upToHalfDollar = cents == 0 ? ps.Price : (cents < 0.5 ? (ps.Price + 0.5 - cents) : (ps.Price + 1 - cents))
             select new
             {
               FinalPrice = roundingId == 0 ? ps.Price : (roundingId == 1 ? upToDollar : upToHalfDollar)
             };

Nice, that's an elegant solution. – Grant H. Feb 16 '12 at 18:20 — Grant H., Feb 16 '12 at 18:20

score 1 · Answer 2 · answered Feb 16 '12 at 16:54

1

You can do it all inline if you want, but honestly that seems like it'd be hard to read. Try this:

int roundingId = 0; //0 = no rounding. 1 = dollar rounding. 2 = half-dollar rounding.

    var prices = from ps in ObjectContext.Products
    select new
    {
         FinalPrice = GetPrice((ps.IsCustomPrice ? ps.CustomPrice : ps.Retail), roundingId),
    }

private double GetPrice(double price, int roundingOption)
{
    switch (roundingOption)
    {
        case 0:
        //Do stuff
        break;
        case 1:
        //Do stuff
        break;
        case 2:
        //Do stuff
        break;
    }
}

answered Feb 16 '12 at 16:54

Grant H.

3,689
2
35
53

Unfortunately you cannot call a method from within LINQ. I tried your example and received: LINQ to Entities does not recognize the method 'System.Decimal GetPrice(System.Decimal, Int32)' method, and this method cannot be translated into a store expression. – Greg Snider Feb 16 '12 at 17:15
1

Ah, you CAN call a method from within LINQ, but not LINQ to Entities, or LINQ to SQL, etc. – Grant H. Feb 16 '12 at 17:17
In that case, I'd recommend you just perform the operation after the query has returned. I don't see another option without simply nesting the inline bools as you have done above. – Grant H. Feb 16 '12 at 17:19
Nesting inline bools would work for me (I don't mind if it is a little messy), but combining that with Rounding to Half-dollar is giving me much trouble... – Greg Snider Feb 16 '12 at 17:20
Yeah I see your concern. What's the aversion to simply making the conversion after you've returned the data? Unless there are millions of entries, I can't see much of a performance penalty. – Grant H. Feb 16 '12 at 17:22
I simplified the example quite a bit... so it does actually perform several joins and a much larger select of hundreds - thousands of records. Not millions though... – Greg Snider Feb 16 '12 at 17:45

LINQ to Entities - Rounding to the nearest dollar, nearest half-dollar, or not at all

2 Answers2