Should I apply counting in DB or in Code?

asp.net-core asp.net-core-mvc c# entity-framework entity-framework-core

Question

I have the following code where I'm trying to get the amount of rows in the same dataset with various matches.

My question is if should I get the count in C# code with a IEnumerable or by querying a IQueryable from database?

Which one is more efficient, multiple database transactions or IEnumerable filtering and count?

public List<Tuple<string, int>> CalismaVeIzinleriHesapla(long personelId, DateTime baslangic, DateTime bitis)
{
    var hesaplamalar = new List<Tuple<string, int>>();
    var puantajList = puantajlar.Where(p => p.PersonelId == personelId && (p.Tarih >= baslangic && p.Tarih <= bitis));

    var haftaTatili = puantajList.Where(p => p.Secenek.Deger == "Ht").Count();
    var resmiTatil = puantajList.Where(p => p.Secenek.Deger == "Rt").Count();
    var yillikIzin = puantajList.Where(p => p.Secenek.Deger == "Yi").Count();
    var odenecekRapor = puantajList.Where(p => p.Secenek.Deger == "R+").Count();
    var dogumIzni = puantajList.Where(p => p.Secenek.Deger == "Di").Count();
    var olumIzni = puantajList.Where(p => p.Secenek.Deger == "Öi").Count();
    var evlilikIzni = puantajList.Where(p => p.Secenek.Deger == "Ei").Count();
    var odenmeyecekRapor = puantajList.Where(p => p.Secenek.Deger == "R-").Count();
    var ucretsizIzin = puantajList.Where(p => p.Secenek.Deger == "Ãœi").Count();
    var devamsizlik = puantajList.Where(p => p.Secenek.Deger == "D").Count(); 

    return hesaplamalar;
}
1
2
2/13/2019 1:33:18 AM

Accepted Answer

As for your case, querying and counting in the db is more efficient. something like this would be efficient.

puantajlar
  .Where(p => p.PersonelId == personelId && (p.Tarih >= baslangic && p.Tarih <= bitis))
  .GroupBy(x => x.Secenek.Deger)
  .Select(group => new { group.Key, Count = group.Count()  })
4
2/12/2019 11:09:41 AM

Popular Answer

My question is if should I get the count in C# code with a IEnumarable or by querying a IQueryable in DB

If you need only the count of the rows then count must be done in database, not in memory. If you do count in memory by pulling the data list from database into memory then it will waste your server memory unnecessarily and cost performance.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow