sql - Entity Framework Core 2.1 关系问题

问题描述

我正在尝试将以下 sql 查询转换为实体框架，但遇到了未加入表的列的问题。

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

这就是我到目前为止所拥有的

数据库上下文：

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;}
    }
}

列类：

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;}

    }
}

表类：

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>();}
    }
}

主要的：

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("");
                }
            }
        }
    }
}

代码运行良好，但是在检查表实例时，所有列实例都为空。我感觉它与表和列之间的关系有关，但是在查看 efcore2.1 关系的文档后，我无法弄清楚我做错了什么。

任何帮助，将不胜感激。

更新： 使用附加键和相关数据加载更新了代码。

标签： sqlef-core-2.1

sql - Entity Framework Core 2.1 关系问题

问题描述

解决方案

推荐阅读