Null doesn't work with WHERE on C#, Editor, sql server

grenzi

I'm unable to get NULL to work with the .net api.

used the generator online (and patched the resultant javascript link - it links to an old version of datatables out of the box)

works fine without the where. works fine with where set to a value.

this gives an "object reference not set" exception.

 using (var db = new Database("sqlserver", "secretconnectionstring"))
            {
                var response = new Editor(db, "Titles", "TitleId")
                    .Model<TitlesModel>()
                    .Where("EEDARTitleId", null)
                    .Field(new Field("CreatedOn")
                            .Validator(Validation.DateFormat(Format.DATE_ISO_8601))
                            .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_8601))
                            .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_8601))
                    )
                    .Process(request)
                    .Data();

                return Json(response);
            }

(i'm a little frustrated - just paid for this today because it's exactly what i need and i'm working on a deadline - have spent the last couple hours now trying to debug.

grenzi

specifically, json returned is this

{"draw":null,"data":[],"recordsTotal":null,"recordsFiltered":null,"error":"Object reference not set to an instance of an object.","fieldErrors":[],"id":null,"meta":{},"options":{},"files":{},"upload":{"id":null}}

allan

it links to an old version of datatables out of the box

Oops - thank you. I will fix that!

.Where("EEDARTitleId", null)

I'm sorry to say that you've hit a bug in the Editor .NET libraries here. I will look into this as soon as possible tomorrow and post back here with the fix required in the library.

Regards,
Allan

grenzi

ugh. this seems to work. not sure a null was ever tested - can't see how the code would have ever worked, which is pretty frustrating after spending a hundred bucks.

 public Query Where(string key, IEnumerable<dynamic> values, string op = "=", bool bind = true)
        {
            if (values != null) //foreach on ienum<dynamic> throws when null
                foreach (var val in values)
                    _Where(key, val, "AND ", op, bind);
            else
                //suppress the binding otherwise get bloody odd sql. maybe more elegant solution
                _Where(key, null, "AND ", op, false);

            return this;
        }

also need to appropriately surround this

        virtual protected void _Where(string key, dynamic value, string type = "AND ", string op = "=", bool bind = true)
        {
            int whereCount = _where.Count;

            if (value == null)
            {
                _where.Add(new Where()
                    .Operator(type)
                    .Field(_ProtectIdentifiers(key))
                    .Query(_ProtectIdentifiers(key) + (op == "=" ?
                        " IS NULL" :
                        " IS NOT NULL"
                    ))
                );
            }
//have to put this in an else block to avoid binding when value == null, which generates an extra (i.e, bind == false and value == null)
            else 
            {
                if (bind)
                {
                    _where.Add(new Where()
                        .Operator(type)
                        .Field(_ProtectIdentifiers(key))
                        .Query(_ProtectIdentifiers(key) + " " + op + " " + _bindChar + "where_" + whereCount)
                    );
                    Bind(_bindChar + "where_" + whereCount, value);
                }
                else
                {
                    _where.Add(new Where()
                        .Operator(type)
                        .Query(_ProtectIdentifiers(key) + " " + op + " " + value)
                    );
                }
            }
        }

allan

Hi,

I hope you'll accept my apologies for this error slipping through. It is a regression error that was caused by the introduction of the overload for Where() that accepts an IEnumerable as the second parameter. Because the second parameter being passed as null could be match either of:

public Query Where(string key, dynamic value, string op = "=", bool bind = true)
public Query Where(string key, IEnumerable<dynamic> values, string op = "=", bool bind = true)

the compiler will always select the second since it is more specific. When that method was introduced it snapped up any null values, causing this error.

The second part was a refactoring at the same time that should also have been caught - it should be else if rather than just else for the bind (which is basically what you've done with the wrapper).

I've added tests for this now (which should have been present before) and the update will ship with the next version of Editor (likely 1.5.4).

Regards,
Allan

grenzi

Allan - thanks. I appreciate you explaining the history there - makes sense and seems a lot less of a @#$#! moment. (I was also grumpy working to try and get some things knocked out quickly on very little sleep.)

Congrats on your new arrival. My first thought on your newest family member - https://xkcd.com/327/

Hoping mom and the little one are doing well.

allan

Haha - the number of times I've tried to persuade my wife that we should call him little Bobby Tables...! I got overruled in the end :-).

I'm planning to release Editor 1.5.4 with this fix at the end of the week.

Allan

matt_rumsey1212

Did this fix get released with the update? I'm receiving the exact same error (Object reference not set to an instance of an object) when using:

.Where("Agent.Campaign_id", null)

Thanks :)

allan

Yes, from the release notes:

null values in a Where method's value would result in an exception.

Are you using the 1.5.4 dll when you see the same error?

Thanks,
Allan