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