I'm pretty new to EF, and have searched around for an answer to this without luck.
In essence, when I get a connection to the db in EF, I need to execute a stored procedure to setup some internal security, which will then limit the data that is brought back in the EF interactions.
Searching around, I have found information that says the following should work:
String currentUser = "Name";
_db = new DBContext();
if (_db.Database.Connection.State != ConnectionState.Open) {
_db.Database.Connection.Open();
}
DbConnection conn = _db.Database.Connection;
DbCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "storedproc";
DbParameter user = cmd.CreateParameter();
user.DbType = DbType.String;
user.Direction = ParameterDirection.Input;
user.Value = currentUser.ToUpper();
cmd.Parameters.Add(user);
cmd.ExecuteNonQuery();
var customer = (from c in _db.Customer where c.ACCOUNT == inputAccount select c);
response = customer.First<Customer>();
However when I try this, I get the "EntityConnection can only be constructed with a closed DBConnection." when I hit the LINQ query.
Does anyone know if this is even possible?
I'm using EF4.1, and the Oracle ODP.NET Beta for my DB access, which is connecting to a 10.2.0.3.0 server.
Thanks in advance for any help!
[EDIT]
I managed to work through this from what Craig mentioned, and by doing the following:
This allowed me to execute my security stored proc, and also forced EF to keep the connection open so my security setup was still valid.
Code as follows:
OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["DBConnect"].ConnectionString);
_db = new DBContext(conn);
_db.UnderlyingContext().Connection.Open();
_db.UnderlyingContext().ExecuteStoreCommand("execute storedproc");
_db.SaveChanges();
var customer = (from c in _db.Customer where c.ACCOUNT == inputAccount select c);
response = customer.First<Customer>();
Don't bust through to the connection like that. Use ObjectContext.ExecuteStoreCommand.