Entity Framework 6 - Extension Methods for DbFunctions

Question

I am unsure if this is possible yet, as I am just diving into Entity Framework 6. We are migrating away from Linq2Sql, but have many user based queries in our engine. We dynamically compile some code based on user requirements, and have to maintain backward compatibility.

An example of some dynamic query that is passed in from the legacy Linq2Sql may looks like this (stripped down, this is only a sample):

from item in context.SomeTableOrView 
  let sub = from sub1 in context.SomeTableOrView where 
  sub1.DataTimeStamp > item.DataTimeStamp.AddMinutes(60) &&
  sub1.DataTimeStamp < item.DataTimeStamp.AddMinutes(300) 
select posSub1 where 
sub.Take(1).Any()
select item

Of course, Entity Framework does not have any kind of .AddMinutes(x) support, and you must use the new DbFunctions static methods. We can't break the code, so we must retrofit it. The first solution that comes to mind, is just to replace the text where anything that has .AddMinutes(x), or .AddSeconds(x), or whatever we do around DateTime, and just replace with the new functions and be done with it. This is all pre-compile, so that technically would work. I just suck at regular expressions. I would gladly accept that as an answer if someone knows how I would do that though.

But I would like understand how EntityFramework works with regards to extension methods. Since the return of DateTime.AddMinutes(x) returns a new DateTime, Could I instead create an extension method to return an expression that would do the equivalent of DbFunctions.AddMinutes(time, increment) or something creative like that? DbFunctions is static, so I cant return a Func<DbFunctions>.

Thoughts / Suggestions. Thanks!

Updated - Ivan gives a great updated answer for anyone looking at this. Less error prone to the below answer, and quite slick IMO. enter link description here

Answer 1

Entity Framework 6 is stupid in this case. It just try to get associated [DbFunction] attribute from method, and replaces the method with DbExpression using System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.Translator private class. And so, there is no way to register custom translator from outside code. Also, .NET does not offer ability to attach attributes to language constructs dynamically.

So, to solve this issue, you can do one of following:

• replace methods with calls to methods of DbFunctions class in source code (ReSharper SSR is good for this);
• implement ExpressionVisitor (and possible IQueryProvider) which will replace method calls with methods of DbFunctions class.
• implement ExpressionVisitor (and possible IQueryProvider) which will translate method call expressions to DbFunctionExpression.

Answer 2

We found that it was easiest to just patch the inbound Linq2Sql code. We also noticed that the DbFunctions.AddMinutes() only accepts an Int32, where the DateTime.AddMinutes() accepts a double. This could potentially break the expected behavior, so we patch that up also. Using some regular expressions, and some simple string operations, resulted in this patched code.

This may not work for everyone, but if you are going from Linq2Sql and have saved queries that follow Linq2Sql and need to patch DateTime expressions... this will work for AddMintues, AddDays, AddHours, AddMilliseconds

    private static string Linq2SqlConvertToEntityFramework(string originalQuery)
    {
        // Finds expressions with .Add____)
        const string dateTimeAdditationPattern = @"(@?[a-z_A-Z]\w*(?:\.@?[a-z_A-Z]\w*)*).Add\s*.+?(?=\))\)";
        // Finds all the matches
        var matchces = Regex.Matches(originalQuery, dateTimeAdditationPattern);

        // Enumerates all the matches, and creates a patch
        foreach (Match m in matchces)
        {
            var inputValue = m.Value;

            string valuePassed = inputValue.Between("(", ")").Trim();
            string typeOfAddition = inputValue.Between(".Add", "(").Trim();
            string dateTimeExpression = inputValue.Before(".Add").Trim();

            // because DateTime.AddMinutes()  (or any other AddXXX(Y) accepts a double, and 
            // DbFunctions only accepts an int,
            // We must move this to milliseconds so we dont lose any expected behavior
            // The input value could be an int or a input variable (such as a sub query)
            var mutipler = 1;
            switch (typeOfAddition)
            {
                case "Seconds":
                    mutipler = 1000;
                    break;
                case "Minutes":
                    mutipler = 1000*60;
                    break;
                case "Hours":
                    mutipler = 1000 * 60 * 60;
                    break;
                case "Days":
                    mutipler = 1000 * 60 * 60 * 24;
                    break;
            }

            // new expression to work with Entity Framework
            var replacementString = string.Format("DbFunctions.AddMilliseconds({0},(int)({1} * {2}))", dateTimeExpression, valuePassed, mutipler);

            // Simple string replace for the input string
            originalQuery = originalQuery.Replace(inputValue, replacementString);
        }

        return originalQuery;
    }

    /// <summary>
    /// Get string value between [first] a and [last] b.
    /// Credit Source: http://www.dotnetperls.com/between-before-after
    /// </summary>
    public static string Between(this string value, string a, string b)
    {
        int posA = value.IndexOf(a, StringComparison.InvariantCulture);
        int posB = value.LastIndexOf(b, StringComparison.InvariantCulture);
        if (posA == -1)
        {
            return "";
        }
        if (posB == -1)
        {
            return "";
        }
        int adjustedPosA = posA + a.Length;
        if (adjustedPosA >= posB)
        {
            return "";
        }
        return value.Substring(adjustedPosA, posB - adjustedPosA);
    }

    /// <summary>
    /// Get string value after [first] a.
    /// Credit Source: http://www.dotnetperls.com/between-before-after
    /// </summary>
    public static string Before(this string value, string a)
    {
        int posA = value.IndexOf(a, StringComparison.InvariantCulture);
        if (posA == -1)
        {
            return "";
        }
        return value.Substring(0, posA);
    }

    /// <summary>
    /// Get string value after [last] a.
    /// Credit Source: http://www.dotnetperls.com/between-before-after
    /// </summary>
    public static string After(this string value, string a)
    {
        int posA = value.LastIndexOf(a, StringComparison.InvariantCulture);
        if (posA == -1)
        {
            return "";
        }
        int adjustedPosA = posA + a.Length;
        if (adjustedPosA >= value.Length)
        {
            return "";
        }
        return value.Substring(adjustedPosA);
    }