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 .edmx
file 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
?
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.
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");
}
}