How do you set the value of an IQueryableTproperty? >'s

entity entity-framework expression-trees iqueryable linq

Question

Entity Framework 4.1 Code First is what I'm utilizing. I have three date/time attributes in my entity:

public class MyEntity
{
    [Key]
    public Id { get; set; }

    public DateTime FromDate { get; set; }

    public DateTime ToDate { get; set; }

    [NotMapped]
    public DateTime? QueryDate { get; set; }

    // and some other fields, of course
}

I always have the From/To dates filled out in the database. I run a basic where clause query on them. However, I want to include the date I questioned for in the result set. In order for some other business logic to function, I need to persist this.

I'm developing an extension technique to do this, however there are issues:

public static IQueryable<T> WhereDateInRange<T>(this IQueryable<T> queryable, DateTime queryDate) where T : MyEntity
{
    // this part works fine
    var newQueryable = queryable.Where(e => e.FromDate <= queryDate &&
                                            e.ToDate >= queryDate);

    // in theory, this is what I want to do
    newQueryable = newQueryable.Select(e =>
                                           {
                                               e.QueryDate = queryDate;
                                               return e;
                                           });
    return newQueryable;
}

This is unsuccessful. It works if I use an IEnumerable, but I like to retain it as IQueryable so that everything executes on the database side and this extension function is still used in any portion of another query. I get the following compilation error when it's IQueryable:

A lambda expression with a statement body cannot be converted to an expression tree

If this were SQL, I would just do the following:

SELECT *, @QueryDate as QueryDate
FROM MyEntities
WHERE @QueryDate BETWEEN FromDate AND ToDate

So the issue is, how can I add this additional property assignment to the expression tree I currently have? I've researched IQueryable. IQueryable and expressions. Provider. There must be a solution hidden in CreateQuery. Could the current expression tree be extended to include an assignment expression? I'm not knowledgeable enough about expression tree approaches to solve this. Any thoughts?

Example of Use

To be more specific, the objective is to be able to carry out the following:

var entity = dataContext.Set<MyEntity>()
                        .WhereDateInRange(DateTime.Now)
                        .FirstOrDefault();

as well as the DateTime. The database query has now been inserted into the QueryDate of the resultant row WITHOUT returning more than one row. Before FirstOrDefault selects the desired row in the IEnumerable solution, many rows are returned.

Another Thought

QueryDate might be mapped as an actual field by setting its DatabaseGeneratedOption to Computed. However, I would then need a method of adding the "@QueryDate as QueryDate" to the SQL that EF's select statements generated. EF won't attempt to supply values during update or insert since it is calculated. What would be the best way to insert my own SQL into the select statements?

1
5
4/15/2011 6:15:02 PM

Accepted Answer

We appreciate all of the insightful comments. It seems that "no, you can't do it that way" is the response.

So I came up with a solution. Although extremely particular to my setup, this works.

public class MyEntity
{       
    private DateTime? _queryDate;

    [ThreadStatic]
    internal static DateTime TempQueryDate;

    [NotMapped]
    public DateTime? QueryDate
    {
        get
        {
            if (_queryDate == null)
                _queryDate = TempQueryDate;
            return _queryDate;
        }
    }

    ...       
}

public static IQueryable<T> WhereDateInRange<T>(this IQueryable<T> queryable, DateTime queryDate) where T : MyEntity
{
    MyEntity.TempQueryDate = queryDate;

    return queryable.Where(e => e.FromDate <= queryDate && e.ToDate >= queryDate);
}

The secret is that I'm caching the query date using a thread static field so that it may be accessed later in the same thread. It is particular to my requirements that I get it back in the QueryDate's getter.

Obviously, this isn't an EF or LINQ answer to the original query, but by removing it from that realm, it achieves the same result.

0
4/13/2011 7:47:03 PM

Popular Answer

Ladislav is entirely correct. However, because you clearly want a response to the second half of your query, here is how you may utilize Assign. But EF won't support this.

using System;
using System.Linq;
using System.Linq.Expressions;

namespace SO5639951
{
    static class Program
    {
        static void Main()
        {
            AdventureWorks2008Entities c = new AdventureWorks2008Entities();
            var data = c.Addresses.Select(p => p);

            ParameterExpression value = Expression.Parameter(typeof(Address), "value");
            ParameterExpression result = Expression.Parameter(typeof(Address), "result");
            BlockExpression block = Expression.Block(
                new[] { result },
                Expression.Assign(Expression.Property(value, "AddressLine1"), Expression.Constant("X")),
                Expression.Assign(result, value)
                );

            LambdaExpression lambdaExpression = Expression.Lambda<Func<Address, Address>>(block, value);

            MethodCallExpression methodCallExpression = 
                Expression.Call(
                    typeof(Queryable), 
                    "Select", 
                    new[]{ typeof(Address),typeof(Address) } , 
                    new[] { data.Expression, Expression.Quote(lambdaExpression) });

            var data2 = data.Provider.CreateQuery<Address>(methodCallExpression);

            string result1 = data.ToList()[0].AddressLine1;
            string result2 = data2.ToList()[0].AddressLine1;
        }
    }
}

1. Update

Here is the modified version of the same code. To make it quite clear that it's the "Assign" expression that EF does not accept, I removed the "Block" expression from the code above that EF choked on. Be aware that Assign does, in theory, function with generic Expression trees; it is the EF provider that does not.

using System;
using System.Linq;
using System.Linq.Expressions;

namespace SO5639951
{
    static class Program
    {
        static void Main()
        {
            AdventureWorks2008Entities c = new AdventureWorks2008Entities();

            IQueryable<Address> originalData = c.Addresses.AsQueryable();

            Type anonType = new { a = new Address(), b = "" }.GetType();

            ParameterExpression assignParameter = Expression.Parameter(typeof(Address), "value");
            var assignExpression = Expression.New(
                anonType.GetConstructor(new[] { typeof(Address), typeof(string) }),
                assignParameter,
                Expression.Assign(Expression.Property(assignParameter, "AddressLine1"), Expression.Constant("X")));
            LambdaExpression lambdaAssignExpression = Expression.Lambda(assignExpression, assignParameter);

            var assignData = originalData.Provider.CreateQuery(CreateSelectMethodCall(originalData, lambdaAssignExpression));
            ParameterExpression selectParameter = Expression.Parameter(anonType, "value");
            var selectExpression = Expression.Property(selectParameter, "a");
            LambdaExpression lambdaSelectExpression = Expression.Lambda(selectExpression, selectParameter);

            IQueryable<Address> finalData = assignData.Provider.CreateQuery<Address>(CreateSelectMethodCall(assignData, lambdaSelectExpression));

            string result = finalData.ToList()[0].AddressLine1;
        }        

        static MethodCallExpression CreateSelectMethodCall(IQueryable query, LambdaExpression expression)
        {
            Type[] typeArgs = new[] { query.ElementType, expression.Body.Type };
            return Expression.Call(
                typeof(Queryable),
                "Select",
                typeArgs,
                new[] { query.Expression, Expression.Quote(expression) });

        }
    }
}


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow