SairuS SairuS - 4 months ago 20
C# Question

Query with Nullable Values using LINQ

I am trying to figure out how to make a query in LINQ where some values are nullable.

Next is my table:

ID int
Key1 int
Key2 int?
Key3 int?
Value string

uniq = Key1+Key2+Key3+Value


Now I need to check if an existing record is there already base on a unique constraint.

I tried the following:

Object tmp = model.table.FirstOrDefault(row => row.Key1 == newItem.Key1 &&
(row.Key2.HasValue && newItem.Key2.HasValue && row.Key2.Value == newItem.Key2.Value) &&
(row.Key3.HasValue && newItem.Key3.HasValue && row.Key3.Value == newItem.Key3.Value) &&
row.Value == newItem.Value);


and:

Object tmp = model.table.FirstOrDefault(row => row.Key1 == newItem.Key1 &&
row.Key2 == newItem.Key2 &&
row.Key3 == newItem.Key3 &&
row.Value == newItem.Value);


But both didn't get me correct results when one of the keys is null!

Is there a way to write a correct LINQ query?

Answer
object tmp= model.table.FirstOrDefault(t => 
    t.Key1 == newItem.Key1 
    && ((!t.Key2.HasValue & !newItem.Key2.HasValue) 
        | t.Key2.Value == newItem.Key2.Value)                             
    && ((!t.Key3.HasValue & !newItem.Key3.HasValue) 
        | t.Key3.Value == newItem.Key3.Value) && t.Value == newItem.Value);