如何从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)>

任何人都可以提供解决方案吗？

Can anyone provide a solution ?

推荐答案

更新（EF Core 3.x）：从EF Core 3.0开始，元数据API再次更改-删除了 Relational（）扩展名，并将属性替换为 Get 和设置扩展方法，因此现在的代码如下所示：

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屋！