Fill ICollection from List, ICollection returns null

Question

I've the following model

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

And

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 is a large View on SQL Server with low performance, I cannot ask the results in one query. The performance is good when retrieved one item at a time.

I retrieve the data using this query

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

As you can see in the comments when I add the ItemPrices to SalesPrices it turns from an empty list, Count = 0, to a List that is NULL.

I'm struggling to figure this out, how can I fill the ICollection with the List?

Kind regards

Jeroen

Answer 1

The problem is that in this piece of code

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 is a list of anonymous objects, and it cannot be casted using as into list of objects of type ItemPrices. Therefore cast operation prices as ICollection<ItemPrices> returns null.

To fix the problem you should not use projection (Select(...)) when getting ItemPrices:

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

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