LINQ to SQL running direct SQL for a scalar value

I a开发者_如何学JAVAm trying to execute the following raw SQL as LINQ has no proper support for DateDiff:

var str = @"SELECT ISNULL(AVG(DATEDIFF(DAY, AddedDate, PresentDate)), 0)
              AS Days
                FROM DummyTable";

Using LINQ to SQL, I'm trying to get the output of the above statement using:

var numberOfDays = Math.Round(db.ExecuteQuery<double>(str).FirstOrDefault());

This give me the error: Specified cast is not valid.

What am I doing wrong?

Thanks in advance!

Your code must change to:

var numberOfDays = db.ExecuteQuery<int>(str).FirstOrDefault());

You think why? I tell you:

Return type of DateDiff in int.So Return Type of AVG will be int. So Return Type of IsNULL will be int too.

References :
DateDiff AVG

IsNull always try to convert the type of second expression to first expression.

Ok, my bad.

Looks like the cast was indeed wrong... Changed the line to:

var numberOfDays = db.ExecuteQuery<int>(str).FirstOrDefault();

And all is well!

DATEDIFF() returns an int. When passed a collection of integers, the AVG() function returns an integer as well. Try this code instead:

    var numberOfDays = db.ExecuteQuery<int>(str).FirstOrDefault();