0

Struggling to get my count correct. I believe I am close but I need it to return 0 if there are no records in the database. The compiler doesnt like what I have now. Any help would be appreciated. Thanks

var count = (_db.cart.Where(c => c.UserId == id)
            .Select(c => (int) c.Quantity)).ToList().Count() ?? 0;
Sealer_05
  • 5,346
  • 8
  • 35
  • 53

3 Answers3

2

You want to use Sum, not Count

var totalQuantity = _db.cart.Where(c => c.UserId == id)
                            .Select(c => c.Quantity)
                            .DefaultIfEmpty(0)
                            .Sum();
tvanfosson
  • 524,688
  • 99
  • 697
  • 795
  • No need to check for null than? – Sealer_05 Mar 26 '14 at 04:11
  • Code actually did not work. Field is set to not null in sql but still bombs on null. Your second query is giving me left opperand of the ?? should be a reg or nullable type – Sealer_05 Mar 26 '14 at 04:31
  • if (_db.cart == null) { return 0; } else { return _db.cart.Where(c => c.UserId == id) .Select(c => c.Quantity) .Sum(); } – Sealer_05 Mar 26 '14 at 04:43
  • That code gives me "Additional information: The cast to value type 'System.Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type." – Sealer_05 Mar 26 '14 at 04:43
  • @osiris355 - ahh - that issue. I had forgotten about that. I'll update. There's a good explanation at http://stackoverflow.com/questions/6864311/the-cast-to-value-type-int32-failed-because-the-materialized-value-is-null – tvanfosson Mar 26 '14 at 04:55
1

No need for the null coalesce.

var count = _db.cart.Count(c => c.UserId == id);  // get record count

If you're actually trying to get a sum:

var total = _db.cart.Where(c => c.UserId == id)
                    .Select(c => (int?)c.Quantity)
                    .Sum() ?? 0;                  // get total

FYI, regarding the code you originally posted...

You wouldn't want to call ToList().Count(), because calling ToList() will execute your query and pull back data. You'll end pulling back a list of quantities and performing the count locally, instead of generating an SQL statement that simply performs the count and returns a single number.

Grant Winney
  • 65,241
  • 13
  • 115
  • 165
0

try this 

var count=_db.cart.where(c=>c.Userid==id).Count();
user2329366
  • 69
  • 1