Column Attribute in Entity Framework Core

Column Data Annotation Attribute in Entity Framework Core

In this article, I will discuss Column Data Annotation Attribute in Entity Framework Core (EF Core) with Examples. Please read our previous article, discussing the Table Data Annotation Attribute in Entity Framework Core with Examples.

Column Data Annotation Attribute in Entity Framework Core:

The Column Data Annotation Attribute can be applied to one or more properties of a domain entity to configure the corresponding database column name, column order, and column data type. That means it represents the database column that a property is mapped to.

In Entity Framework Core (EF Core), the [Column] attribute allows developers to provide additional configuration related to how an entity class property maps to a database column. With the [Column] attribute, you can specify things like the name of the database column, its order, and its data type.

The Column Attribute in EF Core overrides the default convention. As per the default conventions in Entity Framework Core, it creates the database table column with the same name and in the same order as the property names specified in the model class. Now, if you go to the definition of Column Attribute, then you will see the following.

Column Data Annotation Attribute in Entity Framework Core

As you can see in the above image, this class has two constructors. The 0-Argument constructor will create the database column with the same name as the property name. In contrast, the other overloaded constructor, which takes the string name as a parameter, will create the database table column with the name you passed to the constructor. Again, this Column attribute has three properties. The meaning of the properties is as follows:

  1. Name: The name of the column the property is mapped to. This is a read-only property.
  2. Order: The zero-based order of the column the property is mapped to. It returns the order of the column. This is a read-write property.
  3. TypeName: The database provider-specific data type of the column the property is mapped to. It returns the database provider-specific data type of the column the property is mapped to. This is a read-write property.

Note: The default value for the _order filed is -1 means EF Core will ignore the order.
Syntax to use Column Attribute: [Column (string name, Properties: [Order = int], [TypeName = string])]
Example to use Column Attribute: [Column(“DOB”, Order = 2, TypeName = “DateTime2”)]

Examples to understand Column Data Annotation Attribute in EF Core:

Let us understand the Column Data Annotation Attribute in Entity Framework Core with an example. Let us modify the Student Entity class as follows. As you can see, we have specified the Column Attribute with the FirstName and LastName properties. With the FirstName property, we have not provided the string name, i.e., using the 0-Argument constructor, so in this case, it will create the database table column with the same name as the Property name. With the LastName property, we have specified the name as LName, i.e., using the constructor, which takes one string parameter, so in this case, it will create the database table column with the name LName mapped to the LastName property. The Column attribute belongs to the System.ComponentModel.DataAnnotations.Schema namespace.

using System.ComponentModel.DataAnnotations.Schema;
namespace EFCoreCodeFirstDemo.Entities
{
    [Table("StudentInfo", Schema = "Admin")]
    public class Student
    {
        public int StudentId { get; set; }
        [Column]
        public string? FirstName { get; set; }
        [Column("LName")]
        public string? LastName { get; set; }
    }
}

As we already discussed, whenever we add or update domain classes or configurations, we need to sync the database with the model using add-migration and update-database commands using Package Manager Console or .NET Core CLI.

So, open the Package Manager Console and Execute the add-migration and update-database commands as follows. You can give any name to your migration. Here, I am giving Mig3. The name that you are giving it should not be given earlier.

Column Data Annotation Attribute in Entity Framework Core

With the above changes in place, run the application and verify the database, and you should see the following. As you can see, the FirstName is created as FirstName, but the LastName property is created as the LName column in the database, as expected.

Column Data Annotation Attribute in Entity Framework Core with Examples

Column Data Type in EF Core:

As we see, the Column Attribute class has a property called TypeName, and that TypeName property is used to get or set the data type of a database column. That means this property gets or sets the database provider-specific data type of the column the property is mapped to. For a better understanding, please modify the Student Entity as follows. Here, we have set the DateOfBirth column name as DOB and Data type as DateTime2 using TypeName Property.

using System.ComponentModel.DataAnnotations.Schema;
namespace EFCoreCodeFirstDemo.Entities
{
    [Table("StudentInfo", Schema = "Admin")]
    public class Student
    {
        public int StudentId { get; set; }
        [Column]
        public string? FirstName { get; set; }
        [Column("LName")]
        public string? LastName { get; set; }
        [Column("DOB", TypeName = "DateTime2")]
        public DateTime DateOfBirth { get; set; }
    }
}

So, again, open Package Manager Console and Execute the following add-migration and update-database commands as we modify the Student Entity.

Column Data Annotation Attribute in EF Core with Examples

If you verify the database, it should create a column with the name as DOB and the data type as DateTime2 instead of DateTime, as shown in the image below.

Column Data Annotation Attribute in EF Core with Examples

Column Order in Entity Framework Core:

Another property called Order is provided by the Column Attribute class, which is used to set or get the order of the columns. It is 0-based order, i.e., it will start from 0. As per the default convention, the Primary Key columns will come first, and then the rest of the columns based on the order we specified in the Column Attribute Order Property. The most important point you need to remember is that the Order Property of the Column Attribute must be applied to all the properties of an Entity with a different index, starting from zero. For a better understanding, please have a look at the following example.

using System.ComponentModel.DataAnnotations.Schema;
namespace EFCoreCodeFirstDemo.Entities
{
    [Table("StudentInfo", Schema = "Admin")]
    public class Student
    {
        //Primary Key: Order Must be 0
        [Column(Order = 0)]
        public int StudentId { get; set; }
        [Column(Order = 2)]
        public string? FirstName { get; set; }
        [Column("LName", Order = 4)]
        public string? LastName { get; set; }
        [Column("DOB", Order = 3, TypeName = "DateTime2")]
        public DateTime DateOfBirth { get; set; }
        [Column(Order = 1)]
        public string? Mobile { get; set; }
    }
}

