locked
How to get all invoices that has items from one loc_cd only using EF ? RRS feed

  • Question

  • User-1846805900 posted

    Hi

    i try here to get all invoices that has items from one loc_cd only like (all invoice items from loc_cd 1 or all items from loc_cd 2)

    if invoice has items from both loc_cd (1 and 2) i don't need it ?

    i try:

    var model = db.AZTRHs.Where(i => i.AZTRDs.Where(o => o.TR_TY == 102 && o.LOC == "01" 
                                                                                && o.TR_DT >= from 
                                                                                && o.TR_DT <= from).Count() > 0)
                                                                                .Select(i => new { i.TR_NO, i.TR_DT, i.TR_TY })
                                                                                .OrderBy(i => i.TR_NO).ToList();

    loc_cd is in table "AZTRDs"

    so please how can i dot it ?

    Sunday, September 6, 2015 5:29 PM

Answers

  • User614698185 posted

    Hi a.amin,

    a.amin

    so please can you modify my code to let know how can i add the missing part to it ?

    var model = db.AZTRDs.Where(o => o.TR_TY == 102 && o.LOC == "01"&& o.TR_DT >= fromdt && o.TR_DT <= to)
                         .GroupBy(i => i.TR_NO)
                         .Where(group=>group.Select(x => x.LOC_CD).Distinct().Count() == 1)
                         .ToList();

    a.amin

    and last question please: can i get count of items (itm_cd) in each invoice ? >> How

    For this new question, please feel free to open a new thread in this forum for better communication.

    Best Regards,

    Candice Zhou

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 8, 2015 4:34 AM

