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

Question

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))));

score 1 · Answer 1 · answered Apr 28 '14 at 17:15

1

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

answered Apr 28 '14 at 17:15

Ben N

2,883
4
26
49

I also needed to cast UpVote to double. Tried to accept both answers but it wouldn't do it. Both provide part of the answer. – gabnaim Apr 28 '14 at 20:47

Ocelot20 · Accepted Answer · 2014-04-28T18:54:21.107

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.

Thanks for the answer, but casting it to double was one of the things I already tried (see in my notes). So was Math.Abs. I tried switching it around - same error. — gabnaim, Apr 28 '14 at 18:26
See my update. The queries work, so it's surely a data issue. It would also be wise to try these out in Linqpad and play around with the generated SQL to better understand the issue. — Ocelot20, Apr 28 '14 at 18:54
Bingo - the error was with items created today, where Log10 returned 0. I added a day to Today and it worked. Thank you! — gabnaim, Apr 28 '14 at 20:10

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

2 Answers2