1

I'm trying to use Linq to SQL to filter a table with a nullable bool value based on a variable.

But if the variable's value is NULL, LINQ seems to translate the query to Field = NULL instead of Field IS NULL.

Based on other posts I've seen to use object.Equals to map correctly. And that works, but only when the variable's value is null.

Sample code:

    bool? relevantValue = null;
    var q = from m in MyTable
             where ???
             select m;

Case 1:

If where clause is object.Equals(m.IsRelevant, relevantValue), it works when relevantValue is null and true, but returns incorrect results when relevantValue = false. It translates in SQL to

WHERE ( (CASE WHEN [t0].[IsRelevant] = 1 THEN 1 ELSE 0 END)) = 0

Case 2: For where (relevantValue == null ? object.Equals(m.IsRelevant, relevantValue) : m.IsRelevant == relevantValue) it works for relevantValue = true or false, but when null gives an error that:

The argument 'value' was the wrong type. Expected 'System.Boolean'. Actual 'System.Nullable`1[System.Boolean]'.

Do I have other options that will work for null, true and false?

Cameron Castillo
  • 2,712
  • 10
  • 47
  • 77
  • Instead of null try : DBNull.Value or relevantValue.Count() > 0 – jdweng Jan 14 '20 at 12:14
  • Good suggestion. But depending on where and how I use it, I either get "Cannot implicitly convert type 'System.DBNull' to 'bool?" or "Operator '==' cannot be applied to operands of type 'bool?' and 'DBNull'". As relevantValue is a bool? the .Count() suggestion does not work either. – Cameron Castillo Jan 14 '20 at 12:19
  • Is the type of column also bool? in your database definition ? You show only a local variable to us. – Holger Jan 14 '20 at 12:39
  • When you have null you have to cast to (object) . You have [bool?]. So you need : (object)relevantValue == DBNull.Value – jdweng Jan 14 '20 at 12:44
  • @Holger: Yes, it is. It's created as: [IsRelevant] [bit] NULL – Cameron Castillo Jan 14 '20 at 12:45
  • @jdweng: I've changed my where clause to: where ((object)relevantValue == DBNull.Value ? object.Equals(m.IsRelevant, relevantValue) : m.IsRelevant == relevantValue). Works for true and false, but when null it gives a runtime error that: The argument 'value' was the wrong type. Expected 'System.Boolean'. Actual 'System.Nullable`1[System.Boolean]'. – Cameron Castillo Jan 14 '20 at 12:48
  • What are you using to connect to the Database ? Is this Entity Framework ? What type is 'MyTable' ? It's not important what you define in your database, the datatype you are loading, must match.How is your m defined ? – Holger Jan 14 '20 at 12:53
  • MyTable is a standard table in SQL Server. Contains a couple of columns but IsRelevant is scripted out as [IsRelevant] [bit] NULL. No default values. My dbContext was created with sqlmetal. – Cameron Castillo Jan 14 '20 at 13:05
  • Do you still have : bool? relevantValue = null; Change [bool?] to [bool]. – jdweng Jan 14 '20 at 14:20
  • But I need to be able to assign null values to that var in order to filter for the nulls. For now, I've split the query up in 2 parts with a normal if (null) { xx } else { xx }. It works like that, but would be nice to still know the short hand solution. – Cameron Castillo Jan 16 '20 at 05:50

1 Answers1

1

Maybe I missed something about the question but, Why not use the Nullable<> .HasValue and .Value to get a real boolean?

IQueryable<Person> People = new List<Person>
{
    new Person
    {
        PersonId = 1,
        IsMarried = true
    },
    new Person
    {
        PersonId = 2,
        IsMarried = false
    },
}
.AsQueryable();

bool? isMarried = null;
var query = from p in People
            where !isMarried.HasValue || (isMarried.HasValue && p.IsMarried == isMarried.Value)
            select p;
Emiliano Javier González
  • 152
  • 4
  • Thank you. Your suggestion works where (in my case) isRelevant = true or false. But when null, I get an error that "Nullable object must have a value". I've played around with casting in this sample, but not working (yet). – Cameron Castillo Jan 14 '20 at 12:15
  • So you have null value in the table and you want to get them when the variable is null? where (!isMarried.HasValue && !p.IsMarried.HasValue) || (isMarried.HasValue && p.IsMarried == isMarried.Value) This works for me, weird, let me know :) – Emiliano Javier González Jan 14 '20 at 12:29
  • My complete clause is: (!relevantValue.HasValue && !m.IsRelevant.HasValue) || (relevantValue.HasValue && m.IsRelevant == relevantValue.Value). That gives the runtime error that "Nullable object must have a value." when relevantValue = null. – Cameron Castillo Jan 14 '20 at 12:35
  • Wow that's so weird, let me see if I can reproduce – Emiliano Javier González Jan 14 '20 at 12:44