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

Answer 2

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