Living in the Tech Avalanche Generation

Part 1.0 was setting up for the idea and now in part 2.0 we start to flesh out all the moving parts that would be required to work with IronPython and the Entity Framework and furthermore, do so in such a way that is parallel to how we expect to do so in a statically typed language such as C#. What we are going to aim for is POCO classes, a custom Object Context and Code Only mapping.

Before we move on a small piece of housekeeping. When you see [T] in this post it is the IronPython version of <T> in the C# world. For example these are equivalent:

ObjectQuery<T>

//and

ObjectQuery[T]

PODO / POCO Entities

I gave a bit of a sneak peak at this in Part 1.0 but it’s worth repeating here. Using Northwind (of course – it’s law after all) we could start building out our business Entities for Customers and Orders. Let’s start by keeping the members of our entities to a minimum for brevity sake:

Figure 1.0

Simple enough, a couple of classes with members types that include System.String, System.DateTime and System.Int32, a list of Orders in the Customer Entity and a single entity reference to Customer from the Order Entity. I posted a Customer Entity in part 1.0 but let’s list both Customer and Order here:

Listing 1.0

import clrtype
import clr
from System import *
from System.Collections.Generic import *
from Entities2.Order import Order

class Customer(object):

    __metaclass__ = clrtype.ClrClass
    _clrnamespace = “EntityFramework.Podo“
    _clrfields = {  “_customerId“:str,
                    “_companyName“:str,
                    “_contactName“:str,
                    “_orders“:clr.GetClrType(ICollection)
                    .MakeGenericType(Order)
                 }

    @property
    @clrtype.accepts()
    @clrtype.returns(clr.GetClrType(str))
    def CustomerID(self):
        return self._customerId

    @CustomerID.setter
    @clrtype.accepts(clr.GetClrType(str))
    @clrtype.returns()
    def CustomerID(self, value):
        self._customerId = value

    @property
    @clrtype.accepts()
    @clrtype.returns(str)
    def CompanyName(self):
        return self._companyName

    @CompanyName.setter
    @clrtype.accepts(str)
    @clrtype.returns()
    def CompanyName(self, value):
        self._companyName = value

    @property
    @clrtype.accepts()
    @clrtype.returns(str)
    def ContactName(self):
        return self._contactName

    @ContactName.setter
    @clrtype.accepts(str)
    @clrtype.returns()
    def ContactName(self, value):
        self._contactName = value

    @property
    @clrtype.accepts()
    @clrtype.returns(clr.GetClrType(ICollection)
    .MakeGenericType(Order))
    def Orders(self):
        return self._orders

    @Orders.setter
    @clrtype.accepts(clr.GetClrType(ICollection)
    .MakeGenericType(Order))
    @clrtype.returns()
    def Orders(self, value):
        self._orders = value

import clrtype
import clr
from System import *
from Entities.Customer import Customer

class Order(object):

    __metaclass__ = clrtype.ClrClass
    _clrnamespace = “EntityFramework.Podo“
    _clrfields = {  “_customerId“:str,
                    “_orderId“:int,
                    “_orderDate“:clr.GetClrType(DateTime),
                    “_customer“:clr.GetClrType(Customer)
                 }

    @property
    @clrtype.accepts()
    @clrtype.returns(int)
    def OrderID(self):
        return self._orderId

    @OrderID.setter
    @clrtype.accepts(int)
    @clrtype.returns()
    def OrderID(self, value):
        self._orderId = value

    @property
    @clrtype.accepts()
    @clrtype.returns(clr.GetClrType(DateTime))
    def OrderDate(self):
        return self._orderDate

    @OrderDate.setter
    @clrtype.accepts(clr.GetClrType(DateTime))
    @clrtype.returns()
    def OrderDate(self, value):
        self._orderDate = value

    @property
    @clrtype.accepts()
    @clrtype.returns(str)
    def CustomerID(self):
        return self._customerId

    @CustomerID.setter
    @clrtype.accepts(str)
    @clrtype.returns()
    def CustomerID(self, value):
        self._customerId = value

    @property
    @clrtype.accepts()
    @clrtype.returns(clr.GetClrType(Customer))
    def Customer(self):
        return self._customer

    @Customer.setter
    @clrtype.accepts(clr.GetClrType(Customer))
    @clrtype.returns()
    def Customer(self, value):
        self._customer = value

