C# LinqToSql Nullable where Clause

I will discuss for this blog is how to query using LinqToSql with a Nullable WHERE Clause.

PROBLEM:

I have a tree-structured database table with tight relationships. That means, all top/parent nodes are a NULLABLE parentID. How can we query the same level nodes using LinqToSql?

TABLE STRUCTURE:

IdTreeNode	int	PK (Identity Field)
IdParentNode	int	NULLABLE, FK (Relates to IdTreeNode)
Name	nvarchar(50)

EXAMPLE DATA:

Node Name (IdTreeNode/IdParentNode)

• Top node 1 (1/null)
• Top node 2 (2/null)
  • Middle node 1 (3/2)
  • Middle node 2 (4/2)
    • Bottom node 1 (5/4)
    • Bottom node 2 (6/4)
    • Bottom node 3 (7/4)
• Top node 3 (8/null)

LINQ QUERY:

MyDataContext db = new MyDataContext();
int? idParentNode = 2;
int anyvalue = 0; //use anyvalue that you are sure will not be a value of IdParentNode
var result = (from item in db.TreeViews where object.Equals(item.IdParentNode ?? anyvalue, idParentNode ?? anyvalue select item)

RESULTS:

Middle node 1
Middle node 2

Simple c# Linq query

//This is the extension used to call list functions.
using System.Linq;
using System.Collections.Generic;

public class Person
{
public string FirstName { get; set; }
public string Lastname { get; set; }
public int Age { get; set; }
}

public class LinqImplementation
{
List Persons = new List();
public LinqImplementation()
{
//This is the old .Net 2005 Implementation of Person class
Person eve = new Person();
eve.FirstName = "Eve";
eve.Lastname = "Davis";
eve.Age = 39;
Persons.Add(eve);

//This is .Net 2008 Implementation of Person class
Person adam = new Person(){
FirstName = "Adam",
Lastname="Sandler",
Age = 42
};
Persons.Add(adam);

//To Simplify things up, I created method to generate list of person
CreatePerson(Persons, "Bill", "Gates", 58);
CreatePerson(Persons, "Rob", "Thomas", 44);
CreatePerson(Persons, "Kobe", "Bryant", 29);
CreatePerson(Persons, "George", "Bush", 65);
CreatePerson(Persons, "Barak", "Obama", 59);
CreatePerson(Persons, "George", "Washington", 59);
}

private void CreatePerson(List Persons, string firstname, string lastname, int age)
{
Person person = new Person() { FirstName = firstname, Lastname = lastname, Age = age };
Persons.Add(person);
}
//This is the query to get the list of person with firstname equal to George
public List GetPersonFirstName()
{
return (from person in Persons
where person.FirstName == "George"
select person).ToList();
}
//This is the query to get the list of person with the age of 40 up to 60
public List GetPersonAge()
{
return (from person in Persons
where person.Age >= 40 && person.Age <= 60
select person).ToList();
}
//This is the query to get the list of person with the age of 40 up to 60
//And adding order as descending.
public List GetPersonAgeDescending()
{
return (from person in Persons orderby person.Age descending
where person.Age >= 40 && person.Age <= 60
select person).ToList();
}
//This is the query to get the list of person with the age of 40 up to 60
//And adding order as ascending.
public List GetPersonAgeAscending()
{
return (from person in Persons
orderby person.Age ascending
where person.Age >= 40 && person.Age <= 60
select person).ToList();
}
}
//Test this in your console application.
//Hoping that this helps a lot