Create View using EF Core-CodePudding

Following guidance in this

I'm not sure why this is because the create view statement seems to be correctly wrapped with begin and end statements:

CodePudding user response：

CREATE VIEW can’t be inside IF/BEGIN logic; it has to be the only statement in the batch. The typical workaround is to create a script like this:

IF OBJECT_ID(N'dbo.viewname', N'V') IS NULL
BEGIN
  EXEC sys.sp_executesql N'CREATE VIEW dbo.viewname AS SELECT 1;';
END
GO

ALTER VIEW dbo.viewname
AS
  ... real view code ...

In newer versions of SQL Server, you can just do:

CREATE OR ALTER VIEW dbo.viewname
AS
  ... real view code ...

But that still has to live in its own batch. Meaning it can't be inside IF/BEGIN unless you use dynamic SQL for the whole view. e.g.:

IF (some condition)
BEGIN
  EXEC sys.sp_executesql N'CREATE VIEW dbo.viewname
       AS
         ... all that view code ...;';
END

Whether you'll be able to generate any of those forms from EF core, I just don't know. Sometimes we expect an ORM to do absolutely everything but, usually, it is only capable of a very small subset.

I'm not sure of the logic you're trying to get to anyway. (If a row doesn't exist in some table, create a view? That will only work once.)