New Posts New Posts RSS Feed: "Value cannot be null" getting IEntityQuery<T> when using usePersistentDbConnectionPerSession
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

"Value cannot be null" getting IEntityQuery<T> when using usePersistentDbConnectionPerSession
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
smi-mark View Drop Down
DevForce MVP
DevForce MVP
Avatar

Joined: 24-Feb-2009
Location: Dallas, Texas
Posts: 343 		Post Options Post Options   Quote smi-mark Quote  Post ReplyReply Direct Link To This Post Topic: "Value cannot be null" getting IEntityQuery<T> when using usePersistentDbConnectionPerSession
    Posted: 29-Oct-2013 at 12:41pm
just an FYI to anyone looking to do this in EF6.

It is MUCH easier now. You can simply add a class inheriting DbCommandInterceptor and add it using the DbConfiguration or by calling DbInterception.Add

More info here:

http://entityframework.codeplex.com/wikipage?title=Interception

I simply override the NonQueryExecuting method and run my logic first.

Some sample code:


    public class MyCommandInterceptor : DbCommandInterceptor
    {
        private void SetContextInfo(DbConnection connection)
        {
            var user = Thread.CurrentPrincipal as MyUser;

            if (user == null)
                return;

            var sb = new StringBuilder();
            sb.Append("DECLARE @Ctx varbinary(128); ");
            sb.Append("SELECT @Ctx = CONVERT(varbinary(128), @User); ");
            sb.Append("SET CONTEXT_INFO @Ctx");

            var sqlConn = connection as SqlConnection;

            if (sqlConn == null)
                return;

            var cmd = sqlConn.CreateCommand();
            cmd.CommandText = sb.ToString();

            var parameter = cmd.Parameters.AddWithValue("@User", user.Username);
            parameter.SqlDbType = SqlDbType.VarChar;

            cmd.ExecuteNonQuery();
        }

        public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
            SetContextInfo(command.Connection);
            base.NonQueryExecuting(command, interceptionContext);
        }
    }



Edited by smi-mark - 29-Oct-2013 at 12:43pm
Back to Top
stephenmcd1 View Drop Down
DevForce MVP
DevForce MVP


Joined: 27-Oct-2009
Location: Los Angeles, CA
Posts: 166 		Post Options Post Options   Quote stephenmcd1 Quote  Post ReplyReply Direct Link To This Post Posted: 16-May-2013 at 3:25pm
In the same way that you can provider your own custom connection, you can also wire things up to use a custom DbCommand implementation.  There you'll be able to override ExecuteNonQuery to do whatever you need before and/or after you call the base version to actually execute the query.  Note that for full coverage, you'll also need to do your logic in ExecuteScalar and ExecuteDbDataReader since those can be called in some cases - although, perhaps you know that you only care about ExecuteNonQuery.

Here are some snippets from our code for where we reference our CustomDbCommand and how that works...

In our custom DbProviderFactory: 
        public override DbCommand CreateCommand()
        {
            return new CustomDbCommand(DbProviderFactories.GetFactory(Invariants.CopiedSqlClient).CreateCommand(), null);
        }

In our custom DbProviderServices: 
        public override DbCommandDefinitionWrapper CreateCommandDefinitionWrapper(DbCommandDefinition wrappedCommandDefinition, DbCommandTree commandTree)
        {
            return new DbCommandDefinitionWrapper(wrappedCommandDefinition, commandTree,
                                                  (cmd, def) => new CustomDbCommand(cmd, def));
        }

In our custom DbConnection: 
        protected override DbCommand CreateDbCommand()
        {
            return new CustomDbCommand(WrappedConnection.CreateCommand(), null) {Connection = this};
        }
Back to Top
pponzano View Drop Down
Senior Member
Senior Member
Avatar

Joined: 28-Apr-2011
Location: Italy
Posts: 165 		Post Options Post Options   Quote pponzano Quote  Post ReplyReply Direct Link To This Post Posted: 16-May-2013 at 5:43am
Ok, I've fixed...thanks... just a last question....it's possible to oevrride the ExecuteNonQuery on some way? if it would be possible to set the Context Connection per call it would permits me to scale well in future

For Session Context I mean http://msdn.microsoft.com/en-us/library/ms189252%28v=sql.105%29.aspx

Thanks

Paolo


Edited by pponzano - 16-May-2013 at 5:44am
Back to Top
pponzano View Drop Down
Senior Member
Senior Member
Avatar

