FromSqlRaw return value

user_13675552 0
Viewed 1414

My task is to return the number of cars where the age is more than three years

public IActionResult Report()
{
    var result = _context.Cars.FromSqlRaw("SELECT COUNT(*) from Car where (DATEDIFF(DAY, ReleaseDate, GetDate()) / 365.25) > 3");
    ViewBag.result = result;
    return View();
}

After spending time googling and studying Microsoft documentation I figured out that FromSqlRaw will work but in web form it returns either -1 or Microsoft.EntityFrameworkCore.Query.Internal.Entityqueryable<T>.

A direct query to the database returns the correct value which is 1 car. Adding any parameters after FromSqlRaw returns an exception.

I just don't get or getting ReleaseDate and then formatting and dividing it is too much?

entity-framework-core sql-server c#
1 Answers

FromSqlRaw returns the entity type (Car) which is why you have this problem. It doesn't return arbitrary values (like the result of COUNT()). From the documentation (bolding mine):

Raw SQL queries can return regular entity types or keyless entity types that are part of your model.

You can construct this query with LINQ directly using EF.Functions.DateDiffDay and Count. Assuming your entity has a property named ReleaseDate, this would look like the following:

var result = context.Cars.Where(
     c => EF.Functions.DateDiffDay(c.ReleaseDate, DateTime.Now) / 365.25 > 3
).Count();

Or more concisely:

var result = context.Cars.Count(
    c => EF.Functions.DateDiffDay(c.ReleaseDate, DateTime.Now) / 365.25 > 3
);
user_4919070
Related