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

Question

I have spent the past 2 days wondering why this is not working, but for my other tables it is working perfectly fine. I even tested my other model which contains many fields. BUt this particular one, even with ONLY 2 fields is not working. I know I might missing an obvious part, please help.

Here's my Model

public class ReceivedItem
{        
    public int ReceivedItemID { get; set; }
    public int ItemID { get; set; }
}

ViewModel

public class ReceivedItemViewModel
 {
    public int ReceivedItemID { get; set; }
    public int ItemID { get; set; }
 }

Controller

[GridAction]
public ActionResult GetReceivedItems()
  {
      return View(new GridModel(GetReceivedItemsViewModels()));
  }

private IQueryable<ReceivedItemViewModel> GetReceivedItemsViewModels()
{
    return db.ReceivedItems
         .Select(
          c => new ReceivedItemViewModel
            { 
               ItemID = c.ItemID
             });
 }

View

 @(Html.Telerik().Grid<ReceivedItem>()
.Name("grdItems")
.DataBinding(binding => binding.Ajax()
    .Select("GetReceivedItems", "Receiving"))
.DataKeys(keys => keys.Add(o => o.ItemID))
.Columns(cols =>
{  
    cols.Bound(c => c.ItemID);
})
.Pageable()
.Sortable()
.Groupable()
.Filterable()

)

Error i got using firebug:

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

Answer 1

From what I know, you can't initialize non-entity objects in a Linq-to-SQL query. Try enumerating the results, then using Linq to create your view models.

What you have (I'm guessing this is where the exception is being throw):

// Original Code
return db.ReceivedItems
     .Select(
      c => new ReceivedItemViewModel
        { 
           ItemID = c.ItemID
         });

Enumerating, then using Linq to create your view models:

// First statement
var items = db.ReceivedItems.ToArray(); // Enumerates the collection.
// Second statement
return items.Select(
  c => new ReceivedItemViewModel
    { 
       ItemID = c.ItemID
     });

The first Linq statement is translated into a SQL statement, the results are returned and enumerated as an array, then lastly, in the second statement, that array is used to create the collection of view models. With your original statement, SQL translation of the Linq statement would have to account for the view model (which it is unable to do).

I hope this helps. (and makes sense) :)

Answer 2

Check to make sure you're not using IEnumerable for your collection, see more here: https://stackoverflow.com/a/32997694/550975

Answer 3

I believe the issue here is actually stemming from Telerik, because I just ran into this issue using a Telerik Kendo Grid. Projecting to a view model (inside the select) was not the actual cause of the exception.

Notice that the referenced column "ReceivedItemID" is not being mapped in the projection. I fixed my issue by simply assigning the missing property.

return db.ReceivedItems
     .Select(
      c => new ReceivedItemViewModel
        { 
           ReceivedItemID = c.ReceivedItemID
           ItemID = c.ItemID
         });

In my case, there was an additional step the model was running through:

return Json(model.ToDataSourceResult(request));

The Telerik ToDataSourceRequest extension method modifies the SQL query, and it was getting confused.