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

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?

Normally you code each null check individually into your business logic with specific messages. If you really need to automate it, one option is to include the foreign key property as an `int` or `int?` alongside the navigation property. Other than that I imagine you'd have to parse the ObjectContext's metadata workspace. — jnm2, Jun 22 '15 at 11:48
For `strings`, you could [check](http://stackoverflow.com/questions/2051065/check-if-property-has-attribute) if the property has the `[Required]` attribute. — Knelis, Jun 22 '15 at 11:48
@Knelis If Required is specified via fluent model configuration, looking for a CLR attribute will return a false negative. You'd still have to parse the metadata workspace if you wanted to automate this instead of considering each property on a case-by-case basis. — jnm2, Jun 22 '15 at 11:51
The problem is I have an automated, back-process that uses EF. It's used for posting data daily. I need to check the not null specification and validate each file. Yes, I can manually define these fields, and do some form of look-up, but considering the extensive number of tables that I have, I'd like to query for all required fields and use that subset list to verify that each field contains data. I've not dug into the internals of EF, but I would hope/think it would do this validation before making any form of DB call. Are those properties that they use for validation not accessible? — RLH, Jun 22 '15 at 12:26
To be clear, the best case answer would provide a way to take a POCO object type, pass it to a method, and get a list of required fields, that are required in the db. Option two would be to iterate over each POCO field and request the state of the field in the db. — RLH, Jun 22 '15 at 12:31

score 5 · Accepted Answer · answered Jun 26 '15 at 09:49

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.

Using this approach, I tweaked my process above to return a list of strings, containing required columns, which is what I need. Wow, wish this had shown up before I hacked my own solution. ;) — RLH, Jun 26 '15 at 17:55

RLH · Answer 2 · 2015-06-25T18:23:13.480

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

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

2 Answers2