I have DB migration where FormTemplateId - foreign key and I need to set default value as Id of the latest record in [dbo].[FormTemplates]:

  migrationBuilder.AddColumn<int>(
                name: "FormTemplateId",
                table: "ContainerMasterTypes",
                type: "int",
                nullable: false,
                defaultValueSql: "SELECT TOP 1 [Id] FROM [dbo].[FormTemplates] ORDER BY [Id] DESC");

and have error: Subqueries are not allowed in this context. Only scalar expressions are allowed.'

What's wrong? How can I fix that error?

CodePudding user response：

Entity Framework only allows you to do what you can do already in SQL Server. For example, with the following table setup:

create table dbo.FormTemplates (
  Id int not null identity(1,1),
  Name nvarchar(50) not null
);
insert dbo.FormTemplates (Name) values (N'Foo'), (N'Bar'), (N'Baz');

create table dbo.ContainerMasterTypes (
  Id int not null identity(1,1),
  Name nvarchar(50) not null
);

Attempting to modify the ContainerMasterTypes table to add the new column with a subquery in the default constraint as you are doing would fail...

alter table dbo.ContainerMasterTypes
  add FormTemplateId int not null
    constraint DF_ContainerMasterTypes_FormTemplateId
      default (SELECT TOP 1 [Id] FROM [dbo].[FormTemplates] ORDER BY [Id] DESC);

... with the error message that you are seeing in .NET:

Msg 1046 Level 15 State 1 Line 3

Subqueries are not allowed in this context. Only scalar expressions are allowed.

To do this in SQL Server you would instead wrap the query in a Scalar User-Defined Function and reference that from the default constraint, i.e.:

create function dbo.LatestFormTemplateId()
returns int as
begin
  return (SELECT TOP 1 [Id] FROM [dbo].[FormTemplates] ORDER BY [Id] DESC)
end
go
alter table dbo.ContainerMasterTypes
  add FormTemplateId int not null
    constraint DF_ContainerMasterTypes_FormTemplateId
      default (dbo.LatestFormTemplateId());

Testing that with data...

insert dbo.ContainerMasterTypes (Name, FormTemplateId) values (N'Something', 1);
insert dbo.ContainerMasterTypes (Name) values (N'Else');

select * from dbo.FormTemplates;
select * from dbo.ContainerMasterTypes;

Would yield the results:

You need to do the same thing from Entity Framework as well, creating a Scalar UDF that wraps your query and then adding your new column with the default constraint referencing the UDF.