Error in linq query when using average

Question

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

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 now I am able to write the following code

 var combo = from p in db.picturedetails
                    join l in db.picturelikes on p.idpictures equals l.idpictures into pl
                    select new LikesandPictureDetails
                    {
                             IdUserPic = p.iduser,
                           IdPicturess =p.idpictures,
                           Likes = p.likes,
                           NudityLevel = p.nuditylevel,
                           PicTitle = p.picTitle,
                           PicTime = p.pictime,
                           picFilename=p.picFilename,
                           Count=pl.Count(),
                           totalrating = pl.Average(c => c.likenumber) // This line is causing error
                    };

I am using web api to return query total. I am showing picture properties like iduser, picTitle, picFilename, pictime, nuditylevel and fakeslevel.

As per now every thing runs smooth, but when I add totalrating = pl.Average(c => c.likenumber) , I get an exception saying

The cast to value type 'Double' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

How to remove the error?

possible duplicate of [Using Linq to Join,Group By and Count.... facing a trouble](http://stackoverflow.com/questions/19007164/using-linq-to-join-group-by-and-count-facing-a-trouble) — Tim S., Sep 25 '13 at 16:23

score 4 · Answer 1 · answered Sep 26 '13 at 00:57

4

Maybe the problem is that pl has no records, try

replace

totalrating = pl.Average(c => c.likenumber) // This line is causing error

with

totalrating = pl.DefaulIfEmpty(0).Average(c => c.likenumber)

answered Sep 26 '13 at 00:57

Esteban Elverdin

3,552
1
17
21

MichaC · Answer 2 · 2013-09-25T16:33:37.850

2

db.picturelikes.likenumber seems to be nullable? Can't find the property defined in your code though. Anyways you can select all entries which are not null by putting

pl.Where(a=>a.likenumber != DBNull.Value /* or null whatever this is... */).Average...

If it is a nullable property:

pl.Where(a=>a.likenumber.HasValue).Average(a=>a.likenumber.Value)

:edit I think .average returns a null-able decimal in this case. To assign it to your field, simply put

totalrating = pl.Average(c => c.likenumber)

Totalrating seems to be an int, eigther you'll loose some information by casting it down to an int, or you change the type to double...

edited Sep 25 '13 at 16:33

answered Sep 25 '13 at 16:12

MichaC

13,104
2
44
56

Both doesnt work. Hasvalue option is not apeparing in intellisense. – Obvious Sep 25 '13 at 16:25
What kind of type is likenumber? – MichaC Sep 25 '13 at 16:27
Dbnull gives Errr : Operator '!=' cannot be applied to operands of type 'int' and 'System.DBNull' – Obvious Sep 25 '13 at 16:28
Ah, .Average returns nullable decimal. Simply put .Value, pl.Average(c => c.likenumber).Value – MichaC Sep 25 '13 at 16:31

Drew Noakes · Answer 3 · 2013-09-25T16:15:34.087

1

The error appears to be that your DB has a column that can contain null values, yet your object has a non-nullable double type.

Does the query run if you select an anonymous type, instead of a concrete one?

ie. change this

select new LikesandPictureDetails

to

select new

If so, then you should change the type of a value in your target object to be nullable.

edited Sep 25 '13 at 16:15

answered Sep 25 '13 at 16:09

Drew Noakes

300,895
165
679
742

Error in linq query when using average

3 Answers3

Linked