Watch Your Views...

Today I was driven crazy by what I thought was an nHibernate bug. I'd changed the mappings for maybe 6 entities in this project, trying to get a crazy join to work in QueryOver. Although that still doesn't work (nHibernate generated sql isn't including the last table in the join, yet adds the alias for the missing table to the where clause), I ran into another issue while testing.

In part of our app, you can drill down 3 levels, to the detail of one of our aggregate roots. Well, the last level wasn't working. I don't believe I modified any mappings that should affect this object, so I was stumped. The nHProf output was odd. It said

Failed to execute multi criteria[SQL: SELECT count(*) as y0_ FROM ...

WHERE ((this_.MyObjectId= ? and this_.OtherObjectId = ?) and this_.AnotherObjectId = ?);

SELECT TOP (?) this_.FieldName, ...

So I ask myself... Where did these question marks come from? I can't find a thing online that explains it.

In the error message thrown by Visual Studio I get...

{"Input string was not in a correct format."}

And then this cryptic message block

at System.Number.ParseDouble(String value, NumberStyles options, NumberFormatInfo numfmt)

   at System.String.System.IConvertible.ToDouble(IFormatProvider provider)

   at System.Convert.ToDouble(Object value)

   at NHibernate.Type.DoubleType.Get(IDataReader rs, Int32 index)

   at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name)

   at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner)

   at NHibernate.Type.AbstractType.Hydrate(IDataReader rs, String[] names, ISessionImplementor session, Object owner)

   at NHibernate.Persister.Entity.AbstractEntityPersister.Hydrate(IDataReader rs, Object id, Object obj, ILoadable rootLoadable, String[][] suffixedPropertyColumns, Boolean allProperties, ISessionImplementor session)

   at NHibernate.Loader.Loader.LoadFromResultSet(IDataReader rs, Int32 i, Object obj, String instanceClass, EntityKey key, String rowIdAlias, LockMode lockMode, ILoadable rootPersister, ISessionImplementor session)

   at NHibernate.Loader.Loader.InstanceNotYetLoaded(IDataReader dr, Int32 i, ILoadable persister, EntityKey key, LockMode lockMode, String rowIdAlias, EntityKey optionalObjectKey, Object optionalObject, IList hydratedObjects, ISessionImplementor session)

  at NHibernate.Loader.Loader.GetRow(IDataReader rs, ILoadable[] persisters, EntityKey[] keys, Object optionalObject, EntityKey optionalObjectKey, LockMode[] lockModes, IList hydratedObjects, ISessionImplementor session)

   at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies)

   at NHibernate.Impl.MultiCriteriaImpl.GetResultsFromDatabase(IList results)

Um... wha?!? So, somewhere NHibernate is trying to convert something to a double and is failing. Okay, why is it doing that and why is that causing NHibernate to add question marks to the query?

I check out the mappings. Notice that some of the double fields on this aggregate are not nullable when the fields in the view they are populated by is set up to be nullable. Strange. I remember that normally it throws an error that kind of points you to this fact, but I fix it and proceed.

Still no dice.

I check out the query its generating, I check the mapping again, I check everything I can think of... Hours later, I'm going over it again and I start looking at the data. I check the result set for nulls where it should be a non null. Nothing. Then I start comparing the data from the view to the column types.

I find something interesting. We have a double field called Margin. The value returned for this field from SELECT * FROM MyFeature.MyView is '2012-07-01'. My friends, that is NOT a double! I'd missed it my first couple passes because I'd always believed that the types given to a column are inviolate. SQL Server wouldn't lie to me!

Well... Someone updated one of the tables that this view relies on. Apparently they changed the order of some columns. And since the underlying view structure is based (from what I understand) on column index, SQL Server said, "Hey! This is now a Date field!", and started returning values for a different column.

WTH!?!

I'd never even HEARD of such a thing! Called over the DBA and asked him what he thought about a date appearing in a double column and he explained to me what happened. Even gave me the proc he uses to regen all views in a given database. Regenerated the view and voila! Bug is gone :)

