Thursday, April 10, 2014

Distinct, Random Table Rows With Entity Framework

For the purpose of setting up various unit and functional tests in a C# project, I needed to grab some random rows from a database table to analyze. However, rather than just any random batch of records, I specifically wanted to grab a single, random member of each and every unique type.

I created some code which does this handily in TSQL, but for my C# project I needed to do the same using entity framework.


The table consists of an auto-incrementing primary key column, a uniqueidentifier column, and a name column, and the data are a random number of several different types of flowers. Here's how to create a table in SQL with some test data.

-- Reduce clutter in the Results tab as records get inserted or updated.
SET NOCOUNT ON
GO

-- Variables.
DECLARE
 @count int,
 @max int,
 @innercount int,
 @innermax int,
 @name varchar(100)

-- Table variable definition.
DECLARE @Flowers TABLE
(
 ID int IDENTITY (1, 1) PRIMARY KEY NOT NULL,
 Guid uniqueidentifier,
 Name varchar(100)
)

-- Start by inserting several flower names.
INSERT INTO @Flowers (Name)
SELECT 'Black Lotus'
UNION ALL
SELECT 'Dandelion'
UNION ALL
SELECT 'Rose'
UNION ALL
SELECT 'Tulip'
UNION ALL
SELECT 'Orchid' 

-- Set up a WHILE loop based on the number of flowers named in the table.
SELECT @count = 1, @max = COUNT(Name)
FROM @Flowers

-- Iterate through the list of flower names.
WHILE @count <= @max
BEGIN
 
 SELECT @name = Name
 FROM @Flowers
 WHERE ID = @count

 -- Set up another WHILE loop with a minimum value of 1, and a maximum
 -- value randomly picked from between 0 and 99, made effectively 1 to
 -- 100 by adding +1 to the random value.
 SET @innercount = 1
 SET @innermax = ( ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % 99 ) + 1

 WHILE @innercount <= @innermax
 BEGIN

  -- Insert a new record for the current flower Name along with a 
  -- randomly chosen GUID value.
  INSERT INTO @Flowers (Guid, Name)
  SELECT NEWID(), @name

  SET @innercount = @innercount + 1

 END 

 -- Just to indicate how many of each type got inserted.
 PRINT 'Inserted ' + CAST(@innercount AS varchar(4)) + ' ' + @name

 SET @count = @count + 1

END

-- Remove the placeholders with no guid assigned.
DELETE
FROM @Flowers
WHERE Guid IS NULL


Now, I can use the following query in TSQL to grab a single unique member of each type of flower:

-- Return a single member picked at random of each group of flowers.
SELECT DISTINCT
 Name,
 (SELECT TOP 1 GUID FROM @Flowers WHERE Name = f.Name ORDER BY NEWID())
FROM @Flowers f
GROUP BY Name 
      
So now how do we reproduce the above results using entity framework? Like so:

public void Main()
{       
       var result = new List();

       // Declare a new instance of Random.
       var rand = new Random();
 
       // Query to determine how many unique instances of each flower name live in the table.
       var flowerCounts = Flowers
          .GroupBy(f => f.Name)
          .Select(f => new { name = f.Key, count = f.Count() });
 
     // Broad query to grab a list of flower names and unique identifiers.
       var flowers = Flowers
          .Select(f => new FlowerName { name = f.Name, uniqueIdentifier = f.Guid.ToString() });
  
       // Iterate through the list of flower names and their respective counts.
       foreach (var flowerCount in flowerCounts)
       {
          // Here we pick a random number between 1 and the number of flowers of the current name.
          var skip = rand.Next(1, flowerCount.count);
 
          // Now, we refine the broad query to group by flowers of a particular name, skipping
          // according to the randomly determined skip interval, and then grabbing the first. 
          // Then we just order by name.
          var uniqueSingleton = flowers
              .Where(f => f.name == flowerCount.name)
              .GroupBy(a => a.name, (key, s) => s.Skip(skip).First())
              .OrderBy(f => f.name);
  
          foreach (var combo in uniqueSingleton)
          {
               Console.WriteLine(string.Format("{0}: {1}", combo.name, combo.uniqueIdentifier)
               + Environment.NewLine);
          }
       } 
  }

  public class FlowerName
  {
       public string name{ get; set; }

       public string uniqueIdentifier { get; set; }
  }

