Extending EF4 SQL Generation

Question

We're using EF4 in a fairly large system and occasionally run into problems due to EF4 being unable to convert certain expressions into SQL. At present, we either need to do some fancy footwork (DB/Code) or just accept the performance hit and allow the query to be executed in-memory.

Needless to say neither of these is ideal and the hacks we've sometimes had to use reduce readability / maintainability.

What we would ideally like is a way to extend the SQL generation capabilities of the EF4 SQL provider. Obviously there are some things like .Net method calls which will always have to be client-side but some functionality like date comparisons (eg [Group by weeks in Linq to Entities ) should be do-able.

I've Googled but perhaps I'm using the wrong terminology as all I get is information about the new features of EF4 SQL Generation.

For such a flexible and extensible framework, I'd be surprised if this isn't possible. In my head, I'm imagining inheriting from the [SQL 2008] provider and extending it to handle additional expressions / similar in the expression tree it's given to convert to SQL.

Any help/pointers appreciated.

We're using VS2010 Ultimate, .Net 4 (non-client profile) and EF4. The app is in ASP.Net and is running in a 64-Bit environment in case it makes a difference.

Update: In response to some requests for clarification;

We're using a code-first approach and have a console app which creates the database and populates some reference tables.

I'd prefer to stay away from Stored procs unless they could also be generated in a similar way - At present, new versions of the database are generated as required and a separate process migrates/syncs data. everything we currently do with regards to the database uses entities. I admit I can't give a good reason but running SQL scripts to generate stored procs feels wrong in this scenario - But please correct me if I'm mistaken.

With regards to a specific scenario, I'm afraid I can't give one from our code without going through a long red-tape exercise - The link mentioned above is a good example of the sort of thing we're trying to accomplish. In this example, implementing a mechanism to allow date arithmetic:

DateTime firstDay = GetFirstDayOfFirstWeekOfYear();
var userTimes = from t in context.TrackedTimes
                group t by new {t.User.UserName, WeekNumber = (t.TargetDate - firstDay).Days / 7} into ut
                select new
                {
                    UserName = ut.Key.UserName,
                    WeekNumber = ut.Key.WeekNumber,
                    Minutes = ut.Sum(t => t.Minutes)
                };

I can see a way that this could be accomplished using SQL alone but not how to do it using LINQ-To-Entities (Server-side).

Answer 1

I would recommend first looking at EntityFunctions (generic) or SqlFunctions (SQL Server only), which provide a lot of built-in database functionality within a normal LINQ to Entities query. If that's not sufficient, you could use EdmFunctionAttribute to map a built-in database function or user-defined stored procedure. A third option is to attempt to get it working as an Entity SQL query.

The last solution you suggested - writing an EF provider - is pretty heavyweight. It may be possible to write an EF provider that wraps the existing SQL provider, but you'd have to change some of the expression trees to Entity SQL at the very least. Very simple EF provider wrappers have been released on MSDN Code. I'd treat this as a last resort, though.