Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Ian Grainger 65 posts 127 karma points
    Aug 09, 2017 @ 10:51
    Ian Grainger
    1

    Fetching data from custom tables in Umbraco using PetaPoco relation extensions

    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));
            }
        }
    }
    
  • M N 125 posts 212 karma points
    May 18, 2020 @ 03:08
    M N
    0

    This is fantastic, all these years later. Thank you!

Please Sign in or register to post replies

Write your reply to:

Draft