Stored procedure with EF6 Code first - best practice?

In my program I have to import data from a remote sqlserver database.
I am using ASP.NET MVC 5 and EF6 Code First (I'm new with EF and MVC 5).

First, I copy data from a remote view to a table in the local database.
For this part I use this code in the action method (names are in italian):

using (var source = new TimeWebDBContext())
{
 using (var target = new GESTPrefContext())
  {
   // 1 - Truncate table AnagraficaTimeWeb is exists
   target.Database.ExecuteSqlCommand("IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='AnagraficaTimeWeb') TRUNCATE TABLE AnagraficaTimeWeb");
    
   // 2 - Copy from remote view  to  AnagraficaTimeWeb table
   var dati_importati = from i in source.VW_PREFNO_ANAGRAFICORUOLO
        select new AnagraficaTimeWeb()
        {
         Matricola = i.MATRICOLA,
         Cognome = i.COGNOME,
         Nome = i.NOME,
         Sesso = i.SESSO,
         Email = i.EMAIL,
         IdRuolo = i.IDRUOLO,
         Ruolo = i.RUOLO,
         DataInizio = i.DATAINIZIO,
         DataFine = i.DATAFINE,
         DataFineRapporto = i.DATALICENZ,
         DataUltimaImportazione = DateTime.Now
        };
        target.DatiAnagraficaTimeWeb.AddRange(dati_importati.ToList());
        target.SaveChanges();
   }
}

The view returns a list of employees with their role.
Roles have to be imported in a distinct local table called PROFILO, while employees data are saved in the IMPIEGATO table.

The remaining part of the import process consists of :
a) insert new data in the PROFILO table (data already saved are ignored)
b) update of employee data already present in the local IMPIEGATO table (name, email,etc. are overwritten)
c) insert new empoyee not yet in the IMPIEGATO table.

since I’m new to EF6 I thought I’d use SQL code .
In my opinion the possible solutions are :

1. execute SQL code directly in the actionmethod with db.Database.ExecuteSqlCommand
   This is the code i write:

code for point a)

StringBuilder sql = new StringBuilder();
sql.AppendLine("INSERT INTO PROFILO (IdTimeWeb, Descrizione, Ordinamento, Stato, Datainserimento)");
sql.AppendLine(" SELECT DISTINCT IdRuolo, Ruolo, 1," + ((int)EnumStato.Abilitato) + ",'" + DateTime.Now.ToShortDateString()+"'");
sql.AppendLine(" FROM AnagraficaTimeWeb i");
sql.AppendLine(" WHERE NOT EXISTS");
sql.AppendLine("(SELECT 1 FROM PROFILO p WHERE p.Descrizione = i.Ruolo)");
target.Database.ExecuteSqlCommand(sql.ToString());

code for point b)

sql.Clear();
sql.Append("UPDATE i " + Environment.NewLine);
sql.Append(" SET i.Cognome = a.Cognome" + Environment.NewLine);
sql.Append(" , i.Nome = a.Nome" + Environment.NewLine);
sql.Append(" , i.Sesso = a.Sesso" + Environment.NewLine);
sql.Append(" ,i.Email = a.Email" + Environment.NewLine);
sql.Append(" ,i.DataModifica = '" + DateTime.Now.ToShortDateString() +"'"+ Environment.NewLine);
sql.Append(" FROM Impiegato i " + Environment.NewLine);
sql.Append(" JOIN AnagraficaTimeWeb a on i.Matricola=a.Matricola " + Environment.NewLine);
sql.Append(" WHERE i.Stato =" + ((int)EnumStato.Abilitato)  + Environment.NewLine);
target.Database.ExecuteSqlCommand(sql.ToString());

code for point c)

sql.Clear();
sql.Append("INSERT INTO IMPIEGATO(Cognome, Nome, Matricola, Sesso, Email, Stato, DataInserimento) " + Environment.NewLine);
sql.Append("SELECT a.Cognome" + Environment.NewLine);
sql.Append(", a.Nome" + Environment.NewLine);
sql.Append(", a.Matricola" + Environment.NewLine);
sql.Append(", a.Sesso" + Environment.NewLine);
sql.Append(", a.Email" + Environment.NewLine);
sql.Append("," + ((int)EnumStato.Abilitato )+ Environment.NewLine);
sql.Append(",'"+ DateTime.Now.ToShortDateString() +"'" + Environment.NewLine);
sql.Append(" FROM AnagraficaTimeWeb a " + Environment.NewLine);
sql.Append(" LEFT OUTER JOIN IMPIEGATO on a.Matricola = Impiegato.Matricola " + Environment.NewLine);
sql.Append("  WHERE Impiegato.Matricola is null" + Environment.NewLine);

