compare object int or dbnull.value linq

Question

I have a linq query:

var capacityQuery = from Info in mySQLDataTable.AsEnumerable() where Info.Field<object>("location") == Location.ID orderby Info.Field<DateTime>("date")

The line "where Info.Field("location") == Location.ID" is where I am having problems

My Question is. How do I do a comparison in the where statement that might be either a DBNull.Value or an int value on either side

Below is for reference:

Location is a custom class. The ID returns an object.

    private int? addressID;

    public object ID {
        get
        {
            if (addressID == null)
                return DBNull.Value;
            else
                return addressID;
        }
    }

The reason it has to be DBNull is because it is coming from a database. The "table" is also being filled in from a database.

I know == in this case is wrong because its comparing the references which is obviously false.

I know object.equal(var1, var2) should work but it doesnt which im guessing is because they are 2 different objects.

The values could be an int or DBNull both on the table side and the class side.

This is in a foreach loop so I use the same query where Location has an int value or when it has a DBNull value.

Again My Question is. How do I do a comparison in the where statement that might be either a DBNull.Value or an int value on either side

What is your question? Additionally, you should be doing this filtering in your database in the SQL query, not on the application side. — Servy, Oct 27 '14 at 16:52

Darek · Accepted Answer · 2014-10-27T17:24:34.887

1

Use

private int? addressID;

public object ID {
    get
    {
        return addressID.HasValue ? addressID.Value as object : DBNull.Value;    
    }
}

instead. This way, if addressID has not been initialized or if it has been deliberately set to null, the ID property will return a DBNull.Value. Otherwise, it will return an int, but you have to cast it yourself from object to int or int?.

Having said that, creation of the null has been called "a billion dollar mistake" by the author, but by now I think it is in the trillions. You should return an int? datatype from the database if you can, in which case, the comparison will always work. I am not sure which ORM do you use, but Dapper and linq2db handle this scenario correctly as far as I know.

edited Oct 27 '14 at 17:24

answered Oct 27 '14 at 16:53

Darek

4,687
31
47

I dont believe my object is the issue. Just the comparison in the Where statement. Also this returns an error saying "there is no implicit conversion between 'int' and 'System.DBNull'. – BrinkDaDrink Oct 27 '14 at 17:10
Forgot to cast it. It compiles now. This approach provides you with the safest test if a value has been assigned or if it is null. – Darek Oct 27 '14 at 17:16

compare object int or dbnull.value linq

1 Answers1

Linked