无法创建类型为"数据项"的常量值。在此上下文中仅支持基元类型或枚举类型

Question

我使用下面提到的查询来Flatten 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;
}

你能看到上面代码中的两个虚线箭头吗??

出现错误:

无法创建类型为"Data.Item"的常数值。在此上下文中只支持基本类型或枚举类型。

下面是我用来构建上面提到的查询的层次集合:

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

Answer 1

我认为在你得到错误的2虚线，你试图加入一个本地数据集(项目)与数据库查询(非物化)数据集。

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

EF/LINQ2SQL是不允许的。

或者你需要

• 实现两个数据集，然后在本地内存中运行谓词，或者
• 具有可查询的数据集或
• 使用LinqKit中的PredicateBuilder之类的东西来使用本地数据集为IQueryable构建谓词。

我认为最简单的事情是不要在查询中使用Observale Items变量，而使用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;