Using SQRT in a Linq EF Query

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."开发者_StackOverflow社区

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 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.

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.

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

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