You may recall the problem: I have a user account table that stores confidential information that I don't want to directly expose to EF consumers, but I need to occasionally access it (user login, for instance) from the database. I could access it directly through a LINQ to SQL call or something similar, but I was looking for an efficient way to call it using EF.
My first attempts were unsuccessful, as the Entity Wizard is nice enough to identify the stored procedures available from the database and add them to the entity schema, but it only generates methods signatures in the Entity class for procedures that return Entities or are able to map directly to Entities. Granted, this covers most CRUD work inside the db, but it won't cover everything. In my case:
- The user account, when created, creates a password hash based on a unique salt value and the user's selected password. The hash and the salt values are then stored for any later comparisons, but there's no need for them to exist in any entities that come back to the consumer. A create login procedure, in this instance, doesn't map directly to an Entity (on purpose), but we still need to call the procedure if we want accounts created.
- In order to re-encrypt the password correctly on each login, the encryption algortihm needs to see the password salt that was entered when the user created the account. I could use a simple text query using the CreateQuery
method, but this would expose more of what I'm doing than I'd like. I'd much rather call an encrypted SP that does the processing for me, and returns the result as a string.
If you make an entity set using a process similar to what I discussed in my last EF post, take a look at the base ObjectContext class created by the designer (it will have the same name as the name of your entity set). This ObjectContext is the manager between the database and the entities themselves. A Connection property in the ObjectContext exposes all we need to add some extension methods that will enable us to run non-entity based procedures while still maintaining the abstraction provided by the Entity Framework.
Since our Entity data functionality extends System.Data.Common, I've got all I need:
- Expose the EntityConnection using the ObjectContext.Connection.
- The connection can run any function created in our Entity using the CreateCommand method of the EntityConnection.
- In order to access our stored procedures, we need to access them by using our Connection's store name, as well as the name of the imported function in the format StoreName.FunctionName.
- The EntityCommand.CommandType must be set to CommandType.StoredProcedure.
- We need to either accept the passed in parameters (given that they're EntityParameters), convert them if they're DbParameters, or otherwise handle them.
- The passed parameters need to be added to the command.
- The command is then ready for the appropriate execution type.
- private static EntityCommand CreateStoreCommand(ObjectContext context, string functionName, object[] parameters)
- {
- EntityConnection entityConnection = (EntityConnection)context.Connection;
- string commandName = string.Format("{0}.{1}",context.DefaultContainerName,functionName);
- EntityCommand entityCommand = entityConnection.CreateCommand();
- entityCommand.CommandType = System.Data.CommandType.StoredProcedure;
- entityCommand.CommandText = commandName;
- if (null != parameters && parameters.Length > 0)
- {
- EntityParameter[] dbParams = new EntityParameter[parameters.Length];
- if (parameters is EntityParameter[])
- {
- ((EntityParameter[])parameters).CopyTo(dbParams, 0);
- }
- else
- {
- for (int i = 0; i < parameters.Length; i++)
- {
- EntityParameter p = null;
- object param = parameters[i];
- if (param is DbParameter)
- {
- DbParameter parm = (DbParameter)param;
- p = (EntityParameter)parm;
- }
- else if (param is ObjectParameter)
- {
- p = entityCommand.CreateParameter();
- ObjectParameter o = (ObjectParameter)param;
- p.ParameterName = (o.Name.StartsWith("@")) ? o.Name.TrimStart(new char[] {'@'}) : o.Name;
- p.Value = o.Value;
- }
- else
- {
- p = entityCommand.CreateParameter();
- p.Value = parameters[i];
- }
- dbParams[i] = p;
- }
- entityCommand.Parameters.AddRange(dbParams);
- }
- }
- return entityCommand;
- }
To utilize our EntityCommand, we'll build some extension methods for ObjectContext. If you're not familiar with extension methods, C# 3.5 allows us to extend prebuilt classes with custom code, and we can easily reuse this functionality by simply including this class in our projects. These functions are public static methods with a first parameter specified by the keyword this. For instance, if I want to extend the ObjectContext class directly to execute a scalar function from my database using a pre-existing stored procedure, I need to create a method like so:
- public static object ExecuteScalar(this ObjectContext context, string functionName, params object[] parameters)
- {
- if (context == null) return null;
- EntityCommand command = CreateStoreCommand(context, functionName, parameters);
- return command.ExecuteScalar();
- }
I've got my scalar method. Note that the first parameter uses this to specify that this is an Extension method: if I include a reference to this class (or have the class in my namespace), any ObjectContext references will be able to execure these extensions:
If you look at the tool tip in the photo above, you should be able to see that I can now cleanly access any function that I add to the EntitySet that I create. Since we're looking to hide the underlying data specifics (we leave the messy details to the .edmx file), this is intended behavior.
I'll be posting a link to the TVUG site shortly for registered users, and I'll post a copy here when I'm able to.
Posts