Notice the OrderDate property is specifying the System.DateTime data type? Let’s assume the above code will just work; if I run this ad-hoc proof of concept code below in Listing 2.0, I don’t encounter any problems and the output certainly seems to suggest that this should hang together ok when we hook our Customer and Order entities up via mapping to the Entity Framework.

Listing 2.0

import clrtype
import clr
from System import *

class ClrNatives(object):

    __metaclass__ = clrtype.ClrClass
    _clrfields =    {
                        “_clrDate“:clr.GetClrType(DateTime),
                        “_clrInt32“:clr.GetClrType(Int32)
                    }

    @property
    @clrtype.accepts()
    @clrtype.returns(clr.GetClrType(Int32))
    def ClrInt32(self):
        print ‘got into accessor‘
        return self._clrInt32

    @ClrInt32.setter
    @clrtype.accepts(clr.GetClrType(Int32))
    @clrtype.returns()
    def ClrInt32(self, value):
        print “got into the mutator“
        self._clrInt32 = value

    @property
    @clrtype.accepts()
    @clrtype.returns(clr.GetClrType(DateTime))
    def ClrDate(self):
        print ‘got into accessor‘
        return self._clrDate

    @ClrDate.setter
    @clrtype.accepts(clr.GetClrType(DateTime))
    @clrtype.returns()
    def ClrDate(self, value):
        print “got into the mutator“
        self._clrDate = value

c = ClrNatives()

print c.ClrDate.GetType()
print c.ClrInt32.GetType()

c.ClrInt32 = 10
c.ClrDate = DateTime.Now

print c.ClrInt32
print c.ClrDate

Figure 2.0

Figure 2.0 below depicts the output from the code in Listing 2.0 and shows that the underlying CLR type is indeed having the correct types created for it’s backing fields and associated properties.

The next piece of the puzzle that we would require would be the Entity Framework ObjectContext. Based on the recommended C# pattern for POCO implementation, we would do the following:

Listing 3.0

class NorthwindEntities(ObjectContext):

    __metaclass__ = clrtype.ClrClass
    _clrnamespace = “EntityFramework.Podo“
    _clrfields = {
                    “_customers“:clr.GetClrType(IObjectSet)
                        .MakeGenericType((Customer)),
                    “_orders“:clr.GetClrType(IObjectSet)
                        .MakeGenericType((Order))
                 }

    def __init__(self):pass

    def __init__(self, connectionString):
        self._customers = self.CreateObjectSet[Customer]()
        self._orders = self.CreateObjectSet[Order]()
        self.ContextOptions.LazyLoadingEnabled = True;

    @property
    @clrtype.accepts()
    @clrtype.returns(clr.GetClrType(IObjectSet)
        .MakeGenericType((Customer)))
    def Customers(self):
        return self._customers

    @Customers.setter
    @clrtype.accepts(clr.GetClrType(IObjectSet)
        .MakeGenericType((Customer)))
    @clrtype.returns()
    def Customers(self, value):
        self._customers = value

    @property
    @clrtype.accepts()
    @clrtype.returns(clr.GetClrType(IObjectSet)
        .MakeGenericType((Order)))
    def Orders(self):
        return self._orders

    @Orders.setter
    @clrtype.accepts(clr.GetClrType(IObjectSet)
        .MakeGenericType((Order)))
    @clrtype.returns()
    def Orders(self, value):
        self._orders = value

Creating an ObjectContext in IronPython was straightforward, inherit from ObjectContext and create IObjectSet[T] backing fields and properties for the entities managed by the context. As I said earlier, this specialised IronPython ObjectContext follows the pattern set out for POCO development where a roughly equivalent C# version would be as follows:

