CODEWITHSUNDEEP
c#linq-to-sqlexpression
George Duckett
George Duckett

Reputation: 32428

Using a method returning an expression in LINQ-To-SQL

I'm trying to add a computed column to my linq object created from an SQL table. I can't do this in the database.

public partial class History_SHIP
{
    public Expression<Func<DateTime>> TransactionDateTime
    {
        get
        {
            return () => TransactionDate.AddMinutes(SqlMethods.DateDiffMinute(TransactionTime, new DateTime(1900, 1, 1)));
        }
    }
}

I'd like to be able to use it as part of a where clause like below:

where sh.TransactionDateTime >= startDate

startDate is a DateTime and sh is my History_SHIP object.

I'd also like to be able to easily use it in a select statement too (I'm happy to create another property that does a TranslationDateTime.Compile()()).

The problem I have with the where clause is that the LHS in a Expression<Func<DateTime>> and the RHS is a DateTime, so it complains.

I have seen this link http://www.codeproject.com/Articles/32968/QueryMap-Custom-translation-of-LINQ-expressions but don't want additional dependencies for this one property (for now anyway).

Upvotes: 3

Views: 981

Answers (2)

Marc Gravell
Marc Gravell

Reputation: 1062620

Yes, that isn't going to be happy, for various reasons;

  • firstly, the LINQ interpreter can handle that directly - it will need help
  • secondly, the property as implemented returns something in relation to this, but in reality we need something in relation to a ParameterExpression (aka sh)

The easiest thing to do is probably to add an extension method that lets you filter on this conveniently, for example by re-writing an Expression:

var filtered = source.WhereTransactionDate(when => when > DateTime.Now);

with implementation:

static class Utils
{
    static readonly Expression<Func<Foo, DateTime>> tranDateTime =
            x => x.TransactionDate.AddMinutes(SqlMethods.DateDiffMinute(
                     x.TransactionTime, new DateTime(1900, 1, 1)));

    public static IQueryable<Foo> WhereTransactionDate(
        this IQueryable<Foo> source,
        Expression<Func<DateTime, bool>> predicate)
    {
        var visited = SwapVisitor.Swap(predicate.Body,
              predicate.Parameters.Single(), tranDateTime.Body);
        var lambda = Expression.Lambda<Func<Foo, bool>>(
              visited, tranDateTime.Parameters);
        return source.Where(lambda);
    }

    class SwapVisitor : ExpressionVisitor
    {
        public static Expression Swap(Expression source,
             Expression from, Expression to)
        {
            return new SwapVisitor(from, to).Visit(source);
        }
        private SwapVisitor(Expression from, Expression to)
        {
            this.from = from;
            this.to = to;
        }
        readonly Expression from, to;
        public override Expression Visit(Expression node)
        {
            return node == from ? to : base.Visit(node);
        }
    }
}

What this does is merge two expression-trees, i.e.

x => x.TransactionDate.AddMinutes(SqlMethods.DateDiffMinute(
                     x.TransactionTime, new DateTime(1900, 1, 1)))

and:

when => when > DateTime.Now

replacing all when with the body from the first expression (and using the parameter from the first expression) i.e. creating

x => x.TransactionDate.AddMinutes(SqlMethods.DateDiffMinute(
           x.TransactionTime, new DateTime(1900, 1, 1))) > DateTime.Now;

Upvotes: 1

Justin Harvey
Justin Harvey

Reputation: 14672

How about...

where sh.TransactionDateTime.Compile().Invoke() >= startDate

Upvotes: 0

Related Questions