All replies

  • User614698185 posted

    Hi a.amin,

    Based on your requirement, I create the table named aztrd:

    TR_NO        TR_TY           ITEMID      LOC           LOC_CD 
    1             102            010005       01              1
    1             102            011152       01              1
    1             102            054001       01              2 
    
    2             102            052008       01              1
    2             102            053009       01              1 
    
    3             102            052080       01              2
    3             102            053090       01              2
    3             102            055090       01              2 

    Then, I use the LINQ to select the invoice that from one LOC_CD, code shown below:

    var qry = from cust in db.aztrds
              group cust by cust.TR_NO_
              into grp
              where grp.Select(x => x.LOC_CD).Distinct().Count() == 1
              select grp;
    foreach (var group in qry)
        {
              foreach (var invoice in group)
            {
               Console.WriteLine("TR_NO_:" + invoice.TR_NO_);
               Console.WriteLine("TR_TY_:" + invoice.TR_TY_);
               Console.WriteLine("ITEMID:" + invoice.ITEMID);
               Console.WriteLine("LOC:" + invoice.LOC);
               Console.WriteLine("LOC_CD:" + invoice.LOC_CD);
               Console.WriteLine("------------------");
              }
         }

    I hope this will help you.
    Best Regards,
    Candice Zhou

    Monday, September 7, 2015 10:32 AM
  • User-1846805900 posted

    Hi a.amin,

    Based on your requirement, I create the table named aztrd:

    TR_NO        TR_TY           ITEMID      LOC           LOC_CD 
    1             102            010005       01              1
    1             102            011152       01              1
    1             102            054001       01              2 
    
    2             102            052008       01              1
    2             102            053009       01              1 
    
    3             102            052080       01              2
    3             102            053090       01              2
    3             102            055090       01              2 

    Then, I use the LINQ to select the invoice that from one LOC_CD, code shown below:

    var qry = from cust in db.aztrds
              group cust by cust.TR_NO_
              into grp
              where grp.Select(x => x.LOC_CD).Distinct().Count() == 1
              select grp;
    foreach (var group in qry)
        {
              foreach (var invoice in group)
            {
               Console.WriteLine("TR_NO_:" + invoice.TR_NO_);
               Console.WriteLine("TR_TY_:" + invoice.TR_TY_);
               Console.WriteLine("ITEMID:" + invoice.ITEMID);
               Console.WriteLine("LOC:" + invoice.LOC);
               Console.WriteLine("LOC_CD:" + invoice.LOC_CD);
               Console.WriteLine("------------------");
              }
         }

    I hope this will help you.
    Best Regards,
    Candice Zhou

    Thanks a lot

    your code is working, but i try to do depending on your code but by other way as:

    var model = db.AZTRDs.Where(o => o.TR_TY == 102 && o.LOC == "01" && o.TR_DT >= fromdt && o.TR_DT <= to).GroupBy(i => i.TR_NO).ToList();

    but i don't know where i should add this line in my code to make it works as your code:

    Select(x => x.LOC_CD).Distinct().Count() == 1

    so please can you help me with that to let me do it using my above code ?

    Thanks a lot ...

    Monday, September 7, 2015 6:13 PM
  • User614698185 posted

    Hi a.amin,

    I think you could modify your code like below:

    var Model = from tb in db.AZTRDs
                where tb.TR_TY == 102 && tb.LOC == "01" && tb.TR_DT >= fromdt && tb.TR_DT <= to
                group tb by tb.TR_NO
                into grp
                where grp.Select(x => x.LOC_CD).Distinct().Count() == 1
                select grp;
    
                foreach (var group in Model)
                {
                    foreach (var invoice in group)
                    {
                        Console.WriteLine("TR_NO:" + invoice.TR_NO);
                        Console.WriteLine("TR_TY:" + invoice.TR_TY);
                        Console.WriteLine("ITEMID:" + invoice.ITEMID);
                        Console.WriteLine("LOC:" + invoice.LOC);
                        Console.WriteLine("LOC_CD:" + invoice.LOC_CD);
                        Console.WriteLine("------------------");
                     }
                 }

    I hope this will help you.
    Best Regards,
    Candice Zhou

    Monday, September 7, 2015 10:36 PM
  • User-1846805900 posted

    Thanks a lot

    i mean i try to take this part: (this is make it get right invoices)

    Select(x => x.LOC_CD).Distinct().Count() == 1

    and add it to my code :

    var model = db.AZTRDs.Where(o => o.TR_TY == 102 && o.LOC == "01" && o.TR_DT >= fromdt && o.TR_DT <= to).GroupBy(i => i.TR_NO).ToList();

    but it not working throw my code when i use group by i must remove it, it working by your code only ?

    so please can you modify my code to let know how can i add the missing part to it ?

    and last question please: can i get count of items (itm_cd) in each invoice ? >> How

    Tuesday, September 8, 2015 3:36 AM
  • User614698185 posted

    Hi a.amin,

    a.amin

    so please can you modify my code to let know how can i add the missing part to it ?

    var model = db.AZTRDs.Where(o => o.TR_TY == 102 && o.LOC == "01"&& o.TR_DT >= fromdt && o.TR_DT <= to)
                         .GroupBy(i => i.TR_NO)
                         .Where(group=>group.Select(x => x.LOC_CD).Distinct().Count() == 1)
                         .ToList();

    a.amin

    and last question please: can i get count of items (itm_cd) in each invoice ? >> How

    For this new question, please feel free to open a new thread in this forum for better communication.

    Best Regards,

    Candice Zhou

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 8, 2015 4:34 AM
  • User-1846805900 posted

    Hi a.amin,

    a.amin

    so please can you modify my code to let know how can i add the missing part to it ?

    var model = db.AZTRDs.Where(o => o.TR_TY == 102 && o.LOC == "01"&& o.TR_DT >= fromdt && o.TR_DT <= to)
                         .GroupBy(i => i.TR_NO)
                         .Where(group=>group.Select(x => x.LOC_CD).Distinct().Count() == 1)
                         .ToList();

    a.amin

    and last question please: can i get count of items (itm_cd) in each invoice ? >> How

    For this new question, please feel free to open a new thread in this forum for better communication.

    Best Regards,

    Candice Zhou

    Hi

    i did it bu other way, and hope you tell me are that good or there is another better way:

                var fromdt = Convert.ToDateTime("2015-08-01");
                var todt = Convert.ToDateTime("2015-08-05");
    
                var model = db.AZTRHs.Where(i => i.AZTRDs.Where(o => o.TR_TY == 102 && o.LOC == "01" && o.TR_DT >= fromdt && o.TR_DT <= todt)
                                                         .Select(m => m.LOC_CD).Distinct().Count() == 1)
                                                         .Select(i => new { i.TR_NO, i.EntryDate, i.TR_TY, count = i.AZTRDs.Count() })
                                                         .OrderBy(i => i.TR_NO).ToList();

    Thanks a lot ...

    Tuesday, September 8, 2015 5:59 AM
  • User614698185 posted

    Hi a.amin,

    I think it is best way to accomplish your requirement.

    Best Regards,

    Candice Zhou

    Wednesday, September 9, 2015 8:10 AM