public class NorthwindEntities : ObjectContext
{
    private IObjectSet<Customer> _customers;
    private IObjectSet<OrderLine> _orderLines;
    private IObjectSet<Order> _orders;

    public NorthwindEntities()
        : base("name=NorthwindEntities", "NorthwindEntities")
    {

    }

    public NorthwindEntities(string connectionString)
        : base(connectionString, "NorthwindEntities")
    {
        _customers = CreateObjectSet<Customer>();
        _orders = CreateObjectSet<Order>();
        _orderLines = CreateObjectSet<OrderLine>();
    }

    public IObjectSet<Customer> Customers
    {
        get
        {
            return _customers;
        }
    }

    public IObjectSet<OrderLine> OrderLines
    {
        get
        {
            return _orderLines;
        }
    }

    public IObjectSet<Order> Orders
    {
        get
        {
            return _orders;
        }
    }
}

The Mapping Problem

My original goal was to provide all the moving pieces in IronPython code, even the mapping. Entity Framework 4.0 has a code only option with mapping (no XML required) and that made sense – considering there would be no need to bring any managed C# libraries into the mix. Unfortunately however it’s not currently possible to work with Expression[Func[T]] in IronPython and so the code only mapping is just not going to happen for the moment. Hang on you say – what about all the use of Expressions in the Entity Framework itself? Good question. We are not going to use them! The Entity Framework allows the use of Query Builder methods that take strings as arguments, producing ObjectQuery[T] where T is the entity type.

Lets run It!

At this point we have all the constituent pieces required, all that’s left is to see is whether or not it actually works. We start with some simple client code (running in NetBeans Python Edition) to simply load up some Customers:

mappingPart = “metadata=../Northwind.csdl|../Northwind.ssdl|../Northwind.“ + “msl;“
providerPart = “provider=System.Data.SqlClient;provider connection “
dbPart1 = “string=\”Data Source=BOOMER\\BOOM09;Initial Catalog=Northwind;“
dbPart2 = “Integrated Security=True;MultipleActiveResultSets=True\”“
cn = mappingPart + providerPart + dbPart1 +dbPart2

ctx = NorthwindEntities(cn)

custs = ctx.Customers.Where(“it.CustomerID > ‘C’“)

for cust in custs:
    custOutput = “The Customer ID is %(id)s and Company Name is %(name)s“
    custOutTuple = {‘id‘: cust.CustomerID, ‘name‘: cust.CompanyName}
    print custOutput % custOutTuple

Before we look at the result of running this code its worth noting one thing about this code above, the mapping portion of the connection string is pointing to a location on disk that specifies where to find the Entity Framework Mapping files. Remember that I said that we were unsuccessful in getting the code only mapping working due the issue with Expression Trees and IronPython. Entity Framework 4.0 does allow us produce mapping files with no code generated entities; this can be achieved in a C# or VB.NET library. Simply create your model in the VS designer as usual, make sure that it maps to your POCO’s in terms of structure, then build the project, specifying to export the mapping files rather than embedded them in the assembly.

Figure 3.0

In Visual Studio 2010 click on the .edmx file in Solution Explorer and remove the Custom Tool in the properties window so it becomes blank. Next, make the Copy to Output Director property either ‘Copy Always’ or ‘Copy if Newer’. Navigate to the output directory after running a build and you should see something similar to these three files (names will vary of course). These three files constitute the mapping.

Figure 4.0

Celebrations? Cigars?

So what output if any? Time to light up a cigar in celebration? What does the output window in NetBeans show us (see figure 4.0)?

Figure 5.0

Looks like we have been successful in getting the Entity Framework to speak directly to our IronPython class doesn’t it. Upon a quick check in SQL Profiler I could confirm that indeed the Customers were being loaded by our IronPython ObjectContext.

SELECT
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[CompanyName] AS [CompanyName],
[Extent1].[ContactName] AS [ContactName]
FROM [dbo].[Customers] AS [Extent1]
WHERE [Extent1].[CustomerID] > ‘C’

