CODEWITHSUNDEEP
c#.netentity-frameworklinq-to-entitiessql-function
gabnaim
gabnaim

Reputation: 1105

Linq-to-Entities SqlFunctions: How to Convert from int to double? or decimal?

I am trying to sort some articles using Linq to Entities. I am using System.Data.Objects.SqlClient.SqlFunctions. This is what I would like to have:

this.Queryable = this.Queryable
    .OrderByDescending(v => v.UpVote + SqlFunctions.Log10(
        SqlFunctions.DateDiff("d", DateTime.Today, v.Created)));

However, this does not work, since DateDiff returns an int? and Log10 takes either a double? or a decimal?.

I could not find a convert function that does this in SqlFunctions. I tried multiplying it by 1.0, or casting it, but I get the following error:

System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while reading from the store provider's data reader. See the inner exception for details. ---> System.Data.SqlClient.SqlException: An invalid floating point operation occurred.

How do I convert from int? to double? or decimal? using SqlFunctions?

ANSWER:

Based on Ocelot20's response, I switched around the arguments and added a day to the Created day so it never produces a negative or 0 input, which is what caused the error. I also needed to cast UpVote to double. This is what the working version looks like:

var date = DateTime.Today.AddDays(1);
this.Queryable = this.Queryable
    .OrderByDescending(v => (double)v.UpVote - SqlFunctions.Log10(Math.Abs((double)
        SqlFunctions.DateDiff("d", date, v.Created))));

Upvotes: 4

Views: 4233

Answers (2)

Ocelot20
Ocelot20

Reputation: 10800

The exception is suggesting that the problem is related to performing the operation rather than an issue with converting the types involved. Using LOG10 in SQL will throw this exception when passed a negative number (try it: SELECT LOG10(-1)). If SqlFunctions.DateDiff returns a negative number (which it can, depending on the Created date), your query will fail.

I'm guessing you just need to switch DateTime.Today and v.Created to get this working with appropriate casting like so:

this.Queryable
    .Select(v => v.UpVote +
        SqlFunctions.Log10(
            (double?)SqlFunctions.DateDiff("d", v.Created, DateTime.Today));

Or if v.Created happens to include future dates or there's some negative time differences you can use Math.Abs to ensure it's always positive like so:

this.Queryable
    .Select(v => v.UpVote +
        SqlFunctions.Log10(
            (double?)Math.Abs(
                SqlFunctions.DateDiff("d", DateTime.Today, v.Created)));

Edit - In addition to failing with negative numbers, LOG10 will fail when 0 is provided. You can filter out 0, or ignore it as a modifier:

from v in this.Queryable
let dayDiff = SqlFunctions.DateDiff("d", v.Created, DateTime.Today)
let voteWeight = dayDiff == 0 ?
    0 :
    SqlFunctions.Log10((double?)dayDiff)
select v.UpVote + voteWeight

Didn't test this exact query out, but the jist of it is that numbers less than or equal to 0 need to be handled somehow.

Upvotes: 1

Ben N
Ben N

Reputation: 2923

Add a cast to double on v.UpVote. The LINQ converter should insert the appropriate CONVERT directive.

Upvotes: 1

Related Questions