So, again, open Package Manager Console and Execute the following add-migration and update-database commands as we modify the Student Entity.

Column Data Annotation Attribute in EF Core

Here, you need to observe one thing. We are getting one warning: Column orders are only used when the table is first created. In our example, we are not creating the tables but updating the table structure in the database.

Note: It will be ignored when we apply the Order Property of Column Attribute of an existing Entity.

So, to understand the Order Property of Column Attribute, let’s create a new table. So, modify the Student Entity Name from Student to CollegeStudent as follows.

using System.ComponentModel.DataAnnotations.Schema;
namespace EFCoreCodeFirstDemo.Entities
{
    [Table("CollegeStudentInfo", Schema = "Admin")]
    public class CollegeStudent
    {
        //Primary Key: Order Must be 0
        [Column(Order = 0)]
        public int CollegeStudentId { get; set; }
        [Column(Order = 2)]
        public string? FirstName { get; set; }
        [Column("LName", Order = 4)]
        public string? LastName { get; set; }
        [Column("DOB", Order = 3, TypeName = "DateTime2")]
        public DateTime DateOfBirth { get; set; }
        [Column(Order = 1)]
        public string? Mobile { get; set; }
    }
}

Next, modify the Context class as follows.

using Microsoft.EntityFrameworkCore;
namespace EFCoreCodeFirstDemo.Entities
{
    public class EFCoreDbContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV;Database=EFCoreDB;Trusted_Connection=True;TrustServerCertificate=True;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
        }

        public DbSet<CollegeStudent> CollegeStudents { get; set; }
    }
}

Note: Use CollegeStudent instead of Student in other Entities.

With the above changes in place, open Package Manager Console and Execute the following add-migration and update-database commands as we modify the Student Entity.

Column Data Annotation Attribute in EF Core

Now, verify the database table column’s order, and it should be in proper order per the Order parameter shown in the image below.

Column Attribute in EF Core

How to fetch the Column Attribute Details in EF Core?

If you remember, the Column Attribute class has three properties, i.e., Name, Order, and TypeName, and all these three properties have the get accessors. Let us understand how to use these get accessors to fetch the column details. Fetch all the column attribute details like Name, Order, and Data Type. To do so, modify the Main Method of the Program class as follows:

using EFCoreCodeFirstDemo.Entities;
using System.ComponentModel.DataAnnotations.Schema;
using System.Reflection;

namespace EFCoreCodeFirstDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //Fetch Attribute Details
                GetAttribute(typeof(CollegeStudent));
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }

        public static void GetAttribute(Type t)
        {
            PropertyInfo[] propertiesInfo = t.GetProperties();
            foreach (PropertyInfo propertyInfo in propertiesInfo)
            {
                // Get instance of the attribute.
                ColumnAttribute? columnAttribute = (ColumnAttribute)Attribute.GetCustomAttribute(propertyInfo, typeof(ColumnAttribute));
                if (columnAttribute == null)
                {
                    Console.WriteLine("The Attribute was not found.");
                }
                else
                {
                    // Get the Name, Order and DataType values.
                    Console.WriteLine($"Name: {columnAttribute.Name}, Order: {columnAttribute.Order}, Data Type: {columnAttribute.TypeName}");
                }
            }
        }
    }
}

When you run the above code, whatever Name, Order, and Data type you have specified using the Column Data Annotation Attribute, only those details will be printed on the Console window, as shown in the image below.

Column Attribute in Entity Framework Core

Now, let us fetch single-column information. We have set the Name, Order, and Data Type of the DateOfBirth property. Let us fetch that property-related database column information. To do so, modify the Main Method of the Program class as follows:

using EFCoreCodeFirstDemo.Entities;
using System.ComponentModel.DataAnnotations.Schema;
using System.Reflection;

namespace EFCoreCodeFirstDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //Fetch Attribute Details
                GetAttribute(typeof(CollegeStudent));
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }

        public static void GetAttribute(Type t)
        {
            //Fetch the DateOfBirth Property details
            PropertyInfo propertyInfo = t.GetProperty("DateOfBirth");
            // Get instance of the attribute.
            ColumnAttribute columnAttribute = (ColumnAttribute)Attribute.GetCustomAttribute(propertyInfo, typeof(ColumnAttribute));
            if (columnAttribute == null)
            {
                Console.WriteLine("The DateOfBirth Column Property was not found.");
            }
            else
            {
                // Get the Name value.
                Console.WriteLine($"Name of Column is: {columnAttribute.Name}");
                // Get the Order value.
                Console.WriteLine($"Order of Column is: {columnAttribute.Order}");
                // Get the DataType value.
                Console.WriteLine($"Data Type of Column is: {columnAttribute.TypeName}");
            }
        }
    }
}
Output:

Column Attribute in Entity Framework Core

When to use Column Attribute in EF Core?
  • When you want to map a property to a column with a different name.
  • When you need to specify a specific database column type.
  • When you want to control the order of columns when generating a new database schema from your entities.

While the [Column] attribute offers a convenient way to specify column-related configurations, remember that Entity Framework Core also provides a Fluent API with more detailed configuration capabilities. For very advanced scenarios, or when you want to keep configuration separate from your domain classes, the Fluent API can be a better choice.

In the next article, I will discuss the Key Data Annotation Attribute in Entity Framework Core with Examples. I explain Column Data Annotation Attribute in Entity Framework Core with Examples in this article. I hope you enjoyed this Column Attribute in the EF Core with Examples article. Please give your valuable feedback and suggestions about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *