How to get a maximum from an empty table as 0

Question

 

Hi,

I am trying to get a max of a column (CourseOrder INT NOT NULL) with no success.The table is empty so I wanna get 0 as result but I get "The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type."

I all tried

int CourseOrder= db.StudentCourseLists.Where(sc => sc.Student_Id == Student_Id).Select(sc1 => sc1.CourseOrder).Max() ;

 

int CourseOrder= db.StudentCourseLists.Where(sc => sc.Student_Id == Student_Id).Select(sc1 =>(int?) sc1.CourseOrder).Max() ; ///////compile error -Error 1 Cannot implicitly convert type 'int?' to 'int'. An explicit conversion exists (are you missing a cast?) 

int CourseOrder= db.StudentCourseLists.Where(sc => sc.Student_Id == Student_Id).Select(sc1 =>(int?) sc1.CourseOrder ?? 0).Max() ;

 

 

any help?

thanks.

Answer 1

Try ...DefaultIfEmpty().Max();

Answer 2

 

Thanks.

int? CourseOrder= db.StudentCourseLists.Where.... is also working too.

 

Answer 3

I would also argue that is the more correct answer.  It all hinges, of course, on what value you want from null.

 

Answer 4

I personally use queries like that:

int CourseOrder= db.StudentCourseLists.Where(sc => sc.Student_Id == Student_Id).Select(sc1 =>(int?) sc1.CourseOrder).Max() ?? 0;

which is similar to:

int CourseOrder= db.StudentCourseLists.Where(sc => sc.Student_Id == Student_Id).Select(sc1 =>(int?) sc1.CourseOrder ?? 0).Max() ;

I've run simple performance tests (for Max and Sum functions) and it turns out, that the first query is somewhat faster - 0-20%, depends on how many null values there actually are in a set.