Can I use the connection from Entity Framework or should I create a new one?

Question

I have a SQLite Database that I access using System.Data.SQLite. The database is read into my Entity Framework Objectcontext. However, I also need direct access to the database in certain cases.

1. Can I / Should I use the same connection that my Entity Framework object context is using? I.e. ObjectContext.Connection?
2. If yes, how can I access it? Casting Entities.Connection to SQLiteConnection results in "Unable to cast object of type 'System.Data.EntityClient.EntityConnection' to type 'System.Data.SQLite.SQLiteConnection'."
3. If not, should I create a seperate connection, open it and keep it open for the application duration, or should I open and close the connection every time I want to access the database with it?

Answer 1

ObjectContext.Connection is an EntityConnection. You can get the store connection via:

var sc = ((EntityConnection)ObjectContext.Connection).StoreConnection;

You can cast that to the SQLiteConnection.

It's OK to use this connection if you need it.

But, as @Chris mentioned, you can just use ObjectContext.ExecuteStoreQuery, etc., if that will do for you.

Answer 2

Entity Framework has built in methods for executing raw SQL queries if that's what you need to do.

Answer 3

If you're using the DBContext API you can actually execute a direct query by using this: aDBContext.Database.SqlQuery

I wouldn't be surprised if something similar exists for the ObjectContext API, but I barely used that one and thus can't tell you what it is.

Now if for some reason you can't (or don't want) to do that, I'd create a new connection for your custom queries. Put them in a Using and close it as soon as you're done with it.