• Entity Framework Tutorial Basics(39):Raw SQL Query


    Execute Native SQL Query

    You can execute native raw SQL query against the database using DBContext. You can execute the following types of queries:

    1. SQL query for entity types which returns particular types of entities
    2. SQL query for non-entity types which returns a primitive data type
    3. Raw SQL commands to the database

    SQL query for entity types:

    As we have seen in one of the previous chapters, DBSet has SQLQuery() method to write raw SQL queries which return entity instances. The returned objects will be tracked by the context, just as they would be if they were returned by a LINQ query. For example:

    using (var ctx = new SchoolDBEntities())
    {
        var studentList = ctx.Students.SqlQuery("Select * from Student").ToList<Student>();
      
    }

    However, columns returned by SQL query should match the property of an entity type of DBSet otherwise, it will throw an exception. For example:

    using (var ctx = new  SchoolDBEntities())
    {                
        var studentName = ctx.Students.SqlQuery("Select studentid, studentname 
            from Student where studentname='New Student1'").ToList();
    
    }

    If you change the column name in query, then it will throw an exception because it must match column names:

    using (var ctx = new SchoolDBEntities())
    {                
        //this will throw an exception
        var studentName = ctx.Students.SqlQuery("Select studentid as id, studentname as name 
                from Student where studentname='New Student1'").ToList();
    }

    SQL query for non-entity types:

    A SQL query returning instances of any type, including primitive types, can be created using the SqlQuery method on the Database class. For example:

    using (var ctx = new SchoolDBEntities())
    {
        //Get student name of string type
        string studentName = ctx.Database.SqlQuery<string>("Select studentname 
            from Student where studentid=1").FirstOrDefault<string>();
    }

    Raw SQL commands to the database:

    ExecuteSqlCommnad method is useful in sending non-query commands to the database, such as the Insert, Update or Delete command. For example:

    using (var ctx = new SchoolDBEntities())
    {
    
        //Update command
        int noOfRowUpdated = ctx.Database.ExecuteSqlCommand("Update student 
                set studentname ='changed student by command' where studentid=1");
        //Insert command
        int noOfRowInserted = ctx.Database.ExecuteSqlCommand("insert into student(studentname) 
                values('New Student')");
        //Delete command
        int noOfRowDeleted = ctx.Database.ExecuteSqlCommand("delete from student 
                where studentid=1");
    
    }
  • 相关阅读:
    [转]如何让ruby中的ri起作用
    大四中软实习笔记20130304
    VC6.0: fatal error C1010: unexpected end of file while looking for precompiled head
    大四中软实习笔记20130307
    C++ odbc 连接数据库错误 在指定的 DSN 中,驱动程序和应用程序之间的体系结构不匹配
    大四中软实习笔记20130303
    VC6.0: unresolved external symbol "public
    【分布式锁的演化】什么是锁?
    【分布式锁的演化】常用锁的种类以及解决方案
    【数据库】海量数据切分方案
  • 原文地址:https://www.cnblogs.com/purplefox2008/p/5649455.html
Copyright © 2020-2023  润新知