Using SQRT in a Linq EF Query

Question

I need to use the SQRT function as part of a where clause in a Linq EF query. I figured I could do this:

var qry = context.MyTable.Where("sqrt(it.field) > 1");

But it returns an error saying "'sqrt' cannot be resolved into a valid type constructor or function., near function, method or type constructor, line 6, column 5."

I had always assumed that linq literally takes what's in the where clause and translates that into a statement that is executed directly in SQL. That doesn't seem to be the case...

Anyone know the work-around?

Thanks

I assume that the number you need to compare against won't always be one, right? Cause...if it is...just don't use sqrt. Sqrt(1) = 1 — PeterAllenWebb, Oct 24 '09 at 04:45
Retag. There is no C#3.5 (see http://stackoverflow.com/questions/247621/what-are-the-correct-version-numbers-for-c) — Vaccano, Nov 02 '09 at 23:14

score 9 · Answer 1 · answered Nov 07 '10 at 21:28

I don't know how you got your answer working against EF. When I tried it with EF4, it wouldn't work for me. I'd get:

LINQ to Entities does not recognize the method 'Double Sqrt(Double)' method, and this method cannot be translated into a store expression.

So after banging my head against this for a while (after all, Math.Pow(double) is supported, and SQRT is available in SQL), I realised that the square root of a value is the same as its power of a half.

So, this solution works fine.

from e in context.MyEntities
     let s = Math.Pow(e.MyDouble, 0.5)
     select s;

The generated SQL uses POWER instead of SQRT:

SELECT 
POWER( CAST( [Extent1].[MyDouble] AS float), cast(0.5 as float(53))) AS [C1]
FROM [dbo].[MyEntities] AS [Extent1]

A simple enough workaround to a daft problem. Hope this helps someone else out.

This was built against EF: you can see from my code we are using an ObjectQuery, which is an Entity Framework concept. However, as my answer is from October of 2009 and Entity Framework 4 came out in April of 2010, I am pretty sure it wasn't a EF4 solution. I am very surprised that such as simple query would thwart EF4. — Godeke, Nov 08 '10 at 21:33
@Godeke. True enough regarding the dates. But yes, this is surprising. Still, at least there's a workaround. — Drew Noakes, Nov 09 '10 at 00:50

score 3 · Accepted Answer · answered Oct 24 '09 at 20:36

3

I'm using Linq Entities and was able to do this:

        testEntities entities = new testEntities ();

        ObjectQuery<Fees> fees = entities.Fees;

        return from f in fees 
               let s = Math.Sqrt((double)f.FeeAmount)
               where s > 1.0 
               select f ;

When I check the generated SQL, I get

SELECT [t1].[TestTriggerID]
FROM (
    SELECT [t0].[TestTriggerID], SQRT(CONVERT(Float,[t0].[TestTriggerID])) AS [value]
    FROM [TestTrigger2] AS [t0]
    ) AS [t1]
WHERE [t1].[value] > @p0

This seems reasonable. I was unable to use the .Where string format to reproduce the same code, but I'm probably missing something obvious.

answered Oct 24 '09 at 20:36

Godeke

15,383
3
56
83

I did try something similar, except I used Math.Sqrt in the select portion of without a let assignment. Do some items work as a let while they don't work natively in a where? – bugfixr Oct 27 '09 at 14:54
LINQ queries actually operate in the sequence they are constructed. FROM defines the source, which the LET augments with a computed value. The WHERE clause now has the item it needs (via the sub-query constructed from the FROM+let) to filter and finally the SELECT feeds values on as the result. Putting the computation in the SELECT would be "too late" for the WHERE clause to act upon without explicitly using that result as a sub-query). – Godeke Oct 27 '09 at 15:30
1

Hmm. When I write something similar to what you've used here I get a NotSupportedException with `LINQ to Entities does not recognize the method 'Double Sqrt(Double)' method, and this method cannot be translated into a store expression.`. Were you using EF4? – Drew Noakes Nov 07 '10 at 21:07
1

@Drew Noakes - I'm getting that same problem in EF4 – Shawn Mclean Jan 01 '11 at 23:17
@Shawn, did my answer below work for you? http://stackoverflow.com/questions/1616235/using-sqrt-in-a-linq-ef-query/4119792#4119792 – Drew Noakes Jan 02 '11 at 06:12

score 1 · Answer 3 · answered Oct 30 '09 at 13:32

1

Check the msdn document.Entity sql doesn't support the sqrt function.

answered Oct 30 '09 at 13:32

Laurel.Wu

146
1
5

score 1 · Answer 4 · answered May 15 '14 at 17:07

1

You can use System.Data.Entity.SqlServer.SqlFunctions.SquareRoot as of EF6.

answered May 15 '14 at 17:07

Nathan Phillips

10,671
1
26
20

Using SQRT in a Linq EF Query

4 Answers4

Linked