LINQ Grouping a query

Question

I initially asked a question as i was getting and error with a similar query and i found help in fixing that, I now have an question / need for a little help understanding how to formulate the correct group for a return, this will be for WEB API and need the output grouped in a certain way and i cant quite get there.

Class -

public class GoodInWarehouseBM
{
    /// <summary>
    /// Pallet Number
    /// </summary>
    public string pallet_identifier { get; set; }

    public List<ShipmentItems> shipment_items { get; set; }

    public class ShipmentItems
    {
        /// <summary>
        /// SKU Code
        /// </summary>
        public string sku { get; set; }

        /// <summary>
        /// Products on Pallet
        /// </summary>
        public decimal stock_qty { get; set; }
        /// <summary>
        /// Description of Item
        /// </summary>
        public string description { get; set; }

    }
}

Method -

public IQueryable<IGrouping<string, GoodInWarehouseBM>> GetWarehouseToStoreList(string storeId)
{
    var entity = (from consighdrs in mi9TestEntities.consighdrs
                  join consigdests in mi9TestEntities.consigdests on consighdrs.consignment equals consigdests.consignment
                  join consigliness in mi9TestEntities.consiglines on consigdests.condestint equals consigliness
                      .condestint
                  join productcodess in mi9TestEntities.productcodes on consigliness.varint equals productcodess.varint
                  join products in mi9TestEntities.products on productcodess.prodint equals products.prodint
                  where consigdests.destination == storeId && consighdrs.status == "T"
                  select new GoodInWarehouseBM()
                  {
                      pallet_identifier = consigdests.consignment,
                      shipment_items = new List<GoodInWarehouseBM.ShipmentItems>
                    {new GoodInWarehouseBM.ShipmentItems
                    {
                        sku = productcodess.variantcode,
                        stock_qty = consigliness.issueqty,
                        description = products.proddesc
                    }}

                  }).GroupBy(x => x.pallet_identifier);

    return entity;
}

ouput -

[
    [
        {
            "pallet_identifier": "FS300057058",
            "shipment_items": [
                {
                    "sku": "051657",
                    "stock_qty": 1,
                    "description": "BELT 1.25\" 5028"
                }
            ]
        },
        {
            "pallet_identifier": "FS300057058",
            "shipment_items": [
                {
                    "sku": "10121781",
                    "stock_qty": 1,
                    "description": "SLAZ CREW SWEAT"
                }
            ]
        },

wanted output -

[
  {
    "pallet_identifier": "FS300057058",
    "shipment_items": [
            {
              "sku": "051657",
              "stock_qty": 1,
              "description": "BELT 1.25\" 5028"
            },
            {
              "sku": "10121781",
              "stock_qty": 1,
              "description": "SLAZ CREW SWEAT"
            }
        ]
    }
]

I seem to be wrapping myself up here in how to actually get that result, any help and pointers will be gratefully accepted.

Answer 1

Re-order things so that you GroupBy and then Select each group into your desired object:

...
.GroupBy(x => x.consigdests.consignment)
.Select(x => new GoodInWarehouseBM
{
    pallet_identifier = x.Key,
    shipment_items = x.Select(i => new GoodInWarehouseBM.ShipmentItems
    {
        sku = x.productcodess.variantcode,
        stock_qty = x.consigliness.issueqty,
        description = x.products.proddesc
    }
});

You may have to fiddle with the syntax a little as I am not used to the SQL-like LINQ syntax. I would recommend learning to use the lambda based syntax wherever possible.

Answer 2

You need to select you grouped result after applying grouping like

...
}).GroupBy(x => x.pallet_identifier)
  .Select(x => new GoodInWarehouseBM { pallet_identifier = x.Key, shipment_items = x.ToList() });

And change you method return type to List<GoodInWarehouseBM> instead of IQueryable<IGrouping<string, GoodInWarehouseBM>>

Or you can use traditional linq query,

var entity = (from consighdrs in mi9TestEntities.consighdrs
...
...
...
where consigdests.destination == storeId && consighdrs.status == "T"
group new { consigdests, productcodess, consigliness, products } by consigdests.consignment into grp
select new GoodInWarehouseBM
{
    pallet_identifier = grp.Key,
    shipment_items = grp.Select(a => new ShipmentItems
    {
        sku  = a.productcodess.variantcode,
        stock_qty = a.consigliness.issueqty,
        description = a.products.proddesc
    })  
}).ToList();