Entity Framework ObjectContext -> raw SQL calls to native DBMS

.net ado.net entity-framework postgresql

Question

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.

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).

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.

Any ideas?

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.

1
4
5/23/2017 12:13:36 PM

Accepted Answer

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
        }
    }
17
10/16/2009 7:16:29 PM

Popular Answer

Yes, you can do this. Look at EntityConnection.StoreConnection. You can get the connection out of the ObjectContext.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow