Implement LINQ to SQL expressions for a database with custom date/time format

You can't write a method and expect L2S to automatically know how to take your method and translate it to SQL. L2S knows about some of the more common methods provided as part of the .NET framework for primitive types. Anything beyond that and it will not know how to perform the translation.

If you have to keep your db model as is:

You can define methods for interacting with the custom format and use them in queries. However, you'll have to help L2S with the translation. To do this, you would look for calls to your methods in the expression tree generated for your query and replace them with an implementation L2S can translate. One way to do this is to provide a proxy IQueryProvider implementation that inspects the expression tree for a given query and performs the replacement before passing it off to the L2S IQueryProvider for translation and execution. The expression tree L2S will see can be translated to SQL because it only contains the simple arithmetic operations used in the definitions of your methods.

If you have the option to change your db model:

You might be better off using a standard DateTime column type for your data. Then your could model the column as System.DateTime and use its methods (which L2S understands). You could achieve this by modifying the table itself or providing a view that performs the conversion and having L2S interact with the view.

Update: Since you need to keep your current model, you'll want to translate your methods for L2S. Our objective is to replace calls to some specific methods in a L2S query with a lambda L2S can translate. All other calls to these methods will of course execute normally. Here's an example of one way you could do that...

static class DateUtils
{
    public static readonly Expression<Func<int, int>> GetMonthExpression = t => (t / 60 / 60 / 24) % 13;
    static readonly Func<int, int> GetMonthFunction;

    static DateUtils()
    {
        GetMonthFunction = GetMonthExpression.Compile();
    }

    public static int GetMonth(int t)
    {
        return GetMonthFunction(t);
    }
}

Here we have a class that defines a lambda expression for getting the month from an integer time. To avoid defining the math twice, you could compile the expression and then invoke it from your GetMonth method as shown here. Alternatively, you could take the body of the lambda and copy it into the body of the GetMonth method. That would skip the runtime compilation of the expression and likely execute faster -- up to you which you prefer.

Notice that the signature of the GetMonthExpression lambda matches the GetMonth method exactly. Next we'll inspect the query expression using System.Linq.Expressions.ExpressionVisitor, find calls to GetMonth, and replace them with our lambda, having substituted t with the value of the first argument to GetMonth.

class DateUtilMethodCallExpander : ExpressionVisitor
{
    protected override Expression VisitMethodCall(MethodCallExpression node)
    {
        LambdaExpression Substitution = null;

        //check if the method call is one we should replace
        if(node.Method.DeclaringType == typeof(DateUtils))
        {
            switch(node.Method.Name)
            {
                case "GetMonth": Substitution = DateUtils.GetMonthExpression;
            }
        }

        if(Substitution != null)
        {
            //we'd like to replace the method call; we'll need to wire up the method call arguments to the parameters of the lambda
            var Replacement = new LambdaParameterSubstitution(Substitution.Parameters, node.Arguments).Visit(Substitution.Body);
            return Replacement;
        }

        return base.VisitMethodCall(node);
    }
}

class LambdaParameterSubstitution : ExpressionVisitor
{
    ParameterExpression[] Parameters;
    Expression[] Replacements;

    public LambdaParameterExpressionVisitor(ParameterExpression[] parameters, Expression[] replacements)
    {
        Parameters = parameters;
        Replacements = replacements;
    }

    protected override Expression VisitParameter(ParameterExpression node)
    {
        //see if the parameter is one we should replace
        int p = Array.IndexOf(Parameters, node);
        if(p >= 0)
        {
            return Replacements[p];
        }

        return base.VisitParameter(node);
    }
}

The first class here will visit the query expression tree and find references to GetMonth (or any other method requiring substitution) and replace the method call. The replacement is provided in part by the second class, which inspects a given lambda expression and replaces references to its parameters.

Having transformed the query expression, L2S will never see calls to your methods, and it can now execute the query as expected.

In order to intercept the query before it hits L2S in a convenient way, you can create your own IQueryable provider that is used as a proxy in front of L2S. You would perform the above replacements in your implementation of Execute and then pass the new query expression to the L2S provider.