Translating Insert into Select to Entity Framework

Question

I have this sql query that i want to translate to entity Framework

i need to insert more than one row in one run in entity like what this query do

INSERT INTO dbo.tbllistDepartment
(
    department_sys_id,
    department,
    status,
    client_sys_id,
    lang_sys_id
)
SELECT @siDepartmentSysId,@siDepartment, @iiStatus, 
       @siClientSysId,  lang_sys_id
  FROM tblLanguageSettings 
  WHERE lang_sys_id <> 'Admin001' 

i have tried this but its inserting just one row

public static void Insert(string langSysId, string clientSysId,string name, int status)
    {
        Crs2Model context = new Crs2Model();
        string SysId = MyLibrary.StringCreator.GetSysId();

        tblListDepartment deptObj = new tblListDepartment()
        {
            department_sys_id = SysId,
            client_sys_id = clientSysId,
            lang_sys_id=langSysId,
            department=name,
            status=status
        };

        context.tblListDepartments.Add(deptObj);
        context.SaveChanges();
    }

Answer 1

The first, you need filter data from tbllistDepartment table. And then loop through this data. Here is a sample. You can refer, my friend:

using (DBContext ctx = new DBContext())
{     

  var department = ctx.tbllistDepartment.Where(c => c.lang_sys_id != "Admin001");   

    foreach(var item in department)
{
      var obj = new tbllistDepartment()
      {
         department_sys_id = item.department_sys_id,
         department = item.department,
         status = item.status,
         client_sys_id = item.client_sys_id,
         lang_sys_id = item.lang_sys_id
      }
      ctx.tbllistDepartment.Add(obj);
}   

  // Insert into the database.
  ctx.SaveChanges();                        
}

Answer 2

Add on your class:

using Microsoft.EntityFrameworkCore;

You can use the command

_context.Database.ExecuteSQLCommand (sqlcommand,[parameters]);

And these are the following commands:

string sqlCommand = "INSERT INTO dbo.tbllistDepartment (department_sys_id,department,status,client_sys_id,lang_sys_id) SELECT {0},{1},{2}, {3},{4} FROM tblLanguageSettings WHERE lang_sys_id <> {5}";    
_context.Database.ExecuteSqlCommand(sqlCommand, @siDepartmentSysId,@siDepartment,@iiStatus, @siClientSysId, lang_sys_id,'Admin001');