I can't Distinct, Sum, or Count from 3 tables with group clauses

Question

I'm still learning linq, now i'm getting confused. I need data for report from 3 tables, I get the result, but it's duplicate. I have used distinct but it's not working, n I need data to be sum still not working.

here is my table

TBL_MKN_MNM                               TBL_DETAIL                 TBL_TRANSACTION 

ID_A  Name        Price   Stock       ID_B  ID_A ID_C  MANY     ID_C   DATE      PAY   
11   pepsi       2500     15          1     11   1234  1        1234   2013-05-22  6000
22   coca-cola   3000     16          2     22   1234  1        6666   2013-05-22  10000
                                      3     11   6666  2          
                                      4     22   6666  1


the result I want
Name        MANY   PRICE   AMOUNT  LeftStock
Pepsi        3     2500    7500     12
Coca-Cola    2     3000    6000     14 

Total 13500

Here is my query, can somebody explain what's wrong?

var report= (from u in myDb.TBL_TRANSAKSI_MKN_MNMs.AsEnumerable()
  where u.TGL_TRANSAKSI.Value.Date.Equals(dateTimePicker1.Value.Date)
  join l in myDb.TBL_DETAIL_TRANSAKSIs.AsEnumerable() on u.ID_NOTA equals l.ID_NOTA
  join m in myDb.TBL_MKN_MNMs.AsEnumerable() on l.ID_MKN_MNM equals m.ID_MKN_MNM
  group new { u, l, m } by new { m.NAMA_MKN_MNM, m.HARGA_JUAL, u.TGL_TRANSAKSI, l.ID_MKN_MNM, u.USERNAME, l.Jumlah }
  into grp                 
  select new 
  {                         
      MakanMinum = grp.Key.NAMA_MKN_MNM,
      HargaJual = grp.Key.HARGA_JUAL,
      Stok = grp.Sum(groupedthing => groupedthing.l.Jumlah), 
      Tanggal =  grp.Key.TGL_TRANSAKSI,
      Jumlah =(grp.Key.HARGA_JUAL * grp.Sum(groupedthing => groupedthing.l.Jumlah)),
      Total = grp.Sum(grouptotal => grp.Key.HARGA_JUAL * grp.Sum(groupedthing => groupedthing.l.Jumlah)),
      Username = grp.Key.USERNAME
  }).Distinct();

"*it's not working*" what's not working. Is there a compile error, or did you just not get the result you expected? — p.s.w.g, Jun 29 '13 at 21:34
Then please post some (small) sample input, the expected output, and the actual output. — p.s.w.g, Jun 29 '13 at 21:37
the result is transaction by date http://imageshack.us/photo/my-images/547/mn7.png/ there's a link that show result... roti should be 3, and tebs should be 2 — Rio Inggit Dharmawangsa, Jun 29 '13 at 21:40
What linq provider are you using? You should avoid `AsEnumerable` because the entire table will go through memory, the `Where` filters not in the database. http://stackoverflow.com/questions/3311244/understanding-asenumerable-in-linq-to-sql — Tim Schmelter, Jun 29 '13 at 21:41
when I deleted asEnumerable it's throw Column referenced is not in scope — Rio Inggit Dharmawangsa, Jun 29 '13 at 21:43
how to check linq provider..? I'm using Microsoft Visual Studio 2008 — Rio Inggit Dharmawangsa, Jun 29 '13 at 21:49
Just tell us what the base type of `myDb` is (DataContext, ObjectContext, DbContext?). Also, it would help if you show a class diagram so we see navigation properties (collections and references). — Gert Arnold, Jun 29 '13 at 22:08
Please post updates by editing your question, not in comments. — p.s.w.g, Jun 29 '13 at 22:20
myDb is **DataContext**,,, here is my class diagram http://imageshack.us/photo/my-images/854/udnf.png/ TBL_MKN_MNM is master, Transaksi_MKN_MNM is transaction table, and TBL_DETAIL_TRANSAKSI is detail transcation. sorry if I can't explain very good, and not get what you mean. I'll try to understand. — Rio Inggit Dharmawangsa, Jun 29 '13 at 23:17

Tim.Tang · Accepted Answer · 2013-06-30T04:31:10.947

1st: you should not use anonymouse class as your query result.

2nd: you should use Enumerable.Distinct<TSource> Method (IEnumerable<TSource>, IEqualityComparer<TSource>) instead of current Enumerable.Distinct<TSource> Method (IEnumerable<TSource>)

see link:

http://msdn.microsoft.com/en-us/library/bb338049.aspx

here is my suggestion:

var report= (from u in myDb.TBL_TRANSAKSI_MKN_MNMs.AsEnumerable()
  where u.TGL_TRANSAKSI.Value.Date.Equals(dateTimePicker1.Value.Date)
  join l in myDb.TBL_DETAIL_TRANSAKSIs.AsEnumerable() on u.ID_NOTA equals l.ID_NOTA
  join m in myDb.TBL_MKN_MNMs.AsEnumerable() on l.ID_MKN_MNM equals m.ID_MKN_MNM
  group new { u, l, m } by new { m.NAMA_MKN_MNM, m.HARGA_JUAL, u.TGL_TRANSAKSI, l.ID_MKN_MNM, u.USERNAME, l.Jumlah }
  into grp                 
  select new MyClass
  {                         
      MakanMinum = grp.Key.NAMA_MKN_MNM,
      HargaJual = grp.Key.HARGA_JUAL,
      Stok = grp.Sum(x=>x.l.Jumlah),  
      Tanggal =  grp.Key.TGL_TRANSAKSI,
      Jumlah =(grp.Key.HARGA_JUAL *  grp.Sum(x=>x.l.Jumlah)),
      Total = grp.Sum(grouptotal => grp.Key.HARGA_JUAL *  grp.Sum(x=>x.l.Jumlah)),
      Username = grp.Key.USERNAME
  }).Distinct(new MyClassComparer());


class MyClass
{
  public int MakanMinum {get;set;}
  pubic int HargaJual {get;set;}
  ...
}



class MyClassComparer : IEqualityComparer<MyClass>
{

    public bool Equals(MyClass x, MyClass y)
    {

        if (Object.ReferenceEquals(x, y)) return true;


        if (Object.ReferenceEquals(x, null) || Object.ReferenceEquals(y, null))
            return false;


        return x.MakanMinum  == y.MakanMinum  && x.HargaJual= y.HargaJual;
    }

    public int GetHashCode(MyClass m)
    {

        if (Object.ReferenceEquals(m, null)) return 0;

        return m.MakanMinum.GetHashCode()^ m.HargaJual.GetHashCode();
    }

}

now the duplicate have removed, how can I sum? it still won't sum — Rio Inggit Dharmawangsa, Jun 30 '13 at 05:58

I can't Distinct, Sum, or Count from 3 tables with group clauses

1 Answers1