LINQ Dynamic Expression API, predicate with DBNull.Value comparison

Question

I have an issue using the Dynamic Expression API. I cannot seem to compare a DataTable field against DBNull.Value. The API is supposed to be able to "support static field or static property access. Any public field or property can be accessed.". However given the following query:

 var whatever = table1.AsEnumerable()
                   .Join(table2.AsEnumerable(),
                   (x) => x.Field<int>("Table1_ID"),
                   (y) => y.Field<int>("Table2_ID"),
                   (x, y) => new { x, y})
                   .AsQueryable()
                   .Where("x[\"NullableIntColumnName\"] == DBNull.Value");

I end up getting the error: "No property or field 'DBNull' exists in type '<>f__AnonymousType0`2'"

Anyone have ideas on how to get around this? I can't use Submission.Field("NullableIntColumnName") in the string passed to the Where method either, btw, or else I would be able to compare against null instead of DBNull.Value.

Answer 1

What happens when you replace your current .Where with something like

.Where(string.format("x[\"NullableIntColumnName\"] == {0}",DBNull.Value));

Answer 2

Sorry to non-answer with a USL but...

Have you looked in the source? There's not a lot of it. My guess is that DBNull is not in the list of registered root objects.

I dont have the source to hand right now, but it is also likely to tell you what any other constants one might compare against might be.

Answer 3

Well, I finally got it. cptScarlet almost had it.

var values = new object[] { DBNull.Value };    
...
.Where("x[\"NullableIntColumnName\"] == @0", values);

or

.Where("x[\"NullableIntColumnName\"] == @0", DBNull.Value);

Answer 4

Hi, you could just use:

   .Where(a => a.IntColName == null);

Edit:

Sorry, I did't see this dynamic requirement... Dynamic would be: (at least in Framework 4)

    var intColName = "...";
    .Where(string.Format("it.{0} is null", intColName));

Answer 5

If you change x.Field<int>("Table1_ID") to x.Field<int?>("Table1_ID") then you'll get nullable integers instead of regular integers, and any DBNull values will be converted to simple C# null values. Based simply on your code snippet, I'm not even sure you'd need dynamic expressions - a simple .Where(foo => foo.x == null) ought to work.