CODEWITHSUNDEEP
linqentity-framework
Marc
Marc

Reputation: 2083

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

I'm getting this exception :

The specified type member 'Paid' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

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

        return View(debts);
    }

My Model class

public partial class Order
{
    public bool Paid {
        get {
            return TotalPaid >= Total;
        }
    }

    public decimal TotalPaid {
        get {
            return Payments.Sum(p => p.Amount);
        }
    }

Payments is a Related table containing the field amount, The query works if I remove the Where clause showing correct information about the payments, any clue what's wrong with the code?

Solved like the answer suggested with :

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

        return View(debts);
    }

Upvotes: 104

Views: 101450

Answers (8)

Ganesh Nikam
Ganesh Nikam

Reputation: 21

your edmx and context model have some diffrent property which is newly added into db.

Update your EDMX refresh it properly Bulid your project and run again.

It will solve your issue.

Regards, Ganesh Nikam

Upvotes: 1

Serj Sagan
Serj Sagan

Reputation: 30178

The other likely reason is because you are using IEnumerable for your property, instead of ICollection

So instead of:

public class This
{
    public long Id { get; set; }
    //...
    public virtual IEnumerable<That> Thats { get; set; }
}

Do this:

public class This
{
    public long Id { get; set; }
    //...
    public virtual ICollection<That> Thats { get; set; }
}

And you're hunky dory... stupid thing to lose 2 hours over.

Upvotes: 12

Ony
Ony

Reputation: 419

This situation also can happen if you are using unsupported by EntityFramework types, such as unsigned int.

This was my case of such error.

Checkout out further information on supported types: https://msdn.microsoft.com/en-us/library/ee382832(v=vs.100).aspx

There is some workaround for such situations, explained by GFoley83: How to use unsigned int / long types with Entity Framework?

Upvotes: 2

Basheer AL-MOMANI
Basheer AL-MOMANI

Reputation: 15327

I faced this issue because was having a member variable with only get without set property

that's means its auto calculated and not stored as a column in the table

therefore its not exist in the table schema

so make sure that any member variable not auto calculated to have a getter and setter properties

Upvotes: 0

Jess
Jess

Reputation: 25029

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

Upvotes: 19

Koen Luyten
Koen Luyten

Reputation: 283

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.

Upvotes: 26

T Gupta
T Gupta

Reputation: 957

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

Upvotes: 15

Eugene S.
Eugene S.

Reputation: 3265

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.

Upvotes: 116

Related Questions