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!

Unit tests and static methods: a big no no

Yesterday I looked over some code and what appeared to be some nice tested code, was a big fail on dependency injection which resulted in his ‘apear to be’ unit tests being completely rubbish.

Most of the code in itself was decent and the author did seem to grasp a lot of the agile principles and patterns.  He used dependency injection with unity and the code was written clean and expressive.  The average length of his method was no more than 6 lines of code with a clear purpose in mind.  

However…

Then I noticed a folder called ‘Utility’.  I opened the folder and what I saw was more than a dozen of static classes filled with static ‘Utility’ methods.  I scratched my head a bit and then after some hesitation I did alt + f7 (resharper for find all usages) on the utility classes.  What I then found was 22 references to these static ‘Utility’ classes throughout his code base. 

I then took another look at the test covering the methods that used this Utility method… Then it became clear to me, the unit tests … well … they weren’t unit tests.

How can we unit test a method that uses a static utility method?  We can’t.  

A "unit" (as defined in the software testing literature) is the  smallest piece of code software that can be tested in isolation.

When you are trying to test the method that uses a static method, you can’t mock the behavior of that static method.  When you can’t mock calls of the method that needs to be tested then you can’t unit test this method.  Why not?  Well the calls that this method makes has the potential of changing the behavior of your method under test.  Your test won’t be isolated any more to that unit of work, thus it isn’t a unit test.

Consider a static utility method called Util that is used by 4 other methods (respectively A,B,C,D).  Imagine now that you’ve written unit tests for all of these methods.  Now when the implementation of the utility method changes and there is an error in the behavior of this method, this could trigger a fail on all your unit tests.  Should your other methods fail?  No A,B,C and D should remain green.  Their behavior is just fine under the prerequisite that the method Util behaves fine, but because you couldn’t mock the behavior of the static method you’re stuck with the faulted implementation also being tested in methods A,B,C and D.  

So basically you’re not testing 1 unit in your code but you’re testing 2.   So you’re depending on the behavior of another method in your tests, which obviously you shouldn’t do.  

In conclusion: if you’re doing dependency injection and unit testing don’t use static methods.  When you use static method you are in most cases (unless with dirty hacks) bound to the implementation code of that method.  In my opinion static methods in general are dangerous and contradictory to object oriented principles. 

Until next time ;-)

A code brainteaser: the solution

Yesterday I posted a small brainteaser, now I'll reveal what's wrong with the VB.NET code.

As you could see the code wouldn't execute because the boolean did not get set.  This is strange because when you debug the code it clearly jumps into the statement when the Action is invoked:

Now what is the subtle difference between the VB.NET code and the C# code is that C# uses the lambda syntax and VB.NET requires you to use the Sub or Function syntax. The VB.NET syntax requires you to specify whether you want the statement to provide a return value (Function) or whether it should just be a subroutine (Sub).  In this case I made a “mistake” by saying that it should be Function instead of a subroutine and since the assignment operator and the equality operator are the same symbol you hardly notice the difference.

So the fix would be the following:

Module Module1

    Sub Main()

        Dim thisShouldBeSet = False

        DoSomething(Sub() thisShouldBeSet = True)

        If Not thisShouldBeSet Then

            Throw New Exception()

        End If

        Console.WriteLine("yaay")

    End Sub

    Sub DoSomething(action As Action)

        action.Invoke()

    End Sub

End Module

So this is where the difference is in the Action class between VB.NET and C#.  From the definition of msdn:

In C#, the method must return void. In Visual Basic, it must be defined by the Sub…End Sub construct. It can also be a method that returns a value that is ignored.

In my opinion this is a flawed implementation and just allows developers to make more mistakes.  I can also see no valid reason why anyone would use a method with a return value in an Action.  If you do this because the method already exists then split it up because you’re doing too much.

Enough VB-ranting for today, till next time.

A code brainteaser

Why is the following working in C# but not in VB.NET?
 

class Program

{

    static void Main(string[] args)

    {

        bool thisShouldBeSet = false;

        DoSomething(() => thisShouldBeSet = true);

        if (!thisShouldBeSet)

            throw new Exception();

        Console.WriteLine("yaay");

    }

    static void DoSomething(Action action)

    {

        action.Invoke();

    }

}

Module Module1

    Sub Main()

        Dim thisShouldBeSet = False

        DoSomething(Function() thisShouldBeSet = True)

        If Not thisShouldBeSet Then

            Throw New Exception()

        End If

        Console.WriteLine("yaay")

    End Sub

    Sub DoSomething(action As Action)

        action.Invoke()

    End Sub

End Module

The answer is very subtle and incredibly simple but it can make you ponder for a while :-)

I’ll provide the answer next time.