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

Question

var result =
    (from bd in context.tblBasicDetails
     from pd in context.tblPersonalDetails.Where(x => x.UserId == bd.UserId).DefaultIfEmpty()
     from opd in context.tblOtherPersonalDetails.Where(x => x.UserId == bd.UserId).DefaultIfEmpty()
     select new clsProfileDate()
     {
         DOB = pd.DOB
     });

foreach (clsProfileDate prod in result)
{
    prod.dtDOB = !string.IsNullOrEmpty(prod.DOB) ? Convert.ToDateTime(prod.DOB) : DateTime.Today;
    int now = int.Parse(DateTime.Today.ToString("yyyyMMdd"));
    int dob = int.Parse(prod.dtDOB.ToString("yyyyMMdd"));
    string dif = (now - dob).ToString();
    string age = "0";
    if (dif.Length > 4)
    age = dif.Substring(0, dif.Length - 4);
    prod.Age = Convert.ToInt32(age);
}

GetFinalResult(result);

---

protected void GetFinalResult(IQueryable<clsProfileDate> result)
{
    int from;
    bool bfrom = Int32.TryParse(ddlAgeFrom.SelectedValue, out from);
    int to;
    bool bto = Int32.TryParse(ddlAgeTo.SelectedValue, out to);

    result = result.AsQueryable().Where(p => p.Age >= from);
}

Here I am getting an exception:

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

Where Age is not in database it is property I created in clsProfileDate class to calculate Age from DOB. Any solution to this?

Terrell

Answer 1

You cannot use properties that are not mapped to a database column in a Where expression. You must build the expression based on mapped properties, like:

var date = DateTime.Now.AddYears(-from);
result = result.Where(p => date >= p.DOB);
// you don't need `AsQueryable()` here because result is an `IQueryable` anyway

As a replacement for your not mapped Age property you can extract this expression into a static method like so:

public class clsProfileDate
{
    // ...
    public DateTime DOB { get; set; } // property mapped to DB table column

    public static Expression<Func<clsProfileDate, bool>> IsOlderThan(int age)
    {
        var date = DateTime.Now.AddYears(-age);
        return p => date >= p.DOB;
    }
}

And then use it this way:

result = result.Where(clsProfileDate.IsOlderThan(from));

Machzor

In addition check hp93 answer below, from a in db.aitems select new Model { A = a.A } here in this queryable you have to specify explecitly all the properties you want to use in Where, so here you can filter or group, or order only by A, because you have mapping only for A - A = a.A – Fleam

Answer 2

A lot of people are going to say this is a bad answer because it is not best practice but you can also convert it to a List before your where.

result = result.ToList().Where(p => date >= p.DOB);

Slauma's answer is better, but this would work as well. This cost more because ToList() will execute the Query against the database and move the results into memory.

Abstriction

Felt dirty, but effective in small doses. – Laith

when ToList is called, the query will be executed and unnecessary data will be loaded in to memory. so it's better to have some limits in coding! – Subulate

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! – Nonresistant

this worked great for me because we are modifying one of the fields to prepend data on it based on a boolean value in another field. (marking items deactivated) and it was refusing to work until i stuck a tolist in the middle. – Sealey

Exaclty what I needed! – Daberath

So tempted to downvote this but wont since the answer is not wrong, just poor. – Bob

Answer 3

You will also get this error message when you accidentally forget to define a setter for a property. For example:

public class Building
{
    public string Description { get; }
}

var query = 
    from building in context.Buildings
    select new
    {
        Desc = building.Description
    };
int count = query.ToList();

The call to ToList will give the same error message. This one is a very subtle error and very hard to detect.

Mustard

After some foolish re-factoring, this is what got me. – Touchback

But if it is a non-updateable SQL view, why would I need the setter? – Kentiga

That does not matter. Entity Framework still needs a way to set the property. In normal C# code you would not be able to do something like: Building b = new Building(); b.Description = "test"; – Mustard

Answer 4

In my case, I was getting this error message only in Production but not when run locally, even though my application's binaries were identical.

In my application, I'm using a custom DbModelStore so that the runtime-generated EDMX is saved to disk and loaded from disk on startup (instead of regenerating it from scratch) to reduce application startup time - and due to a bug in my code I wasn't invalidating the EDMX file on-disk - so Production was using an older version of the EDMX file from disk that referenced an older version of my application's types from before I renamed the type-name in the exception error message.

Deleting the cache file and restarting the application fixed it.

Lemon

Answer 5

I forgot to select the column (or set/map the property to a column value):

IQueryable<SampleTable> queryable = from t in dbcontext.SampleTable
                                    where ...
                                    select new DataModel { Name = t.Name };

Calling queryable.OrderBy("Id") will throw exception, even though DataModel has property Id defined.

The correct query is:

IQueryable<SampleTable> queryable = from t in dbcontext.SampleTable
                                    where ...
                                    select new DataModel { Name = t.Name, Id = t.Id };

Induce

Answer 6

Advanced answer:

Search in edmx file EntitySetMapping and check if the field is mapped to a column in database:

<EntitySetMapping Name="MY_TABLE">
    <EntityTypeMapping TypeName="MYMODEL.MY_TABLE">
      <MappingFragment StoreEntitySet="MY_TABLE">
        <ScalarProperty Name="MY_COLUMN" ColumnName="MY_COLUMN_NAME" />
      </MappingFragment>
    </EntityTypeMapping>
  </EntitySetMapping>

I was having this problem because the edmx had changes I didn't want and through git I discarded too many changes...

Skelly

Answer 7

Checking Count() before the WHERE clause solved my problem. It is cheaper than ToList()

if (authUserList != null && _list.Count() > 0)
    _list = _list.Where(l => authUserList.Contains(l.CreateUserId));

Alvey