CODEWITHSUNDEEP
c#asp.netlinq
Hazel Wang
Hazel Wang

Reputation: 111

How could I query average and group by using LINQ in ASP.NET C#

I am a beginner of C#. I am familiar with SQLstatement, but not good in LINQ. Now what I need to do is using a LINQ statement query booking.PackageId and AvG(booking.Rating) Grouping by booking.Package. (I need to display the average rating according to different PackageID).

Here is my booking table

I want to use a listview for displaying all information

   <asp:ListView 
   ID="lvBookings" runat="server" 
   DataKeyNames="BookingId" 
   ItemType="TravelPlannerSystem.Models.Booking" 
   SelectMethod="GetFeedbacks">

This is my code behind for GetFeedbacks

public IQueryable<Booking> GetFeedbacks()
{
    var db = new TravelPlannerContext();
    var query = from booking in db.Bookings
              group booking by booking.PackageId into grouping
              select new {
              PackId = grouping.Key,
              AverageRating = grouping.Average(ed => ed.Rating)
              };
    return query;
}

It keeping gives me the error, I have been searching online for 2 days. I really don't know how to make change on this. Could anyone help here?

Upvotes: 1

Views: 231

Answers (2)

Gilad Green
Gilad Green

Reputation: 37281

The reason is that your function is expecting a return value of IQueryable<Booking> but you are returning IQueryable<AnonymousType>: Your projection (the select part) is creating a new {} which is an anonymous type with PackId and AverageRating.

This new type is not the Booking type and is why it is failing.

What you should do, as you can't return an anonymous type (and I think using dynamics here wouldn't be a good practice) is to create a new class and then:

public IQueryable<YourClass> GetFeedbacks()
{    
    return from booking in (new TravelPlannerContext()).Bookings
           group booking by booking.PackageId into grouping
           select new YourClass // Now you are projecting the expected type
           {
               PackId = grouping.Key,
               AverageRating = grouping.Average(ed => ed.Rating) ?? 0
           };
}

See how both the rerurn value and the projection are of the same type

Upvotes: 2

Wintergreen
Wintergreen

Reputation: 234

Try to convert this query .to before it passed to the view. use Tolist() to convert.

Upvotes: -1

Related Questions