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?

Are you asking for an alternative for the `as` keyword in LINQ to Entities? — Arsen Khachaturyan, Aug 17 '20 at 16:22
@ArsenKhachaturyan yes, because I got `'int' does not contain a definition for 'Sum'` on the `TotalBerat = drdp.total_berat_packing.Sum()`. — robyiset, Aug 17 '20 at 16:35
@robyiset the "Wowo Ot" suggestion is correct because in order to use aggregate functions over the LINQ queries you need to use the `GroupBy` method. Check [this](https://stackoverflow.com/questions/16522645/linq-groupby-sum-and-count) post as an example. — Arsen Khachaturyan, Aug 17 '20 at 16:53
This is an example using group by on LinQ https://stackoverflow.com/questions/7285714/linq-with-groupby-and-count — David Hurtado, Aug 17 '20 at 18:05
First, *use navigation properties*! It's almost impossible to comprehend a statement with joins because the cardinality of the relationships isn't visible, as it is with well-named navigation properties. Also, it will probably enable you to write your statement correctly right-away. — Gert Arnold, Aug 17 '20 at 18:43
In your LINQ, what do you expect the `.Sum` to sum over? `drdp` is a single row so `total_berat_packing` is a single number, and you can't sum that. — NetMage, Aug 17 '20 at 18:44

score 1 · Accepted Answer · answered Sep 04 '20 at 08:27

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.

Linq count and sum in join

1 Answers1