如何从 Entity Framework Core 中的 DbContext 获取列名和对应的数据库类型 [英] How to get Column name and corresponding Database Type from DbContext in Entity Framework Core

问题描述

假设我有这张表:

如何从 Entity Framework Core 的 DbContext 获取列名和数据库数据类型?

How can I get the column name and database datatype from DbContext in Entity Framework Core?

提示

1. 名为 clg# 的列被 EF Core Scaffold 工具转换为 clg1，所以我需要真实的列名而不是当前的 EF 名称

1. The column with name clg# converted to clg1 by EF Core Scaffold tool so I need real column name not current EF name

我需要数据库类型，而不是clrType，当然必须是跨平台的.也许我会更改数据库，因此该方法也必须有效.

I need database type, not clrType, of course the must be cross platform. Maybe I will change the database so the method must work too.

想要的结果:

    <D.clg#, int>
    <D.clgname, nvarchar(50)>
    <D.city, nvarchar(50)>
    <D.pname, nvarchar(50)>

谁能提供解决方案?

推荐答案

更新(EF Core 3.x): 从 EF Core 3.0 开始，元数据 API 再次发生变化 - Relational() 扩展被移除，属性被替换为 Get 和 Set 扩展方法，所以现在代码看起来像这样:

Update (EF Core 3.x): Starting with EF Core 3.0, the metadata API has changed again - Relational() extensions have been removed, and properties have been replaced with Get and Set extension methods, so now the code looks like this:

var entityType = dbContext.Model.FindEntityType(clrEntityType);

// Table info 
var tableName = entityType.GetTableName();
var tableSchema = entityType.GetSchema();

// Column info 
foreach (var property in entityType.GetProperties())
{
    var columnName = property.GetColumnName();
    var columnType = property.GetColumnType();
};

更新(EF Core 2.x):从 EF Core 2.0 开始，情况发生了变化，因此原来的答案不再适用.现在 EF Core 为每种数据库类型构建单独的模型，因此代码更简单，直接使用 Relational() 扩展:

Update (EF Core 2.x): Starting with EF Core 2.0, the things have changed, so the original answer does not apply anymore. Now EF Core builds separate model for each database type, so the code is much simpler and uses directly the Relational() extensions:

var entityType = dbContext.Model.FindEntityType(clrEntityType);

// Table info 
var tableName = entityType.Relational().TableName;
var tableSchema = entityType.Relational().Schema;

// Column info 
foreach (var property in entityType.GetProperties())
{
    var columnName = property.Relational().ColumnName;
    var columnType = property.Relational().ColumnType;
};

原始答案(EF Core 1.x):

与 EF 相比，在 EF Core 中获取对关联元数据的访问要容易得多 - 您可以从 DbContext.Model 属性开始获取 IModel，使用 GetEntityTypes 或 >FindEntityType 获取IEntityType，然后是 GetProperties 或 FindProperty 以获取 IProperty 等

Getting the access to the associated metadata is much easier in EF Core compared to EF - you start from DbContext.Model property to get IModel, use GetEntityTypes or FindEntityType to get IEntityType, then GetProperties or FindProperty to get IProperty etc.

然而，问题是 EF Core 允许您对不同的目标数据库使用不同的设置.为了获取上下文使用的当前数据库对应的属性，您需要访问 IRelationalDatabaseProviderServices 并使用 AnnotationProvider 和 TypeMapper 属性以获取所需信息.

However the problem is that EF Core allows you to use different setting fro different target database. In order to get the attributes corresponding to the current database used by the context, you need to get access to the IRelationalDatabaseProviderServices and use AnnotationProvider and TypeMapper properties to get the information needed.

这是一个例子:

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Storage;

public class DbColumnInfo
{
    public string Name;
    public string Type;
}

public static class RelationalDbHelpers
{
    public static IEnumerable<DbColumnInfo> GetDbColums(this DbContext dbContext, Type clrEntityType)
    {
        var dbServices = dbContext.GetService<IDbContextServices>();
        var relationalDbServices = dbServices.DatabaseProviderServices as IRelationalDatabaseProviderServices;
        var annotationProvider = relationalDbServices.AnnotationProvider;
        var typeMapper = relationalDbServices.TypeMapper;

        var entityType = dbContext.Model.FindEntityType(clrEntityType);

        // Not needed here, just an example 
        var tableMap = annotationProvider.For(entityType);
        var tableName = tableMap.TableName;
        var tableSchema = tableMap.Schema;

        return from property in entityType.GetProperties()
               let columnMap = annotationProvider.For(property)
               let columnTypeMap = typeMapper.FindMapping(property)
               select new DbColumnInfo
               {
                   Name = columnMap.ColumnName,
                   Type = columnTypeMap.StoreType
               };
    }
}

这篇关于如何从 Entity Framework Core 中的 DbContext 获取列名和对应的数据库类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！