c# - Finding Average using nested queries on another table -
i have common problem unable solve looking several forums on internet.
i have restaurant rating application uses asp.net webapi. app has 2 tables restaurant , comments table. each restaurant may have several comments , each comment has rating value. trying put method in webapi pull details restaurant table average rating value each restaurant looking comments table. attempt far in code below, doesn't work. have tried using aggregate function, average function, nested queries, joins, etc still unable pull average value. grateful if help?
public iqueryable<restaurantview> getrestaurants(string all) { var query = x in db.restaurants select new restaurantview { restaurantid = x.restaurantid, restaurantname = x.restaurantname, restaurantdecription = x.restaurantdecription restaurantratingaverage = (from in db.restaurants join b in db.comments on a.restaurantid equals b.commentsrestaurantid z c in z group c c.commentsrestaurantid g select new { ratingaverage = convert.todouble(g.average(a => a.commentsrating)) };) }; return query; }
update: using jonathan's technique (see below)
public iqueryable<restaurantview> getrestaurants(string all) { var query = x in db.restaurants select new restaurantview { restaurantid = x.restaurantid, restaurantname = x.restaurantname, restaurantdecription = x.restaurantdecription, restaurantratingaverage = (from in db.comments a.commentsrestaurantid.equals(x.restaurantid)select a.commentsrating).average() }; return query; }
but following exception now
error has occurred.","exceptionmessage":"the cast value type 'double' failed because materialized value null. either result type's generic parameter or query must use nullable type."
here's example can execute in linqpad
edit: updated show handling of nulls sub query
void main() { var restaurants = new list<restaurant>(); restaurants.add(new restaurant(1, "mcdonalds")); restaurants.add(new restaurant(2, "wendy's")); restaurants.add(new restaurant(3, "kfc")); var comments = new list<comment>(); comments.add(new comment(1, 1, "i love clowns!", 9.5)); comments.add(new comment(2, 1, "disgusting", 1.0)); comments.add(new comment(3, 1, "average", 5.0)); comments.add(new comment(4, 2, "hmmm tasty", 8.5)); comments.add(new comment(5, 2, "yuck", 4.0)); // edit - removed comment kfc, updated code below handle nulls var restaurantswithratings = restaurants.select(r => new { restaurantid = r.restaurantid, name = r.name, rating = ( comments.where(c => c.restaurantid == r.restaurantid) .select(c => c.rating) .defaultifempty(0) ).average() }); foreach(var r in restaurantswithratings) console.writeline("{0}: {1}", r.name, r.rating); } class restaurant { public restaurant(int restaurantid, string name) { restaurantid = restaurantid; name = name; } public int restaurantid { get; set; } public string name { get; set; } } class comment { public comment(int commentid, int restaurantid, string message, double rating) { commentid = commentid; restaurantid = restaurantid; message = message; rating = rating; } public int commentid { get; set; } public int restaurantid { get; set; } public string message { get; set; } public double rating { get; set; } }
Comments
Post a Comment