Is SubSonic's CodingHorror the only way to do WHERE ISNULL?

Question

I'm trying to do a simple UPDATE ... WHERE ISNULL() using SubSonic ActiveRecord and the only way I can get it to work is by using CodingHorror. eg:

    public void MarkMessagesRead(long? from_person)
    {
        if (from_person.HasValue)
        {
            _db.Update<message>()
                .Set(x => x.is_read == true)
                .Where(x => x.from_id == from_person && x.to_id == people_id)
                .Execute();
        }
        else
        {
            new SubSonic.Query.CodingHorror(_db.DataProvider, "UPDATE messages SET is_read=1 WHERE ISNULL(from_id) AND to_id=@toid", people_id).Execute();
        }
    }

Am I missing something?

Answer 1

Did you try to do an "And(x => x.from_id).IsEqualTo(null)?

Answer 2

There's two reasons why this wasn't working.

Firstly, it should be Where<message>() not Where() :

db.Update<message>()
            .Set(x => x.is_read == true)
            .Where<message>(x => x.from_id == from_person && x.to_id == people_id)
            .Execute();

The Where() method has bugs - it loses the comparison type so everything becomes a comparison for equality and operators like < <= etc.. all get lost. Also, it only uses the first comparison. In the example above, the && x.to_id == people_id is silently discarded.

Secondly, sub-sonic's support for ==null and !=null has been commented out.

I've logged both of these issues in the sub-sonic's github:

• http://github.com/subsonic/SubSonic-3.0/issues/issue/210
• http://github.com/subsonic/SubSonic-3.0/issues/issue/211

Answer 3

The code supplied by cantabilesoftware should work - the two issues have been fixed in current source on github!