Custom Search

July 24, 2010

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:


IdTreeNodeintPK (Identity Field)
IdParentNodeintNULLABLE, FK (Relates to IdTreeNode)
Namenvarchar(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