从列表填充ICollection，ICollection返回null

Question

我有以下模型

 public class Items
{
    [Key]
    public Guid Id { get; set; }       
    public string ItemCode { get; set; }

    public virtual ICollection<ItemPrices> SalesPrices { get; set; }

    public App4Sales_Items()
    {

        SalesPrices = new HashSet<ItemPrices>();
    }
 }

和

public class ItemPrices
{
    [Key, Column(Order = 0), ForeignKey("Items")]
    public Guid Id { get; set; }
    public virtual App4Sales_Items Items { get; set; }

    [Key, Column(Order=1)]
    public Guid PriceList { get; set; }        
    public decimal PriceExcl { get; set; }
    public decimal PriceIncl { get; set; }
    public decimal VatPercentage { get; set; }        

}

ItemPrice是SQL Server上的一个大视图，性能很低，我不能在一个查询中询问结果。当一次检索一个项目时，性能很好。

我使用以下查询检索数据

  var test =  db.Items
           .OrderBy(i => i.ItemCode)
           .Skip(opts.Skip.Value)
           .Take(100)
           .Select(i => new
           {
               i.Id,
               i.ItemCode,                   
           }).ToList();

        List<Items> result = new List<Items>();
        for (int i = 0; i < test.Count; i++)
        {
            Items item = new Items()
            {
                Id =test[i].Id,
                ItemCode = test[i].ItemCode,
            };
            Guid testId = test[i].Id;
            var prices = db.ItemPrices
                         .Where(a => a.Id == testId)
                         .Select(a => new
                         {
                             a.Id,
                             a.PriceList,
                             a.PriceExcl,
                             a.PriceIncl,
                             a.VatPercentage
                         }).ToList();
              // ItemPrices is for example a list of 15 items
             // Here is SalesPrices Count = 0

            item.SalesPrices = prices as ICollection<ItemPrices>;
            //Here is Salesprices =null
            result.Add(item);

正如您在注释中看到的，当我将计数添加到SalesPrices中时，它会从一个空列表ItemPrices = 0变成一个空列表。

我正在努力弄清楚这一点，我如何才能用列表填充ICollection？

亲切的问候

耶罗恩

Answer 1

问题是在这段代码中

var prices = db.ItemPrices
    .Where(a => a.Id == testId)
    // Here anonymous objects are created.
    // Therefore "prices" is a list of anonymous objects.
    .Select(a => new
    {
        a.Id,
        a.PriceList,
        a.PriceExcl,
        a.PriceIncl,
        a.VatPercentage
    }).ToList();

item.SalesPrices = prices as ICollection<ItemPrices>;

prices是匿名对象的列表，不能使用as将其强制转换为ItemPrices类型的对象列表。因此，强制转换操作prices as ICollection<ItemPrices>返回null。

要解决此问题，在获取ItemPrices时不应使用投影(Select(...)

var prices = db.ItemPrices
    .Where(a => a.Id == testId)
    .ToList();

// Now casting to ICollection<ItemPrices> is not needed.
item.SalesPrices = prices;