Ignore properties in data model while keeping them in EF Core migrations and Database Tabel

Question

I have properties in my Model like

public class Test{
        public int Id{ get; set; }
        public string Title { get; set; }
        public DateTime? CreatedDate { get; set; }
        public DateTime? ModifiedDate { get; set; }
        public DateTime? DeletedDate { get; set; }
}

Here, I am using this Test class for creating Table in Database using Migration, Now the table is created successfully but the problem is when i want do any operation using stored procedure which is like " Select Title from Test where Id=1" ,When i run the this i am facing error like this

"The required column 'CreatedDate' was not present in the results of a 'FromSql' operation"

I have used

1. NotMapped Attribute it works fine but when i add another migration the NotMapped properties gets Dropped from the database after updating the database

2. Also use Shadow properties and Ignore properties like

     protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Test>().Property<DateTime?>("CreatedDate");
        modelBuilder.Entity<Test>().Property<DateTime?>("ModifiedDate");
        modelBuilder.Entity<Test>().Property<DateTime?>("DeletedDate");
    }
   

Also try this,

protected override void OnModelCreating(ModelBuilder modelBuilder) {  
 modelBuilder.Entity<Test>().Ignore(x => x.DeletedDate);  
 modelBuilder.Entity<Test>().Ignore(x => x.IsDeleted);  
 modelBuilder.Entity<Test>().Ignore(x => x.ModifiedDate); }

But the issue remains the same ,

So the issue is i want to ignore the CreateDate, ModifiedDated, DeletedDated property while performing DB operation and also not want to drop these columns from Database when i add and update new migration.

Answer 1

"The required column 'CreatedDate' was not present in the results of a 'FromSql' operation"

The first thing you need to know is that the root problem of this error is not your CreatedDate field, but the type you return after executing FromSql.

When you execute FromSql, the return type is Test, and the Test type contains all fields(Id,Title,CreatedDate...), but your stored procedure only selects the Title field,therefore, the received type does not match, and this error occurs.

You can solve this problem from two methods.

The first method is to change the stored procedure to return data consistent with the Test type.

Select * from Test where Id=1

The other method changes from the perspective of receiving types.

You can customize the FromSql method to make the returned type dynamic.

 public static class CustomFromSqlTest
    {
        public static IEnumerable<dynamic> FromSql(this DbContext dbContext, string Sql, Dictionary<string, object> Parameters)
        {
            using (var cmd = dbContext.Database.GetDbConnection().CreateCommand())
            {
                cmd.CommandText = Sql;
                if (cmd.Connection.State != ConnectionState.Open)
                    cmd.Connection.Open();

                foreach (KeyValuePair<string, object> param in Parameters)
                {
                    DbParameter dbParameter = cmd.CreateParameter();
                    dbParameter.ParameterName = param.Key;
                    dbParameter.Value = param.Value;
                    cmd.Parameters.Add(dbParameter);
                }

                //var retObject = new List<dynamic>();
                using (var dataReader = cmd.ExecuteReader())
                {

                    while (dataReader.Read())
                    {
                        var dataRow = GetDataRow(dataReader);
                        yield return dataRow;

                    }
                }
            }
        }

        private static dynamic GetDataRow(DbDataReader dataReader)
        {
            var dataRow = new ExpandoObject() as IDictionary<string, object>;
            for (var fieldCount = 0; fieldCount < dataReader.FieldCount; fieldCount++)
                dataRow.Add(dataReader.GetName(fieldCount), dataReader[fieldCount]);
            return dataRow;
        }
    }

Use it：

  var result = _context.FromSql("spName @Id", new Dictionary<string, object> { { "@Id", 1 } }).ToList();