Many to One relationships with PetaPoco

One small ‘gotcha’ to be aware of with the excellent lightweight ORM PetaPoco is when mapping many to one object references to entity properties.
The following repository call returns a list of Schedule entities. A schedule enitity has two object properties: Category and Resource. So the call to db.Fetch accepts an SQL statement that will return all the entities we’re interested in while correctly mapping the Resource and Category objects.

		       /// Fetches all schedules with a start date greater then or equal to the supplied date for all active resources
       public List<Schedule> FetchAllFutureSchedules(DateTime from)
        {
            // Ensure columns are grouped by table!!!!!
            // the poco parser expects all poco fields to be sequential, after the poco id

            var db = new PetaPoco.Database("DatabaseConnection");

            var relator = new ScheduleRelator();

            return db.Fetch<Schedule, Resource, Category, Schedule>(
            relator.MapResources,
            @"SELECT [ScheduleId]
              ,[Title]
              ,[StartDate]
              ,[EndDate]
              ,[Utilisation]
              ,[Resource].[Name]
              ,[Resource].[ResourceId]
              ,[Resource].[IsActive]
              ,[Category].[Name]
              ,[Category].[CategoryId]
              ,[Category].[IsActive]
              ,[Category].[Colour]
	        FROM [Schedule]
	        LEFT JOIN [Resource] ON Schedule.ResourceId = [Resource].ResourceId
	        LEFT JOIN [Category] ON Schedule.CategoryId = [Category].CategoryId
            WHERE
                    [Resource].[IsActive] = 1
                AND (StartDate >= @0 OR EndDate >= @0)
	        ORDER BY [Resource].[Name]"

            , from
            );
        }

I ran into an issue where my entities where not being correctly loaded – properties where either null, or being assigned the wrong value from the database. The issue is with the way PetaPoco delineates the returned data reader field to construct your entity.
To help it out:
– Construct your SQL to keep all the properties for each entity grouped together.
– When specifying the fields for a new entity, don’t specify the primary key first, PetaPoco can get confused as to whether it’s the foreign or primary key.
As a little extra, I’ve included the code to resolve and map two many to one entities as the PetaPoco example only show’s how to achieve this with one .
internal class ScheduleRelator

 {
        // A dictionary of known resources
        Dictionary<int, Resource> _resources = new Dictionary<int, Resource>();
        Dictionary<int, Category> _categories = new Dictionary<int, Category>();

        public Schedule MapResources(Schedule p, Resource r, Category c)
        {
            // Match resource
            Resource aResource;
            if (_resources.TryGetValue(r.ResourceId, out aResource))
                r = aResource;
            else
                _resources.Add(r.ResourceId, r);

            // Wire up objects
            p.Resource = r;

            // Match categories
            Category aCategory;
            if (_categories.TryGetValue(c.CategoryId, out aCategory))
                c = aCategory;
            else
                _categories.Add(c.CategoryId, c);

            // Wire up objects
            p.Category = c;

            return p;
        }
    }

MCPD Certifications

Back in December 2010 I made a concerted effort to complete my MCPD certifications in both Enterprise Application Development and ASP.NET.

So without any further ado:

MCPD Certification

These certifications are less relevant to my current technical/sales orientated role, but I’m still proud in having achieved them and will most likely upgrade to .Net 4.0 in the near future.

Returning a DTO from a one to many relationship

Wrote this neat query the other day.  It queries a 1:M relationship (a make can have zero or more models) and returns a list of tuples, with the third property being a dictionary of makes.  This neatly preserves the make/model relationship and returns the entire hierarchy in one database call.  Thanks to Blair Davidson for the help on this!

		  public IEnumerable<System.Tuple<string,int,Dictionary<string, int>>> SelectAllMakeModelsAsReverseDictionary()
        {
            var session = GetCurrentSession();

            return session.Query<Model>()
                .Where(x => !x.IsDeleted)
                .Select(x => new
                                 {
                                     MakeId = x.Make.Id,
                                     MakeName = x.Make.Name,
                                     ModelId = x.Id,
                                     ModelName = x.Name
                                 })
                .AsEnumerable()
                .GroupBy(x => new {x.MakeId, x.MakeName})
                .Select(x => Tuple.Create(x.Key.MakeName,x.Key.MakeId,x.Select(y => y).ToDictionary(z => z.ModelName, z => z.ModelId, StringComparer.OrdinalIgnoreCase)));
        }

Hello world

This is the first post on my new blog, so HelloWorld().

Custom software development often involves managing client expectations.  As someone who works for a software development company, this is a task I’m reasonably familiar with, so feel it’s a good place to start.

My name is Padgett, and this might be my blog.

This is an experiment for me.  I’m going to start small and see if:

(a) What I write has any value to anyone else and

(b) If I can manage to build an effective blog without having to sink in an inordinate amount of time.

The time issue is a big one for me.  I like the idea of having a blog.  I like the idea of adding value and contributing to the local Perth .NET community.

But I’m just not sure how much of my free time I’m willing to spend doing so.

So this is me testing the water.  I’m going to blog as often as I feel able to.  I’m going to write mostly about software and information technology, but I might slip in a post or two about my other passions (namely anything with an engine) – I haven’t decided yet.  I’d love to get feedback.   I’ll certainly read any and all comments, but I may not always reply, so please don’t be offended if I don’t respond to yours .

I also occasionally tweet, though usually only in response to abuse from my co-workers.  You can find me on twitter.com/padgettrowell

My name is Padgett, and I’m an Apple addict

At least that’s what I think it is.  I mean, only Apple users are masochistic enough to buy the newest iWhatsIts every 12 months regardless of the nature of incremental updates, right?

