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

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 ?

Answer 1

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

Answer 2

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 ...

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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 ...

Answer 7

User614698185 posted

Hi a.amin,

I think it is best way to accomplish your requirement.

Best Regards,

Candice Zhou