Home > Enterprise >  How to Add a Where Not Equals condition in Linq query
How to Add a Where Not Equals condition in Linq query

Time:09-17

I use this code to cast my DataGridView and then display results in another DataGridView.

Basically, I'm getting some int values and calculating their sum, while also grouping these values with GroupBy.

I display the results in another DataGridView like so:

datagridview2.DataSource= Su.ToList();

My problem is that the query seems to stop if the cell has a string value.

I've tried adding a 'where not' condition to exclude rows that contain string values in several different ways but did not succeed.

Here an example of the condition I've tried:

.Where(row => !row.Cells[2].Value.ToString().Equals("example"))

This code is in the datagridview_cellvaluechanged method:

    try
{
    var Su = dataGridView1.Rows.Cast<DataGridViewRow>()
    .Where(row => row.Cells[8].Value != null)
    .Where(row => row.Cells[2].Value != null)
    .GroupBy(row => row.Cells[8].Value.ToString()) 
    .Select(g => new
    {
        Gruppo = g.Key,
        Serie = g.Sum(row => Convert.ToInt32(row.Cells[2].Value)),
        Reps = g.Sum(row => Convert.ToInt32(row.Cells[3].Value) * Convert.ToInt32(row.Cells[2].Value)),
        Percent = (g.Sum(row => Convert.ToDecimal(row.Cells[2].Value)) / Convert.ToDecimal(label15.Text)) * 100
    });
}
catch (FormatException)
{
    //MessageBox.Show("Formato non corretto");
}

This is what the DataGridView should look like, if it suceeds; dgv1:

column 1 | column 2 |column 3
  2          3        group1
  3          5        group1
  4          6        group2
  3          4        group3

And I see this in the result on dgv2:

    column 1 | column 2 |column 3
      5          8        group1
      4          6        group2
      3          4        group3

But if I add a string value in column2 like this:

column 1 | column 2 |column 3
  2          3        group1
  3          5        group1
  4        string     group2
  3          4        group3

Then I see this in the result on dgv2:

column 1 | column 2 |column 3
  5          8        group1

CodePudding user response:

While I'm still not entirely sure what the issue is, I will attempt to answer your question.

First off, if you are not 100% sure that a specific cell contains an Int value, you should not just blindly convert it.

There are ways to check if it is an int before converting it. One way is this:


    try
{
    var Su = dataGridView1.Rows.Cast<DataGridViewRow>()
    .Where(row => row.Cells[8].Value != null)
    .Where(row => row.Cells[2].Value != null)
    .GroupBy(row => row.Cells[8].Value.ToString()) 
    .Select(g => new
    {
        Gruppo = g.Key,
        Serie = g.Sum(row => {
            if(row.Cells[2].Value is int rowInteger)
                return rowInteger;

            // if it is not an integer then try to convert
            // return a default value (in this case -1) if it can't be converted
            else
            {
                try
                {
                    return Convert.ToInt32(row)
                }
                catch(Exception)
                {
                    return -1;
                }
            }
        },
        Reps = g.Sum(row => Convert.ToInt32(row.Cells[3].Value) * Convert.ToInt32(row.Cells[2].Value)),
        Percent = (g.Sum(row => Convert.ToDecimal(row.Cells[2].Value)) / Convert.ToDecimal(label15.Text)) * 100
    });
}
catch (FormatException)
{
    //MessageBox.Show("Formato non corretto");
}

This will make sure that all rows are kept but only the rows that are valid are calculated on.

A simpler approach, that won't keep the original number of rows is to check whether the value of the cell is an int or can be converted into an int is to add the following Where statement:

Where(row => row.Cells[2].Value is int || int.TryParse((row.Cells[2].Value as string), out var result))

Note that you are actually parsing the value in each iteration and discarding the result, this is of course far from being optimized but will get you the wished result.

  • Related