Now as with the SQL example above, executing the above results in a collection of distinct flower names along with a unique row identifier for each:





Thursday, March 6, 2014

Total Thyroidectomy

Around 18 years of age, my thyroid gland decided to start getting busy.
 


Meaning, bigger.

Called a "goiter", the result of Hashimoto's thyroiditis, my immune system assaulted the gland over the years and prompted it to create scar tissue and increase its size. As far as it was concerned, this thing was a foreign body needing termination.

Fast-forward to age 40.

A friend of mine who works with kids contracts pertussis, aka "whooping cough", a childhood disease largely controlled with childhood vaccines, but which has found new life thanks at least in part to anti-vaccination activism. I happen to contract almost identical symptoms a short while later. I wake up nights with unprecedented difficulty breathing. My throat feels clogged with mucus, and the hacking cough lasts a good month or two.

I decide it's time to get this thing out of me. Total thyroidectomy.

I consult a local ear-nose-throat doctor. We commit to surgery. She gives me a warm hug on my way out of the initial appointment.

I go to a pre-op appointment. Alex, a Korean physician's assistant, opens the floor to me to ask questions. I ask my wife's questions first, how soon prior to the surgery can she see me? How long is recovery?

Questions answered, I'm out. The following Friday, I wake up at 5-dark-thirty to arrive at the hospital around 7am. The reception nurse, sleepy but awake, leads me into the prep area. I'm asked to disrobe into one of those humiliating ass-open hospital robes, and need help tying off the lower part. She reassures me, she's seen plenty o' butts, and helps tie the knot.

I'm led to a bed, where an intake nurse sees me next. We review my meds, whether I've taken aspirin, fish oil, any other OTC drugs that might cause excessive bleeding. Being a compliant patient, I've refrained from taking these for a full 5 days prior. I've even showered and scrubbed the area with a chlorhexidine-containing soap to minimize the number of microbes loitering around my neck.

The nurse inserts the IV into my forearm. The vein is sketchy, so apologetically she tries again, this time atop my hand. Success!

Bruce, the anesthesiology nurse, warmly greets me. A friend snaps a shot of me smiling wanly in the hospital bed, and then I'm wheeled off toward the operating room.

Upon arrival, Bruce whips a syringe out from his chest pocket, then injects its contents into my IV. Versed, he says. Cool! I'm hopefully I'll start feeling woozy, euphoric. No such luck, however.

I'm there, in the OR, a few minutes. Someone places a mask halfway across my nose and mouth. I breathe normally and then... like some ridiculously swift transition in a movie, I find myself in recovery. I have utterly no recollection of the events that transpired.

I gingerly probe my neck, and it seems genuinely less massive. The thyroid is gone, it would seem. A tube leading to a squeeze bulb meant to suck out fluids hangs at my chest, and the remainder of the wound is sealed with some sort of novel purple "glue" meant to bind incisions.

A friend along with my wife rescue me from the hospital and we drive. We decide to have dinner at a local sushi restaurant we enjoy. Despite the lingering haze of the anesthetic (which imposes a noticeable delay as I try to piss in the restroom), I find I can fairly easily chew and swallow our food. Famished, I devour it eagerly.





The first few days following surgery, I numb the pain with hydrocodone provided by the surgeon. Recovery is, thankfully, largely uneventful. Now at a week afterwards, the glue has all but fallen away, and the surfacemost areas of the scar have healed over. I now apply Mederma several times daily to deter the formation of an annoyingly visible scar.

I can swallow much more easily, and although now I add another medication to my daily regimen (levothyroxine, the brand name of the well-regarded thyroid replacement hormone), along with Humalog and Lantus to manage my type 1 diabetes.


It doesn't bother me as much. Just being able to breathe and swallow more easily is a wondrous thing.