Extensions for Entity Framework Code First

A couple of months ago I blogged about an extension I wrote to execute a stored procedure more easily with Entity Framework CodeFirst.  It certainly bumped up the number of visitors I got on my blog, which I of course love to see, but it also made me think of so many things that are missing in EF CF.

During my time spend on a project that uses EF CF, I’ve bundled some extensions I wrote to facility my needs and to make developing a whole lot easier.  It surprises me that the Entity Framework team didn’t ship these features in their release. 

Below you’ll find an explanation for every extension I wrote.  The code is on github, so feel free to make any additions as you like.

1.  Executing Stored Procedures

I already wrote an extensive blog post about this one so feel free to read it here.

To  summarize, the extension allows you to execute a stored procedure as so:

var testProcedureStoredProcedure = new TestProcedureStoredProcedure() { Iets = 5, NogIets = true };
var result = DbContext.Database.ExecuteStoredProcedure(testProcedureStoredProcedure);

I’ve made a small addition that now you can decide which class will be used to resolve the stored procedure name: the result class or the stored procedure class.

2.  Map to custom dto’s with ESQL

By default you can only map to entities when using ESQL. If you write your select statement explicitly then you get a DbDataRecord.   This is a PIA when customizing you selects to minimize data transfer.  The extension I wrote can map all selected fields to your dto.

var query = _unitOfWork.CreateQuery<DbDataRecord>(queryString);var dtos  = query.Map<YourDTO>();

3. Retrieve the DbSet with a generic

The datacontext object of EF CF exposes a number of DbSet’s.  I found it quit useful to be able to generically address these DbSet’s.  So I’ve come up with the following solution.  With this extension you can select a DbSet by supplying the type of the entity as a generic.

dataContext.GetDbSetReference<YourEntityType>();

4. Include with lamda

Why on earth they didn’t include this in version 1.0 I have no idea but unless you’ve already wrote your own extension for it (probably!), I’ve included a way to do this in the extensions project also.  Basically it enables you to write:

EntitySet.Include(x => x.NavigationProperty)

So that’s about it.  You can find the code on github.  As I’ve said, I’m happy to accept any useful additions to the code base.

Have fun coding, till next time!

Calling a stored procedure with Entity Framework Code First

Currently I’m doing a project that uses Entity Framework Code First for data access.  Though I’m not 100% convinced that this technology is mature enough, a coworker of mine recommended it.  For the read side of our project we want flattened dto’s that are made to fit our view’s need.  Since some of the views were a bit too complex to write with linq to entities we thought it would be best to write stored procedures for them.

The problem here is that Entity Framework Code First does not support Stored Procedures out of the box.  Well… it actually does, but in such a way that it is indistinguishable from plain ADO.NET.  Yet I wanted a more type safe solution.  I’m still not sure if this is the best solution for this problem but I thought I would share the code with you.

Imagine we have a stored procedure:

DECLARE @Iets int

DECLARE @NogIets bit

SET @Iets = 5

SET @NogIets = 1

EXECUTE [dbo].[sp_TestProcedure]

   @Iets

  ,@NogIets

GO

The standard way of calling the stored procedure in EF Code First would be something like this:

var ietsParameter = new SqlParameter("@Iets",5);

var nogIetsParameter = new SqlParameter("@NogIets",true);

DbContext.Database.SqlQuery<ProcedureResult>("sp_TestProcedure @Iets @Nogiets", ietsParameter, nogIetsParameter);

After creating the database extension you can write a more typesafe solution:

var testProcedureStoredProcedure = new TestProcedureStoredProcedure() { Iets = 5, NogIets = true };

var result = DbContext.Database.ExecuteStoredProcedure(testProcedureStoredProcedure);

The typed stored procedure looks like the following:

public class TestProcedureStoredProcedure : IStoredProcedure<ProcedureResult>

{
    public int Iets { get; set; }
    public bool NogIets { get; set; }
}

Notice that you have to inherit from a generic IStoredProcedure and supply the type of the result of the stored procedure.  The stored procedure class contains properties that are used as the parameters for the execution of the stored procedure.  The extension will use the name of the class to determine which stored procedure to execute.  The default behavior is ‘sp_’+classname but you can edit the source to your own naming convention.

You can download the database extension class here, or get it from github.  Just add a reference to the project and import the namespace to start using the extension method.

There is a drawback however.  The execution time is slower because before it can call the query it has to reflect the properties of the stored procedure class.  It’s still a work in progress but it can be undoubtedly be executed faster by adding some kind of cache for the stored procedure names into it.  If you have any improvements then I would be happy to hear about them!

A small note: if you have any questions about my blog posts or if there are any links broken then please add a comment to the blog post in question.  Mails often get lost in my mailbox and it can take a couple of weeks before I can answer them.

I've created a new extensions project for Entity Framework Code First.  You can read all about it here!

Editable Views in Entity Framework

Last time I explained how you could create an association between a view and a table inentity framework.  Now let’s take it a step further.  

The inherent ‘problem’ with working with a view as opposed to working with a table is that a view is read only.  Yet out of the box, Entity Framework can work with this view like it would with a table and update and delete rows out of this view.

      I will be explaining two ways for updating your view in this post:  

