locked
How to load Navigation Properties in a generic method RRS feed

  • Question

  • User1434241939 posted

    I would like to read data from a DB context and eagerly load the navigation properties.  My code successfully does this:

    List<Peoplemvc> ap = await _context.People.Include(x => x.Links).ToListAsync();

    The model I'm using is this

        [Table("peoplemvc")]
        public partial class Peoplemvc
        {
            [Key]
            [Column(TypeName = "int(11)")]
            public int PersonNumber { get; set; }
            [Display(Name = "First Name")]
            public string FirstName { get; set; }
            [Column(TypeName = "varchar(255)")]
            public string LastName { get; set; }
            [Column(TypeName = "varchar(255)")]
    
            public ICollection<Linksmvccore> Links{ get; set; }
        }
    

    The navigation property references the following model

        [Table("linksmvccore")]
        public partial class Linksmvccore
        {
            [Key]
            [Column(TypeName = "int(11)")]
            public int LinkNumber { get; set; }
            [Column("BagID", TypeName = "int(11)")]
            public int? BagId { get; set; }
            [Column("PersonID", TypeName = "int(11)")]
            public int? PersonId { get; set; }
    
            public Bagsmvc Bag { get; set; }
            public Peoplemvc Person { get; set; }
        }
    

    I currently have a generic method that reads tables successfully

    public async Task<List<T1>> GetFromTable<T1>(DbSet<T1> TableToRead) where T1 : class
            {
                List<T1> results = new List<T1>();
                results = await TableToRead.ToListAsync() as List<T1>;
                return results;
             }

    The question is how do I get this method to include / populate navigation properties if they exist in my model?  I imagine I'd use reflection but not sure how to implement it.  I am fine passing along an argument to the method with the navigation property like this, but don't know how to do it.

    // I know this is not correct!
    public async Task<List<T1>> GetFromTable<T1, T2>(DbSet<T1> TableToRead, Icollection<T2> nav) 
    where T1 : class
    where T2 : class
    {
    List<T1> results = new List<T1>();
    results = await TableToRead.Include(x => x.nav).ToListAsync() as List<T1>;
    return results;
    }

    Any help appreciated

    Saturday, May 23, 2020 2:22 PM

All replies

  • User1434241939 posted

    I think I finally got it!  This seems to work, although I did have to pass in the Navigation Property to make it work.  I suppose I can still use reflection to check for any ICollection properties and then .Include() those, but that's still not completely generic.

    Here's what seems to work

    public async Task<List<T1>> GetFromTable<T1>(DbSet<T1> TableToRead, string navprop) 
       where T1 : class 
    {
     List<T1> results = new List<T1>(); 
    
                    PropertyInfo property = null;
                    foreach (var prop in typeof(T1).GetProperties())
                    {
                        if (prop.Name == navprop)
                        {
                            property = prop;
                            break;
                        }
                    }
    
    // This assumes a navigation property 'navprop' is found in the model
     results = await TableToRead.Include(property.Name).ToListAsync() as List<T1>; 
     return results; 
    }

    If you know of a better or more generic way to refactor this, let me know.

    Sunday, May 24, 2020 1:20 PM
  • User1120430333 posted
    What is the purpose of this? It seems like overkill.
    Sunday, May 24, 2020 2:39 PM
  • User1434241939 posted

    It definitely is overkill for what I'm doing, but I'm trying to learn .Net Core and this is the type of thing that would be useful for an enterprise-sized project.

    Imagine I have 100 tables to read, 20 of them have a navigation property to another table ... all of which I'd like to eagerly read.  I have refactored the code to look like this:

    PropertyInfo property = typeof(T1).GetProperties()
         .FirstOrDefault(x => x.PropertyType.Name.Contains("ICollection"));
    
    if (property == null)
       {
          results = await TableToRead.ToListAsync() as List<T1>;
       } else {
          results = await TableToRead.Include(property.Name).ToListAsync() as List<T1>;
       }
    

    This doesn't populate all collections if some tables have more than 1 foreign key, but none of mine do.

    Sunday, May 24, 2020 3:04 PM
  • User1120430333 posted
    And why can't you use EF Core for an existing database that would build the model, navigation properties and all from a database schema you created using a DBA tool like SSMS?
    Sunday, May 24, 2020 3:46 PM
  • User1434241939 posted

    I did use scaffolding to create the model from my MySQL database.  Maybe SSMS would generate the Navigation Properties, but I ended up doing that manually.

    I think what you're suggesting is that I could just call the database explicitly for each table using the context whenever I need the data.  However, I decided to implement a cache for all the tables.  This would add several lines of code that gets repeated for each table/cache.  So I implemented a generic caching routine to handle tables that may or may not have Navigation Properties.

            public async Task<List<T1>> GetFromTable<T1>(bool refresh, DbSet<T1> TableToRead) where T1 : class
            {
                string cacheKey = typeof(T1).Name;
                List<T1> results = new List<T1>();
    
                if (_cache.TryGetValue(cacheKey, out results) && !refresh)
                {
                    _logger.LogInformation($"Data pulled from cache: {cacheKey}");
                }
                else
                {
                    // First let's see if there are any foreign key navigation properties into another table (Links table)
                    // We do this by seeing if there's an ICollection property that references the "many" table
                    PropertyInfo property = typeof(T1).GetProperties()
                        .FirstOrDefault(x => x.PropertyType.Name.Contains("ICollection"));
    
                    // If no Navigation Property is found, just read the table.  Otherwise read the table AND the related table
                    if (property == null)
                    {
                        results = await TableToRead.ToListAsync() as List<T1>;
                    } else
                    {
                        results = await TableToRead.Include(property.Name).ToListAsync() as List<T1>;
                    }
    
                    // Configure the cache
                    var cacheEntryOptions = new MemoryCacheEntryOptions().SetSlidingExpiration(TimeSpan.FromHours(hours));
                    _cache.Set(cacheKey, results, cacheEntryOptions);
                    _logger.LogInformation($"Data pulled from the Database: {cacheKey}");
                }
    
                return results;
            }

    With this, a single call will populate any table once and then keep it cached whenever I need it, instead of making an expensive DB call every time.  This is how I invoke it

                var allpeople = await _cache.GetFromTable(refresh, _context.People);
    

    Sunday, May 24, 2020 9:31 PM
  • User1120430333 posted

    I did use scaffolding to create the model from my MySQL database. Maybe SSMS would generate the Navigation Properties, but I ended up doing that manually.

    MS SQL Server Management Stuido aka SSMS is of course is a MS SQL Server DBA tool. I don't know what you're using for MySQL as a DBA tool.


    I think what you're suggesting is that I could just call the database explicitly for each table using the context whenever I need the data.

    Yeah basically...

    Maybe, you should look into EF Core caching. 

    https://www.alachisoft.com/blogs/caching-in-entity-framework-ef-core/

    Sunday, May 24, 2020 10:01 PM
  • User1434241939 posted

    Maybe, you should look into EF Core caching. 

    https://www.alachisoft.com/blogs/caching-in-entity-framework-ef-core/

    That's a great idea!  The is the first time I have ever heard of it.  It has to be faster and more optimized than the code I wrote, especially when I Create, Update or Delete.

    Monday, May 25, 2020 10:11 AM