Database Reference
In-Depth Information
FROM EFRecipesEntities.Zips AS z join
(select top(1) z2.Latitude as Latitude,z2.Longitude as
Longitude
from EFRecipesEntities.Zips as z2
where z2.ZipCode = @Zip
) as t2 on 1 = 1
) as matchingzips on matchingzips.ZipCode = c.Zip
where matchingzips.DistanceInMiles <= @RadiusInMiles";
var objectContext = (context as IObjectContextAdapter).ObjectContext;
var custs = objectContext.CreateQuery<WebCustomer>(esql,
new ObjectParameter("Zip", "76039"),
new ObjectParameter("RadiusInMiles", 5));
Console.WriteLine("Customers within 5 miles of 76039");
foreach (var cust in custs)
{
Console.WriteLine("Customer: {0}", cust.Name);
}
}
}
}
The output of the code in Listing 11-17 is as follows:
Customers within 5 miles of 76039
Customer: Alex Stevens
Customer: Janis Jones
How It Works
Okay, the eSQL is a little complex, but the complexity is because we're calling a bunch of database functions. Using
the database functions in eSQL is fairly simple. These functions are available in the SqlServer namespace. Not all
database functions are available in eSQL, so check the current Microsoft documentation to get a complete list. These
functions are available only for SQL Server database.
In this example, the Zip entity has the latitude and longitude for each ZIP code. These values represent the
geographic location of the center of the ZIP code. To calculate the distance between two ZIP codes involves a bit of
math. Luckily, the database side provides the necessary functions to do the calculation.
11-11. Calling Database Functions in LINQ
Problem
You want to call a database function in a LINQ query.
Solution
Let's say that you have an Appointment entity in your model, and you want to query for all of the appointments that
you have on a given day of the week. The Appointment entity might look like the one shown in Figure
11-11
.
