Home > Enterprise >  Entity Framework HasComputedColumnSql method returns an error
Entity Framework HasComputedColumnSql method returns an error

Time:04-16

I am trying to add computed column to my model by adding new readonly property to my entity and using HasComputedColumnSql method in the Context class. However this results in a SqlException with message

'Invalid column name 'Date2'

Entity class:

public partial class NetAssetsValue
{
     public string Key { get; set; }
     public string Date { get; set; }
     public DateTime? Date2 { get; } //readonly property for computed column
}

Context class:

public partial class EfficiencyContext : DbContext
{  
        public virtual DbSet<NetAssetsValue> Portfolio { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
                optionsBuilder.UseSqlServer("Server=someServer; Database=someDB; User Id=someID; Password=somePSD;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {    
            modelBuilder.Entity<NetAssetsValue>(entity =>
            {
                entity.HasNoKey();

                entity.ToTable("PortfFromSpecDep", "csv");

                entity.Property(e => e.Key)
                    .HasMaxLength(200)
                    .IsUnicode(false)
                    .HasColumnName("key");

                entity.Property(e => e.Date)
                    .HasMaxLength(200)
                    .IsUnicode(false)
                    .HasColumnName("date");
 
                // Error 'Invalid column name 'Date2' occurs here            
                entity.Property(e => e.Date2)
                    .HasComputedColumnSql("CONVERT(date, [date], 105)"); 
            }
        }
}

Code to retrieve data from database:

using EfficiencyContext db = new EfficiencyContext();

var query = db.Portfolio
              .Where(f => f.Date2 >= new DateTime(2021, 12, 30))
              .ToList();

foreach (NetAssetsValue nav in query)
{
    Console.WriteLine($"{nav.EntType} - {nav.FundName}");
}

Console.WriteLine(query.Count);
Console.ReadKey();

NotMappedAttribute and Ignore Method of Fluent Api also did not helped. I am using EF Core 5. I am stuck with this . I'll be grateful for any advice.

NOTE: unfortunately date column in database has string (varchar) datatype and I cannot change it. Any solutions with conversions from string to DateTime are not successful because date column has dd.MM.yyyy format. That's why I decided to use a computed column.

CodePudding user response:

Try following :

    public partial class NetAssetsValue
    {
        public string Key { get; set; }
        DateTime Date2 { get; set; } //readonly property for computed column
        public string Date { 
            get { return Date2.ToString("dd.MM.yyyy");}
            set {Date2 = DateTime.ParseExact(value, "dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture) ;} 
        }
    }
  • Related