0

I am counting certain int values (1, 2, 3, or 4) in particular columns of a datatable. The only values the column can contain are ints between 1 and 9 (exclusive) or null.

For example, a typical column with 10 entries would look like:

Level
  1
  2
  1
  4
  3
  7
  3
  3
(null)
  9

This works fine, despite the null value. As long as there is a non-null, my code works.

The code fails when the column only contains null values, however, which can happen, as I am pulling this data from a database, and depending on the conditions, all the results may be null.

I am using the code shown below to do this count. I looked at this question: Input string was not in a correct format yet I am not sure of a way to use TryParse() in this context.

int countnum = tab2table.AsEnumerable().Where(x => int.Parse(x["Level"].ToString()) == 1 || int.Parse(x["Level"].ToString()) == 2 ||
                                                    int.Parse(x["Level"].ToString()) == 3 || int.Parse(x["Level"].ToString()) == 4).ToList().Count;

For the example Level column shown above, countnum should be 7. When the column is all null countnum should be 0.

How can this be changed to work even when the column is all null? I suppose if there were a way to check if the column if null, I could put this in an if statement, but I have not found a way to do that.

DrakeMurdoch
  • 765
  • 11
  • 26
  • 2
    Did you try `x["Level"] != null`? – José Pedro Apr 26 '19 at 17:07
  • 1
    @JoséPedro since this is a `DataTable`, they may need to actually check for [DBNull](https://learn.microsoft.com/en-us/dotnet/api/system.dbnull?view=netframework-4.8). See also: https://stackoverflow.com/questions/4604414/best-way-to-check-if-a-data-table-has-a-null-value-in-it – Matt Burland Apr 26 '19 at 17:14
  • @MattBurland if I add ` x["MPerfLevel"] != DBNull.Value && ...` to my code, it still gives me the same error – DrakeMurdoch Apr 26 '19 at 17:18
  • `int countnum = tab2table.AsEnumerable().Where(f => f["Level"] != DBNull.Value && (int)f["Level"] > 0 && (int)f["Level"] < 5).Count();` – Jimi Apr 26 '19 at 17:32

1 Answers1

1

You could try something like this:

int countnum = tab2table.AsEnumerable()
     .Select(x => int.TryParse(x["Level"].ToString(), out var d) ? d : (int?)null)
     .Where(x => x >= 1 && x <= 4)
     .Count();

Using TryParse avoids the problem with null values (or otherwise non-integer values). If x["Level"] is null (or DBNull) then the selected value will be null. Using Select followed by Where you also avoid potentially parsing your integers 4 times, which is pretty inefficient, and since you are now dealing with nullable ints, any nulls will always return false in the where clause, causing them to be skipped.

If the values in your datatable are actually int, then you can just check for DBNull and cast your datatable cells to int.

Matt Burland
  • 44,552
  • 18
  • 99
  • 171
  • Do you know why it is giving me an error on the `x` in the `Where` clause? It says: `The name 'x' does not exist in the current context`. – DrakeMurdoch Apr 26 '19 at 17:39
  • @DrakeMurdoch - whoops. My bad. Try it now. – Matt Burland Apr 26 '19 at 17:40
  • So how do I make `countnum = 0` if this doesn't work? Because what is exactly to `countnum` when the whole column is `DBnull`? – DrakeMurdoch Apr 26 '19 at 17:53
  • If your column is all null values then `countnum` will be zero. Because no cells will pass your `Where` clause. – Matt Burland Apr 26 '19 at 17:54
  • Here's what I don't understand, then. How come, when I change my code to anything other than what I'm showing in the actual question, it won't break on all null values, but it also doesn't compute anything with that table properly? For example: `for (int i = 1; i < 5; i++) {float percent = Convert.ToInt32(tab2table.Compute("COUNT(Level)", "Level =" + i.ToString())); if (countnumRW > 0) tab2tableExtra.Rows[i-1][2] = Math.Round(100 * percent / countnum,2);}` just outputs null – DrakeMurdoch Apr 26 '19 at 18:10
  • @DrakeMurdoch - I'm not sure what you are saying. You need to come up with a [MVCE](https://stackoverflow.com/help/mcve) that reproduces the problem you are seeing because as you've described it, this should work. – Matt Burland Apr 26 '19 at 18:13