Joined: 28-Apr-2011
Location: Italy
Posts: 165 		Post Options Post Options   Quote pponzano Quote  Post ReplyReply Direct Link To This Post Posted: 15-May-2013 at 11:56pm
Is there a reason why my app continues loopping on creating a CustomDBConnection? have I to set the  usePersistentDbConnectionPerSession= true?

Thanks
Back to Top
stephenmcd1 View Drop Down
DevForce MVP
DevForce MVP


Joined: 27-Oct-2009
Location: Los Angeles, CA
Posts: 166 		Post Options Post Options   Quote stephenmcd1 Quote  Post ReplyReply Direct Link To This Post Posted: 15-May-2013 at 8:41am
I forgot to include the ProviderRow class.  It's just a simple class that hides away some of the ugly code needed to deal with the DataTable and DataRow.  It's basically just a wrapper that makes it strongly typed.  Here is the code for it: 
    /// <summary>
    /// Represents a DbProviderFactory's registration information.
    /// </summary>
    internal class ProviderRow
    {
        public ProviderRow(string name, string invariantName, string description, string assemblyQualifiedName)
        {
            Name = name;
            InvariantName = invariantName;
            Description = description;
            AssemblyQualifiedName = assemblyQualifiedName;
        }

        private ProviderRow()
        {
        }

        public string Name { get; set; }
        public string InvariantName { get; set; }
        public string Description { get; set; }
        public string AssemblyQualifiedName { get; set; }

        /// <summary>
        /// Add the data from this instance to the given table.
        /// </summary>
        public void AddToTable(DataTable table)
        {
            var newRow = table.NewRow();

            newRow["Name"] = Name;
            newRow["InvariantName"] = InvariantName;
            newRow["Description"] = Description;
            newRow["AssemblyQualifiedName"] = AssemblyQualifiedName;
            table.Rows.Add(newRow);
        }

        /// <summary>
        /// Creates a new <see cref="ProviderRow"/> based on the data contained in
        /// the given row.
        /// </summary>
        public static ProviderRow FromDataRow(DataRow dataRow)
        {
            return new ProviderRow
                       {
                           Name = (string) dataRow["Name"],
                           Description = (string) dataRow["Description"],
                           InvariantName = (string) dataRow["InvariantName"],
                           AssemblyQualifiedName = (string) dataRow["AssemblyQualifiedName"]
                       };
        }
    }
Back to Top
pponzano View Drop Down
Senior Member
Senior Member
Avatar

Joined: 28-Apr-2011
Location: Italy
Posts: 165 		Post Options Post Options   Quote pponzano Quote  Post ReplyReply Direct Link To This Post Posted: 15-May-2013 at 2:24am
Excuse me where's the  ProviderRow.FromDataRow defined?
I've lost it...
Thanks
Back to Top
stephenmcd1 View Drop Down
DevForce MVP
DevForce MVP


Joined: 27-Oct-2009
Location: Los Angeles, CA
Posts: 166 		Post Options Post Options   Quote stephenmcd1 Quote  Post ReplyReply Direct Link To This Post Posted: 04-Feb-2013 at 4:29pm

Yeah, I tried a lot of different methods but they all required changes to the connection strings and/or EDMX files - which would totally confuse the EF Designer.  And we weren't really willing to sacrifice design-time experience....especially since we make changes to our models all the time.

I'll have to find some time to try to contribute these things back to the EF Provider Wrappers toolkit.  It would be nice if future users don't have to run into the same roadblocks that you and I have run into!

Most of all, I'm glad to hear it is working so well for you.
Back to Top
ewertonrp View Drop Down
Newbie
Newbie
Avatar

Joined: 14-Dec-2012
Posts: 5 		Post Options Post Options   Quote ewertonrp Quote  Post ReplyReply Direct Link To This Post Posted: 28-Jan-2013 at 7:50am
The puzzle is complete! 

Thanks for your help. Without your knowledge we would not have found the solution.

I especially like to commend about installing the provider ... your idea was brilliant!
With this idea we have not had to change anything in the connection string and even in our EDMX, just call Install method.

Maybe even the "Community Entity Framework Provider Wrappers" could use parts of this solution as alternative ways to inject the Provide wrappers.

I'd appreciate your help!
Back to Top
stephenmcd1 View Drop Down
DevForce MVP
DevForce MVP


Joined: 27-Oct-2009
Location: Los Angeles, CA
Posts: 166 		Post Options Post Options   Quote stephenmcd1 Quote  Post ReplyReply Direct Link To This Post Posted: 25-Jan-2013 at 11:11am
You are correct, the EFProviderWrapperToolkit just lays the groundwork for making your own custom classes.  The example I gave above was a custom DbConnection that inherits from DbConnectionWrapper which is a class defined in the EFProviderWrapperToolkit - one of the many helpful base classes it provides.

