Possible to dynamically join a related model in Entity Framework Core?

user_7228860 0
Viewed 202

Say there's a project that involves a lot of tables, all of which reference a single table that holds a field called group ID. However, not all of the tables reference this single table directly.

For example, the db contains a garages table, and each garage is referenced by rows in a cars table, and each car is referenced by rows in a tire table. I want to organize the data into two groups, group1 and group2. Each garage has a group ID, however the cars and tires do not. Here is a diagram of the tables: Diagram of my example database

As you can see, assets in group 1 are highlighted red, and assets in group 2 are highlighted yellow. But not all rows highlighted necessarily have a group ID. You can imagine how this may go even longer, with hub caps having forgien keys for their respective tires, bolts with forgien keys to their respective hub caps, etc.

Is there a way to dynamically call an EF query that can get the group ID from the related garage, given EITHER a child car, tire, hub cap, and so on? Implementation might be something like this:

var tire = Context.Tires.where(t => t.ID == 3).FirstOrDefault<Tire>();
tire.findShortestJoinToEntity(entity => entity.GetProperty("GroupID") != null);
// Returns 2, the group of "Toyota Tires"

In more technical terms, it would need to recursively check for any forgien keys in the passed in model, then all the forgein keys of the referenced models, all their referenced models, etc. until there are either no more forgein keys or a field with the passed in name is found.

entity-framework-core asp.net-core sql
1 Answers

I think you can do it using reflection, it is not the ideal solution due to possible performance issues, but it does what you are looking for.

The key idea here is having all entity classes implementing a common interface IEntity so we can call a recursive method over any entity. Also assumes that we have both parentId (foreign key) and parent object in any entity, although you can tweak the method to use only the parentId, I have put the parent object only to take advantage of EF tracking mechanism linking parents and child entities

    public interface IEntity
    {
        int Id { get; set; }
    }

    public class Group : IEntity
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class Garage : IEntity
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public virtual int GroupId { get; set; }
        public virtual Group Group { get; set; }
    }

    public class Car : IEntity
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public virtual int GarageId { get; set; }
        public virtual Garage Garage { get; set; }
    }

    // ... other entity classes here


    public static class EntityExtensions
    {
        public static int FindGroupId(this IEntity entity, Context context, string propertyName = null)
        {
            var groupId = 0;

            if (entity != null)
            {
                var entityType = entity.GetType();

                if (entity is Group)
                {
                    // we found it
                    groupId = propertyName == null
                        ? entity.Id
                        : (int)entityType.GetProperty(propertyName).GetValue(entity);
                }
                else
                {
                    // look into properties for parent entities
                    foreach (var property in entityType.GetProperties())
                    {
                        if (property.GetGetMethod().IsVirtual
                            && property.PropertyType.GetInterface(nameof(IEntity)) != null)
                        {
                            // it is parent entity
                            var parentEntity = property.GetValue(entity) as IEntity;

                            if (parentEntity == null)
                            {
                                // if parent entity is null, let's get it from db
                                var parentIdName = $"{property.PropertyType.Name}Id";
                                var parentId = (int)entityType.GetProperty(parentIdName).GetValue(entity);
                                parentEntity = context.Find(property.PropertyType, parentId) as IEntity;
                            }

                            groupId = FindGroupId(parentEntity, context, propertyName);
                        }

                        if (groupId > 0)
                        {
                            // we found it
                            break;
                        }
                    }
                }
            }

            return groupId;
        }
    }
user_63515810
Related