6 replies

Last post Feb 05, 2016 09:16 AM by Zhi Lv - MSFT

• Captain Plan...

Member

23 Points

193 Posts

Linq - rounding up the average?

Feb 01, 2016 11:18 AM|Captain Planet|LINK

Hi.

If I have the following nested query:

`AverageRating = (from pqr in db.PQuote where pqr.PQuoteRating != null select pqr.PQuoteRating).Average()`

How can I round that average up to the nearest whole number?  I've tried this but it doesn't like it:

`AverageRating = Math.Round((from pqr in db.PQuote where pqr.PQuoteRating != null select pqr.PQuoteRating).Average())`

Thanks...

• Lokesh B R

Star

9011 Points

2408 Posts

Re: Linq - rounding up the average?

Feb 01, 2016 11:29 AM|Lokesh B R|LINK

Hi,

try this

```var query = from p in db.Table1
let AverageRating = (from pqr in db.PQuote where pqr.PQuoteRating != null select pqr.PQuoteRating).Average()
select new {                           Name = p.Name,                           AverageRate = Math.Round(AverageRating)                        };
```

Mark the post as answer, if it helps you.
• Captain Plan...

Member

23 Points

193 Posts

Re: Linq - rounding up the average?

Feb 01, 2016 05:22 PM|Captain Planet|LINK

....this looks good thanks.  I'll get back to you once I've tested....

• Zhi Lv - MSF...

All-Star

44701 Points

6713 Posts

Microsoft

Re: Linq - rounding up the average?

Feb 02, 2016 04:20 AM|Zhi Lv - MSFT|LINK

Hi Captain Planet,

When we use Average() method, it will return a decimal? type value. So, before using the Math.Round method, you could use Convert.ToDecimal() method to convert the average value. Please refer to the following code:

`AverageRating = Math.Round(Convert.ToDecimal((from pqr in db.PQuote where pqr.PQuoteRating != null select pqr.PQuoteRating).Average()));`

Best regards,
Dillion

MSDN Community Support
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
• Captain Plan...

Member

23 Points

193 Posts

Re: Linq - rounding up the average?

Feb 04, 2016 01:50 PM|Captain Planet|LINK

Thanks guys - just checking....sorry for the delay.  My first test I get:

Additional information: LINQ to Entities does not recognize the method 'System.Decimal ToDecimal(System.Object)' method, and this method cannot be translated into a store expression.

• Lokesh B R

Star

9011 Points

2408 Posts

Re: Linq - rounding up the average?

Feb 05, 2016 05:48 AM|Lokesh B R|LINK

Hi,

did you try my query?

Mark the post as answer, if it helps you.
• Zhi Lv - MSF...

All-Star

44701 Points

6713 Posts

Microsoft

Re: Linq - rounding up the average?

Feb 05, 2016 09:16 AM|Zhi Lv - MSFT|LINK

Hi Captain Planet,

Captain Planet

Additional information: LINQ to Entities does not recognize the method 'System.Decimal ToDecimal(System.Object)' method, and this method cannot be translated into a store expression.

I suggest you could post the whole code about this LINQ query statement. In my previous reply the ToDecimal is outside the LINQ statement. Code like this:

```Var AverageRating = Math.Round(Convert.ToDecimal((from pqr in db.PQuote where pqr.PQuoteRating != null select pqr.PQuoteRating).Average()));

```

The boldfaced words are the LINQ statement.

Best regards,
Dillion

MSDN Community Support