So after some fist pumping and jig dancing, I decided I should see how deep it really went. First thing to check for my mind was loading an entity graph deeper than one entity and also doing that with lazy and eager loading.

Lazy Load the Entity Graph

By making a small change to our code that consumes our IronPython ObjectContext class and iterating over the Order collection present in each Customer record, we would hope to see the Lazy Loading kick in? Drum Roll. Great tension in the air as we press F6 (don’t shoot me it’s NetBeans remember)……..

for cust in custs:
    print cust.CustomerID
    for order in cust.Orders:
        print OrderID

And sadly folks it blows up. What happened exactly? Interestingly enough, all the errors I had encountered up till this point when writing the proof of concept code, were related to mapping mistakes between the PODO’s (IronPython Entities) and my .csdl / .msl and .ssdl mapping files and therefore not insurmountable. This error was more than troubling and had me thinking we had struck a real road block that might not be movable. So how did the error manifest itself?

Figure 6.0

Unfortunately something about iterating the Customer Orders collection was blowing IPY.exe up and producing a crash dump file. I’m starting to believe in destiny just about now because just a week ago I happened across Scott Hanselman’s channel 9 interview of Tess Ferranadez who was demonstrating the new dump file debugging capabilities of VS 2010. Looks like I’m about to be checking that feature out a little earlier than I originally imagined.

Let’s hold that thought for a moment and revisit the code briefly before we embark on our voyage into dump files! The most obvious three things I saw as worthy for consideration as to the failure were, the iteration of Orders, something about the nature of the Order Entity itself or lazy loading was a goner? I started to examine the differences between the Customer and Order entities to look for differences that might proffer some clues. Immediately it was clear that the Customer entity only had strings for properties, surely that cant be it I thought, but decided to remove it from the list of possibilities immediately nonetheless. So after quickly testing the Order entity on it’s own (as the aggregate root) it became clear that indeed there might be a problem with primitives translating from Entity Framework over to the type declarations made in the IronPython classes.

orders = ctx.Orders.Where(“it.CustomerID > ‘C’“)
for order in Orders:
    print Order.CustomerID

Unfortunately where this was successful with Customer (all strings), it was not so with the Order entity which contained integers and Dates amongst them. Incredulous and unconvinced I decided to create a pure ad-hoc table with a mixture of primitives other than strings and this also confirmed my findings. Even when more exotic (tongue in cheek) types like DateTime were removed from the equation and only Int32 and String were present, the problem remained present.

A sanity check was required at this point. I recalled that I had a pre-existing proof of concept to show that specifying CLR types to the _clrfields and decorators were indeed working in other un-persisted entities, furthermore the IronPython class deriving from ObjectContext itself was example of this (see Listing 2.0 and 3.0). So what on earth was this snag? Using Python either int or clr.GetClrType(Int32) had the same negative effect, the Entity Framework didn’t seem to like either of them. This left me scratching a big whole in my head.

