'Select last n chars from a string in EF Core using HasComputedColumnSql() Method
I am using EF Core 5. Given is the following class
public class Account
{
public int Id { get; set; }
public string Iban { get; set; }
public string AccountNumber { get; set; }
}
The default value of AccountNumber is equal to last ten digits of iban. Based on Microsoft documentation I tried to calculate its default value by using the HasComputedColumnSql() method like this:
modelBuilder.Entity<Account>()
.Property(x => x.AccountNumber)
.HasComputedColumnSql("RIGHT([Iban], 10)");
but I do get the following error when updating the database: "Incorrect syntax near 'IBAN, 10'." What is the correct way to use the SQL RIGHT() method in HasComputedColumnSql() method?
UPDATE: EF Core LogFile:
Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DECLARE @var0 sysname;
SELECT @var0 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Account]') AND [c].[name] = N'AccountNumber');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Account] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [Account] DROP COLUMN [AccountNumber];
ALTER TABLE [Account] ADD [AccountNumber] AS RIGHT[IBAN, 10];
Update: My latest migration is the following:
public partial class V10 : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.AlterColumn<string>(
name: "AccountNumber",
table: "Account",
type: "nvarchar(max)",
nullable: true,
defaultValue: "IBAN",
oldClrType: typeof(string),
oldType: "nvarchar(max)",
oldNullable: true,
oldComputedColumnSql: "RIGHT(Iban,10)");
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.AlterColumn<string>(
name: "AccountNumber",
table: "Account",
type: "nvarchar(max)",
nullable: true,
computedColumnSql: "RIGHT(Iban,10)",
oldClrType: typeof(string),
oldType: "nvarchar(max)",
oldNullable: true,
oldDefaultValue: "IBAN");
}
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
