How can I determine which fields are specified as NOT NULL within the database?

Question

How can I determine which fields are specified as NOT NULL within the database?

c# entity-framework entity-framework-6

Question

I have a database-first, code generated EF data project in my solution.

What I need to do is determine which fields within my models are defined as NOT NULL (i.e. required) in the database. This isn't difficult for value-types (ints, floats, decimals, etc.) because if the DB allows for nulls they are defined as nullable types by the EF code generator.

However, I can't figure out how to determine if a field is a either defined as NULL/NOT NULL in the database when the field is of a reference type.

This seems like an obvious question, but I can't find a solution by Google, or otherwise. When working with EF models, how can I query which fields are and aren't required to be populated on the model before a save operation can succeed?

A DIFFERENT APPROACH

After re-visiting this issue, I thought I'd do a little further research. First, the entities have been added through Visual Studio, and after stepping through the EF wizard, an .edmxfile was generated. I've come to this file many times before, and I've both updated and added tables to this file.

Today, I decided to open it up in the designer and check out some properties on some of my fields. Sure enough, there is a property on my nvarchar NOT NULL fields that is called Nullable.

So, how can I use EF and get a list of fields on an entity that have the Nullable property set as false?

RLH

Popular Answer

I'm not sure if this is the best approach but I created an extension method for the DbContext class that takes a string name of a data table and then queries the sys tables for this meta information. Specifically, I created the following 2 classes and extension method.

TableSchema

This is the high-level, table class used to store pertinent schema details:

public class TableSchema
{
    public string Database {  get; internal set; }
    public string TableName { get; internal set; }
    public List<ColumnSchema> Columns { get; internal set; }
}

ColumnSchema

Much like TableSchema, this is the class which will contain all schema related details for each column.

public class ColumnSchema
{
    public string ColumnName { get; internal set; }
    public int ColumnPosition { get; internal set; }
    public string Collation { get; internal set; }
    public string TypeName { get; internal set; }
    public short Size { get; internal set; }
    public byte Precision { get; internal set; }
    public byte Scale { get; internal set; }
    internal int _PK { get; set; }
    public bool IsIdentity { get; internal set; }
    public bool IsNullable { get; internal set; }

    public bool IsPrimaryKey
    {
        get { return _PK == 1; }
    }
}

The Extension method (GetDbTableSchema)

This method extends the DbContext class. This makes acquiring the underlying table details as simple as passing a name into a method, right off of your instantiated context.

public static class DbContextExtensions
{
    public static TableSchema GetDbTableSchema(this DbContext ctx, string tableName)
    {
        string qry = string.Format(
    @"SELECT * FROM (SELECT DISTINCT 
        c.name AS ColumnName, 
        c.column_id AS ColumnPosition,
        ty.name AS TypeName,
        c.max_length AS Size,
        c.precision AS Precision,
        c.scale AS Scale,
        CASE WHEN ic.column_id IS NOT NULL THEN 1 ELSE 0 END AS [_PK], 
        c.is_identity AS [IsIdentity],
        c.is_nullable AS [IsNullable]
    FROM sys.columns c 
        INNER JOIN sys.tables t ON c.object_id = t.object_id 
        INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
        LEFT OUTER JOIN sys.indexes i ON c.object_id = i.object_id AND i.is_primary_key = 1
            LEFT OUTER JOIN sys.index_columns ic ON i.object_id = ic.object_id 
                AND i.index_id = ic.index_id 
                AND c.column_id = ic.column_id
    WHERE t.name = '{0}') t
    ORDER BY _PK DESC, ColumnPosition", tableName);", tableName);
        return new TableSchema
        {
            Columns = ctx.Database.SqlQuery<ColumnSchema>(qry).ToList(),
            Database = ctx.Database.Connection.Database,
            TableName = tableName
        };
    }
}

Usage is very simple. Assuming you have the name of the data table, pass it into your context.

using (var ctx = new MyEntityContext()
{
    TableSchema ts = ctx.GetDbTableSchema("MyTable");

    foreach (ColumnSchema cs in ts.Columns)
    {
        Debug.WriteLine("Column: {0}, {1}", cs.ColumnName, cs.IsNullable ? "NULL" : "NOT NULL");
    }
}

RLH

View more on Stack Overflow

Gert Arnold · Accepted Answer · 6/26/2015 9:49:45 AM

You can get this information from Entity Framework's the meta data:

var metadata = ((IObjectContextAdapter)db).ObjectContext.MetadataWorkspace;
var tables = metadata.GetItemCollection(DataSpace.SSpace)
                     .GetItems<EntityContainer>().Single()
                     .BaseEntitySets
                     .OfType<EntitySet>()
                     .Where(s => !s.MetadataProperties.Contains("Type") || s.MetadataProperties["Type"].ToString() == "Tables");

foreach (var table in tables)
{
    Console.WriteLine(string.Format("{0}.{1}", table.Schema, table.Name));
    foreach (var member in table.ElementType.Members)
    {
        var column = string.Format("    {0}, Nullable: {1}",
            member.Name,
            ((TypeUsage)member.MetadataProperties["TypeUsage"].Value).Facets["Nullable"].Value);
        Console.WriteLine(column);
    }
}

(where db is a DbContext)

This will give you an output like:

dbo.Category
    CategoryId, Nullable: False
    CategoryName, Nullable: False
    Description, Nullable: True
    RowVersion, Nullable: False
dbo.Product
    ProductId, Nullable: False
    ProductName, Nullable: False
    QuantityPerUnit, Nullable: False
    UnitPrice, Nullable: True
    StartDate, Nullable: False
    RowVersion, Nullable: False
    Image, Nullable: True
dbo.CategoryProduct
    CategoryID, Nullable: False
    ProductID, Nullable: False

I borrowed the first part from Rowan Miller.

How can I determine which fields are specified as NOT NULL within the database?

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related