Generate "IS NULL" for nullable types with value of null

[ghoppermaster]

Hi,

First of all thanks for creating such a handy utility, I really appreciate your efforts!

Not sure if I can put this question here or not, apologies if it's the wrong place. I asked the full question here: http://stackoverflow.com/questions/33862504/dapper-simplecrud-isnull

Basically wondering if SimpleCRUD can auto-detect that a nullable integer is null and generate an "IS NULL" where clause if it is? Not sure if the functionality exists or not, apologies if I've missed it.

Thanks again,

John

[elmar-de-groot]

Hi John,
I actually also ran into this issue today as well. Problem appears to be originating from

private static void BuildWhere(StringBuilder sb, IEnumerable<PropertyInfo> idProps, object sourceEntity) {  .... }

where there's a hard-coded "=" equal sign.

[ghoppermaster]

Hi,

Thanks for the help.

I modified the method and it seems to work for my test case. I pass in the whereConditions, do a simple check to see if the parameter is null then use 'is null' or '=' accordingly. Any comments or advice welcomed.

Cheers!

private static void BuildWhere(StringBuilder sb, IEnumerable<PropertyInfo> idProps, object sourceEntity, object whereConditions = null)
var propertyInfos = idProps.ToArray();
string param = "";

...

try
{
    // use the 'is null' operator if value of parameter null, else use '=' operator
    param = (whereConditions.GetType().GetProperty(propertyToUse.Name).GetValue(whereConditions, null) != null ? "{0} = @{1}" : "{0} is null");
}
catch (Exception err)
{
    param = "{0} = @{1}";
}                

sb.AppendFormat(param, GetColumnName(propertyToUse), propertyInfos.ElementAt(i).Name);

[ericdc1]

Good suggestion. I will get this added soon

[ericdc1]

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.

[ghoppermaster]

Hi there,

I included the try/catch as I'd tested it for my use case but not a lot of others. I figured if it failed at least it would default back to the standard functionality. If tested for more scenarios the try/catch could probably be removed?

I guess the reason I was hoping for this feature (and #63) is that I'm dropping SimpleCRUD in as a default generic Repository for my projects. It's really handy to use before creating a more specific data layer with custom SQL etc. It means that I can use the same Repository for all my POCO's and it really speeds that initial development phase up for me.

I'm using this feature (and #63) in my current project and it's saved lots of time. It just feels like it follows the 'simple' ethos of SimpleCRUD.

Anyway, that' just my 2 cents :). It's a great utility and I really appreciate your time on it and respect your decision going forward.

public class Repo
{                
    public static void Delete<T>(IDbConnection db, int id)
    {
        db.Delete<T>(id);
    }

    public static T Find<T>(IDbConnection db, int? id)
    {            
        var result = db.Get<T>(id);            
        return result;            
    }

    public static T Find<T>(IDbConnection db, object whereConditions)
    {
        var result = db.GetList<T>(whereConditions).FirstOrDefault();
        return result;
    }

    public static T Save<T>(IDbConnection db, T entity) where T : BaseModel
    {         
        if (entity.Id <= 0)
        {
            entity.Id = 0;                
            entity.Id = Convert.ToInt32(db.Insert(entity));
        }
        else
        {
            db.Update(entity);
        }

        return entity;           
    }
    ...
}

[ghoppermaster]

Hi Eric,

I would like to ask (if not too bold) to discuss this one a little further? I think the auto-generation of the 'IS NULL' operator is essential for the library to maintain it's 'simple' ethos.

If you are really against it, I wonder if there's a compromise to be made with a configuration flag option?

The main reason I'd like it included as well is so I don't have to maintain a custom modified version of SimpleCRUD on my side. For example today when you updated #63 I would have to reapply my custom changes etc.

Not trying to be a pain. Really love the library and would love to be able to use it this way out of the box.

Many thanks,

John

[elmar-de-groot]

Hi John,

I'm having a look over the weekend to see if I can't come up with something that doesn't require a try-catch. To be able to pass "null" values as part of a where-condition seems logical and I for one would be grateful if I don't have to fall back on raw-sql to get this to work.

Cheers,
Elmar

[elmar-de-groot]

Couldn't resist :)

Based on John' proposal, but without the need to do a try-catch

    private static void BuildWhere(StringBuilder sb, IEnumerable<PropertyInfo> idProps, object sourceEntity, object whereConditions = null)
    {
        var propertyInfos = idProps.ToArray();
        for (var i = 0; i < propertyInfos.Count(); i++)
        {
            var useIsNull = false;

            //match up generic properties to source entity properties to allow fetching of the column attribute
            //the anonymous object used for search doesn't have the custom attributes attached to them so this allows us to build the correct where clause
            //by converting the model type to the database column name via the column attribute
            var propertyToUse = propertyInfos.ElementAt(i);
            var sourceProperties = GetScaffoldableProperties(sourceEntity).ToArray();
            for (var x = 0; x < sourceProperties.Count(); x++)
            {
                if (sourceProperties.ElementAt(x).Name == propertyInfos.ElementAt(i).Name)
                {
                    propertyToUse = sourceProperties.ElementAt(x);

                    if (whereConditions != null && propertyInfos.ElementAt(i).CanRead
                        && (propertyInfos.ElementAt(i).GetValue(whereConditions) == null
                            || propertyInfos.ElementAt(i).GetValue(whereConditions) == DBNull.Value))
                    {
                        useIsNull = true;
                    }
                    //break;        //property was located, should we not exit?
                }
            }
            sb.AppendFormat(
                useIsNull ? "{0} is null" : "{0} = @{1}",
                GetColumnName(propertyToUse),
                propertyInfos.ElementAt(i).Name);

            if (i < propertyInfos.Count() - 1)
                sb.AppendFormat(" and ");
        }
    }

Essentially, the functions that use 'object whereContions', should pass this object along to the BuildWhere function in order to allow inspection of the property values.

I created the following test method, which successfully passes:

    public void TestGetListNullableWhere()
    {
        using (var connection = GetOpenConnection())
        {
            connection.Insert(new User { Name = "TestGetListWithoutWhere", Age = 10, ScheduledDayOff = DayOfWeek.Friday});
            connection.Insert(new User { Name = "TestGetListWithoutWhere", Age = 10 });

            //test with null property
            var list = connection.GetList<User>(new { ScheduledDayOff = (DayOfWeek?) null });
            list.Count().IsEqualTo(1);


            // test with db.null value
            list = connection.GetList<User>(new { ScheduledDayOff = DBNull.Value });
            list.Count().IsEqualTo(1);

            connection.Execute("Delete from Users");
        }
    }

@ericdc1, I think, but am not sure, you want to exit the for-loop after the property is found on sourceEntity. I've added this in, but it's commented out as I'm not sure if my interpretation is accurate.

Cheers,
Elmar

[ericdc1]

This will be in the next release. Thanks for the code above.

My main resistance is the slippery slope where people then will want greater than, less than, not equal, between, etc which is why there is a where clause option. I can see the use case for this without breaking existing functionality.