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.

Solution 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.

Solution 2

I'm guessing IsShipped is not mapped to a field in the database? That would explain why Linq to Entities complains - it cannot construct a sql statement based on this property.

Is your /* do stuff */ within the property based on fields that are in the database? If so, you could use that logic in your .Where().

Comments

[mxmissile]

Warning: This would load the entire table into memory if I am not mistaken.

[ekkis]

I see your point. EF can't figure out how to write SQL for whatever .IsShipped does. so in essence I have to fetch everything which will be expensive; any other thoughts?

[ekkis]

correct, it is not mapped to the database. as I mentioned I want to avoid writing the functionality into the query as it's not very DRY

[Darin Dimitrov]

@ekkis, yes this could be expensive. As I said in my answer the best solution would be to perform the querying inside the database (i.e. implement this logic as a query) or at least part of it, in order to reduce the amount of the returned result set before applying a second filter on the client.

[ekkis]

no, the /* do stuff */ is just a series of database queries. I need to count some stuff, compute some ratios and make a decision returned as a boolean. Also, I'm loath of getting all products in memory (as there are no other criteria to consider) since that will place a heavy burden on the server. the number of products shipped will be a tiny fraction of the total products... so I'm a bit at a loss. Darin suggests I perform a database query instead but I'm not quite sure how this is done.

[Merlyn Morgan-Graham]

@ekkis: Then the implementation of IsShipped becomes important. If it is "a series of database queries", how do you define it? Under the covers this should be some sort of extended join, possibly using sub-queries/temporary tables. You may have to give us your IsShipped implementation and possibly some of your DB schema for us to be able to help further.

[Merlyn Morgan-Graham]

@ekkis: Also, I noticed this when reviewing the question: "there's functionality there that I don't want to build into the LINQ query itself". You could look into solutions that allowed that clause to still be part of the Linq query, but to be defined elsewhere, such as making a WhereIsShipped extension method to IQueryable<Product>. I've taken this approach to encapsulating paging logic before, which is just a different type of criteria.

[ekkis]

wow @Slauma! you've given me a lot to digest, thank you. let me think it over. back in a bit.

[ekkis]

the logic is not very complex but it requires instantiating a new copy of the EF container (to do the lookups) and a loop, and I couldn't figure out how to embed it into my top query, which is why I pulled it out into a property of the class

[ekkis]

with reference to my last comment, I like your approach of the query extension, however, I'm in the same situation where I don't know how to embed a loop into an expression... for example, if a product has a number of components and I need to iterate through them looking for specific properties... how could I do that? (I'm experimenting to see if I can figure it out)

[Slauma]

@ekkis: Perhaps open better another question. It sounds like you must explain in more detail what "Do stuff" and what Component really is. "...Looking for specific properties..." (at runtime) makes me think that there will be some reflection involved, but for me your question is not tangible enough to give an answer. Apparently my example model above is too simple for what you are really trying to do.

[ekkis]

yes, you're right. here's the new question: stackoverflow.com/questions/7787625/…

[ekkis]

I'm pursuing your suggestion (and @Slauma's) of an extension method. I posted a link in my exchange with Slauma to a new question which contains the logic involved. thanks for your help.

[Johann]

@mxmissile you are not mistaken. Doing .ToList() on a table is generally a bad idea.