Month: June 2014

IE incompatibility notes

Quick post for the day. We had an issue where the site was rendering great in IE and Chrome locally, but when we promoted it out – kablooey! How to fix?

Checking the client browser mode (and using IE’s dev tools) showed that our default browser mode settings were to IE7. (Not sure why this was, but it’s part of our awesome standard config evidently). Edge setting (or the most recent, a la Chrome) would be more correct. So, in the header, try this tag:

<meta http-equiv=”X-UA-Compatible” content=”IE=edge”>

Code snippet – expanding hierarchies in SQL Server

I seem to remember posting on this subject back in December – but I can’t find it. Sigh. (note – just found the post, here – it’s down a ways but still there.) I have a table called Symptom that contains a hierarchical structure – i.e. a ID field and a ParentID field, in this implementation. (Yes I could have used HierarchyID – we opted not to, after looking at all our options.)

Note we could have used recursion. If this was >10 levels or so, I would do that. But SQL is great at joins and they execute very fast; we’ll use this approach for now. See or the Microsoft SQL Server Bible 2008 by Paul Nielsen for more on expanding hierarchies and the adjacency pattern.

-- Retrieve the whole hierarchy from level 1 to level 5
declare @t table (
l1 int, l2 int, l3 int,  l4 int, l5 int, id int, depth int)

-- Populate @t: expand msg hierarchy for levels 1-5
insert into @t
select m1.SymptomID, m2.SymptomID, m3.SymptomID, m4.SymptomID, m5.SymptomID, null, 1
from Symptom as m1
left outer join Symptom m2 on m1.SymptomID=m2.ParentID
left outer join Symptom m3 on m2.SymptomID=m3.ParentID
left outer join Symptom m4 on m3.SymptomID=m4.ParentID
left outer join Symptom m5 on m4.SymptomID=m5.ParentID
where m1.ParentID is NULL

-- Calculate node level for each node and get tree depth
declare @depth int
update @t set depth = depth + 1 where l2 is not null
update @t set depth = depth + 1 where l3 is not null
update @t set depth = depth + 1 where l4 is not null
update @t set depth = depth + 1 where l5 is not null
select @depth = max(depth) from @t

-- Since we have made several joins, we have only leaf nodes of level 4 in  -- @t. Add missing leaf nodes of level 1
insert into @t select distinct l1, NULL, NULL, NULL, NULL, NULL, 1 from @t where l2 is not NULL
-- Add missing leaf nodes of level 2
insert into @t select distinct l1, l2, NULL, NULL, NULL, NULL, 2 from @t where l3 is not NULL
-- Add missing leaf nodes of level 3
insert into @t select distinct l1, l2, l3, NULL, NULL, NULL, 3 from @t where l4 is not NULL
-- Add missing leaf nodes of level 4
insert into @t select distinct l1, l2, l3, l4, NULL, NULL, 3 from @t where l5 is not NULL

-- Populate id field, get the rightmost msg id from @t
update @t set id=coalesce(l5, l4, l3, l2, l1)

select id
, depth
, SymptomFullDesc = 
CASE WHEN depth = 1 THEN l1.SymptomDesc 
WHEN depth = 2 THEN l1.SymptomDesc + ' > ' + l2.SymptomDesc
WHEN depth = 3 THEN l1.SymptomDesc + ' > ' + l2.SymptomDesc + ' > ' + l3.SymptomDesc 
WHEN depth = 4 THEN l1.SymptomDesc + ' > ' + l2.SymptomDesc + ' > ' + l3.SymptomDesc + ' > ' + l4.SymptomDesc
WHEN depth = 5 THEN l1.SymptomDesc + ' > ' + l2.SymptomDesc + ' > ' + l3.SymptomDesc + ' > ' + l4.SymptomDesc + ' > ' + l5.SymptomDesc
from @t t
left outer join Symptom l1 on t.l1 = l1.SymptomID
left outer join Symptom l2 on t.l2 = l2.SymptomID
left outer join Symptom l3 on t.l3 = l3.SymptomID
left outer join Symptom l4 on t.l4 = l4.SymptomID
left outer join Symptom l5 on t.l5 = l5.SymptomID
ORDER BY depth, SymptomFullDesc

Don’t shortchange architecture.

Can I say, I’m actually a little disappointed in EntityFramework right now. We were moving to more of a model based solution with the second version of our software, but found these pitfalls:

  1. In all but the simplest Admin forms stored procedures will be necessary. For example, you will want to update against a table – but run checks against a balance, or update a ParentID, or modify related information – you’ll have to spill across multiple entities. The simplest way I know of doing this without a lot of wasted code is db-side sprocs.
  2. EF works TERRIFIC when you’re binding directly to a table. But as soon as you have to use sprocs there’s a TON of manual coding and clicks that has to happen. This means the same or more amount of work than in old-school DAL layers.

For example, look at this EF sproc mapping:

Say it with me – Eeeeessshhhhh. Having done a few dozen of these mappings so far I can say it’s a painful process and as fraught with peril as any DAL layer. And, since it’s graphically based and not a class, I frankly don’t trust it as much when it comes to making changes. EF does a good job of prepopulating many of these, but not always – especially if the table name and the field name are the same, or if the casing is slightly off (Id versus ID for example). I may end up sighing, throwing up my hands, and rolling back to a SqlDataSource instead.

Long story short – I would love to blame our ever-increasing list of functionality but specs are specs, and I don’t think things like soft-delete or cascading changes are unique to this application/working environment. And, I’m looking back on the road we chose not to take – BOWA (Breeze-OData-WebAPI-AngularJS) and wishing we had spent a few more weeks on architecture and a firm set of guidelines/standards. It would have paid off handsomely when it came to maintenance and durability for this app.