Can I override the Entity Framework query generator?

Question

The Entity Framework context generates 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()); 

The following line is displayed here:

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

My table contains a spatial column called geometry. Entity Framework does not contain geometric functions. For example, this is a geometric function:

 SELECT ST_AREA(geom) FROM Cities WHERE CityID = 3 

So, I could not use the context extension method as follows:

 context.Cities.Where(....) 

Perhaps, or is there any entity method for redefining geometric functions.

Answer 1

You do not need to redefine anything. The best thing is to simply execute a simple SQL query through the Entity Framework and return its filled 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 would suggest that the logistics for implementing the LINQ to Entities solution packaged in EF is the reason that they haven't done it yet. You can try to do this, but there is a much easier solution.

Answer 2

Starting with EF 6.0 (database first) there should be the possibility of using sql functions. This is done using 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 a 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 a function

With EF 6.1, this feature should be expanded 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/