In an attempt to be complete, I still wanted to test lazy and eager loading. Let’s begin by moving our attention to a different Entity Model based on two tables where all the fields were typed as strings, that way I thought I could have a go at eager loading using the .Include(“Load.Path”) extension method which takes a string and returns an ObjectQuery[T] (<T> for C#). Here is the new model and query code.

parents = ctx.ParentRecords.Where(“it.ParentID != ‘bob’“).Include(“ChildRecords“)

for parent in parents:
    print parent.ParentID
    for child in parent.ChildRecords:
        print “\t“ + child.ChildID

Success! Judging from the output from this query and iteration, eager loading does appear to work as expected:

Figure 7.0

The SQL Profiler confirms that eager loading has in fact worked as expected. I dislike the SQL that Entity Framework produces – but that’s a subject for a very different post, I just suddenly felt the need for a mini rant.

SELECT
[Project1].[C1] AS [C1],
[Project1].[ParentID] AS [ParentID],
[Project1].[ParentName] AS [ParentName],
[Project1].[C2] AS [C2],
[Project1].[ChildID] AS [ChildID],
[Project1].[ParentID1] AS [ParentID1],
[Project1].[ChildName] AS [ChildName]
FROM ( SELECT
    [Extent1].[ParentID] AS [ParentID],
    [Extent1].[ParentName] AS [ParentName],
    1 AS [C1],
    [Extent2].[ChildID] AS [ChildID],
    [Extent2].[ParentID] AS [ParentID1],
    [Extent2].[ChildName] AS [ChildName],
    CASE WHEN ([Extent2].[ChildID] IS NULL)
        THEN CAST(NULL AS int) ELSE 1 END AS [C2]
    FROM  [dbo].[ParentRecords] AS [Extent1]
    LEFT OUTER JOIN [dbo].[ChildRecords] AS [Extent2]
        ON [Extent1].[ParentID] = [Extent2].[ParentID]
    WHERE [Extent1].[ParentID] <> ‘bob’
)  AS [Project1]
ORDER BY [Project1].[ParentID] ASC, [Project1].[C2] ASC

How about Lazy Loading? Remember we set the Lazy Loading property of the ObjectContext options in the __init__ (see Listing 3.0 above). So let’s check and leave out the .Include() this time:

parents = ctx.ParentRecords.Where(“it.ParentID != ‘bob’“)

for parent in parents:
    print parent.GetType().FullName
    print parent.ParentID
    for child in parent.ChildRecords:
        print “\t“ + child.ChildID

BOOM…no joy here! Let’s have a look at the output and see what went wrong this time.

Figure 8.0

We can see here that we are able to retrieve our ParentRecords without any problem however when ask for the ChildRecords for each Parent then we get a NoneType error, but we know for a fact that record ABCDE indeed has children as exhibited by our eager loading example. I feel myself about to descend down yet another rabbit hole. Lets make a quick check and see if a null check gets us past this point.

for parent in parents:
    print parent.GetType().FullName
    print parent.ParentID
    if parent.ChildRecords != None:
        for child in parent.ChildRecords:
            print “\t“ + child.ChildID

Ok, so no error this time but still no lazy load, but at least are we getting closer to knowing the theory around the primitives is accurate?

How about the mapping what part is that playing in this primitives issue? I added an extra column to the ChildRecord table and use int as it’s data type. Let’s give the ChildRecord Entity an Int64 member whilst the mapping files retain the Int32 mapping. We would expect a mapping error to be reported right? As you can see below that’s exactly what we get.

Traceback (most recent call last):

  File “C:\Users\simon.segal\Documents\NetBeansProjects\IronPythonEntityFramework\src\main.py“, line 33, in C:\Users\simon.segal\Documents\NetBeansProjects\IronPythonEntityFramework\src\main.py

  File “C:\Users\simon.segal\Documents\NetBeansProjects\IronPythonEntityFramework\src\Context.py“, line 36, in __init__

SystemError: The type ‘Edm.Int32‘ of the member ‘ChildAgeInYears‘ in the conceptual side type ‘NorthwindModel.ChildRecord‘ does not match with the type ‘System.Int64‘ of the member ‘ChildAgeInYears‘ on the object side type ‘EntityFramework.Podo.ChildRecord‘.

I also made a point of including some code to get a look at the Types full name and we can see that it’s the fully qualified name of the underlying CLR type as specified – that’s good you say, exactly what we want __clrtype__ to do for us. The problem with that is that the Entity Framework 4.0 dials up proxies at runtime when we use POCO, if you debug C# code you can see that asking for the POCO entities fully qualified name returns the proxy details as shown in Figure  9.0. This is not the case however for our IronPython Entities (see figure 8.0), regardless of our implementing __clrtype__.

Figure 9.0

In Part 3.0 we will dig into proxies and what they offer, how to setup to use them for different requirements and what effect it may or may not be having on our attempts to get POCO (or rather PODO) lazy loading and change tracking entities working with Entity Framework version 4.0.