If I were a better person, I'd dig into NHibernate's source and offer a patch that gives a better error message for this issue.

Instead I'm writing this blog post, that outlines my frustration, but has all of the keywords I searched for in my initial journey to find the solution to this problem. Hopefully another developer having this issue will stumble upon my blog and waste less time than I did.

Moral of the story...

Don't trust SQL to always return the type you expect

And

If you can't find the solution to your issue on Google, maybe you really have a different problem.

jQuery 1.6.1+ and .prop()

Upgrading our version of jQuery at work, and some of our code broke. We have a really nifty tree control, and we use the checkbox "indeterminate" property to get that cool tri-state look.

Well, that all broke. Pre-jQuery 1.6.1, you could set indeterminate using .attr("indeterminate", true). Now, that doesn't work. The indeterminate property needs to be set with jQuery .prop().

This may affect other properties that were previously set with .attr().

Ensuring a Unique Name for User Entities

        public static string GetUniqueName(string[] existingNames, string targetName)
        {
            int copyCount = 0;
 
            Regex reg = new Regex("^" + targetName + "(\\s?COPY\\s?\\d+|\\s?COPY)$");
            foreach (var name in existingNames)
            {
                if (reg.IsMatch(name))
                {
                    copyCount++;
                }
            }
            string copyName = targetName + " COPY" + (copyCount == 0 ? "" : " " + copyCount);
            return copyName;
        }

As you can see, I've hard coded COPY and the number scheme in the code above. It does a decent job of finding all names that have been previously copied and adding on another copy. Doing it this way prevents us from iterating over the existingNames collection more than once.

nHibernate and Table Value Parameters

nHibernate is a great tool for interacting with data. However, much of our data involves pretty huge parameters. Some of our queries can have ID counts in the thousands, and sometimes they select all of them at once and want us to do stuff with them. I was working on just this issue with a section of an app, and found a neat solution. Typically we pass large ID lists using a table valued parameter. Unfortunately nHibernate doesn't know about those. My solution was to create an XML element with all of the id's as sub elements. Then, in the named SQL I query them out of the XML into a table. 

In your method, you want to build your XML string. I didn't create an XML doc and add it that way. This is simple, so I used a string builder to create the xml by hand.

            StringBuilder sb = new StringBuilder();
            sb.Append("<StoreIds>");
            foreach (int storeId in storeIds)
            {
                sb.AppendFormat("<Id>{0}</Id>", storeId);
            }
            sb.Append("</StoreIds>");

Then I passed the xml string into my GetNamedQuery call.

                var query = Session.GetNamedQuery("MyAwesomeNamedQuery")
                    .SetParameter("userId", userId)
                    .SetParameter("condition", whereClause)
                    .SetParameter("storeIds", sb.ToString(), NHibernateUtil.StringClob);
                result = query.List<MyObject>().AsQueryable();

NOTE: The key to this is defining the third parameter of the SetParameter method as NHibernateUtil.StringClob. If you don't, nHibernate automatically truncates your string to 4,000 characters.

​The SQL statement was changed from using 

​       INSERT INTO @storeIds2

​       SELECT StoreId FROM Client.Store

​       WHERE StoreId IN (:storeIds)

To

 INSERT INTO @storeIds2
 SELECT T.x.value('.', 'int') ID
 FROM @storeIds.nodes('/StoreIds/Id') T(x)​

And that's all the SQL it takes to extract the ID's from the xml string we passed into a sql @table variable!

When I tested this code, I tested it with a db that held 11,500+ stores. All stores were passed perfectly.

Making a div fill the page height

I overheard a co-worker complain about some of our HTML in the office today. There's a grid that we have in a div that is pretty much always too short for its content. It would be great if we could get a div to fill the entire page, excluding our header content.

