Only initializers, entity members, and entity navigation properties are supported

Entity is trying to convert your Paid property to SQL and can't because it's not part of the table schema.

What you can do is let Entity query the table with no Paid filter and then filter out the not Paid ones.

public ActionResult Index()
{
    var debts = storeDB.Orders
        //.Where(o => o.Paid == false)
        .OrderByDescending(o => o.DateCreated);

    debts = debts.Where(o => o.Paid == false);

    return View(debts);
}

That, of course, would mean that you bringing all of the data back to the web server and filtering the data on it. If you want to filter on the DB server, you can create a Calculated Column on the table or use a Stored Procedure.


Just had to solve a similar problem. Solutions above require in-memory processing, which is a bad practice (lazy loading).

My solution was to write a helper that returned a predicate:

public static class Extensions
{
    public static Expression<Func<Order, bool>> IsPaid()
    {
        return order => order.Payments.Sum(p => p.Amount) >= order.Total;
    }
}

You can rewrite your linq statement as:

var debts = storeDB.Orders
                    .Where(Extensions.IsPaid())
                    .OrderByDescending(o => o.DateCreated);

This is handy when you want to reuse the calculation logic (DRY). Downside is that the logic is not in your domain model.


This problem can also come from a [NotMapped] property that has the same name in your DB Model and View Model.

AutoMapper tries to select it from the DB during a projection; and the NotMapped property obviously does not exist in the DB.

The solution is to Ignore the property in the AutoMapper config when mapping from the DB Model to the View Model.

  1. Look for a [NotMapped] property with name Foo in your DB Model.
  2. Look for a property with the same name, Foo, in your View Model.
  3. If that is the case, then change your AutoMapper config. Add .ForMember(a => a.Foo, b => b.Ignore());

Linq converts the statements into SQL statements and execute them into database.

Now, this conversion only occurs for entities members, initializers and entity navigation properties. So to achieve function or get property comparison, we need to first convert them into in-memory listing and then apply function to retrieve data.

Therefore in totality,

var debts = storeDB.Orders.toList()
        .Where(o => o.Paid == false)
        .OrderByDescending(o => o.DateCreated);