How to assign a property value of an IQueryable<T>?

Question

I'm using Entity Framework 4.1 Code First. In my entity, I have three date/time properties:

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
}

In the database, I always have the From/To dates populated. I query against them using a simple where clause. But in the result set, I want to include the date I queried for. I need to persist this for some other business logic to work.

I'm working on an extension method to do this, but I'm running into problems:

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 doesn't work. It works if I use an IEnumerable, but I want to keep it as IQueryable so everything runs on the database side, and this extention method can still be used in any part of another query. When it's IQueryable, I get a compile error of the following:

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

If this was SQL, I would just do something like this:

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

So the question is, how can I transform the expression tree I already have to include this extra property assignment? I have looked into IQueryable.Expression and IQueryable.Provider.CreateQuery - there's a solution in there somewhere. Maybe an assignment expression can be appended to the existing expression tree? I'm not familiar enough with the expression tree methods to figure this out. Any ideas?

Example Usage

To clarify, the goal is to be able to perform something like this:

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

And have the DateTime.Now persisited into the QueryDate of the resulting row, WITHOUT having more than one row returned from the database query. (With the IEnumerable solution, multiple rows are returned before FirstOrDefault picks the row we want.)

Another Idea

I could go ahead and map QueryDate like a real field, and set its DatabaseGeneratedOption to Computed. But then I would need some way to inject the "@QueryDate as QueryDate" into the SQL created by EF's select statements. Since it's computed, EF won't try to provide values during update or insert. So how could I go about injecting custom SQL into the select statements?

Solution 1

Thank you for all of the valuable feedback. It sounds like the answer is "no - you can't do it that way".

So - I figured out a workaround. This is very specific to my implementation, but it does the trick.

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 magic is that I'm using a thread static field to cache the query date so it's available later in the same thread. The fact that I get it back in the QueryDate's getter is specific to my needs.

Obviously this isn't an EF or LINQ solution to the original question, but it does accomplish the same effect by removing it from that world.

Solution 2

Ladislav is absolutely right. But since you obviously want the second part of your question to be answered, here is how you can use Assign. This won't work with EF, though.

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;
        }
    }
}

Update 1

Here is the same code after some tweaking. I got rid of the "Block" expression, that EF choked on in the code above, to demonstrate with absolute clarity that it's "Assign" expression that EF does not support. Note that Assign works in principle with generic Expression trees, it is EF provider that does not support Assign.

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) });
            
        }
    }
}

Solution 3

No, I don't think there is a solution. It is true that you can modify expression tree but you will get exactly the same exception as you got with your linq query because that query actually is what you will build in expression tree. The problem is not in expression tree but in the mapping. EF can't map QueryData to the result. Moreover you are trying to do projection. Projection can't be done to mapped entity and anonymous type can't be returned from the method.

You can off course do the select you mentioned but simply you can't map it to your entity. You must create a new type for that:

var query = from x in context.MyData
            where x.FromDate <= queryDate && x.ToDate >= queryDate
            select new MyDateWrapper
                {
                   MyData = x,
                   QueryDate = queryDate
                };

Solution 4

Automapper has Queryable Extensions, i think it can resolve your needs. You can use ProjectTo to calculate property on runtime.

Ef Core 2 set value to ignored property on runtime

http://docs.automapper.org/en/stable/Queryable-Extensions.html

Example configuration:

 configuration.CreateMap(typeof(MyEntity), typeof(MyEntity))
    .ForMember(nameof(Entity.QueryDate), opt.MapFrom(src => DateTime.Now));

Usage:

queryable.ProjectTo<MyEntity>();

View more solutions

Comments

[Slauma]

I think, the problem is not in the mapping. You can reproduce the error without EF at all. Something like Expression<Func<Person, Person>> e = p => { p.Name = "X"; return p; }; doesn't compile because of the statement part inside of the lambda. (He doesn't actually project, he tries to make a function returning a full entity, but a function with internal "side effect" (the querydate assignment).) I think it's pure expression tree problem, not EF. Normal lambdas for IEnumerable work though: Func<Person, Person> f = p => { p.Name = "X"; return p; }; compiles.