I figured it out. It's okay, except there doesn't seem to be a nice way of automagically aligning to the header height. You pretty much have to set the "top" property to the height of your header.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta http-equiv="content-type" content="text/html; charset=windows-1250">
  <meta http-equiv="x-ua-compatible" content="IE=8">
 <style type="text/css">
  *{
   box-sizing: border-box;
  }
  .container{
   border: 3px solid red;
   margin: 5px;
   position: absolute;
   bottom:0px;
   top:0px;
   left:0px;
   right:0px;
  }
  .header{
   border: 3px solid green;
   margin: 5px;
   height: 200px;
   position: relative;
  }
  .tableContainer{
   border: 3px solid blue;
   position: absolute;
   margin: 5px 5px 5px 5px;
   bottom:0px;
   top:210px; /* Required for positioning. Could tie this with javascript to make it work */
   left:0px;
   right:0px;
   overflow: auto;
  }
  .tableMock{
   height:400px;
   background-color: #3399ff;
   width: 100%;
   color: white;
   text-align: center;
   font-weight: bold;
   font-size: 30px;
   font-family: arial, helvetica, 'san-serif';
   padding: 30px;
  }
 </style>
</head>
<body>
 <div class="container">
  <div class="header"></div>
  <div class="tableContainer">
   <div class="tableMock">
    The table here could be as tall or short as you want. If it is larger than the containing div, scroll bars will appear (x and y). Otherwise no matter how large the screen gets, the containing div will always take up as much of it as it can.
   </div>
  </div>
 </div>
</body>
</html>

Dates, Time Zones, JSON and MVC

Ran into an issue with sending a date to MVC. Needed to have a date within a certain range, and I'm using a Telerik date picker to do the date selection. Worked great, here in California. Didn't work so well in India.

When you create a date in Javascript, it appends the time zone.

Sat Jan 01 2000 00:00:00 GMT-0800 (Pacific Standard Time)

When you send it via ajax, it's converted to UTC, or 

"2000-01-01T08:00:00.000Z"

This works fine for time zones with a negative UTC offset. For time with a positive offset, this can set their selection outside of the date range,

"1999-12-31T18:30:00.000Z"

I think this could be a fix...

myDate = new Date("1/1/2000")

Sat Jan 01 2000 00:00:00 GMT-0800 (Pacific Standard Time)

myDate.setMinutes(-myDate.getTimezoneOffset())

946684800000

myDate

Fri Dec 31 1999 16:00:00 GMT-0800 (Pacific Standard Time)

myDate.toJSON()

"2000-01-01T00:00:00.000Z"

And with my time set to IST

var myDate = new Date("1/1/2000")

undefined

myDate

Sat Jan 01 2000 00:00:00 GMT+0530 (India Standard Time)

myDate.setMinutes(-myDate.getTimezoneOffset())

946684800000

myDate

Sat Jan 01 2000 05:30:00 GMT+0530 (India Standard Time)

myDate.toJSON()

"2000-01-01T00:00:00.000Z"

Telerik MVC 3 Controls Injected Dynamically via Template

So... I want to use the Telerik MVC controls (numeric textbox, datepicker, etc) in a template, but rendering the template breaks Telerik's code. Also, I don't want to use an ID to identify them, since I'm using them for each child item. What's a guy to do?

Well, I looked at the response from my partial view, and found that Telerik is initializing them each in their own javascript in the partial view.

So, I pulled out that logic and did it myself.

            $("input[name=editorDate]", control).tDatePicker({
                format: 'M/d/yyyy',
                minValue: $this._model.startDate,
                maxValue: $this._model.endDate
            });
 
            $("[name=percentValue]", control).tTextBox({
                val: 0,
                step: '1',
                minValue: 0,
                maxValue: 100,
                digits: 2,
                groupSize: 3,
                positive: 0,
                negative: 0,
                text: 'Enter value',
                type: 'percent'
            });
            $("[name=amountOffValue],[name=priceValue]", control).tTextBox({
                val: 0,
                step: '1',
                minValue: 0,
                maxValue: 1000000,
                digits: 2,
                groupSize: 3,
                positive: 0,
                negative: 0,
                text: 'Enter value',
                type: 'currency'
            });

 I call these functions after I instantiate a template, and it works great! There are some .css issues with the z index of Telerik's controls, but some finely applied css classes fixed that. 

I didn't see anything on the internet that solved this issue, so I posted this fix. Hope this helps someone!

F~