For the full solution, you'll need a CustomDbConnection that does all your special logic.  You'll also need a custom Db Provider Factory that will be used to create your custom connection classes (this factory class is what Entity Framework will end up talking with).  The way the EFProviderWrapperToolkit works, I think you also need a custom Db Provider Services class - that is a somewhat weird class and I forget exactly why it is necessary.  Here is some of that code...

First is the Provider Services class.  It is not too excited.  I think the main point of this class is for you to define what kind of provider you are wrapping and what to call the provider that you've made.  In our case, we are wrapping the SQL client - but you'll need to do Oracle I guess: 
    /// <summary>
    /// Custom implementation of <see cref="DbProviderServices"/>.
    /// </summary>
    internal class CustomDbProviderServices : DbProviderServicesBase
    {
        /// <summary>
        /// Initializes the <see cref="CustomDbProviderServices"/> class.
        /// </summary>
        static CustomDbProviderServices()
        {
            Instance = new CustomDbProviderServices();
        }

        /// <summary>
        /// Gets the singleton instance.
        /// </summary>
        /// <value>The singleton instance.</value>
        public static CustomDbProviderServices Instance { get; private set; }

        /// <summary>
        /// Gets the provider invariant name.
        /// </summary>
        /// <returns>Provider invariant name.</returns>
        protected override string ProviderInvariantName { get { return Invariants.StandardSqlClient; } }

        /// <summary>
        /// Gets the default name of the wrapped provider.
        /// </summary>
        /// <returns>Default name of the wrapped provider (to be used when 
        /// provider is not specified in the connection string)</returns>
        protected override string DefaultWrappedProviderName { get { return Invariants.CopiedSqlClient; } }
    }

That "Invariants" reference is for this simple class.  Just a single place where I can have those constants and not have to worry about typing them every time: 
    /// <summary>
    /// Common invariants used to identify DbProviderFactories
    /// </summary>
    internal static class Invariants
    {
        /// <summary>
        /// The default Invariant for the SqlClient.  This Invariant will actually point
        /// to our custom provider at runtime
        /// </summary>
        public const string StandardSqlClient = "System.Data.SqlClient";


        /// <summary>
        /// The new Invariant that represents the default SqlClient Factory after we replaced
        /// the original Invariant with our custom provider.
        /// </summary>
        public const string CopiedSqlClient = StandardSqlClient + ".Copy";
    }

The Provider Factory itself is not too complicated.  It is the one that is in charge of making your CustomDbConnection: 
    /// <summary>
    /// Implementation of <see cref="DbProviderFactory"/> that provides custom behavior specific to MyApp.
    /// </summary>
    public class MyAppDbProviderFactory : DbProviderFactoryBase
    {
        /// <summary>
        /// Gets or sets the singleton instance of the provider.
        /// </summary>
        /// <value>The instance.</value>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2104:DoNotDeclareReadOnlyMutableReferenceTypes", Justification = "Factory is immutable.")]
        public static readonly MyAppDbProviderFactory Instance = new MyAppDbProviderFactory();


        private MyAppDbProviderFactory()
            : base(CustomDbProviderServices.Instance)
        {
        }

        /// <summary>
        /// Returns a new instance of the provider's class that implements the <see cref="T:System.Data.Common.DbConnection"/> class.
        /// </summary>
        /// <returns>
        /// A new instance of <see cref="T:System.Data.Common.DbConnection"/>.
        /// </returns>
        public override DbConnection CreateConnection()
        {
            return new CustomDbConnection();
        }
    }

