Entity Framework Core 2.1 trouble with relationships

Question

I'm trying to convert the following sql query to entity framework but running into problems with what appears columns not joining to tables.

SELECT 
a.TABLE_NAME AS tableName,
b.COLUMN_NAME AS columnName,
b.DATA_TYPE AS dataType,
CASE WHEN b.IS_NULLABLE = 'NO' THEN 'FALSE' ELSE 'TRUE' END AS allowNull
FROM INFORMATION_SCHEMA.TABLES a
INNER JOIN INFORMATION_SCHEMA.COLUMNS b ON a.TABLE_NAME = b.TABLE_NAME

This is what i have so far

DB Context:

using Microsoft.EntityFrameworkCore;

namespace EFCoreTest.Models 
{
    public class InformationContext : DbContext
    {   
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) 
        {
            optionsBuilder.UseSqlServer(@"Server=localhost;Database=master;Trusted_Connection=True;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Table>()
                .HasKey(t => new {t.tableName, t.catalogName, t.schemaName});

            modelBuilder.Entity<Column>()
                .HasOne(c => c.table)
                .WithMany(c => c.columns)
                .HasForeignKey(c => new {c.tableName, c.catalogName, c.schemaName});

        }

        public DbSet<Table> Tables {get; set;}
        public DbSet<Column> Columns {get; set;}
    }
}

Column Class:

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace EFCoreTest.Models
{
    [Table("COLUMNS", Schema = "INFORMATION_SCHEMA")]
    public class Column
    {
        [Key]
        [Column("COLUMN_NAME")]
        public String columnName {get; set;}
        [Column("DATA_TYPE")]
        public String dataType {get; set;}
        [Column("IS_NULLABLE")]
        public String allowNUlls {get; set;}
        [ForeignKey("Table")]
        [Column("TABLE_NAME")]
        public String tableName {get; set;}
        [ForeignKey("Table")]
        [Column("TABLE_CATALOG")]
        public String catalogName {get; set;}
        [ForeignKey("Table")]
        [Column("TABLE_SCHEMA")]
        public String schemaName {get; set;}
        public Table table {get; set;}

    }
}

Table class:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace EFCoreTest.Models
{
    [Table("TABLES" , Schema = "INFORMATION_SCHEMA")]
    public class Table
    {
        [Key]
        [Column("TABLE_NAME")]
        public String tableName {get; set;}
        [Key]
        [Column("TABLE_CATALOG")]
        public String catalogName {get; set;}
        [Key]
        [Column("TABLE_SCHEMA")]
        public String schemaName {get; set;}
        public ICollection<Column> columns {get; set;}

        protected Table() {columns = new List<Column>();}
    }
}

Main:

using System;
using Microsoft.EntityFrameworkCore;
using System.Linq;
using EFCoreTest.Models;

namespace EFCoreTest
{
    class Program
    {
        static void Main(string[] args)
        {
            using(InformationContext context = new InformationContext())
            {
                var results = context.Tables.Include(t => t.columns).ToList();

                foreach(var t in results)
                {
                    Console.WriteLine(t.tableName);
                    Console.WriteLine("-----------------------------");
                    var columns = t.columns.ToList();

                    foreach(var c in columns)
                    {
                        Console.WriteLine(c.columnName);
                    }

                    Console.WriteLine("");
                }
            }
        }
    }
}

Code runs fine, but when inspecting the table instances all the column instances are null. I have a feeling it has something to do with the relationship between table and column but after going over the docs for relationships for efcore2.1 i cant work out what I'm doing wrong.

Any help would be appreciated.

Update: Updated code with additional keys and related data loading.

Answer 1

Try this:

context.Tables.Include(t => t.columns).ToList();

Answer 2

First of all, welcome to stack overflow.

As per the answer from Gonzalo, the Include statement will let you include a collection:

context.Tables.Include(t => t.columns).ToList();

I would like to however highlight a few other minor improvements you could do to make your code more robust and maintainable as the time goes.

1. Remember to initialize your entities with a protected constructor to avoid null pointer exceptions, as in most cases returning empty collections with an entity should be a valid scenario for business apps:

   protected Table() { columns = new List(); }

2. Use ICollection for collection definition instead of List.

3. The common naming standards in C# is using Pascal casing when declaring public properties and collections.

4. You are mixing 2 different ways of defining relationships.

This:

modelBuilder.Entity<Column>()
    .HasOne(c => c.table)
    .WithMany(c => c.columns)
    .HasForeignKey(c => c.tableForeignKey);

and the annotations that you use on the relevant properties of the entities like [Key] are actually two different ways to do the same thing. Use one, preferably the code first i.e through configurations.

5, I would suggest using separate entity type configuration file, otherwise your schema will eventually be very difficult to maintain, example base configuration:

public class BaseEntityConfiguration<TEntity> : IEntityTypeConfiguration<TEntity>
    where TEntity : BaseEntity
{
    public virtual void Configure(EntityTypeBuilder<TEntity> builder)
    {
        builder.HasKey(be => be.Guid);

        builder.Property(be => be.CreatedBy).IsRequired();

        builder.Property(be => be.CreatedDate).IsRequired();
    }
}

public class AddressConfiguration : BaseEntityConfiguration<Address>
{
    public override void Configure(EntityTypeBuilder<Address> builder)
    {
        builder.HasOne(a => a.Contact)
            .WithMany(c => c.Addresses)
            .HasForeignKey(a => a.ContactGuid);

        builder.HasOne(a => a.Partner)
            .WithMany(a => a.Addresses)
            .HasForeignKey(a => a.PartnerGuid);

        base.Configure(builder);
    }
}

and in the context:

modelBuilder.ApplyConfiguration(new AddressConfiguration());

As you probably noticed I also use BaseEntity to hold all the repeating properties like Id and just derive all my entities from it. I would suggest you do the same.

Hope that helps.