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

问题描述

我有一个使用 ADO.NET 实体框架(VS2008 版本，而不是更新、更酷的版本)的应用程序，我需要能够调用底层 DBMS(它是 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)我可以使用 hacky 解决方案(例如，从 EF 中提取 DBMS 的连接字符串信息，并使用它来使用 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).

我知道 Entity Framework 的第一个版本的限制，但是不值得将这个应用程序切换到另一个 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.

有什么想法吗?

顺便说一句，这与 Is-it-possible-to-run-native-sql-with-entity-framework">Is可以使用实体框架运行本机 sql?，但该答案中描述的解决方法对我不起作用，因为我确实需要执行原始 SQL.

BTW, this is the same question as 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.

推荐答案

Craig 的回答虽然没有按原样工作，但让我找到了正确的方向.原来有一个 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 ->对本机 DBMS 的原始 SQL 调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！