Hi
I have a lot of DAL code using the ConnectionManager, e.g.
public void Insert(int accountID)
{
using (var ctx = ConnectionManager<SqlConnection>.GetManager(Database))
{
using (var command = ctx.Connection.CreateCommand())
{
command.CommandText = "INSERT etc";
command.Parameters.AddWithValue("@AccountID", accountID);
command.ExecuteNonQuery();
}
}
}
I now want to start adding DAL code using EF5 and the DbContext but also share the connection and transactions with the old and new. I can't do this:
public void Insert(int accountID)
{
using (var ctxManager = ConnectionManager<SqlConnection>.GetManager(Database))
{
// create an entityconnection inside the constructor
var ctx = new ScsEntities(ctxManager.Connection);
ctx.Accounts.Add(new Account
{
AccountID = accountID
});
ctx.SaveChanges();
}
}
because I get a exception 'EntityConnection can only be constructed with a closed DbConnection'.
Can you suggest a way to achieve this without massive amounts of rewriting the DAL?
Many thanks
Andrew
Hi,
Sorry - but this is not a supported scenario. You MUST create the EntityContext first and then you can get the Connection but only EntityContext CAN own the connection.
I guess the proper solution would be to change all DataAccess to use the ObjectContextManager or DbContextManager and get the underlying SqlConnection from the EF Context manager . Haven't had the need to do this myself.
Hi Jonny
Yes I now see this is the only way according to the EF docs. So I think I have to accept that I shouldn't try to mix ordinary ADO.NET code and EF code in the same transaction. That's not so bad.
What is important is that I can use the DbContextManager and ConnectionManager in the same DAL. I'm using the DalManager to retrieve various types. This initializes/disposes the ConnectionManager. So my question is should I initialize a DbContextManager in here as well or would you recommend I create a new DalManager for use with the EF stuff ('EfDalManager')? I'm starting to think the later but would appreciate your thoughts.
Of course the DalManager is supposed to be data access technology agnostic so the additional DalManager idea isn't good either?
Thanks again
Andrew
Hi,
Well, I haven't had the need to do this myself - but I would first look into using the existing DbContextManager as-is and add extension method (if necessary) to this manager to get the database connection.
If I understand correctly you are moving to a EF5 based repository and so would be better if you could update all your data access code to use the DbContextManager. And make a small test project to make sure that transactions and everything works as expected.
Hi
I think we're going to have to stick with Linq to SQL. At least we can use the same connection from the ConnectManager without any mods to the other DataAccess code. In many ways L2S is perfect for our requirements.
I guess we'll just leave EF for new projects and hope that L2S doesn't get removed from future .NET assemblies! DataSets/DataTables are still alive and well!
Thank you
Andrew
Copyright (c) Marimer LLC