Hi. I'm storing some custom info in an Umbraco database for use by my Umbraco plugin. I can create and read simple data from a single table fine - but I struggled to find any way to read data from more than one table with a one-to-many relationship.

So for example, given a BudgetPeriod - which has many Expense objects linked to it - how can I populate a BudgetPeriods and BudgetPeriod.Expenses lists in a single DB call?

I've figured out how to do this using this nice little NuGet package: https://www.nuget.org/packages/PetaPoco.RelationExtensions/1.0.2 (info on how to use is in this blog post). But because Umbraco hides PetaPoco away inside Umbraco.Persistance I needed to change a couple of type names.

So now I can write: DB.FetchOneToMany<BudgetPeriod, Expense>(x => x.Id, "SELECT * FROM BUDGET_PERIOD LEFT JOIN EXPENSE ON BUDGET_PERIOD.Id = EXPENSE.BudgetPeriodId") and get my lists of expenses populated on each budget period.

My question is: Is there not already a way to do this using just Umbraco.Core(.Persistance) rather than having to write my own custom code - and if not, do you guys think it would be worth me putting my slightly rewritten PetaPoco.RelationExtensions on NuGet so that anyone else who needs a one-to-many or many-to-one relation in a custom table inside an Umbraco DB can access it?

Here's my rewritten version of PetaPoco.RelationExtensions 1.0.2 which I've currently only tested for one very particular one-to-many relationship (so provided very much without warranty and use at your own risk!)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using Umbraco.Core.Persistence;

namespace PetaPoco
{
    public static class PetaPocoRelationExtensions
    {
        public static List<T> FetchOneToMany<T, T1>(this UmbracoDatabase db, Func<T, object> key, Sql Sql)
        {
            var relator = new Relator();
            return db.Fetch<T, T1, T>((a, b) => relator.OneToMany(a, b, key), Sql);
        }

        public static List<T> FetchManyToOne<T, T1>(this UmbracoDatabase db, Func<T, object> key, Sql Sql)
        {
            var relator = new Relator();
            return db.Fetch<T, T1, T>((a, b) => relator.ManyToOne(a, b, key), Sql);
        }

        public static List<T> FetchManyToOne<T, T1, T2>(this UmbracoDatabase db, Func<T, object> key, Sql Sql)
        {
            var relator = new Relator();
            return db.Fetch<T, T1, T2, T>((a, b, c) => relator.ManyToOne(a, b, c, key), Sql);
        }

        public static List<T> FetchManyToOne<T, T1, T2, T3>(this UmbracoDatabase db, Func<T, object> key, Sql Sql)
        {
            var relator = new Relator();
            return db.Fetch<T, T1, T2, T3, T>((a, b, c, d) => relator.ManyToOne(a, b, c, d, key), Sql);
        }

        public static List<T> FetchOneToMany<T, T1>(this UmbracoDatabase db, Func<T, object> key, string sql, params object[] args)
        {
            return db.FetchOneToMany<T, T1>(key, new Sql(sql, args));
        }

        public static List<T> FetchManyToOne<T, T1>(this UmbracoDatabase db, Func<T, object> key, string sql, params object[] args)
        {
            return db.FetchManyToOne<T, T1>(key, new Sql(sql, args));
        }

        public static List<T> FetchManyToOne<T, T1, T2>(this UmbracoDatabase db, Func<T, object> key, string sql, params object[] args)
        {
            return db.FetchManyToOne<T, T1, T2>(key, new Sql(sql, args));
        }

        public static List<T> FetchManyToOne<T, T1, T2, T3>(this UmbracoDatabase db, Func<T, object> key, string sql, params object[] args)
        {
            return db.FetchManyToOne<T, T1, T2, T3>(key, new Sql(sql, args));
        }
    }

    public class Relator
    {
        private Dictionary<string, object> existingmanytoone = new Dictionary<string, object>();
        private List<string> properties = new List<string>();
        private PropertyInfo property1, property2, property3;

        public T ManyToOne<T, TSub1>(T main, TSub1 sub, Func<T, object> idFunc)
        {
            property1 = GetProperty<T, TSub1>(property1);
            sub = GetSub(main, sub, idFunc);
            property1.SetValue(main, sub, null);

            return main;
        }

        public T ManyToOne<T, TSub1, TSub2>(T main, TSub1 sub1, TSub2 sub2, Func<T, object> idFunc)
        {
            property1 = GetProperty<T, TSub1>(property1);
            property2 = GetProperty<T, TSub2>(property2);

            sub1 = GetSub(main, sub1, idFunc);
            sub2 = GetSub(main, sub2, idFunc);

            property1.SetValue(main, sub1, null);
            property2.SetValue(main, sub2, null);

            return main;
        }

        public T ManyToOne<T, TSub1, TSub2, TSub3>(T main, TSub1 sub1, TSub2 sub2, TSub3 sub3, Func<T, object> idFunc)
        {
            property1 = GetProperty<T, TSub1>(property1);
            property2 = GetProperty<T, TSub2>(property2);
            property3 = GetProperty<T, TSub3>(property3);

            sub1 = GetSub(main, sub1, idFunc);
            sub2 = GetSub(main, sub2, idFunc);
            sub3 = GetSub(main, sub3, idFunc);

            property1.SetValue(main, sub1, null);
            property2.SetValue(main, sub2, null);
            property3.SetValue(main, sub3, null);

            return main;
        }

        private PropertyInfo GetProperty<T, TSub>(PropertyInfo property)
        {
            if (property == null)
            {
                property = typeof (T).GetProperties()
                    .Where(x => typeof (TSub) == x.PropertyType && !properties.Contains(x.Name))
                    .FirstOrDefault();

                if (property == null)
                    ThrowPropertyNotFoundException<T, TSub>();

                properties.Add(property.Name);
            }

            return property;
        }

        private TSub GetSub<T, TSub>(T main, TSub sub, Func<T, object> idFunc)
        {
            object existing;
            if (existingmanytoone.TryGetValue(idFunc(main) + typeof (TSub).Name, out existing))
                sub = (TSub) existing;
            else
                existingmanytoone.Add(idFunc(main) + typeof(TSub).Name, sub);
            return sub;
        }

        private object onetomanycurrent;
        public T OneToMany<T, TSub>(T main, TSub sub, Func<T, object> idFunc)
        {
            if (main == null)
                return (T)onetomanycurrent;

            if (property1 == null)
            {
                property1 = typeof(T).GetProperties().Where(x => typeof(ICollection<TSub>).IsAssignableFrom(x.PropertyType)).FirstOrDefault();
                if (property1 == null)
                    ThrowPropertyNotFoundException<T, ICollection<TSub>>();
            }

            if (onetomanycurrent != null && idFunc((T)onetomanycurrent).Equals(idFunc(main)))
            {
                ((ICollection<TSub>)property1.GetValue((T)onetomanycurrent, null)).Add(sub);
                return default(T);
            }

            var prev = (T)onetomanycurrent;
            onetomanycurrent = main;

            property1.SetValue((T)onetomanycurrent, new List<TSub> { sub }, null);

            return prev;
        }

        private static void ThrowPropertyNotFoundException<T, TSub1>()
        {
            throw new Exception(string.Format("No Property of type {0} found on object of type: {1}", typeof(TSub1).Name, typeof(T).Name));
        }
    }
}