1

I am having trouble with getting a ‘not equals’ to work in a LINQ to SQL select statement.

I have three radio buttons for a Search function for my recipe program, Regular, Healthy, and All. The recipes have a field called Healthy, and the only way it is accessed is through the program, and the only thing it can have is NULL or Healthy.

The search button code is below. The Healthy and All work fine, but the rbRegRecipes doesn’t show any recipes at all.

This is not a join, all fields are from the Recipe table.

private void bnSearchRec_Click(object sender, EventArgs e)
        {
            string srch = txtSearchRec.Text;
            using (RecipeClassesDataContext dbContext = new RecipeClassesDataContext())
            {
                if (rbRegRecipes.Checked == true)
                {
                    var reclist = from r in dbContext.Recipes where r.Name.Contains(srch) && (!r.Healthy.Equals("Healthy")) select r;
                    dgvRecipes.DataSource = reclist;

                }
                else if (rbHlthRecipes.Checked == true)
                {
                    var reclist = from r in dbContext.Recipes where r.Name.Contains(srch) && (r.Healthy == "Healthy") select r;
                    dgvRecipes.DataSource = reclist;
                }
                else
                {
                    var reclist = from r in dbContext.Recipes where r.Name.Contains(srch) select r;
                    dgvRecipes.DataSource = reclist;
                }
            }
        }

I have tried all of the following:

!r.Healthy.Equals("Healthy")
r.Healthy != "Healthy"
!(r.Healthy == "Healthy")
(r.Healthy == null | r.Healthy == "")  // the database shows it as NULL, but the datagrid just shows it as blank, so I figured I would cover both bases.

What am I doing wrong?

TBaildon
  • 33
  • 1
  • 5
  • Maybe `r.Healthy == DBNull.Value` ? Feel like I've used that before – Timothy G. Feb 18 '21 at 01:45
  • I get "Operator == cannot be applied to operands of type 'string' and 'DBNull'. DBNull is a type, so it isn't letting me compare them. – TBaildon Feb 18 '21 at 02:47
  • 1
    Look at the query history in `SSMS` to see the actual SQL query. E.g. https://stackoverflow.com/questions/5299669/how-to-see-query-history-in-sql-server-management-studio – Loathing Feb 18 '21 at 06:42
  • Why are you using `!r.Healthy.Equals("Healthy")` to check for inequality and `r.Healthy == "Healthy"` to check for equality: why does one use the word `Equals`, and the other `==`? Shouldn't both use Equals and !Equals, or both use `==` and `!=`? – Harald Coppoolse Feb 18 '21 at 10:07
  • @Harald - If you look a little lower you can see a list of all of the things I tried for the 'not equals'. The top one is just the last thing I had tried. == worked for the Healthy search so I didn't change it. – TBaildon Feb 18 '21 at 16:03

1 Answers1

0

Turns out there were two things happening.

First is I had manually added the Healthy property, and I had not set it to Nullable in the LINQ to SQL .dmbl. I found that out while debugging a different part of the program.

Second, no form of 'not equals' worked in a regular SQL query either, since the only two values for the Healthy property are Healthy and NULL. I guess NULL is not a value it can not be equal to. However, once I fixed the nullable issue in LINQ to SQL, the following worked:

 var reclist = from r in dbContext.Recipes where r.Name.Contains(srch) && r.Healthy == null select r;
TBaildon
  • 33
  • 1
  • 5