Unable to create a constant value of type 'Data.Item'. Only primitive types or enumeration types are supported in this context

Question

I use the below mentioned query to Flatten an ObservableCollection:

using(RateDifferenceDBEntities db = new RateDifferenceDBEntities())
{
    Items = new ObservableCollection<Item>(db.Items);


    Orders = new ObservableCollection<ClaimDTO>(from order in db.Orders
                                                from orderItemDetail in order.OrderItemDetails
                                                from dispatch in order.Dispatches
                                                from dispatchItemDetail in dispatch.DispatchItemDetails
                                                where ((orderItemDetail.ItemId == dispatchItemDetail.ItemId && dispatchItemDetail.Rate > orderItemDetail.Rate)
                                                        && (dispatch.DateOfDispatch >= StartDate && dispatch.DateOfDispatch <= EndDate))
                                                select new ClaimDTO
                                                {
                                                    SalesOrderId = order.SalesOrderId,
                                                    DateOfOrder = order.DateOfOrder,
                                                    PartyName = order.Party.PartyName,
                     ------------>                  OrderItemName = Items.Where(x => x.ItemId == orderItemDetail.ItemId).FirstOrDefault().ItemName,
                                                    OrderQuantity = orderItemDetail.Quantity,
                                                    OrderRate = orderItemDetail.Rate,
                                                    OrderAmount = orderItemDetail.Amount,
                                                    SalesInvoiceId = dispatch.SalesInvoiceId,
                                                    DateOfDispatch = dispatch.DateOfDispatch,
                     ------------>                  DispatchItemName = Items.Where(x => x.ItemId == dispatchItemDetail.ItemId).FirstOrDefault().ItemName,
                                                    DispatchQuantity = dispatchItemDetail.Quantity,
                                                    DispatchRate = dispatchItemDetail.Rate,
                                                    DispatchAmount = dispatchItemDetail.Amount
                                                });

    return Orders;
}

Can you see two dotted arrows in above code??

I geet an error there:

Unable to create a constant value of type 'Data.Item'. Only primitive types or enumeration types are supported in this context.

Here is the Heirarchical collection that I use to build the above mentioned query:

Order //Collection
 |-OrderId
 |-DateOfOrder
 |-PartyName
 |-OrderDetails //Collection
 |    |-ItemName
 |    |-Quantity
 |    |-Rate
 |    |-Amount
 |-Dispatch //Collection
 |    |-InvoiceNo
 |    |-DateOfDispatch
 |    |-DispatchDetails //Collection
 |    |    |-ItemName
 |    |    |-Quantity
 |    |    |-Rate
 |    |    |-Amount

Raja Nadar Raja Nadar · Accepted Answer · 2016-06-29T23:14:47

I think in the 2 dotted lines where you are getting errors, you are trying to join a local dataset (items) with a database query (non-materialized) dataset.

Items.Where(x => x.ItemId == orderItemDetail.ItemId).FirstOrDefault().ItemName,

This is not allowed by EF or LINQ2SQL.

Either you need to

materialize both datasets and then run predicates on them locally in-memory, or
have both datasets as IQueryable or
use something like PredicateBuilder from LinqKit to build the predicate for the IQueryable using the local dataset.

i think the easiest thing to do would be not use the Observale Items variable in the query and use db.Items.

Orders = new ObservableCollection<ClaimDTO>(from order in db.Orders
                                            from goodItems in db.Items
                                            from orderItemDetail in order.OrderItemDetails
                                            from dispatch in order.Dispatches
                                            from dispatchItemDetail in dispatch.DispatchItemDetails
                                            where ((orderItemDetail.ItemId == dispatchItemDetail.ItemId && dispatchItemDetail.Rate > orderItemDetail.Rate)
                                                    && (dispatch.DateOfDispatch >= StartDate && dispatch.DateOfDispatch <= EndDate))
                                            select new ClaimDTO
                                            {
                                                SalesOrderId = order.SalesOrderId,
                                                DateOfOrder = order.DateOfOrder,
                                                PartyName = order.Party.PartyName,
                 ------------>                  OrderItemName = goodItems.Where(x => x.ItemId == orderItemDetail.ItemId).FirstOrDefault().ItemName,
                                                OrderQuantity = orderItemDetail.Quantity,
                                                OrderRate = orderItemDetail.Rate,
                                                OrderAmount = orderItemDetail.Amount,
                                                SalesInvoiceId = dispatch.SalesInvoiceId,
                                                DateOfDispatch = dispatch.DateOfDispatch,
                 ------------>                  DispatchItemName = goodItems.Where(x => x.ItemId == dispatchItemDetail.ItemId).FirstOrDefault().ItemName,
                                                DispatchQuantity = dispatchItemDetail.Quantity,
                                                DispatchRate = dispatchItemDetail.Rate,
                                                DispatchAmount = dispatchItemDetail.Amount
                                            });

return Orders;

Unable to create a constant value of type 'Data.Item'. Only primitive types or enumeration types are supported in this context

1 Answers