[Ladislav Mrnka]

@Slauma: Thanks, I didn't think about that this way. In such case this is another (and the frist) problem because my mentioned problems are just other in the queue.

[Matt Johnson-Pint]

@Slauma: That's right - it doesn't matter if I'm using EF or something else. I can't seem to get an assignment expression into an existing expression tree. There is Expression.Assign() - but I can't find a clear example of how to use it.

[Ladislav Mrnka]

Even if you find the way to use Assign it will still not work because Ef will not be able to materialize result.

[Andrew Savinykh]

If your app is asp.net I would certainly not recommend you do this and go with Ladislav's solution instead. In fact I'd choose Ladislav's solution even if it was not asp.net, as you setting yourself up for potential problems with threading, the most difficult problems to debug. This is especially true if someone else will have to support your project after you. Also see hanselman.com/blog/… and piers7.blogspot.com/2005/11/…

[Matt Johnson-Pint]

Yes, I saw those posts, and all the other warnings about thread local storage & thread static fields. However, in my case I ONLY need the value to be correct in between when it is first set and when I next use it, and that is ONLY ever neccessary in the same thread. So it should be safe to do. This is specifically why I copy it to a regular field within the entity itself.

[Matt Johnson-Pint]

Also, I cannot use the wrapper solution proposed by Ladislav, because I need to continue to work with an IQueryable of the entity I started with. If I project the results into a wrapper, then I am now working with an IEnumerable of the wrapper. That prevents me from doing additional filtering/sorting at the database.

[Andrew Savinykh]

>>"If I project the results into a wrapper, then I am now working with an IEnumerable of the wrapper." This is a rather strange thing to say. Why would you say that?

[Andrew Savinykh]

>>"and that is ONLY ever necessary in the same thread". Without knowledge of your threading assumption someone can change your code, in a way you can't imagine now, and then spend a week trying to understand, why the code sometimes doesn't work. Ex. in ASP.NET MVC there are Async Controllers. Convert your stuff to execute as part of this controller later down the track and bum you are in trouble. This is the kind of things that come and bite you, at the least expected time. And also, this is what you see in other ppl code you support, which you wish was not there. It's not safe programming.

[Matt Johnson-Pint]

What I mean is that when I use my WhereDateInRange method, I expect to get back an IQueryable<MyEntity>. If I use Ladislav's suggestion to wrap the result set, I now have to return an IEnumerable<MyDateWrapper>. If I'm going to return an IEnumerable, I might as well do it the original way I proposed in the question and use IEnumerable<MyEntity>.

[Matt Johnson-Pint]

(continued) I will have usage in my application such as dataContext.Set<MyEntity>().WhereDateInRange(DateTime.Now).S‌​ingleOrDefault(); If SingleOrDefault is performed against the IEnumerable<MyEntity>, then the query from the database could contain many rows of data I don't need - causing a performance problem. If it returns IQueryable<MyEntity>, then SingleOrDefault will alter the expression tree and I will be successful in returning only one row from the database.

[Matt Johnson-Pint]

(continued) Regarding the threading, I understand that there are other scenarios where my code could be called in different threading models. But the only scenario that would have an impact is if the query was defined in one thread and materialized in another. This never happens. It doesn't matter if you are calling it synchronously or asynchronously, after the query materalizes, the TempQueryDate is no longer used, and thus validity is irrelavent. That is why it is marked internal. And since DateTime is a value type, I shouldn't have to worry about releasing resources.

[Matt Johnson-Pint]

(continued) I know you feel strongly against this solution, but unless you can think of something better, this will work for me. (I was really hoping I could use Assign in the expression, but as you demonstrated, I can't.)

[Matt Johnson-Pint]

(Please also see the "Another Idea" section I added to the original question)

[Matt Johnson-Pint]

(Oops, in above comment, I meant FirstOrDefault, not SingleOrDefault. Obviously if there is only one row there, the point is mute.)

[Matt Johnson-Pint]

The question was asked many years ago. I'll review your solution, but in general I am not doing this any more. Thanks anyway.