1. For simple views we can just remove the defining query
2. For more advanced views we can map stored procedures

A cautious word: only do this with the simplest of views, or even better, write stored procedures that will do this for you.  I’m just explaining the possibility that exists in Entity Framework to do this and not really encouraging the use of updating views.

So that being said, let’s get down to business.

Removing the defining query

Open you edmx in your xml editor and then search for the line that describes your View:

          <EntitySet store:Name="vw_Person" EntityType="Model.Store.vw_Person" store:Type="Views" store:Schema="dbo" store:Name="vw_Person">

            <DefiningQuery>

               store:Name="vw_Person"

              SELECT

              [vw_Person].[UserId] AS [UserId],

              [vw_Person].[PersonId] AS [PersonId],

              [vw_Person].[FirstName] AS [FirstName],

              [vw_Person].[LastName] AS [LastName],

              [vw_Person].[Sex] AS [Sex],

              [vw_Person].[Titel] AS [Titel],

              [vw_Person].[Email] AS [Email],

              [vw_Person].[Street] AS [Street],

              [vw_Person].[Street2] AS [Street2],

              [vw_Person].[Zipcode] AS [Zipcode],

              [vw_Person].[City] AS [City],

              [vw_Person].[Country] AS [Country],

              [vw_Person].[CountryCode] AS [CountryCode],

              [vw_Person].[OrganisatieNaam] AS [OrganisatieNaam],

              [vw_Person].[Rrn] AS [Rrn],

              [vw_Person].[OnlineModified] AS [OnlineModified],

              [vw_Person].[DatePasswordSent] AS [DatePasswordSent],

              [vw_Person].[CreateDate] AS [CreateDate],

              [vw_Person].[CountryId] AS [CountryId],

              [vw_Person].[ModifyDate] AS [ModifyDate],

              [vw_Person].[Image] AS [Image]

              FROM [dbo].[vw_Person] AS [vw_Person]

            </DefiningQuery>

          </EntitySet>

In order to make it updatable you have to remove the defining query.  When that is done also remove the store:Name attribute and  then remove the store prefix from the store:Schema attribute. When this is done the EntitySet element should look something like this:

          <EntitySet Name="vw_Person" EntityType="Model.Store.vw_Person" store:Type="Views" Schema="dbo" />

Now save the edmx and try to update your view like you would with any other entity.

This method will only work when your view is updatable.  When your view contains a calculated value or something similar then this method will fall short.  In this case we’ll have to manually assign the stored procedures that are responsible for updating the entity.

Using stored procedures

So in my example I have made a simple stored procedure that will update the values of the Person view.  I then import the stored procedure by clicking ‘Update Model from Database’ and then selecting the appropriate stored procedure to add.

Once the stored procedure is added, click on your view and go to the ‘Mapping Details’ toolwindow.  On the left side of this toolwindow you can select ‘Map Entity to Functions’.  In here is where you can define your Update, Insert and Delete functions that Entity Framework should use when doing these operations.

Voila that was it, see you next time ;-)

How to map a relation between a View and a Table in Entity Framework

Imagine creating a view in your database with some data you need aggregated from different tables.  This data could be associated to another table in your database.  While you might not want to make any changes in your database you may want the conceptual diagram in your application to link up these entities so it makes it easier for you to develop with them.

The first thing you need to do is create you edmx and add the wanted tables and views.  Make sure that you have a primary key defined for your view, entity framework will by default take all non nullable columns (… go figure).  Then when you have the desired view and table on your entity diagram you create a new association.

In the ‘add association ‘ window you can then select the two entities you want to have a relation between.  Deselect the ‘Add foreign key properties to the ‘xxxx’ Entity’, as it will add a new property to the associated table that will act as a foreign key.  In this case it won’t be necessary because I already have my foreign keys mapped.

Once you’ve done this you should receive the following error:

No mapping specified for the following EntitySet/AssociationSet – PersonFunctie

So this is where the entity designer falls short.  We defined the association between the two entities but entity framework cannot find the association in the storage model so it cannot figure out which properties have to be used.  To fix this open the edmx in an xml editor.  Then browse to the line that the error indicated and you’ll find this:

<Association Name="PersonFunctie">

  <End Type="eOpvolgingModel.Person" Role="Person" Multiplicity="1" />

  <End Type="eOpvolgingModel.Functie" Role="Functie" Multiplicity="*" />

</Association>

Now you just need to say to the conceptual model which properties define this association:

<Association Name="PersonFunctie">

  <End Type="eOpvolgingModel.Person" Role="Person" Multiplicity="1" />

  <End Type="eOpvolgingModel.Functie" Role="Functie" Multiplicity="*" />

  <ReferentialConstraint>

    <Principal Role="Person">

      <PropertyRef Name="PersonId" />

    </Principal>

    <Dependent Role="Functie">

      <PropertyRef Name="Persoon_Id" />

    </Dependent>

  </ReferentialConstraint>

</Association>

Save it, build it, run it, and voila.  Everything should work now.

Until next post!