c# - Join 2 list <t> one as a sub-list of the other

Question

I have two list that have been returned from two separate tables in a SQL server database using dapper lstPart and lstSite. Both lists have a ITEMNUM field which correspond with each other. In the part list the ITEMNUM field is unique but in the Site list it is not. Some parts have multiple sites some have none.

using System;

namespace CWIC_Data_Framework.Definition.MP2Definitions
{
    public class MP2BaseTableDefinition

    {
        public string ITEMNUM { get; set; }
        public string? DESCRIPTION { get; set; }
        public string? NOTES { get; set; }
        public string? UOM { get; set; }
        public DateTime? DATEADDED { get; set; }
        public DateTime? LASTEDITDATE { get; set; }
        public string? COMMENTS { get; set; }
        public string? SITE { get; set; }
        public string? LOCATION { get; set; }
        public int? QTYONHAND { get; set; }
    }
}

using System;

namespace CWIC_Data_Framework.Definition.MP2Definitions
{
    public class MP2SiteTableDefinition

    {
        public string ITEMNUM { get; set; }
        public int? REORDERPOINT { get; set; }
        public int? REORDERQTY { get; set; }
        public string? SITE { get; set; }
        public DateTime? LASTDATECOUNTED { get; set; }
        public DateTime? LASTDATERCVD { get; set; }
        public DateTime? LASTACTIVITY { get; set; }
    }
}

I am creating an app with C# WPF and I would like to have a final result similar to this. Example Of What I'm looking for

I think I need to create a single list that contains the site list as a sublist to the parts list using LINQ and then bind it to a Datagrid but I am not positive.

    public class Part
    {
        public int PartID { get; set; }
        public string Description { get; set; }
        public list<site> Location { get; set; }
   }

Any help or direction would be appreciated.

Here is the class for calling one of the list (The part list) , the other is the same.

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using CWIC_Data_Framework.Definition.MP2Definitions;
using Dapper;
using SerilogTimings;

namespace CMMS_Status.Data
{
    internal class MP2BaseTableData

    {
        private static string ThisMethod;
        private string ThisClass;

        public List<MP2BaseTableDefinition> ListOfMp2Base(Dictionary<string, object> paramDictionary)
        {
            ThisMethod = MethodBase.GetCurrentMethod().Name;
            using (var op = Operation.Begin("{Class}{Method}", ThisClass, ThisMethod))

            using (IDbConnection connection = new SqlConnection(Helper.ConnectionValue("CreeMesRtp")))
            {
                var parameters = new DynamicParameters(paramDictionary);
                var SQL = @"
                            Select
                            CMMSSB.MP2_INVY.ITEMNUM,
                            CMMSSB.MP2_INVY.DESCRIPTION,
                            CMMSSB.MP2_INVY.UOM,
                            CMMSSB.MP2_INVY.NOTES,
                            CMMSSB.MP2_INVY.DATEADDED,
                            CMMSSB.MP2_INVY.LASTEDITDATE,
                            CMMSSB.MP2_INVCOMM.COMMENTS,
                            CMMSSB.MP2_STOCK.SITE,
                            CMMSSB.MP2_STOCK.LOCATION,
                            CMMSSB.MP2_STOCK.QTYONHAND
                            From
                            CMMSSB.MP2_INVY Inner Join
                            CMMSSB.MP2_STOCK On CMMSSB.MP2_STOCK.ITEMNUM = CMMSSB.MP2_INVY.ITEMNUM Left Join
                            CMMSSB.MP2_INVCOMM On CMMSSB.MP2_INVCOMM.ITEMNUM = CMMSSB.MP2_INVY.ITEMNUM
                            ";

                var Output = connection.Query<MP2BaseTableDefinition>(SQL, parameters).ToList();
                op.Complete();
                return Output;
            }
        }
    }
}

The class for pulling the Site info.

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using CMMS_Status.Definition;
using CWIC_Data_Framework.Definition.MP2Definitions;
using Dapper;
using SerilogTimings;

namespace CMMS_Status.Data
{
    internal class MP2SiteTableData

    {
        private static string ThisMethod;
        private string ThisClass;

        public List<MP2SiteTableDefinition> ListOfMp2Site(Dictionary<string, object> paramDictionary)
        {
            ThisMethod = MethodBase.GetCurrentMethod().Name;
            using (var op = Operation.Begin("{Class}{Method}", ThisClass, ThisMethod))

            using (IDbConnection connection = new SqlConnection(Helper.ConnectionValue("CreeMesRtp")))
            {
                var parameters = new DynamicParameters(paramDictionary);
                var SQL = @"
                        Select
                        CMMSSB.MP2_SITEINFO.ITEMNUM,
                        CMMSSB.MP2_SITEINFO.REORDERPOINT,
                        CMMSSB.MP2_SITEINFO.REORDERQTY,
                        CMMSSB.MP2_SITEINFO.LASTDATECOUNTED,
                        CMMSSB.MP2_SITEINFO.LASTDATERCVD,
                        CMMSSB.MP2_SITEINFO.LASTACTIVITY
                        From
                        CMMSSB.MP2_SITEINFO
                        ";

                var Output = connection.Query<MP2SiteTableDefinition>(SQL, parameters).ToList();
                op.Complete();
                return Output;
            }
        }
    }
}

question from:https://stackoverflow.com/questions/65907785/join-2-list-t-one-as-a-sub-list-of-the-other

Answer 1

Update: I have checked the below code

But why you changed full description??

  public class Site
{
    public int PartID { get; set; }
    public string Location { get; set; }

}

public class Part
{
    public int PartID { get; set; }
    public string Description { get; set; }
    public List<Site> Location { get; set; }
}
var siteList = new List<Site>() {
            new Site{PartID = 1, Location = "Dhaka"},
            new Site{PartID = 2, Location = "Rajshahi"},
            new Site{PartID = 3, Location = "Khulna"},
            new Site{PartID = 2, Location = "Barisal"},
            new Site{PartID = 1, Location = "Narsingdi"},
        };
        var dictionary = siteList.GroupBy(s => s.PartID)
              .ToDictionary(s => s.Key,
                 s => s.ToList());

        var partList = new List<Part> (){
            new Part{ PartID  = 3, Description = "A", Location = null},
            new Part{ PartID  = 2, Description = "C", Location = null},
            new Part{ PartID  = 1, Description = "B", Location = null},
        };


        foreach(var part in partList)
        {
            part.Location = dictionary[part.PartID];
        }