The final piece of the puzzle is getting Entity Framework to use this connection.  There are a lot of ways to do this but there was only one way that worked for us.  Basically at runtime we replace the existing factory for SQL Server with our custom factory.  That way, when EF sees that our model is targeting SQL Server, it will lookup the factory for SQL Server and it will end up getting our factory instead.  However, we have to do some somewhat ugly reflection to replace the SQL Server factory - but it's something we will just live with.  And I think it's a somewhat common thing to do.  Here is our code: 
        /// <summary>
        /// Installs our custom Db Provider Factory as a replacement to the SqlClient factory.
        /// </summary>
        public static void InstallCustomProvider()
        {
            //Get the existing factory registered for SqlClient
            //Note: Doing this also forces the DbProviderFactories class to build an in-memory list of
            //      the registered factories.  We will be manipulating that list below via reflection so
            //      it's important that we make sure the class has 'initialized'
            var existingSqlFactory = DbProviderFactories.GetFactory(Invariants.StandardSqlClient);

            //If we are already the one registered, don't do anything else
            if (existingSqlFactory is MyAppDbProviderFactory)
                return;

            //Use ugly reflection to get the provider table that the DbProviderFactory built
            var tableField = typeof (DbProviderFactories).GetField("_providerTable",
                                                                   BindingFlags.NonPublic | BindingFlags.Static);
            if (tableField == null)
                throw new MyAppApplicationException("Could not find _providerTable field on DbProviderFactories.");
            var dataTable = (DataTable) tableField.GetValue(null);

            //Find the row for SqlClient
            var originalDataRow = dataTable.Rows.Find(Invariants.StandardSqlClient);

            //Build a copy of that row and change the invariant name to the 'copy name'
            var sqlRow = ProviderRow.FromDataRow(originalDataRow);
            sqlRow.InvariantName = Invariants.CopiedSqlClient;

            //Remove the original row
            dataTable.Rows.Remove(originalDataRow);

            //Add our copied row
            sqlRow.AddToTable(dataTable);

            //Make a new row for our custom provider and add it to the table
            new ProviderRow("MyApp Custom SqlClient Data Provider", Invariants.StandardSqlClient,
                            ".Net Framework Data Provider for SqlServer with custom behavior for MyApp",
                            typeof (MyAppDbProviderFactory).AssemblyQualifiedName)
                .AddToTable(dataTable);
        }

We call that InstallCustomProvider method in the App Startup method on the server and everything just magically falls together.

Best of luck!
Back to Top
ewertonrp View Drop Down
Newbie
Newbie
Avatar

Joined: 14-Dec-2012
Posts: 5 		Post Options Post Options   Quote ewertonrp Quote  Post ReplyReply Direct Link To This Post Posted: 23-Jan-2013 at 12:10pm
Hello stephenmcd1,

  After a long time, I came to implement this functionality and from what I analyze, I will try to use the solution you presented.

  However, I need a little help if possible.

  The EFProviderWrapperToolkit doesn't work alone, it is only used to create custom wrapped providers (EFTracingProvider and EFCachingProvider). Am I correct?

  If yes, I think you should have created a EFCustomProvider project, which includes CustomDbConnection.

  But now comes the part I really don't know how to do it. How can I link the project EFCustomProvider with DevForce? Could you explain in details? Maybe with a few lines of code.

  I really need to get this solution to work.

  I appreciate your help. Thank you so much for now.
Back to Top
stephenmcd1 View Drop Down
DevForce MVP
DevForce MVP


Joined: 27-Oct-2009
Location: Los Angeles, CA
Posts: 166 		Post Options Post Options   Quote stephenmcd1 Quote  Post ReplyReply Direct Link To This Post Posted: 28-Dec-2012 at 4:09pm
We had a similar requirement in our application.  In our case, we needed to call a custom stored procedure every time a DB connection was opened - the procedure would 'mark' the connection with what user was currently active because we have lots of triggers that need to know the current user.

We were able to handle this with the help of the EF Provider Wrapper Toolkit (also seems to be on Nuget now).  That basically lets you inject your own logic into various ADO.NET objects - so at the very lowest level of database access.  We then made our own custom DbConnection class that DevForce/EntityFramework end up using.  It was actually pretty easy and has given us a lot of nice 'hooks' into the lowest level of database access that has come in handy a lot.

Note: We are using SQL Server - but I would assume this approach would work for Oracle as well.

