Custom properties and lambda expressions

问题:

I've added a custom property to a LINQ-to-SQL entity:

public partial class User
{
    public bool IsActive
    {
        get
        {
            // note that startDate and endDate are columns of User table
            return startDate <= DateTime.Now && endDate >= DateTime.Now;
        }
    }
}

and now I need to use this property with lambda expressions:

activeUsers = users.Where(u => u.IsActive);

but when this code is executed I get a System.NotSupportedException. The exception message says that "SQL conversions for the member 'User.IsActive' are not supported".

Is there a way to solve this problem?

回答1:

The IsActive you show is regular C# - it will be compiled to IL and will not be available for LINQ-to-SQL (etc) to inspect and turn into TSQL to execute at the database. One option here might be:

public static Expression<Func<User,bool>> GetIsActiveFilter() {
    return user => user.StartDate <= DateTime.Now &&
                   user.EndDate >= DateTime.Now;
}

then you should be able to use:

activeUsers = users.Where(User.GetIsActiveFilter());

Or similarly - maybe an extension method:

public static IQueryable<User> ActiveOnly(this IQueryable<User> users) {
    return users.Where(user => user.StartDate <= DateTime.Now &&
                               user.EndDate >= DateTime.Now);
}

then:

activeUsers = users.ActiveOnly();

The difference here is that we are using the IQueryable<T> interface and expression trees throughout, which allows LINQ to understand our intent, and create suitable TSQL to achieve the same.

回答2:

You can try the following:

activeUsers = users.AsEnumerable().Where(u => u.IsActive);

Note though that the criteria IsActive will not be translated to SQL since the logic behind IsActive is not known from a database perspective. So the database will execute a read on the whole table users, and the criteria will be applied in-memory.

回答3:

I solved my similar problem in this way:

my ORM was NHibernate and the underlying DB was Oracle, when i need to add some field like IsActive, did somthing like this

public partial class User
{
   public bool IsActive{get;set;}
}

in the related mapping file add something like this

<property name="IsActive" access="property" insert="false" update="false"
  formula="case when startDate  <= sysdate And endDate  >= sysdate then 1 else 0 end" />

now you can use

activeUsers = users.Where(u => u.IsActive);