target.Database.ExecuteSqlCommand(sql.ToString());		

2. create a storedprocedure to call in the actionmetghod. in this case how to create the storedprocedure?

2.a) in the Up method of a migration ?

2.b) or running the storedprocedure creation script directly in the database (after it was first created ) and then run the stored from the action method?

You can create stored procedure directly in db and write all three remaining part of your process (point a,b,c) in one SP (stored procedure). SP is stored as object file in sql so it's fast and sql server don't spend time in making execution plan and other extra things.

In order to create sp, you need to more familiar with sql statements and it's control structures etc. It will feel like learning new language. You can start with below link

https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/stored-procedures-database-engine?view=sql-server-ver16

To save that stored procedure as migration script you can create a blank migration using

add-migration 'SPName' -IgnoreChanges


Then add your sp

public partial class SPName: DbMigration
{
    public override void Up()
    {
        Sql(@"Create Stored Procedure script");

    }

    public override void Down()
    {
        Sql(@"Drop Stored Procedure script")
    }
} 

don't forget to change migration script every time you modify/alter sp. You can also make migration script of modified sp.

To execute your sp you can refer the below code snippet. SqlPatameter helps you to make query clean,you can avoid sql injection.

List<SqlParameter> sqlParms = new List<SqlParameter>
  {
    new SqlParameter { ParameterName = "@Id", Value = employee.EmployeeID },
    new SqlParameter { ParameterName = "@FirstName ", Value = employee.FirstName },
    new SqlParameter { ParameterName = "@LastName", Value = employee.LastName}
};
db.Database.ExecuteSqlRaw("EXEC dbo.spName @Id, @FirstName, @LastName" sqlParms.ToArray());

In EF with Code First Migrations, if this is one-time code that is only needed as part of the upgrade or initial database migration process and will not be needed again then you should constrain this logic to the Up method of the associated migration.

For once off or infrequent execution, defining and executing a stored procedure should result in a very similar amount of effort, but now you have introduced an artifact that in the database, the stored procedure, that will confound future DBAs if it is never executed. There is little value in packaging it in this way.

• If you did use a stored procedure, then the create script for the SP should also be executed via the Up method, so if the SQL is going to be defined in there anyway in one form or another, In most circumstances we do not get any additional benefit from encapsulating this logic in an SP.

If you are going to script upgrade logic like this in the migration (and I heartily encourage you to do so) then you should also considering implementation of the reverse logic in the Down method. Or simply put a Truncate statement in the Down.

public override void Up()
{
    // ...Generated Migration Operations...

    // Manual Migration Logic:
    Sql($@"
    INSERT INTO PROFILO (IdTimeWeb, Descrizione, Ordinamento, Stato, Datainserimento)
    SELECT DISTINCT IdRuolo, Ruolo, 1,{(int)EnumStato.Abilitato},'{DateTime.Now.ToShortDateString()}'
    FROM AnagraficaTimeWeb i
    WHERE NOT EXISTS 
    (SELECT 1 FROM PROFILO p WHERE p.Descrizione = i.Ruolo);
    ");

    Sql($@"
    UPDATE i 
     SET i.Cognome = a.Cognome
     , i.Nome = a.Nome
     , i.Sesso = a.Sesso
     ,i.Email = a.Email
     ,i.DataModifica = '{DateTime.Now.ToShortDateString()}'
     FROM Impiegato i 
     JOIN AnagraficaTimeWeb a on i.Matricola=a.Matricola 
     WHERE i.Stato ={(int)EnumStato.Abilitato})
     ");

    Sql($@"
    INSERT INTO IMPIEGATO(Cognome, Nome, Matricola, Sesso, Email, Stato, DataInserimento)
    SELECT a.Cognome
    , a.Nome
    , a.Matricola
    , a.Sesso
    , a.Email
    , {(int)EnumStato.Abilitato}
    , '{DateTime.Now.ToShortDateString()}'
     FROM AnagraficaTimeWeb a 
     LEFT OUTER JOIN IMPIEGATO on a.Matricola = Impiegato.Matricola
      WHERE Impiegato.Matricola is null
     ");
}

public override void Down()
{
    // TODO: Downgrade logic
}

If the script needs to be executed many times in the lifetime of the app, so it is not associated with a specific data schema version, then a stored procedure starts to make more sense but the same simple script could be encapsulated as a C# coded utility. If you are choosing to manage the schema through code then this makes sense too.