How to execute raw SQL query by using DBcontext

Execute raw SQL query by using DBcontext

executing raw SQL query by using command below

• DbSet.SqlQuery()
• DbContext.Database.SqlQuery()
• DbContext.Database.ExecuteSqlCommand()

DbSet.SqlQuery()

Use the DbSet.SqlQuery() method to write raw SQL queries which return entity instances. The resulted entities will be tracked by the context, as if they were returned by the LINQ query.

using (var ctx = new companyEntities())

{

    var studentList = ctx.employee

                        .SqlQuery("Select * from employee")

                        .ToList<employee>();

}

The above query executes select * from employee SQL in the database to get all students and will be converted into a list of employee entities. The column names in the SQL query must match with the properties of an entity type, otherwise, it will throw an exception.

You can specify the parameters using the object of SqlParameter, as shown below.

using (var ctx = new companyEntities())

{

    var student = ctx.employee

                    .SqlQuery("Select * from employee where employeeId=@id", new SqlParameter("@id", 1))

                    .FirstOrDefault();

}

If you change the column name in SQL query, then it will throw an exception because it must match column names. The following example will throw an exception.

using (var ctx = new companyEntities())

{               

    //this will throw an exception

    var employeeName = ctx.employee.SqlQuery("Select employeeid as id, employeename as name

            from employee where employeename='Souvik'").ToList();

}

The DbSet<TEntity>.SqlQuery() executes the SQL query only for the table which is mapped with the specified entity (e.g. DbSet<Student>.SqlQuery() only returns the result from the corresponding students table and not from any other table). The following will throw an exception.

using (var ctx = new companyEntities())

{               

    //this will throw an exception

    var employeeName = ctx.employee.SqlQuery("Select * from Courses").ToList();

}

Database.SqlQuery()

The Database class represents the underlying database and provides various methods to deal with the database. The methodDatabase.SqlQuery() returns a value of any type.

using (var ctx = new CompanyDBEntities())

{

    //Get employee name of string type

    string employeeName = ctx.Database.SqlQuery<string>("Select employeename from employee where employeeid=1")

                            .FirstOrDefault();

    //or

    string employeeName = ctx.Database.SqlQuery<string>("Select employeename from employee where employeeid=@id", new SqlParameter("@id", 1))

                            .FirstOrDefault();

}

Database.ExecuteSqlCommnad()

The Database.ExecuteSqlCommnad() method is useful in executing database commands, such as the Insert, Update and Delete command.

using (var ctx = new CompanyDBEntities())

{

    

using (var ctx = new companyEntities())

{

    int noOfRowUpdated = ctx.Database.ExecuteSqlCommand("Update employee

            set employeename ='changed student by command' where employeeid=1");

    int noOfRowInserted = ctx.Database.ExecuteSqlCommand("insert into student(employeename)

            values('New employee')");

    int noOfRowDeleted = ctx.Database.ExecuteSqlCommand("delete from employee

            where employeeid=1");

}