3

I'm making a query with Linq, backed by an Entity Framework datasource.

I'm getting the following error:

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

Here's a simplified version of my function (my version is more complex and uses ACos, sin, cos and other C# Math class functions).

  var objects =
            from n in context.Products.Where(p => p.r == r)
            let a = Math.Sqrt((double)n.Latitude)
            where a < 5
            orderby a
            select n;

        return objects.Take(100).ToList();

I think the problem may be related to the situation that Linq to EF (and a SQL datasource) has a limited set of supported function compared to Linq to SQL. I'm relatively new to this so I'm not 100% sure.

Can anyone give me a pointer in the right direction?

Cheers,

kodikas
  • 387
  • 1
  • 5
  • 12
  • 1
    http://stackoverflow.com/questions/1616235/using-sqrt-in-a-linq-ef-query/4119792#4119792 – Nix Feb 20 '12 at 00:25

3 Answers3

14

Try SquareRoot function defined in SqlFunctions

    var objects =
        from n in context.Products.Where(p => p.r == r)
        let a = SqlFunctions.SquareRoot((double)n.Latitude)
        where a < 5
        orderby a
        select n;

    return objects.Take(100).ToList();
Eranga
  • 32,181
  • 5
  • 97
  • 96
5

If you start of learning LINQ with LINQ-to-objects, you'll run into this a lot once you start using LINQ-to-Entities.

You can do pretty much anything that will compile in LINQ-to-objects, because LINQ-to-objects translates into code when compiled.

LINQ-to-Entities (and LINQ-to-SQL) translates into expression trees. So, only the syntax that that specific LINQ provider allowed for is valid. In my first "for real" LINQ-to-Entities expression, which compiled just fine, I ran into this error about 5 times, as one by one I removed code that wasn't handled by LINQ-to-Entities.

So when you see this, it's normal and common. You need to find another way each time.

You could avoid the problem with a logical equivalent: 

var objects =
    from n in context.Products.Where(p => p.r == r)
    where (double)n.Latitude < 25
    orderby a
    select n;

return objects.Take(100).ToList();

You could also pull all the data to the client and then run your code using LINQ-to-objects:

var objects =
    from n in context.Products.Where(p => p.r == r).ToList()
    let a = Math.Sqrt((double)n.Latitude)
    where a < 5
    orderby a
    select n;

return objects.Take(100).ToList();

Finally, you should be able to do this math on the server. Check out the System.Data.Objects.SqlClient.SqlFunctions SqlFunctions Class. These functions will translate into the expression. This in particular looks like it might be the ticket.

Patrick Karcher
  • 22,995
  • 5
  • 52
  • 66
1

Please try 

var objects =
        from n in context.Products.Where(p => p.r == r)
        let a = Math.Pow((double)n.Latitude, 0.5)
        where a < 5
        orderby a
        select n;
findcaiyzh
  • 647
  • 3
  • 7