实体框架的ObjectContext - ＆GT;原始SQL调用本地DBMS [英] Entity Framework ObjectContext -> raw SQL calls to native DBMS

问题描述

我有使用ADO.NET实体框架（VS2008的版本，而不是更新，更酷的一种）的应用程序，我需要能够拨打电话到底层数据库管理系统（它的Postgres的），以调用一些SQL是实体框架不支持。

I have an app using the ADO.NET entity framework (the VS2008 version, not the newer, cooler one) and I need to be able to make a call down to the underlying DBMS (it's postgres) in order to call some SQL that Entity Framework doesn't support.

有没有办法从实体框架的ObjectContext去的东西，可以让我执行原始SQL？ （我需要在插入之前运行TRUNCATE TABLE）我有一个哈克解决方案确定（如拉出从EF数据库管理系统的连接字符串信息，并用它来创建一个使用Postgres的ADO.NET提供者的连接），但不要管理两套连接字符串（一个用于实体框架，一个是ADO.NET）。

Is there a way to go from an Entity Framework ObjectContext to something that will let me execute raw SQL? (I need to run a TRUNCATE TABLE before inserting) I'm OK with a hacky solution (e.g. pull out the DBMS's connection string info from EF, and use that to create a connection using the postgres ADO.NET provider) but don't want to manage two sets of connection strings (one for entity framework, one for ADO.NET).

我所知道的实体框架的第一个版本的limitatons的，但它不是值得这个应用程序切换到另一个ORM所需的投资，并采用EF 4.0是不是一种选择，无论是。

I'm aware of the limitatons of Entity Framework's first version, but it's not worth the investment required to switch this app to another ORM, and using EF 4.0 isn't an option either.

任何想法？

顺便说一句，这是同一个问题的<一个href="http://stackoverflow.com/questions/915329/is-it-possible-to-run-native-sql-with-entity-framework">http://stackoverflow.com/questions/915329/is-it-possible-to-run-native-sql-with-entity-framework,但在这个问题的答案中描述的变通办法将不会为我工作，因为我确实需要执行原始的SQL。

BTW, this is the same question as http://stackoverflow.com/questions/915329/is-it-possible-to-run-native-sql-with-entity-framework, but the workaround described in that answer won't work for me since I really do need to execute raw SQL.

推荐答案

克雷格的答案，而没有奏效原来的样子，让我找对了方向。原来有一个EntityConnection.StoreConnection财产让你到底层DBMS的连接。因此，在执行本地SQL是那么容易，因为这样的：

Craig's answer, while it didn't work as-is, got me looking in the right direction. Turns out there's an EntityConnection.StoreConnection property which gets you a connection to the underlying DBMS. So executing "native" SQL is as easy as this:

    static void ExecuteSql(ObjectContext c, string sql)
    {
        var entityConnection = (System.Data.EntityClient.EntityConnection)c.Connection;
        DbConnection conn = entityConnection.StoreConnection;

        ConnectionState initialState = conn.State;
        try
        {
            if (initialState != ConnectionState.Open)
                conn.Open();  // open connection if not already open
            using (DbCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
        }
        finally
        {
            if (initialState != ConnectionState.Open)
                conn.Close(); // only close connection if not initially open
        }
    }

这篇关于实体框架的ObjectContext - ＆GT;原始SQL调用本地DBMS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！