CODEWITHSUNDEEP
c#linqt-sql
LocustHorde
LocustHorde

Reputation: 6399

How do detect non-existant rows in linq?

I have a tsql snippet similar to this: 

select p.Make, count(p.Make) as _count
from Products p
where p.GroupId = @GroupId
group by p.Make

the full result set would be something with 5 rows: 

Make    _count
``````````````
Make1    32
Make2    54
Make3    60
Make4    09
Make5    47

In C#, I am trying to calculate percentages of each make (assume that I know total products already) so I can construct a google image chart (pie) so I need to pass 5 values there.

I am tring to do this : 

int Make1Count = from row in DataSetProducts.tables[0].AsEnumerable()
                 where row.Field<string>("Make") == MyEnum.Makers.Make1.ToString()
                 select row.Field<int>("_count");

and so on upto Make5Count. Ideally, I should have 5 results, but this is not always the case sometimes the result is like this:

Make    _count
``````````````
Make1    32 
Make4    09
Make5    47

But I still need Make2 and 3 (they're just zero)

My problem is, this code throws an exception when Make2 is not found, for instance. How do I avoid this please?

note 1) In my database, there is no list of all makers so I can't really coalesce it with anything

note 2) Here, I have avoided Convert.toInt32 just before from row in ... to keep it understandable

this should be quite trivial, but i can't get it somehow.

Upvotes: 1

Views: 101

Answers (3)

Jodrell
Jodrell

Reputation: 35716

How about somthing like,

var counts = DataSetProducts.tables[0].Select(row => 
                 make = row.Field<string>("Make"),
                 count = row.Field<int>("_count"));

int Make1Count = counts.SingleOrDefault(c => 
                     c.make == MyEnum.Makers.Make1.ToString()).count;

Upvotes: 0

ratneshsinghparihar
ratneshsinghparihar

Reputation: 301

change your sql to following select X.Make, count(X.Make)

(

(select p.Make, count(p.Make) as _count from Products p where p.GroupId = @GroupId group by p.Make) t1

union select 'Make1', 0

union select 'Make2', 0

union select 'Make3', 0

union select 'Make4', 0

union select 'Make5', 0 )

X

group by X.Make

Upvotes: 0

iamserious
iamserious

Reputation: 5465

Firstly, it looks like you are trying to select one single row (from your sql and linq example), but your linq returns multiple rows.. instead, try this: 

int Make1Count = (from row in DataSetProducts.tables[0].AsEnumerable()
             where row.Field<string>("Make") == MyEnum.Makers.Make1.ToString()
             select row.Field<int>("_count"))
             .SingleOrDefault();

That should return one row, and solve one problem.

Now, for your real problem, when some row doesn't exist, SingleOrDefault() will return default values for data types, check it against that. For example default value of int is 0, which is what you want, and default for bool is false etc

Upvotes: 3

Related Questions