Like most addicts, my relationship with my drug of choice is complicated.  Take my iPhone, on one hand it satisfies my need to communicate but in doing so I’m often left feeling somewhat frustrated.  I love my iPhone.  It has exactly the right number of hardware buttons in exactly the right places.  I don’t have to think about how to work the phone, I just use it and it does what I want it to do.  This wonderfully polished, absolute ease of use is dubbed by the Apple marketing guru’s as ‘magical’.  And I think it’s deserved.

Apple achieves this magical experience by enforcing consistency on developers, and therein lays my issue.  Apple publishes a set of design guidelines and rules that all app developers must follow should they wish access to the Apple garden, and your app purchasing dollars.  And I can see Apple’s point – to keep the magic, Apple have to control the environment.  And this is what I loathe.  Apple doesn’t just control the environment; they also control the distribution and the market.   This pisses me off to no end.  If I want my phone to whistle Dixie every time I receive an SMS, then I should have appropriate level of access to be able to do so.  If I want to replace mobile Safari with a browser that farts every time a page is loaded, that’s my prerogative.

Someone far funnier than I once said something about how the only thing faster than the speed of light is the speed at which a pizza slice can go from blister-your-mouth-hot to oil-soaked-box-luke-warm.

Well, he was wrong.  The only thing faster than light is the time it takes for an Apple product to go from chic to passé.  And that’s the exact unit of time it take’s Apple to announce its latest iWhatsIt.  In fact, it’s even less time than that because during the lead up to the we-never-actually-know-when-if-or-what-Apple is-going-to-announce, the rumour mill is already in over drive and our deliciously svelte Apple gear starts to lose some of its lustre as we anticipate what’s around the corner.

What sucks, what really gets my goat, ties it to a tree and throws *cough* apples at it is that knowing this, I still want, nae desperately desire a new iPad, knowing there’s a shiny newer one just around the corner.

About a year ago we did some development for a new client who wanted his application on the iPad.  So we purchased a couple of iPads, did a feasibility study and reported back to our client.  Lucky me got to do the study.  Luckier me got to keep an iPad.

Apart from masochistic (for all the about stated reasons) I’ve always considered Mac users to be people with little sense but really great hair.  I’ve never seen the point of a Mac.  Their hideously expensive and underpowered when compared to a PC at the same price point, and the component upgrade route is, well no one ever gets there because by the time an upgrade is required a new version is out and we all know what that means.

Then I started using my iPad.  It was just a little at first.  Some light browsing on the couch if I couldn’t be arsed to get up and go to the PC in my office.   Then I tried iBooks, ditching A. A. Milne for Neal Stephenson.  Before you know it I was over the embarrassment of using my iPad in public and getting my morning blog fix on the train ride into work.  Pretty soon I was using daily, hello Drop Box, hello ABC news, hello Instapaper.

Wifey and I drove from Melbourne to Perth.  An hour (ok, sometimes 1/2 an hour, Telstra’s national coverage is good, but there’s still great swaths of Australia where the Cooee is a far more reliable form of communication)  out of town we’d hit wotif and lastminute to find the best deal’s and reviews for accommodation.  On more than one occasion we had Google Maps navigate us to recently discovered accommodation as our (relatedly new) GPS didn’t have a Scooby.  Shit, now Wifey was using as well.

So December rolls round and I get an email from my boss, “Infrastructure have a big project coming up, please return your iPad because they need it.”  (I say *my* iPad, really it was works as they bought it and were paying the 3G bill, but hey – if you lick it it’s yours right?).

Meh, so what.  It’s just a big iPhone.  I mean, I’m not addicted or anything.

So it’s been just over a month, and I’m in hell.  I’ve looked at the Kindle (nice , but for someone who reads a lot the screen *blinking* at me every time I turn a page may as well carry a warning sticker ‘may induce homicidal tendencies’) and read every review of every tablet that was even hinted at during CES.  I tried a couple of different Android phones (it’s just a small iPad right?) and while I really, really wanted to like them they just didn’t spark anything in me.  There was no shizzle, no amazement, and no magic.

During this last month I’ve realise how much I relied on my iPad.  It got me through 3 exams (I achieved my MCPD Enterprise Application Developer having studying every day for two weeks on my train ride to work), it got us across Australia.  It’s what I’d pull out to show Mom our latest family photos on when we’d go visit.  It was part of my life, and I miss it.

Even knowing that any day a new version’s going to be announced, I still want one.  Knowing the price will drop as soon as the new version comes out, I still want one.  Knowing I’d probably buy the new version as well.  And it’s not just me, Wifey and I nearly went halves on one over the weekend.  I’m still not sure how we avoided that purchase.

I know I’ll cave, I know I’m weak.  It’s just a matter of when.  Please Lord, give me the strength hold out for the iPad 2.

I’ve been 33 days without a fix.  My name is Padgett, and I’m an Apple addict.

Wrapping up

Apart from the odd bit of maintenance, I’m no longer taking on new work or clients. Handing in my contractor / business owner / taking-Friday-off-to-ride-motorbikes hats to go work for ‘the man’ was a somewhat daunting prospect, but couldn’t have worked out better. I’m finding I quite enjoy the interaction with people who don’t want to sit on my keyboard and meow at me until they get fed.

Kiandra are far from ‘the man’, they are very good at what they do, professional, fun and thorough in everything they undertake. In house we have the expertise to cover a wide range of disciplines so I’m going to shamelessly take this opportunity to recommend that anyone who is looking for a development or infrastructure supplier give me a call at Kiandra. A complete list of services and contact details can be found on their website: kiandra.com.au.