Question
I'm trying to retrieve the root node in a hierarchy using Dapper.SimpleCRUD. The root node is identified by having no parent node. This is the call I'm making:
var whereConditions = new object { ParentId = (int?)null, CompanyId = 1 };
var root = db.GetList<T>(whereConditions).FirstOrDefault();
And this is the SQL that is generated:
exec sp_executesql N'Select * from [Folders] where [CompanyId] = @CompanyId and [ParentId] = @ParentId',N'@CompanyId int,@ParentId int',@CompanyId=13,@ParentId=NULL
The problem is that [ParentId] = @ParentId will return no records as @ParentId is null. To match a record the statement would need to be [ParentId] IS NULL.
I wonder if SimpleCRUD can detect when a nullable parameter equals NULL and can generate an IS NULL statement? Something like this:
exec sp_executesql N'Select * from [Folders] where [CompanyId] = @CompanyId and [ParentId] IS NULL',N'@CompanyId int,@ParentId int',@CompanyId=13,@ParentId=NULL
I know I can send in the WHERE string in manually but was hoping for an automated SimpleCRUD approach.
Apologies in advance if I've missed something obvious and thanks for your time.
Popular Answer
I looked at this a little closer and really don't like the idea of implementing something that has to be in a try/catch to work.
Could you use the manual where method for this case?
var user = connection.GetList("where age = 10 or Name like '%Smith%'");
or in your case:
var user = connection.GetList("where ParentId is null AND CompanyId = 1");
You could easily generate a different where clause depending on if the ParentId is null.