SqliteLiteExtensions.cs

OneToManyAttribute.cs

 public class OneToManyAttribute : RelationAttribute
    {
        public string ForeignKey { get; }

        public OneToManyAttribute(string foreignKey)
        {
            this.ForeignKey = foreignKey;
        }

        public override void SetProperty(PropertyInfo info)
        {
            if (info.PropertyType.IsConstructedGenericType &&
                info.PropertyType.GetGenericTypeDefinition() == typeof(List<>))
            {
                this.PropertyInfo = info;
                this.PropertyType = info.PropertyType.GetGenericArguments()[0];
                this.Mapping = new TableMapping(this.PropertyType);
                return;
            }

            throw new ArgumentException("Can apply OneToManyAttribute only to List<> property");
        }
    }

RelationAttribute.cs

 public class RelationAttribute : IgnoreAttribute
    {
        public Type PropertyType { get; protected set; }
        public PropertyInfo PropertyInfo { get; protected set; }
        public TableMapping Mapping { get; protected set; }

        public virtual void SetProperty(PropertyInfo info)
        {
            PropertyInfo = info;
            PropertyType = info.PropertyType;
            Mapping = new TableMapping(info.PropertyType);
        }
    }

SqliteLietExensions.cs

using System;
using System.Collections;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using MvvmCross.Platform;
using SQLite;
using WB.Core.GenericSubdomains.Portable;

namespace WB.Core.SharedKernels.Enumerator.Utils.SqliteExtensions
{
    public static class SqliteExtensions
    {
        public static T FindWithFetch<T>(this SQLiteConnection conn, object pk) where T : new()
        {
            var entity = conn.Find<T>(pk);
            conn.Fetch(entity);
            return entity;
        }

        public static void InsertOrReplaceWithChild<T>(this SQLiteConnection conn, T entity)
        {
            conn.InsertOrReplace(entity);

            var map = GetMapping(typeof(T));
            var entityId = map.PK.GetValue(entity);
            foreach (var relation in GetRelations(typeof(T)))
            {
                if (relation is OneToManyAttribute oneToMany)
                {
                    conn.Execute($"delete from {oneToMany.Mapping.TableName} where {oneToMany.ForeignKey} = ?",
                        entityId);
                    var childs = oneToMany.PropertyInfo.GetValue(entity) as IEnumerable;
                    conn.InsertAll(childs);
                }
            }
        }

        public static void CreateTables<T>(this SQLiteConnection conn)
        {
            conn.CreateTable<T>();

            foreach (var relationType in GetRelations(typeof(T)).Select(t => t.PropertyType).Distinct())
            {
                conn.CreateTable(relationType);
            }
        }

        public static List<T> LoadAllWithFetch<T>(this SQLiteConnection conn) where T:new()
        {
            var entities = conn.Table<T>().ToList();

            var relations = GetRelations(typeof(T));
            var entityMapping = GetMapping(typeof(T));

            var entityMap = entities.ToDictionary(e => entityMapping.PK.GetValue(e));

            foreach (RelationAttribute relation in relations)
            {
                if (relation is OneToManyAttribute oneToMany)
                {
                    var childs = conn.Query(oneToMany.Mapping, "select * from " + oneToMany.Mapping.TableName);

                    var map = from child in childs
                        group child by oneToMany.Mapping.FindColumn(oneToMany.ForeignKey).GetValue(child)
                        into g
                        select new {g.Key, Values = g.ToList()};

                    foreach (var childMap in map)
                    {
                        FillList(entityMap[childMap.Key], oneToMany, childMap.Values);
                    }
                }
            }

            return entities;
        }

        private static void Fetch<T>(this SQLiteConnection conn, T entity)
        {
            var relations = GetRelations(typeof(T));
            var entityMapping = GetMapping(typeof(T));
            foreach (RelationAttribute relation in relations)
            {
                if (relation is OneToManyAttribute oneToMany)
                {
                    var childs = conn.Query(oneToMany.Mapping,
                        $@"Select * from {oneToMany.Mapping.TableName} where {oneToMany.ForeignKey} = ?",
                        entityMapping.PK.GetValue(entity));

                    FillList(entity, oneToMany, childs);
                }
            }
        }

        private static void FillList<T>(T entity, OneToManyAttribute oneToMany, IList<object> childs)
        {
            var listType = typeof(List<>);
            var constructedListType = listType.MakeGenericType(oneToMany.PropertyType);
            var instance = (IList) Activator.CreateInstance(constructedListType);
            childs.ForEach(c => instance.Add(c));
            oneToMany.PropertyInfo.SetValue(entity, instance);
        }

        private static readonly Func<Type, TableMapping> GetMapping =
            ThreadsafeMemoize<Type, TableMapping>(type => new TableMapping(type));

        private static readonly Func<Type, List<RelationAttribute>> GetRelations =
            ThreadsafeMemoize<Type, List<RelationAttribute>>(type =>
            {
                var result = new List<RelationAttribute>();

                foreach (var prop in type.GetProperties())
                {
                    var relation = prop.GetCustomAttribute<RelationAttribute>();
                    if (relation != null)
                    {
                        relation.SetProperty(prop);
                        result.Add(relation);
                    }
                }

                return result;
            });

        static Func<A, R> ThreadsafeMemoize<A, R>(this Func<A, R> f)
        {
            var cache = new ConcurrentDictionary<A, R>();
            return argument => cache.GetOrAdd(argument, f);
        }
    }
}