Can I override Entity Framework query generator?

Question

Entity Framework context is generating queries for me.

var query = from c in context.Cities where c.CityID == 3 select c;
var objectQuery=query as System.Data.Objects.ObjectQuery;
Console.WriteLine(objectQuery.ToTraceString());

This outputs the following string:

SELECT
[Extent1].[CityID] AS [CityID],
[Extent1].[geom] AS [geom],
[Extent1].[Name] AS [Name],
FROM [dbo].[Cities] AS [Extent1]
WHERE 3 = [Extent1].[CityID]

My table is including spatial column named geometry. Entity Framework does not contain geometry functions. For example this is a geometry function:

SELECT ST_AREA(geom) FROM Cities WHERE CityID = 3

So I could not use context extension method like this:

context.Cities.Where(....)

Is possible, or is there any entity framework method to override geometric functions.

Answer 1

You don't need to override anything. Your best bet would be to just execute a plain SQL query through Entity Framework and have it return your populated objects.

// Add in whatever spatial stuff you need here.
var sql = "SELECT * FROM Cities WHERE CityId = {0} AND ...";

// Add whatever other parameters you need to the rest of the parameters.
var cities = context.Database.SqlQuery<City>(sql, cityId, ...);

It's not as "clean" as using LINQ, but I'd imagine the logistics to implementing a LINQ to Entities solution packaged into EF is the reason why they haven't done it yet. You can try to do it, but there is a far easier solution.

Answer 2

Starting in EF 6.0 (Database First) there should be the possibility of using sql functions. It's done through the EdmFunction attribute. See for example http://blogs.msdn.com/b/efdesign/archive/2008/10/08/edm-and-store-functions-exposed-in-linq.aspx

In the blog article they show for example:

var query = 
    from p in context.Products 
    where EntityFunctions.DiffYears(DateTime.Today, p.CreationDate) < 5 
    select p;

where EntityFunctions.DiffYears is the function

With EF 6.1 this feature should have been extended to Code First. See for example http://blog.3d-logic.com/2014/04/09/support-for-store-functions-tvfs-and-stored-procs-in-entity-framework-6-1/