I can't Distinct, Sum, or Count from 3 tables with group clauses
- by Rio Inggit Dharmawangsa
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 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();
