Thursday, 21 May 2020

C# - LINQ (Join Table On Multiple Columns, Inside Method Call)


private static int count = 0;

private static int GetUnknownCount(IEnumerable<string> Bibs, 
                                   IEnumerable<string> LotNum, 
                                   IEnumerable<string> SplitLevel, 
                                   IEnumerable<string> ReburnLevel)
 {
     List<LotReport> lotreport2 = lotreport.Where(a => a.Process == "BurnIn").ToList();
     count = 0;

     List<string> mbib = Bibs.ToList();
     List<string> mLotNum = LotNum.ToList();
     List<string> mSplitLevel = SplitLevel.ToList();
     List<string> mReburnLevel = ReburnLevel.ToList();

     for (int i = 0; i < mbib.Count(); i++)
     {
         var bib = mbib[i].ToString();
         var lot = mLotNum[i].ToString();
         var splitlevel = mSplitLevel[i].ToString();
         var reburnlevel = mReburnLevel[i].ToString();

         foreach (var item in lotreport2)
         {
             if (item.LotNum == lot && item.SplitLevel == splitlevel
                  && item.ReburnLevel == reburnlevel && item.BIB == bib)
             {
                 if (item.ResultType == "Unknown")
                 {
                     count += int.Parse(item.Total);
                 }
             }

         }
     }
     return count;
 } 


private void button1_Click(object sender, EventArgs e)
{           

    List<LotReport> lotreport1 = lotreport.Where(a => a.Process == "Bin2").ToList();

    List<LotReport> lotreport2 = lotreport.Where(a => a.Process == "BurnIn").ToList();


    // Join Table On Multiple Columns
    var list3 =  from t1 in lotreport1
                join t2 in lotreport2
                on new { X = t1.LotNum, Y = t1.SplitLevel, Z = t1.ReburnLevel, 
                         W = t1.BIB ,  V = t1.ResultType } equals
                   new { X = t2.LotNum, Y = t2.SplitLevel, Z = t2.ReburnLevel,
                         W = t2.BIB, V = t2.ResultType }
                select new Table
                {
                    LotNum = t1.LotNum,
                    Process = t1.Process,
                    FreshLot = t1.FreshLot,
                    SplitLevel = t1.SplitLevel,
                    ReburnLevel = t1.ReburnLevel,
                    BIB = t1.BIB,
                    FD_Bin2 = t1.FD_Bin2,
                    FD_BurnIn = t2.FD_BurnIn,
                    ResultType = t1.ResultType,
                    Total = t1.Total,
                    ZeroCount = t1.ZeroCount.ToString(),
                    UnknownCount = t1.UnknownCount.ToString()
                };


    // Inside Method Call
    IEnumerable<TableB> list4 = lotreport1.GroupBy(a => new {
                                      a.FreshLot,

                                    })
                                    .Select(b => new TableB {

                                        LotNum = b.Select(c => c.LotNum),
                                        Process = b.Select(c => c.Process),
                                        FreshLot = b.Select(c => c.FreshLot),
                                        SplitLevel = b.Select(c => c.SplitLevel),
                                        ReburnLevel = b.Select(c => c.ReburnLevel),
                                        BIB = b.Select(c => c.BIB),
                                        FD_Bin2 = b.Select(c => c.FD_Bin2),
                                        FD_BurnIn = b.Select(c => c.FD_BurnIn),
                                        ResultType = b.Select(c => c.ResultType),
                                        Total = b.Select(c => c.Total),

                                        ZeroCount = b.Sum(c => c.ResultType == "Pass" ?
                                                               (c.Total == "0" ? 1 : 0 ) : 0 ),

                                        UnknownCount = GetUnknownCount(b.Select(c =>
                                                                  c.ResultType == "Pass" ?
                                                                  (c.Total == "0" ?
                                                                  c.BIB : string.Empty) :
                                                                  string.Empty),
                                                                 b.Select(c =>
                                                                  c.ResultType == "Pass" ?
                                                                  (c.Total == "0" ?
                                                                  c.LotNum : string.Empty) :
                                                                  string.Empty),
                                                                 b.Select(c =>
                                                                  c.ResultType == "Pass" ?  
                                                                  (c.Total == "0" ?
                                                                  c.SplitLevel : string.Empty) :
                                                                  string.Empty),
                                                                 b.Select(c =>
                                                                  c.ResultType == "Pass" ?
                                                                  (c.Total == "0" ?
                                                                  c.ReburnLevel :
                                                                  string.Empty) :
                                                                         string.Empty)),

                                     }).ToList();
           

}






No comments:

Post a Comment