Can I use a DBContext connection to run a SQL statement before the first EF object is loaded?

.net entity-framework

Question

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:

  • Supplying a connection to the DbContext
  • Opening the connection before I did any work

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>(); 
1
3
9/22/2011 3:42:09 PM

Accepted Answer

Don't bust through to the connection like that. Use ObjectContext.ExecuteStoreCommand.

9
9/22/2011 1:09:59 PM


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