Diego Vega

Entity Framework news and sporadic epiphanies

January, 2012

Posts
  • Diego Vega

    Stored procedures with output parameters using SqlQuery in the DbContext API

    • 13 Comments

    The DbContext API introduced in Entity Framework 4.1 exposes a few methods that provide pass-through access to execute database queries and commands in native SQL, such as Database.SqlQuery<T>, DbSet<T>.SqlQuery, and also Database.ExecuteSqlCommand.

    These methods are important not only because they allow you do execute your own native SQL queries but because they are right now the main way you can access stored procedures in DbContext, especially when using Code First.

    Implementation-wise these are just easier to use variations of the existing ObjectContext.ExecuteStoreQuery<T> and ObjectContext.ExecuteStoreCommand that we added in EF 4.0, however there still seems to be some confusion about what these methods can do and in particular about the query syntax they support.

    I believe the simplest way to think about how these methods work is this:

    1. A DbCommand from the underlying ADO.NET provider is setup with the query text passed to the method
    2. The DbCommand is executed with the CommandType property set to CommandType.Text
    3. In addition, if the method can return results (e.g. SqlQuery<T>) objects of the type you passed are materialized based on the values returned by the DbDataReader

    For a stored procedure that returns the necessary columns to materialize a Person entity, you can use syntax like this:

     1: var idParam = new SqlParameter { 
     2:     ParameterName = "id",  
     3:     Value = 1}; 
     4: var person = context.Database.SqlQuery<Person>( 
     5:     "GetPerson @id",  
     6:     idParam);

    For convenience these methods also allow parameters of regular primitive types to be passed directly. You can use syntax like “{0}” for referring to these parameters in the query string:

     1: var person = context.Database.SqlQuery<Person>(
     2:     "SELECT * FROM dbo.People WHERE Id = {0}", id);

    However this syntax has limited applicability and any time you need to do something that requires finer control, like invoking a stored procedure with output parameters or with parameters that are not of primitive types, you will have to use the full SQL syntax of the data source.

    I want to share a simple example of using an output parameter so that this can be better illustrated.

    Given a (completely useless Smile) stored procedure defined like this in your SQL Server database:

     1: CREATE PROCEDURE [dbo].[GetPersonAndVoteCount] 
     2: ( 
     3:   @id int, 
     4:   @voteCount int OUTPUT 
     5: ) 
     6: AS 
     7: BEGIN 
     8:   SELECT @voteCount = COUNT(*) 
     9:   FROM dbo.Votes 
     10:   WHERE PersonId = @id; 
     11:   SELECT * 
     12:   FROM dbo.People 
     13:   WHERE Id = @id; 
     14: END

    You can write code like this to invoke it:

     1: var idParam = new SqlParameter {
     2:      ParameterName = "id",
     3:      Value = 1};
     4: var votesParam = new SqlParameter {
     5:      ParameterName = "voteCount",
     6:      Value = 0,
     7:      Direction = ParameterDirection.Output };
     8: var results = context.Database.SqlQuery<Person>(
     9:     "GetPersonAndVoteCount @id, @voteCount out",
     10:      idParam,
     11:      votesParam);
     12: var person = results.Single();
     13: var votes = (int)votesParam.Value;

    There are few things to notice in this code:

    1. The primary syntax that SqlQuery and ExecuteSqlCommand methods support is the native SQL syntax supported by the underlying ADO.NET provider Note: someone mentioned in the comments that SQL Server 2005 won't accept this exact syntax without the keyword EXEC before the stored procedure name.
    2. The DbCommand is executed with CommmandType.Text (as opposed to CommandType.StoredProcedure), which means there is no automatic binding for stored procedure parameters, however you can still invoke stored procedures using regular SQL syntax 
    3. You have to use the correct syntax for passing an output parameter to the stored procedure, i.e. you need to add the “out” keyword after the parameter name in the query string
    4. This only works when using actual DbParameters (in this case SqlParameters because we are using SQL Server), and not with primitive parameters which SqlQuery and ExecuteSqlCommand also support
    5. You will need to read the whole results before you can access the values of output parameters (in this case we achieve this with the Single method) but this is just how stored procedures work and not specific to this EF feature

    Once you have learned that you can use provider specific parameters and the native SQL syntax of the underlying data source, you should be able to get most of the same flexibility you can get using ADO.NET but with the convenience of re-using the same database connection EF maintains and the ability to materialize objects directly from query results.

    Hope this helps,
    Diego

  • Diego Vega

    Exception from DbContext API: EntityConnection can only be constructed with a closed DbConnection

    • 3 Comments

    UPDATE: After I posted this article we found that the plan we had to enable the pattern with contex.Database.Connection.Open() would cause breaking changes in important scenarios: the connection, the EF model, and potentially the database would be initialized any time you somehow acess the Database.Connection property. We decided to back out of this plan for EF 5, therefore we will revisit fixing this issue completely on EntityConnection in EF 6.0.

    In several situations we recommend opening the database connection explicitly to override Entity Framework's default behavior, which is to automatically open and close the connection as needed. You may need to do this if for example you are using:

    • SQL Azure and you want to test that the connection is valid before you use it (although the failures in this situation have been reduced with the release of an update to SqlClient in August).
    • federated database and you need to issue the USE FEDERATION statement before you do anything else.
    • TransactionScope with a version of SQL Server older than SQL Server 2008 and you want to avoid the transaction from promoting to twi-phase commit.
    • TransactuinScioe with a database - like SQL CE - that doesn't support two-phase-commit and hence you want to avoid the ambient transactions from being promoted.

    The code with the ObjectContext API usually looks similar to this:

     1: using(var context = new MyEntities())
     2: {
     3:     context.Connection.Open();
     4:     var query = 
     5:         from e in context.MyEntities
     6:         where e.Name.StartsWith(name)
     7:         select e;
     8:     EnsureConnectionWorks(context.Connection);
     9:     foreach(var entity in query)
     10:     {
     11:         // do some stuff here
     12:     }
     13: }

    If you try to use similar code with a DbContext in the current version of Entity Framework, i.e. if you try calling context.Database.Connection.Open(), things won’t work as expected. Most likely, you will get an exception with the following message:

    EntityConnection can only be constructed with a closed DbConnection

    The issue occurs because the connection object exposed in the DbContext API (context.Database.Connection) is not an EntityConnection but the actual database connection. We made that design choice on purpose because it allows us to remove the need to learn about the existence of a whole API layer in order to use Entity Framework. Unfortunately, the choice also kills the pattern of opening the connection explicitly.

    If you are not curious about the technical implementation details, you just need to know that the best approach available in the current version of EF to avoid this exception and still control the lifetime of the connection is to drop down to the underlying ObjectContext instance and open the EntityConnection on it:

     1: ((IObjectContextAdapter)context).ObjectContext.Connection.Open();

    If everything goes according to plan, EF 5.0 will include changes that will make this unnecessary so that simply invoking context.Database.Connection.Open() will work.

    If you do want to hear what happens under the hood and how things will work in EF 5.0, here are some more details:

    Similar to other classes in the EntityClient namespace, EntityConnection was designed to behave like the DbConnection of a real ADO.NET provider. But the implementation of EntityConnection wraps the underlying database connection object and takes over its state. On the other hand, any time an ObjectContext needs the connection to perform some operation against the database, it asks the EntityConnection for its current state state and if it finds that the connection is closed, it infers that the implicit on-demand open/close behavior is needed.

    When you open the database connection exposed in context.Database.Connection, one of two things may happen:

    1. You may open the database connection before the underlying ObjectContext gets initialized: If this is the case the operation will succeed, but you will get the exception as soon as the underlying ObjectContext instance gets initialized (e.g. as a side effect of executing a query), because initializing the ObjectContext also involves initializing the EntityConnection. As the exception message says, the reason this will fail is that the constructor of EntityConnection validates that the database connection passed to it is in the closed state. The main reason the constructor of EntityConnection only takes a closed database connection is to simplify the implementation and to mitigate the need to synchronize the state among the two connection objects.
    2. You may instead open the database connection after the underlying ObjectContext get initialized, in which case you won’t get the exception but you won't get the desired effects either: EF will still close the connection after using it. The reason that happens is that EF checks the state of the EntityConnection and not the state of the real database connection. EntityConnection maintains its own state independently from the state of the underlying connection, so even if the database connection is in the opened state, the EntityConnection will appear closed. 

    We considered changing the behavior of EntityConnection in .NET 4.5 so that the constructor would accept an open database connection, and to make its connection state property delegate to the corresponding property of the underlying database connection. This would have meant that an EntityConnection could be now created in the open state. After some analysis we realized that things would get very tricky and that in certain cases the proposed changes could break existing applications. .NET 4.5 is an in-place update for .NET 4.0 so we are not making deliberate changes that may break existing .NET 4.0 apps.

    Instead we figured out a way (I think it was Andrew Peters who suggested it) to make the fix in the DbContext API by making the EntityConnection follow the state of the underlying database connection. DbConnection exposes an event, StateChage that is perfect for this purpose, so we just subscribe to the event in the database connection and then call Open and Close on the EntityConnection as necessary. This implies that whenever someone access the context.Database.Connection property, the underlying ObjectContext and EntityConnection have to be initialized. This is a breaking change too, but one that we are willing to take given the benefits and given that EF 5.0 (i.e. the new version of the EntityFramework.dll) is not an in-place update for EF 4.x.

    We made one exception to this new behavior though: if you access the Connection property in context.Database during model creation (i.e. inside OnModelCreating) we won’t initialize the underlying ObjectContext (how could we, if we still don’t know what the final model is going to look like?).

  • Diego Vega

    Why Entity Framework vNext will be EF5 and nothing else

    • 5 Comments

    This post touches on some history and on how different rationales have driven versioning of the Entity Framework for the last few years. I recommend you to continue reading only if you care about learning how things went in detail, otherwise here is all you need to know:

    • The Entity Framework now versions separately from the .NET Framework as some of its most important APIs ship outside .NET Framework (learn more details here)
    • We started using Semantic Versioning since EF 4.2, which means that  in the future any version increment will follow simple rules and will respond exclusively to technical criteria
    • The next planned version of EF after EF 4.3.0 is EF 5.0.0 (or EF 5 for short). This version will take advantage of the improvements we have made in the core libraries of EF in .NET 4.5 to deliver much awaited features such as support for enum and spatial types, TVFs and stored procedures with multiple results, as well as significant performance improvements

    As with everything in this blog, this post represents my personal view and not the position of my employer or of my team (although I am doing my best to just presents the facts :)).

    1

    I have very little to say about how we thought about versioning during the development of the first version of EF. Perhaps other people in the team gave it more thought, but I think the majority of us were just focused on getting EF out of the door. As naïve as it might sound, I think when software engineers are working on the first version of a product, they seldom think about versioning.

    The only hint of versioning I remember being evident at the time were there many cool features we had to postpone to “vNext” because they didn’t fit in the schedule.

    4 = 2^2

    When development of what today is called EF4 started, we used to refer to it in various ways, like EF vNext, EF v2, etc. At the time the concern emerged that versioning EF separately from .NET would cause confusion among some customers. After all, the EF runtime was going to be only a library that would ship as part of the .NET Framework, much like the core ASP.NET technologies or WinForms, and there were no reasons in sight to think that would ever change.

    Although EF was a new library compared with ASP.NET and WinForms – which had been part of .NET from day one – at least with the information we had at the time it seemed that aligning the version of EF with the version of .NET would minimize fragmentation of the .NET brand and the confusion among a certain crowd.

    I feel tempted to say something like “in retrospective, we were wrong”. But what really happened is that there were too many important things we wouldn’t learn until later…

    4.1 = 4 + 5 CTPs + 1 Magic Unicorn

    Forward to June 2009, with .NET 4.0 still in development, we released the EF Feature CTP 1, which included the Self-Tracking-Entities template, the POCO template and the first version of Code First. The idea of the Feature CTPs was to have a vehicle to get semi-experimental features of EF out and gather customer feedback that would help us develop them or discard them rapidly. Then the plan was that once finished, those features would be integrated into .NET and Visual Studio in the next available window.

    That way Self-Tracking-Entities made it on time for the final version of Visual Studio 2010, and the POCO template was released out of band in the Visual Studio Gallery. In the meanwhile, the most interesting piece of the EF Feature CTP 1, Code First, was a runtime piece, and the schedule to integrate runtime improvements into .NET 4.0 was already very tight. We also knew that the design could be improved substantially, and wanted to have multiple rounds of shipping previews and gathering feedback.

    Code First ended up missing the .NET 4.0 release. The only part that made it was the addition of DDL methods to ObjectContext and the EF provider model (e.g. CreateDatabase, DeleteDatabase, DatabaseExists).

    More than a year later and after several previews more, we released EF Feature CTP 4, which included for the first time another critical piece of the new EF: the “Productivity Improvements”, also known as the DbContext API. The .NET 4.0 train had departed long ago, but the .NET 4.5 train hadn’t still been announced.

    As the popularity of Code First and DbContext grew rapidly, it became obvious that we could not wait for the next version of .NET to release it.

    We ended up releasing the RTM version of Code First and DbContext under the name Entity Framework 4.1 in April 2011.

    At the time I remember some people asked me why we called it EF 4.1 and not EF 5. Version number 4.1 comes from the fact that it builds on top of version 4, and it is a purely additive and incremental change (i.e. it is a separate DLL that uses the public API of EF 4). There were many new features in 4.1, but we wanted to reinforce that it was just an incremental improvement and that EF 4 was still there.

    x = 4 + 1

    Things have changed a lot since we decided to call the second version EF 4. It was never a popular choice among those people that really care about versioning. Some have even suggested that it was a marketing stunt to make EF look like a more mature product than it was. Although not strictly an engineering decision, this was never the goal. Regardless, by the time we released EF 4.1, EF 4 was completely established.

    When deciding how to call the next major version of EF, we looked at different alternatives. We could have chosen to just wait and hope that things would align magically, e.g. make sure we don't go over EF 4.4 based on .NET 4.0, and then say that the version of EF that releases at the same time as .NET 4.5 was also EF 4.5, but there were other forces at work...

    Since the first Feature CTPs we have released more and more of our new functionality outside of .NET. At this point the way we release EF resembles more the model of ASP.NET MVC, which ships outside .NET and hence evolves at a different pace. We have achieved greater agility with the things that we ship out-of-band, and given the sustained customer demand for improvments, it only makes sense to move more pieces to the out-of-band releases. From this perspective, and for the long term, it makes more sense for EF to have versioning that is independent from .NET.

    When we looked for alternatives to rationalize our versioning system, we run into Semantic Versioning almost immediately (Scott Hanselman had been evangelizing it for some time). Semantic Versioning is a versioning system that consists on a small set of very straightforward, common sense rules.

    Adopting Semantic Versioning has lots of advantages. For starters, any software pieces that has a dependency on e.g. version 2 of certain component can assume that it will work with any version greater or equal to 2 and lesser than 3 as long as they are using Semantic Versioning. This simplifies managing dependencies and authoring installers. SemVer is also not something completely new, but a formalization of common practices, therefore everyone can understand it.

    Last August we announced that we were considering Semantic Versioning and asked for feedback. Last October we made it official: we will be using "Entity Framework" to refer to the bits we ship outside the .NET framework, and “EF core libraries” for the libraries we ship in .NET. We will continue versioning EF outside of .NET but we will use Semantic Versioning. There is one caveat: we have to start counting versions from where we already are. Since we started versioning EF separately from .NET with EF 4.1, we actually had very few option but to continue doing so. Unfortunately there was no way we could go back in time and change the decisions we made years before. Decrementing version numbers was obviously not an option, and besides, changing the name of the product and resetting to v1 sounded like a really bad idea.

    x = 5.0.0

    Two days ago, in the announcement of EF4.3 beta we mentioned that the next major version of EF, which will contain much awaited features like Enum and Spatial type support, will be called EF 5 and that a beta is just around the corner.

    It seems that this has triggered some questions even among people that usually follow the evolution of EF very closely. Here is why EF 5.0.0 is our only option:

    The Semantic Versioning spec clearly states:

    9. Major version X (X.y.z | X > 0) MUST be incremented if any backwards incompatible changes are introduced to the public API. It MAY include minor and patch level changes. Patch and minor version MUST be reset to 0 when major version is incremented.

    There are in fact some breaking changes coming in EF5. For instance, DataAnnotations that in the .NET 4.0-based versions of EF were defined inside EntityFramework.dll have been moved to System.ComponentModel.DataAnnotations.dll in .NET 4.5.

    My favorite thing about Semantic Versioning is that it reduces the question of versioning to an engineering problem! EF 5.0 is what it has to be.

    Hope this helps make the reasons clearer,
    Diego

Page 1 of 1 (3 items)