Answered here: https://stackoverflow.com/a/76690011/4905310

Summary:

If you plan to use idempotent scripts, you need to wrap every statement that must be the only one on the batch into an EXEC command. That includes creating SPs, triggers, views, among others.

So your code should look something like:

var sp = @"CREATE PROCEDURE [dbo].[MyStoredProcedureFromMigration]
    AS
    BEGIN
        SET NOCOUNT ON;
        SELECT * FROM Students WHERE FirstName LIKE '%dummy%'
    END";

migrationBuilder.Sql($"EXEC('{sp}')");

PS: code not tested for error. Double-check it before copy-pasting.

Reference doc from MS: https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/managing?tabs=dotnet-core-cli#arbitrary-changes-via-raw-sql