PetaPoco ExecuteScalar with nullable Guid

Question

I'm scratching my head trying to work out why petapoco's "Exists()" returns a false where it should return true. I use something like this to test:

A simple petapoco model:

[TableName("WorkLog")]
[PrimaryKey("Id")]
public class WorkLog : DefaultConnectionDB.Record<WorkLog>
{
     public int Id { get; set; }
     public Guid? Customer { get; set; }
     public Guid? Project { get; set; }
}

A database table with the same structure as above, filled with Id's and Customers (only nulls for Project).

Create a test object:

var x = new WorkLog {Id = 0, Customer = 'xxx-xxx-xxx...', Project = null}

Query if a the Worklog exists in DB, based on "Project":

var exist = db.Exists<WorkLog>("Project=@p", new { p = x.Project });

The result is "false" in this scenario. If I fill model, db and query with a Guid for Project it returns "true" like it should though.

So I guess this has something to do with null and DbNull comparison, but I can't seem to find the right place to dig. I've tried with a helper method for converting "null" to "DbNull" before query, but it still returns false.

Temporary solution:

Currently I've solved this by doing...

var exist = db.Exists<WorkLog>(x.Project == null ? "Project IS NULL" : "Project=@p", new { p = x.Project });

This now works as expected, but I'd rather the method could be called normally and still return the expected result.

score 3 · Accepted Answer · answered Dec 12 '13 at 15:08

3

The problem is more in the ADO.net side of thing than in PetaPoco. PetaPoco only create the Params and fill them with your values.

But, handling a null in the WHERE clause is tricky, as this MSDN article says:

Testing for Null

If a column in a table (in your database) allows nulls, you cannot test for a parameter value of "equal to" null. Instead, you need to write a WHERE clause to test whether both the column is null and the parameter is null. The following SQL statement returns rows where the LastName column equals the value assigned to the @LastName parameter, or whether both the LastName column and the @LastName parameter are null.
SELECT * FROM Customers
WHERE ((LastName = @LastName) OR (LastName IS NULL AND @LastName IS NULL))

answered Dec 12 '13 at 15:08

Eduardo Molteni

38,786
23
141
206

Thanks for the info. Don't know why I just didn't test this out in a query-window, but I was quite certain that I had used checks against "NULL" before. Anyway, I stand corrected. – Mackan Dec 12 '13 at 20:00
It suspect it would not be impossible for PetaPoco to add support for null-checks though, but perhaps that is out of their scope. I'll just use my "temporary solution". – Mackan Dec 12 '13 at 20:02
It's not impossible, but not easy. It will need to find out if the param it's in the WHERE clause or if you are trying to update a field. Besides that, your query is not common, I'm using PetaPoco extensively and never found the problem. I always have something like this: `if (x != null) {sql.append(" AND x = @0", x);}` – Eduardo Molteni Dec 12 '13 at 20:09
1

Just to give you an idea why I ran in to the problem: I needed a "IsNew" function that, instead of checking for id, checked all other columns in a table (knowing that if all columns were true, it was a unique record). I agree that this is not something you run in to every day .. or year for that matter. I'm doing some imports from Google spreadsheets to my database, and only want to insert "new" records. Thanks again. – Mackan Dec 12 '13 at 23:28

PetaPoco ExecuteScalar with nullable Guid

1 Answers1

Testing for Null