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

Answer 1

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.