c# - Type member support in LINQ-to-Entities?

Question

I have an MVC3 project using the Entity Framework model in which I've marked up a class like this:

public partial class Product
{
    public bool IsShipped
    {
        get { /* do stuff */ }
    }
}

and which I want to use in a LINQ expression:

db.Products.Where(x => x.IsShipped).Select(...);

however, I get the following error:

System.NotSupportedException was unhandled by user code Message=The specified type member 'IsShipped' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported. Source=System.Data.Entity

I've googled but not found anything definitive about this usage to I tried:

public partial class Product
{
    public bool IsShipped()
    {
        /* do stuff */
    }
}

db.Products.Where(x => x.IsShipped()).Select(...);

but then I get:

System.NotSupportedException was unhandled by user code Message=LINQ to Entities does not recognize the method 'Boolean IsShipped()' method, and this method cannot be translated into a store expression.
Source=System.Data.Entity

there's functionality there that I don't want to build into the LINQ query itself... what's a good way to handle this?

* update *

Darin makes the valid point that whatever is done in the implementation of IsShipped would need to be converted to a SQL query and the compiler probably doesn't know how to do it, thus retrieving all objects into memory seems the only choice (unless a direct query to the database is made). I tried it like this:

IEnumerable<Product> xp = db.Quizes
    .ToList()
    .Where(x => !x.IsShipped)
    .Select(x => x.Component.Product);

but it generates this error:

A relationship multiplicity constraint violation occurred: An EntityReference can have no more than one related object, but the query returned more than one related object. This is a non-recoverable error.

though curiously this works:

IEnumerable<Product> xp = db.Quizes
    .ToList()
    .Where(x => x.Skill.Id == 3)
    .Select(x => x.Component.Product);

why would that be?

* update II *

sorry, that last statement doesn't work either...

* update III *

I'm closing this question in favour of pursuing a solution as suggested here to flatten my logic into a query - the discussion will move to this new post. The second alternative, to retrieve the entire original query into memory, is likely unacceptable, but the third, of implementing the logic as a direct query to the database, remain to be explored.

Thanks everyone for the valuable input.

Answer 1

The only way to make this "DRY" (avoid repeating the logic inside of IsShipped in the Where clause again) and to avoid loading all data into memory before you apply the filter is to extract the content of IsShipped into an expression. You can then use this expression as parameter to Where and in IsShipped as well. Example:

public partial class Product
{
    public int ProductId { get; set; }           // <- mapped to DB
    public DateTime? ShippingDate { get; set; }  // <- mapped to DB
    public int ShippedQuantity { get; set; }     // <- mapped to DB

    // Static expression which must be understood
    // by LINQ to Entities, i.e. translatable into SQL
    public static Expression<Func<Product, bool>> IsShippedExpression
    {
        get { return p => p.ShippingDate.HasValue && p.ShippedQuantity > 0; }
    }

    public bool IsShipped // <- not mapped to DB because readonly
    {
        // Compile expression into delegate Func<Product, bool>
        // and execute delegate
        get { return Product.IsShippedExpression.Compile()(this); }
    }
}

The you can perform the query like so:

var result = db.Products.Where(Product.IsShippedExpression).Select(...).ToList();

Here you would have only one place to put the logic in (IsShippedExpression) and then use it for database queries and in your IsShipped property as well.

Would I do this? In most cases probably no, because compiling the expression is slow. Unless the logic is very complex, likely a subject to change and I am in a situation where the performance of using IsShipped doesn't matter, I would repeat the logic. It's always possible to extract often used filters into an extension method:

public static class MyQueryExtensions
{
    public static IQueryable<Product> WhereIsShipped(
        this IQueryable<Product> query)
    {
        return query.Where(p => p.ShippingDate.HasValue && p.ShippedQuantity >0);
    }
}

And then use it this way:

var result = db.Products.WhereIsShipped().Select(...).ToList();

You would have two places though the maintain the logic: the IsShipped property and the extension method, but then you can reuse it.