Friday, August 29, 2008

A small extension to the Entity Framework

After my trials and tribulations with the Entity Framework that I posted last week, I thought I'd dig a little deeper and come up with a solid way to use Stored Procedures through the Entity Framework while maintaining the level of abstraction provided by EF.

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.
The basis for all data connections inside the Entity Framework is a set of classes contained in the System.Data.EntityClient namespace. If you've used the classes contained in the System.Data.Common namespace to execute stored procedures previously, you'll probably be happy to learn that all of the Entity based classes inherit this functionality.

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:

  1. Expose the EntityConnection using the ObjectContext.Connection.
  2. The connection can run any function created in our Entity using the CreateCommand method of the EntityConnection.
  3. 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.
  4. The EntityCommand.CommandType must be set to CommandType.StoredProcedure.
  5. We need to either accept the passed in parameters (given that they're EntityParameters), convert them if they're DbParameters, or otherwise handle them.
  6. The passed parameters need to be added to the command.
  7. The command is then ready for the appropriate execution type.
EntityCommand and DbCommand both have several different methods attached based on the type of stored procedure: my solution for this was to build a method that creates the EntityCommand (I called it CreateStoreCommand in the provided code sample), then run the correct method based on the procedure itself.

  1. private static EntityCommand CreateStoreCommand(ObjectContext context, string functionName, object[] parameters)
  2. {
  3. EntityConnection entityConnection = (EntityConnection)context.Connection;
  4. string commandName = string.Format("{0}.{1}",context.DefaultContainerName,functionName);
  5. EntityCommand entityCommand = entityConnection.CreateCommand();
  6. entityCommand.CommandType = System.Data.CommandType.StoredProcedure;
  7. entityCommand.CommandText = commandName;
  8. if (null != parameters && parameters.Length > 0)
  9. {
  10. EntityParameter[] dbParams = new EntityParameter[parameters.Length];
  11. if (parameters is EntityParameter[])
  12. {
  13. ((EntityParameter[])parameters).CopyTo(dbParams, 0);
  14. }
  15. else
  16. {
  17. for (int i = 0; i < parameters.Length; i++)
  18. {
  19. EntityParameter p = null;
  20. object param = parameters[i];
  21. if (param is DbParameter)
  22. {
  23. DbParameter parm = (DbParameter)param;
  24. p = (EntityParameter)parm;
  25. }
  26. else if (param is ObjectParameter)
  27. {
  28. p = entityCommand.CreateParameter();
  29. ObjectParameter o = (ObjectParameter)param;
  30. p.ParameterName = (o.Name.StartsWith("@")) ? o.Name.TrimStart(new char[] {'@'}) : o.Name;
  31. p.Value = o.Value;
  32. }
  33. else
  34. {
  35. p = entityCommand.CreateParameter();
  36. p.Value = parameters[i];
  37. }
  38. dbParams[i] = p;
  39. }
  40. entityCommand.Parameters.AddRange(dbParams);
  41. }
  42. }
  43. return entityCommand;
  44. }

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:

  1. public static object ExecuteScalar(this ObjectContext context, string functionName, params object[] parameters)
  2. {
  3. if (context == null) return null;
  4. EntityCommand command = CreateStoreCommand(context, functionName, parameters);
  5. return command.ExecuteScalar();
  6. }

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.

Wednesday, August 27, 2008

Blog hiatus

There's been a small delay in my blogging this past week, due mostly to the birth of Gregory Thor Townsend, our third child. I'll put up more on this, and on a working way to use stored procedures easily with the Entity Framework by adding an Extension to EF.

More on the way, including pictures of Greg, and some backstory on the choice of his middle name.

Tuesday, August 19, 2008

Initial Observations on the Entity Framework

After spending a few hours getting acquainted with the Entity Framework (EF) in VS/C# SP1, I've got a few things that I'd like to discuss. In all, I find EF to be a very robust, mostly flexible framework that allows developers to create an ORM data layer very quickly with what seems to be little overhead.

To give you an idea of my tests, I built a simple yet modular db for multiple types of related web applications. For instance, if you are looking at a blog, a forum/announcement board, or Flickr, you're looking at three seemingly disparate applications that have a lot of the same elements in implementation (comments, keywords/tags, and categories, for instance).

With this in mind, consider the following structure:

Yes, it's very basic, but we're not going overboard on this example. I can take this database, and create an EntitySet similar to the following using the built in wizard:

This was extremely easy, and took a few short steps:

  1. From your project, Add >New Item > ADO.Net Entity Diagram. You will only see this option once SP1 is installed if you haven't installed any of the EF Betas or CTFs.

  2. In the Entity Data Model Wizard, pick the Add >New Item >Generate from database option.

  3. A choose your data connection option will appear: either add the db to your project, or add a standard db connection (I'll revise this later if you need to learn the steps to connect to a db, just ask).

  4. Choose all the Tables, Views, and Stored Procedures that you wish to add.
  5. Click Finish, and enjoy your new Entity Model (ModelName.edmx in your project).
So far, things are pretty clean, but I noticed a few gotchas while working with a slightly more complex model:

If you've used any of the EF betas and are looking for the CSDL, MSL, and SSDL files, they are all parts of the .edmx structure. The all in one format is quite a bit easier to manage file-wise (and it makes sense), but it is a little more cumbersome to edit by hand (the corresponding portions of the XML are identified with comments, so it's not a big deal).

Entities have trouble being created without keys, and a simple db design error can set you back quickly. If I were to add a BlogCommentID column as an identity column for the BlogComment table (a so- so- practice, but not uncommon one), it would have to exist in the created Entity (If I want to be able to access the Entity directly).

In one of my initial passes with EF, I had incorrectly mapped the FK from Blog-BlogComment (I went from BlogID->CommentID, not BlogID->BlogID). The EF Wizard allowed this relationship, but didn't allow the valid one to process. Manually adjusting the edmx was an experience, but it worked, at least until I tried to add any new db elements. The automated update Entities from Database process overwrote my changes each time, and it was a real mess.

Of course, the error was my own as far as the relationship in the database, but I have a feeling that purist db design is unfortunately the exception rather than the norm in companies that have been out there for quite a while, and the second that you figure a workaround, it's potentially a breaking change every time you update your schema.

Stored Procedure support is really nice, but can be cumbersome, as you have to create Entities for all stored procedures that return column subsets, unless you start to add null support for all non-important columns in your entities. For instance, if I want BlogTitles for category, I'd usually add a stored procedure that did the dirty work, then make a SQLcommand call that returns an untyped DataSet with my values. The Entity Framework does provide Entity versions of the same classes, but we're using the Entity Framework for data abstraction, so I'd rather have a GetBlogTitlesByCategory that returns a strongly typed class structure built for me. These strongly typed classes need to be added as Entities to the edmx so that they can be effectively mapped to the stored procedure.

It makes sense why this step is necessary (you can't determine data types for columns by looking directly at a stored procedure), but the frustrating portion is that these procedures might be added as function calls into the edmx file (and visible in your schema), but no actual method is created because the Entity return values are not created by the Wizard. You will not see the actual method definitions in the generated classes until you add the Entity(-ies) into your edmx, or you map the return value of the stored procedure to existing Entities (hence the need for nullable support, as the blog example would return blogid, title, and the create date, but not necessarily the body of the blog). A code generation tool for this would be nice, but converting a pre-existing data structure over to EF could turn into a painful exercise (but may help you refactor out similar procedures).

I'll give you another example of the problem: an account table stores a password salt, username, and password hash values. The user, when logging in, passes in the username and password. The business logic of the app takes the password and the salt, and creates the password hash value, and the Account Entity is returned if the username, hash, and salt values all match. I want the actual hashing algorithm independent of the db, but the db will store the salt, which is generated when the account is created. I don't want the Account Entity to contain the Hash or Salt values when the Account is returned, but I also want them to be required on the DB side. All I have to do on the Entity side is delete the autogenerated hash and salt columns, and they are not available for use.

The problem comes when we need to get the salt from the db in order to hash up the password when the user logs in. I have a stored procedure that will return the appropriate salt value in a one row, one column select statement, but I have to create an entity to get the value, and I get an entity back, when all I need is a single string! I tried mapping the function to a scalar, but that required re-writing the stored procedure to fit (not a good option for pre-existing sp's). I've tried setting ReturnType="Collection(String)" in the FunctionImport tag, but the entity designer doesn't create the method for me.

Of course, there's always LINQ to Entities, but I'm not sure that the LINQ query:

string passSalt = (from a in entity.Account
where a.Username == username
select a.PasswordSalt).First();

Or similar is more efficient(Keep in mind LINQ queries are designed to return a collection, not a scalar, so there's an additional level of processing to get the required value in this instance - the First method), and it forces me to keep at least the PasswordSalt in the return set.

I'm sure that I've missed something in my searching, or am simply expecting too much from the first production iteration of EF, but it would make sense that the Stored Procedure return values conundrum be just a little better documented in MSDN.

I'll be doing quite a bit more with EF over the next few weeks, and despite the hurdles, I can't help but like the abstraction that is created with EF, and I feel that it will make app scaling and changing schemas MUCH nicer (in relation to straight DB mapping, not other ORM tools), as long as the EF is flexible enough to fit the DB in its current state without too much effort.

Friday, August 15, 2008

About me

Hi, all, welcome to my blog. I'm hoping that this post will actually make it to the publish phase, as it is my 3rd or 4th attempt at putting something up here in the 3 weeks since I created this account. Since I'm ramping up my speaking engagements over the next few months, I really should have a voice other than the one that .Net developers in the Albany, NY area have listened to for the past few years.

About me - My name is Griff, and I like to code. I first decided that I liked to code when I was about 13, working in BASIC on an Atari 400 (then an 800XL -> WOOT), legal pad at my side with the actual lines of code that I would have to retype each time I turned the computer on because a floppy drive was $250 at the time, and I was a kid with an allowance a lot less than that. I had opened up the world of computing, and there was no limit to where I could go.

Then, puberty hit, and about ten years passed.

In the early '90s, I found myself disillusioned with college (I was an Econ major, no idea why) and in a band. The band was a blast, but we were self-marketed, and I found myself needing to teach myself Photoshop so that I could make flyers for the band. It was around that point that Netscape 1.1 was released, and I realized that computers were my bag, baby.

Long story short, I've been programming, first as a hobbyist, then professionally, for over 15 years. I got an MCP.Net a while ago, but I've really focused more on diving into and working in the technologies than expanding the number of commas after my name the past few years. I've done both the Web- and Windows- side of things, and have about 8 years of teaching and speaking experience (Syracuse University and occasionally ITT Tech) tied in with the development work. I'm currently the President of the Tech Valley .Net Users Group, and an avid gamer.

While most of my focus for this blog will be riffs on .Net development (C#, .Net 3.5, XNA, etc), I reserve the right to also talk about family, friends, games, football, or anything else that comes to mind. I may pop into teaching mode, or talk about new stuff that I'm learning. My focus will be on posting twice a week (I'm thinking Tuesday and Friday) on something that is at least a little interesting.

That all said and done, stay tuned.