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;
}
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() })
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.