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.
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.