Imagine you have a database function like the DistanceBetween function in Nerd Dinner:
CREATE FUNCTION [dbo].[DistanceBetween](
@Lat1 as real,
@Long1 as real,
@Lat2 as real,
@Long2 as real)
And you want to use it with the Entity Framework.
Declaring the Function
The first step is to open your EDMX file in the XML editor and add a <Function> inside the <Schema> inside the <edmx:StorageModels> element.
When you are done it should look like this:
Using the Function in eSQL
Now this function can be called in eSQL like this:
SELECT VALUE(D) FROM MyModel.Dinners AS D
D.Latitude,D.Longitude,-34,174) < 50
MyModel is simply the name of your EntityContainer (generally the same as your ObjectContext) and StorageNamespace is your storage model schema namespace.
Using the Function in LINQ
Most of you don’t use eSQL so you are probably wondering can I do this with LINQ?
In 3.5 SP1 this is how:
var nearbyDinners =
from d in ctx.Dinners.Where(
“StorageNamespace.DistanceBetween(it.Latitude, it.Longitude, –34, 174) < 50”
) select d;
Here we are mixing LINQ with eSQL by using a query builder method, which takes an eSQL fragment, in which we call our database function. Notice how this snippet refers to the current item using the ‘it’ keyword. You can even refer to parameters if necessary.
This is great.
But it would be much better without any strings.
Improvements in EF 4.0
Well in EF 4.0 you can write something like this instead:
var nearbyDinners =
from d in ctx.Dinners
where DistanceBetween(d.Latitude, d.Longitude, –34,174) < 50
Which looks a lot better. No magic strings *and* compile time checking.
To make this work you need a method like this:
You may be wondering why the method throws an exception?
Well we never actually need to execute this method directly. We just use it to write LINQ queries, which are in translated into SQL without ever really calling the method.
The EF uses the EdmFunction attribute to know which database function needs to be called instead.