Calculate Average

Question

Hi

how can I calculate the average on a mobile services field. this is my attempt but it returns null...

   private async void AvgRatings()
        {
           
            Int32 PicID;           
            var selectedItem = (PictureViewModel)this.itemsViewSource.View.CurrentItem;
            if (selectedItem != null)
            {
                PicID = selectedItem.UniqueId;

                itemsR = await ratingstable
                        .Where(ratings => ratings.RatingsPictureID == PicID)
                        .ToCollectionAsync();
           

                var average = itemsR.Where(x => x.RatingsPictureID == PicID).Select(x => x.MyRating).Average();
                xRating.Value = average;
            }    

        }

Answer 1

Must be a better way?

Answer 2

If you want to programmatically query data (instead of binding it to a UI component), don't use the ToCollectionAsync method - use ToListAsync or ToEnumerableAsync instead. Here's an example of a code which would do what you want:

PicID = selectedItem.UniqueId;

itemsR = await ratingstable
        .Where(rating=> rating.RatingsPictureID == PicID)
        .Select(rating => rating.MyRating)
        .ToEnumerableAsync();

var average = itemsR.Average();
xRating.Value = average;

Now, to do that you still need to retrieve all items from the database and then calculate the average. Another alternative is to do this at the server side, where you can do it with a custom API. For example, this would be an API which would ask SQL directly to calculate the average

exports.get = function(request, response) {
    var picId = request.query.picId;
    var sql = 'SELECT AVG(MyRating) FROM RatingsTable WHERE RatingsPictureID = ?';
    request.service.mssql.query(sql, [picId], {
        success: function(results) {
            response.send(200, results[0]);
        }
    });
}

And on the client side you'd call it like this (built by notepad, I may have made some syntax errors here):

var apiParams = new Dictionary<string, string> { { "picId", PicID } };
var average = await MobileService.InvokeApiAsync<double>("avgRating", HttpMethod.Get, apiParams);

---

Carlos Figueira

Answer 3

Yeah, make it a User-Defined Function on your SQL database (which can query data), then add the function as a column in your Pictures table. Using this trick in my ZuMo instance for the exact same thing, and I love it.

I wrote a blog post about it here that goes over the solution in detail. I hope it helps!

Answer 4

This worked a treat Thank you

Answer 5

Hi Carlos

I followed the example from Robert that worked from the server side.

But I am still keen to work see client side for future reference.

If I change to   .ToEnumerableAsync(); or ToListAsync

await throws error:

Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<CW.DataModel.Ratings>

Thanks

Answer 6

Is it the full error message? This error usually says something like "Cannot implicit convert type 'Type1' to 'Type2''. In which line do you get that exception?

---

Carlos Figueira