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?

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

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.

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.

Answer 4

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

Answer 5

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.