Here is some sample code for our custom DbConnection class that shows the kinds of things you can accomplish: 
   /// <summary>
    /// Custom implementation of a wrapper to <see cref="DbConnection"/>.
    /// Allows custom behavior at the connection level.
    /// </summary>
    internal class CustomDbConnection : DbConnectionWrapper
    {
        /// <summary>
        /// Opens a database connection with the settings specified by 
        /// the <see cref="P:System.Data.Common.DbConnection.ConnectionString"/>.
        /// </summary>
        public override void Open()
        {
            base.Open();

            //After the connection has been opened, do our logic to prep the connection
            SetContextInfo();

            //...and we do some other stuff not relevant to this discussion
        }

        /// <summary>
        /// Closes the connection to the database. This is the preferred method of closing any open connection.
        /// </summary>
        /// <exception cref="T:System.Data.Common.DbException">
        /// The connection-level error that occurred while opening the connection.
        /// </exception>
        public override void Close()
        {
            //Before closing, we do some cleanup with the connection to make sure we leave it clean
            //   for the next person that might get it....

            base.Close();
        }

        /// <summary>
        /// Attempts to set context_info to the current connection if the user is 
        /// logged in to our application.
        /// </summary>
        private void SetContextInfo()
        {
            //See if a user is logged in
            var user = Thread.CurrentPrincipal as OurCustomUserType;

            //If not, we don't need to do anything - this is probably a very early call in the application
            if (user == null)
                return;

            //Create the ADO.NET command that will call our stored procedure
            var cmd = CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "p_prepare_connection_for_use";

            //Set the parameters based on the currently logged in user
            cmd.CreateParameter("as_session_id", user.SessionID, null, DbType.Guid);
            cmd.CreateParameter("ai_user_sid", user.UserID, null, DbType.Int32);

            //Run the SP
            cmd.ExecuteNonQuery();
        }
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 17-Dec-2012 at 1:51pm
Unfortunately, this isn't something supported by usual DevForce processing, so maybe persistent connections are the answer. DevForce normally opens and closes a database connection for each request, using the same non-user specific connection string. This also means that when using an n-tier EntityServer all user requests will use the same database credentials, given the data source extension and composition context. When not using persistent connections, we don't currently offer any access to the connection or EF ObjectContext/DbContext, so there isn't a way to do what you need.
 
So back to your initial problem. The exception is occurring because the persistent connection data structures on the server haven't been initialized yet, since this is normally done following a login. In order to execute a query (or save) from an EntityManager in your login method you'd need to first temporarily disable the persistent setting in the IdeaBladeConfig.Instance, but this isn't a good idea since the EntityServer supports multiple concurrent requests and turning the connection flag on and off is going to cause other problems.
 
So I can't think of a way to make this work currently.   We've had several requests to provide some sort of access to the ObjectContext to enable this type of use case, but these requests have never yet moved up the queue to a high priority.  You might want to have a quick chat with our Professional Services group to see if they have any ideas, or your account rep so we can understand your requirements.
Back to Top
ewertonrp View Drop Down
Newbie
Newbie
Avatar

Joined: 14-Dec-2012
Posts: 5 		Post Options Post Options   Quote ewertonrp Quote  Post ReplyReply Direct Link To This Post Posted: 15-Dec-2012 at 2:58am
I really did not like the idea of ​​using "persistent database connections." It was just an attempt to solve my main problem:

I'm working with global time, and Oracle has a very easy control using the field type TIMESTAMP WITH LOCAL TIME ZONE.

But for this to work, for each open session, I need to change the TIME_ZONE of the logged user session. For this I need to call a PROCEDURE to execute the following command: ALTER SESSION SET TIME_ZONE = '+2:00' (germany)

Not using "persistent database connection", there is any way I can intercept the creation of each session in order to set specific database settings?

Thanks
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 14-Dec-2012 at 2:22pm
We don't recommend using persistent databae connections generally, they're for certain narrow use cases.  They've also been tested only against SQL Server.
 
If you have a use case for using persistent connections, please first contact your account rep about your DevForce license.
Back to Top
ewertonrp View Drop Down
Newbie
Newbie
Avatar

Joined: 14-Dec-2012
Posts: 5 		Post Options Post Options   Quote ewertonrp Quote  Post ReplyReply Direct Link To This Post Posted: 14-Dec-2012 at 11:37am
I forgot to mention that I use Oracle database.

I audited the created sessions and when using usePersistentDbConnectionPerSession="true" the oracle had no sessions created, but the EntityManager properties IsConnected and IsLoggedIn are both True.

Thanks
Back to Top
ewertonrp View Drop Down
Newbie
Newbie
Avatar

Joined: 14-Dec-2012
Posts: 5 		Post Options Post Options   Quote ewertonrp Quote  Post ReplyReply Direct Link To This Post Posted: 14-Dec-2012 at 11:15am
I have the following code:

public class LoginManager : IEntityLoginManager
{
public IPrincipal Login(ILoginCredential credential, EntityManager entityManager)               
{
...
var query = em.PersonUsers.Where(u => u.Login == credential.UserName);
var result = query.Execute();
...
}
}

The Problem:

When using usePersistentDbConnectionPerSession="false" all works fine.

But when using usePersistentDbConnectionPerSession="true", the PersonUsers (IEntityQuery<PersonUser>) return "Value cannot be null" exception. So, when call query.Execute() I get an EntityServerException.

What might be happening?
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down