using System;

using System.Collections.Generic;

using System.Linq;

public class Program

{

    public static void Main()

    {

        var material1 = new MaterialMaster

        {

            Id = Guid.NewGuid(),

            Code = "Test01",

        };

​

        var material2 = new MaterialMaster

        {

            Id = Guid.NewGuid(),

            Code = "Test02",

        };

​

        var plantId1 = Guid.NewGuid();

        var plantId2 = Guid.NewGuid();

​

        var location = new Location

        {

            Id = Guid.NewGuid(),

            Name = "Test Location",

            PlantId = plantId1

        };

​

        var stock1 = new PartStock

        {

            Id = Guid.NewGuid(),

            MaterialId = material1.Id,

            LocationId = location.Id,

            Location = location,

            Qty = 1

        };

​

        var stock2 = new PartStock

        {

            Id = Guid.NewGuid(),

            MaterialId = material2.Id,

            LocationId = location.Id,

            Location = location,

            Qty = 2

        };

​

        material1.Stock = new List<PartStock> { stock1, stock2 };

​

        var releasedStatusId = Guid.NewGuid();

​

        var order = new DemoOrder

        {

            Id = Guid.NewGuid(),

            PlantId = plantId1,//plantId2, plantId1

            StatusId = releasedStatusId,

        };

​

        var item1 = new DemoOrderItem

        {

            Id = Guid.NewGuid(),

            Description = "Test Description",

            Qty = 1,

            MaterialId = material1.Id,

            Material = material1

        };

​

        var item2 = new DemoOrderItem

        {

            Id = Guid.NewGuid(),

            Description = "Test Description 2",

            Qty = 2,

            MaterialId = material2.Id,

            Material = material2

        };

​

        order.Items = new List<DemoOrderItem> { item1, item2 };

​

        var orders = new List<DemoOrder> { order };

​

        var selectedOrders = orders

                            .Where(entity => entity.StatusId == releasedStatusId)

                            .Select(entity => new

                            {

                                entity.Id,

                                entity.StatusId,

                                //..Remaining columns

                                AvailableStockCount = entity.Items

                                                            .Where(item => item.Material.Stock

                                                                                .Select(stock => new { stock.Location.PlantId, stock.MaterialId })

                                                                                .Any(stockItem => stockItem.PlantId == order.PlantId && stockItem.MaterialId == item.MaterialId))

                                                            .Sum(item => item.Qty)

                            });

​

​

        foreach (var selectedOrder in selectedOrders){

            Console.WriteLine($"Order Id {selectedOrder.Id} - AvailableStockCount - {selectedOrder.AvailableStockCount}");

        }

​

​

    }

}

​

public class DemoOrder

{

    public Guid Id { get; set; }

    public Guid PlantId { get; set; }

    public Guid StatusId { get; set; }

​

    public IList<DemoOrderItem> Items { get; set; } = new List<DemoOrderItem>();

}

​

public class DemoOrderItem

{

    public Guid Id { get; set; }

​

    public string Description { get; set; } = string.Empty;

    public int Qty { get; set; }

​

    public Guid MaterialId { get; set; }

    public MaterialMaster Material { get; set; } = null!;

}

​

public class MaterialMaster

{

    public Guid Id { get; set; }

    public string Code { get; internal set; } = string.Empty;

​

    public IList<PartStock> Stock { get; set; } = new List<PartStock>();

}

​

public class PartStock

{

    public Guid Id { get; set; }

​

    public Guid MaterialId { get; set; }

    public MaterialMaster Material { get; set; } = null!;

​

    public Guid LocationId { get; set; }

    public Location Location { get; set; } = null!;

​

    public int Qty { get; set; }

}

​

public class Location

{

    public Guid Id { get; set; }

    public string Name { get; set; } = null!;

    public Guid PlantId { get; set; }

}