How to get list of database table primary key columns in EF Core
Asked Answered
G

1

6

ASP.NET Core MVC application using EF Core. In Linq to SQL, this code returns list of database table primary key columns names:

    /// <summary>
    /// Database primary key names
    /// </summary>
    public IList<string> DatabasePrimaryKey(Type dbContextPocoType)
    {
        List<string> pk = new List<string>();

        foreach (PropertyInfo p in dbContextPocoType.GetProperties())
        {
            var ca = p.GetCustomAttributes(typeof(ColumnAttribute), true);

            if (ca.Length == 0) continue;
            var atr = (ColumnAttribute)ca.Single();

            if (!atr.IsPrimaryKey) continue;
            pk.Add(atr.Name);
        }

        return pk;
    }

In EF Core I tried

var entry = ctx.Entry(dbContextPocoType);
var primaryKey = entry.Metadata.FindPrimaryKey();

IList<string>  keys = primaryKey.Properties.Select(x => x.Name).ToList();
return keys;

But this returns C# property names - how to get database table column names in EF Core?

Update: using answer I created this method:

    public IList<string> DatabasePrimaryKey<TPoco>()
    {
        var entry = ctx.Entry(typeof(TPoco));
        var primaryKey = entry.Metadata.FindPrimaryKey();
        var entityType = ctx.Model.FindEntityType(typeof(TPoco).Name);
        var schema = entityType.GetSchema();
        var tableName = entityType.GetTableName();
        IList<string> keys = primaryKey.Properties
             .Select(x => x.GetColumnName(StoreObjectIdentifier.Table(tableName, schema)))
             .ToList();
        return keys;
    }

Can this method be improved?

Gnome answered 21/1, 2021 at 23:18 Comment(0)
T
13

In EF Core 3.x You could try the IProperty extension method GetColumnName instead.

var entry = ctx.Entry(dbContextPocoType);
var primaryKey = entry.Metadata.FindPrimaryKey();
var primaryKeyColumns = primaryKey.Properties
                     .Select(property => property.GetColumnName())
                     .ToList()

return primaryKeyColumns;

For EF Core 5, you need to use the overload that accepts a StoreObjectIdentifier: GetColumnName(IProperty, StoreObjectIdentifier).

Update for EF 5:

public IList<string> DatabasePrimaryKey<TPoco>()
{
    var entityType = ctx.Model.FindEntityType(typeof(TPoco));
    var primaryKey = entityType.FindPrimaryKey();
    var schema = entityType.GetSchema();
    var tableName = entityType.GetTableName();
    var storeObjectIdentifier = StoreObjectIdentifier.Table(tableName, schema);
    IList<string> primaryKeyColumns = primaryKey.Properties
        .Select(x => x.GetColumnName(storeObjectIdentifier))
        .ToList();
    return primaryKeyColumns;
}
Tinny answered 21/1, 2021 at 23:43 Comment(3)
Thank you. I updated answer with method which I created. Can this imporved ?Gnome
@Gnome you are welcome, gald to help. I have updated my answer too with a small change suggestion. I haven't tested it myself, but I believe you don't need the entry, since you can find the primaryKey from the entityType. Ref.: learn.microsoft.com/en-us/dotnet/api/…Tinny
once in a long while I come to SO and find EXACTLY the answer I needTragedian

© 2022 - 2024 — McMap. All rights reserved.