Using Linq to Join,Group By and Count.... facing a trouble

Question

I have two tables, picture and pictureratings in my db.

public partial class picture
{
    public int idpicture { get; set; }
    public int iduser { get; set; }
    public string picTitle { get; set; }
    public string picFilename { get; set; }
    public System.DateTime pictime { get; set; }
    public int nuditylevel { get; set; }
    public int fakeslevel { get; set; }

    // This property will hold the total accumulated/summed 
    // up rating of a picture
    public int totalrating { get; set; }    
}

public partial class pictureratings 
{
    public int idrating { get; set; }
    public int idpictures { get; set; }
    public int iduser { get; set; }
    public System.DateTime iddatetime { get; set; }
    public int iduserrateddby { get; set; }
    public int rating { get; set; } 
}

For every rating a new pictureratings row will be created. I want to group pictureratings table by the picture id and then count the likes. I want to show those likes in picture table in totalrating property.

So as per my research till now, I am able to write this code

var total = from p in db.picturedetails
            join l in db.picturelikes on p.idpictures equals l.idpictures 
            group l by l.idpictures into g
            select new 
            {
                IdUserPic = g.First().iduser,
                IdPictures = g.First().idpictures,
                totalrating = g.Count()    
            }

I am using web api to return query total. So here comes my problem: how to show the picture properties like iduser, picTitle, picFilename, pictime, nuditylevel and fakeslevel? Am I doing it right? How should I do it?

You don't tell us _how_ you're "showing" the properties so how can we say? — Grant Thomas, Sep 25 '13 at 14:11
Thanks for asking , i am using web api that shows all the querry total data in a json. — Obvious, Sep 25 '13 at 14:14
Check this post http://stackoverflow.com/questions/9173410/linq-combining-join-and-group-by — Esteban Elverdin, Sep 25 '13 at 14:19

Tim S. · Answer 1 · 2013-09-25T16:45:45.430

1

I think a group join is what you're looking for:

var total = from p in db.picturedetails
            join l in db.picturelikes on p.idpicture equals l.idpictures into pl
            select new
            {
                Picture = p,
                AverageRating = pl.Any()
                                    ? pl.Average(l => l.rating)
                                    : (double?)null,
                RatingsCount = pl.Count(),
                TotalRating = pl.Sum(l => (float?)l.likenumber) ?? 0
            };

If a picture has no likes, this will give a 0 total and null average for that picture.

edited Sep 25 '13 at 16:45

answered Sep 25 '13 at 14:21

Tim S.

55,448
7
96
122

it works, could you please tell me how to average all the rattings? like its just giving me count, i need total average of those ratings? For average i need sum.. how to find the sum of alll the pictures with same picture id? – Obvious Sep 25 '13 at 15:05
@Hmmmmmmmmmm added code to calculate average using LINQ's [`Average`](http://msdn.microsoft.com/en-us/library/system.linq.enumerable.average.aspx). – Tim S. Sep 25 '13 at 15:10
I am getting this error while using average or sum "The cast to value type 'Single' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.", This is my code totalrating = (pl.Sum(l =>l.likenumber)) – Obvious Sep 25 '13 at 15:24
Could be a bug due to how the translated SQL handles an empty list. Try `TotalRating = pl.Select(l => l.rating).DefaultIfEmpty(0).Sum()` – Tim S. Sep 25 '13 at 15:29
Or `totalrating = (pl.Sum(l => (float?)l.likenumber))` – Tim S. Sep 25 '13 at 15:31
TotalRating = pl.Select(l => l.rating).DefaultIfEmpty(0).Sum() gives "An error occurred while executing the command definition. See the inner exception for details.Inner exption: "Unknown column 'Project1.idpictures' in 'where clause'" – Obvious Sep 25 '13 at 15:39
totalrating = (pl.Sum(l => (float?)l.likenumber)) gives Cannot implicitly convert type 'float?' to 'float'. An explicit conversion exists (are you missing a cast?) – Obvious Sep 25 '13 at 15:40
@Hmmmmmmmmmm `totalrating = pl.Sum(l => (float?)l.likenumber).GetValueOrDefault()` – Tim S. Sep 25 '13 at 16:21
"LINQ to Entities does not recognize the method 'Single GetValueOrDefault()' method, and this method cannot be translated into a store expression.", – Obvious Sep 25 '13 at 16:24
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/38041/discussion-between-tim-s-and-hmmmmmmmmmm) – Tim S. Sep 25 '13 at 16:27

score 1 · Accepted Answer · answered Sep 25 '13 at 14:24

1

You could group an anonymous class. That way you can access both p & l properties.
Looks like this :

var total = from p in db.picturedetails
            join l in db.picturelikes on p.idpictures equals l.idpictures 
            group new {p,l} by l.idpictures into g
            select new 
            {
                IdUserPic = g.First().p.iduser,
                IdPictures = g.First().p.nuditylevel,
                totalrating = g.Count()    
            }

answered Sep 25 '13 at 14:24

Kristof

3,267
1
20
30

I am getting this error after using your technique, ExceptionMessage: "The method 'First' can only be used as a final query operation. Consider using the method 'FirstOrDefault' in this instance instead.", – Obvious Sep 25 '13 at 14:38
Not sure why the first is considered a final when grouping as anonymous but have you tried using FirstOrDefault instead? That being said if you are only intrested in the number of joined records i would go for Tim's group join solution, a little less flexible but it looks cleaner imho :) – Kristof Sep 25 '13 at 14:42
FirstOrDefault will just give one picture details. – Obvious Sep 25 '13 at 14:45

Using Linq to Join,Group By and Count.... facing a trouble

2 Answers2

Linked