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;
        }
    }
Advertisements

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.