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

c# entity-framework entity-framework-6

Question

In my solution, I have an EF data project that is code generated and database first.

I must ascertain which fields in my models are defined asNOT NULL (that is, necessary) in the database. Value types (such as integers, floats, decimals, etc.) don't have a problem with this because the EF code generator defines them as nullable types if the DB permits null values.

But I'm having trouble figuring out how to tell if a field is either defined asNULL /NOT NULL when a reference type field is present in the database.

Although it sounds like a simple query, I can't find an answer on Google or anywhere else. How can I determine which fields on an EF model must be filled up and which are optional before a save action can be successful?

a different strategy

I decided to look into this matter a little more after revisiting it. First, after adding the entities using Visual Studio and completing the EF wizard, an.edmx file was produced. This file has been modified and added to numerous times since I last saw it.

I made the decision to check out some of the characteristics on some of my fields today by opening it in the designer. Indeed, a property appears on mynvarchar NOT NULL fields that are known asNullable .

How then can I use EF to obtain a list of entity attributes that include theNullable attribute set asfalse ?

1
1
6/22/2015 7:06:53 PM

Accepted Answer

ZZZ_tmp
5
6/26/2015 9:49:45 AM

Popular Answer

This might not be the ideal strategy, however I developed an extension mechanism for theDbContext class that accepts a data table's string name and then queries thesys tables to store this meta data. I specifically made the two classes and extension method listed below.


TableSchema

The high-level table class that is used to contain important schema information is as follows:

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

ColumnSchema

This is the class that will have all schema-related information for each column, similar to TableSchema.

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

Extension technique (GetDbTableSchema)

This approach increases theDbContext class. This allows getting the details of the underlying table from your instantiation context as easy as sending a name into a function.

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

It's incredibly easy to use. Pass the data table's name into your context assuming you already know it.

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow