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

Question

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

Simple answer: You cannot use not mapped properties in linq-to-entities queries! Only mapped properties are translated to SQL. — Ladislav Mrnka, Aug 03 '11 at 08:12
Does this answer your question? [Can I make a preprocessor directive dependent on the .NET framework version?](https://stackoverflow.com/questions/4535622/can-i-make-a-preprocessor-directive-dependent-on-the-net-framework-version) — Chris Schaller, Jul 29 '23 at 13:58

Eugene S. · Accepted Answer · 2012-10-21T17:04:57.763

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.

Koen Luyten · Answer 2 · 2015-02-03T10:27:43.957

26

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.

edited Feb 03 '15 at 10:27

answered Feb 03 '15 at 10:21

Koen Luyten

263
3
5

1

There are a number of libraries that attempt to make this approach more "built in" see: http://stackoverflow.com/a/27383641/470183. Linq-to-entities is limited to expressions using the "Canonical Functions" - which can be turned into SQL. C# 6 introduced "Expression bodied functions" but these are not true lambdas (see: http://stackoverflow.com/a/28411444/470183). Still it would be good to have this in the framework hence the WIBNI https://data.uservoice.com/forums/72025-entity-framework-feature-suggestions/suggestions/15087837-support-c-6-expression-bodied-function-members-in – James Close Jul 05 '16 at 16:19
1

Thank you for this simple and concise example of `Expression>`. I ever understood it before, but it looks like obvious now. – AlexB Dec 23 '16 at 11:02

Jess · Answer 3 · 2016-11-30T13:50:12.890

19

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.

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

edited Nov 30 '16 at 13:50

answered Jun 21 '16 at 15:17

Jess

23,901
21
124
145

Dang AutoMapper Projection caught me too, thanks for the answer! – Chase Florell May 07 '18 at 23:03

score 15 · Answer 4 · answered Apr 18 '13 at 15:57

15

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

answered Apr 18 '13 at 15:57

T Gupta

947
11
11

22

I would suggest that asking someone to make a toList() on orders is dangerous since it would mean retrieving the entire list – elgrego Feb 24 '16 at 11:34
This is good for me because my problematic property is in a Sum Linq function not in the Where clause. So I'm not getting unnecessary data and on the data retreived I'm doing the Linq Sum function which is working on the List. Thank you! What may look bad at first can be very helpful in certain situations! – Dov Miller Nov 22 '17 at 11:34

Serj Sagan · Answer 5 · 2018-11-05T16:32:53.357

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.

score 2 · Answer 6 · answered Jun 04 '18 at 02:38

2

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?

answered Jun 04 '18 at 02:38

Ony

399
6
16

This link saved quite a lot of time! Thank you a lot! – Vladimir Semashkin Jun 21 '18 at 19:50

score 1 · Answer 7 · answered Apr 15 '19 at 15:51

1

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

answered Apr 15 '19 at 15:51

Ganesh Nikam

21
5

This was my issue! I am new to the project and have never seen the use of EDMX files before. Thanks so much for documenting this niche corner case. – cchapin Jul 12 '22 at 01:53

Basheer AL-MOMANI · Answer 8 · 2017-02-12T04:19:51.270

0

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

edited Feb 12 '17 at 04:19

answered Nov 16 '16 at 15:21

Basheer AL-MOMANI

14,473
9
96
92

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

8 Answers8

Linked

Related