Linq count and sum in join

Question

I want to get rows with the count column and sum column like in the SQL command ... count(column1.table1) as countname, sum(column2.table1) as sumname ..., but I don't know the right way to write it in Linq, for the example:

    var get = (from dbrg in db.data_barangs
               join pbrg in db.pengiriman_barangs
               on dbrg.kode_barang equals pbrg.kode_barang
               join jdkp in db.jadwal_kapals
               on pbrg.id_jadwal equals jdkp.id_jadwal
               join dplb in db.data_pelabuhans
               on jdkp.kode_pelabuhan equals dplb.kode_pelabuhan
               join drdp in db.data_redpacks
               on dbrg.kode_barang equals drdp.kode_barang
               select new
               {
                   KodeBarang = dbrg.kode_barang,
                   TanggalKedatangan = jdkp.tgl_kedatangan,
                   WaktuKedatangan = jdkp.waktu_kedatangan,
                   NamaPelabuhan = dplb.nama_pelabuhan,
                   Kota = dplb.kota,
                   Provinsi = dplb.provinsi,
                   NamaKapal = jdkp.kapal,
                   JumlahPacking = drdp.id_jadwal.Count(),
                   TotalBerat = drdp.total_berat_packing.Sum()
                }).ToList();

Do you guys know the correct way?

Answer 1

The comments above correctly saying that group by should be used.

---

It is also good to know what keys should be used in the group by. In the select above you are listing a lot of properties and two of them should be grouped. Please take consider adding and removing some columns. Why remove columns? Because of performance. If more columns used to group id_jadwal and total_berat_packing that has a cost at database level.

You may ask why add more columns? This can be because of correct functionality. Right now you have seven classic property, these are enough for correct summation? If not add more columns and create index for those

---

I did modify the query to fulfil grouping if you have question please let me know in the comment section.

var result = (from dbrg in db.data_barangs
           join pbrg in db.pengiriman_barangs
           on dbrg.kode_barang equals pbrg.kode_barang
           join jdkp in db.jadwal_kapals
           on pbrg.id_jadwal equals jdkp.id_jadwal
           join dplb in db.data_pelabuhans
           on jdkp.kode_pelabuhan equals dplb.kode_pelabuhan
           join drdp in db.data_redpacks
           on dbrg.kode_barang equals drdp.kode_barang
           group new { JumlahPacking = drdp.id_jadwal, TotalBerat = drdp.total_berat_packing }
           by new {
                KodeBarang = dbrg.kode_barang,
                TanggalKedatangan = jdkp.tgl_kedatangan,
                WaktuKedatangan = jdkp.waktu_kedatangan,
                NamaPelabuhan = dplb.nama_pelabuhan,
                Kota = dplb.kota,
                Provinsi = dplb.provinsi,
                NamaKapal = jdkp.kapal,
           }
           into beratAndPackingSumGroup
           select new
           {
               KodeBarang = beratAndPackingSumGroup.Key.KodeBarang,
               TanggalKedatangan = beratAndPackingSumGroup.Key.TanggalKedatangan,
               WaktuKedatangan = beratAndPackingSumGroup.Key.WaktuKedatangan,
               NamaPelabuhan = beratAndPackingSumGroup.Key.NamaPelabuhan,
               Kota = beratAndPackingSumGroup.Key.Kota,
               Provinsi = beratAndPackingSumGroup.Key.Provinsi,
               NamaKapal = beratAndPackingSumGroup.Key.NamaKapal,
               JumlahPacking = beratAndPackingSumGroup.Select(x => x.JumlahPacking).Count(),
               TotalBerat = beratAndPackingSumGroup.Sum(x => x.TotalBerat)
            });

If JumlahPacking property is not correct you can call a distinction on it: beratAndPackingSumGroup.Select(x => x.JumlahPacking).Distinct().Count() this is require more performance.