Tip 7 – How to fake Foreign Key Properties in .NET 3.5 SP1


Background

If you’ve been reading the EF Design Blog you will have seen that we recently announced something called “FK Associations” for the EF in .NET 4.0.

However in .NET 3.5 SP1, we only support Independent Associations. What that means is that the FK Columns aren’t available as Properties in the entity. Which in turn means you are forced to build relationships via References to other Entities.

For example, unlike LINQ to SQL, this is not possible in the EF:

product.CategoryID = 2;

Because there is no “CategoryID” property in product entity.

You have to do something like this instead:

product.Category = ctx.Categories.First(c => c.ID == 2);

I.e. you need to use the Category reference. Which means you have to have a Category in memory, or do a query to get one like the example above.

The problem is that not having a property for the CategoryID that you can set directly can be a big pain, and that is why we’ve added “FK Associations” and “FK Properties” in .NET 4.0.

All well and good, but as Julie Lerman has a habit of reminding me, what about everyone who is using .NET 3.5 SP1 today? Our new features don’t really help today.

So…

How can you set the CategoryID if it doesn’t actually exist?

Trick question. You can’t of course.

But you can achieve the same effect, with code like this:

product.CategoryReference.EntityKey
   = new EntityKey(“MyContainer.Categories”, “ID”, 2);

Looks a little complicated doesn’t it, so let’s break it down:

  1. For each reference “xxx” (like Category) the entity framework also generates an “xxxReference” property that returns an EntityReference. (EntityReference is used heavily by the EF to provide all the plumbing and services required for things like maintaining relationship consistency, and doing Entity lookups based on EntityKeys)
  2. One of the properties of the EntityReference is an EntityKey. We need to set the EntityKey to a new EntityKey if we wish to change the value of the Foreign Key.
  3. In order to create a new EntityKey we need to know 3 things.
    1. The value we want to set the “Foreign Key” to. To be consistent with the earlier code snippets that is 2.
    2. The fully qualified name of the EntitySet that the target Entity belongs to. In this case our target is a Category and we get Categories from the “MyContainer.Categories” set.
    3. The name of the Primary Key Property in the target entity. In this case the primary key of Category is the “ID” property.

Now obviously this is not the sort of code you want to write everywhere you need it. So lets hide this away. So …

 

How can you use this code to fake a Foreign Key property?

Luckily the Entity Framework generates partial classes for Entities, so you can simply put this logic in your own Product partial class like this:

public int CategoryID {
    get {
       
if (this.CategoryReference.EntityKey == null) return 0;
       
else return (int) this.CategoryReference
            .EntityKey.EntityKeyValues[0].Value;
    }
    set {
        this.CategoryReference.EntityKey 
           = new EntityKey(“MyContainer.Categories”, “ID”, value);
    }
}

Notice that we use the CategoryReference in the getter too. This means that our CategoryID property is simply a view over the CategoryReference, which means that if the EF makes any changes under the hood we don’t need to be notified.

With this in place you can write what we wanted all along:

product.CategoryID = 2;

And that can be extremely useful in lots of scenarios, for example MVC controllers and data-binding.

Extra Credit

This solution is essentially just a workaround that hides some of the limitations of independent associations, and like all workarounds it has limitations.  The key then is to make an informed tradeoff between the benefits and the drawbacks. So here are some of the key drawbacks:

  1. Properties in the partial class are not recognized by the Entity Framework, so you can’t use them in your LINQ queries.
  2. By referencing the fully qualified EntitySet name in our setter, we have coupled your Entity Class directly to the EntitySet. Now for most people this is not a problem, but it won’t work if you try to re-use your entity classes between contexts or if you use MEST.
  3. There are probably more… I’ll add them as I think of them!
Comments (22)

  1. Meta-Me says:

    Hopefully if you’re reading this you’ve noticed that I’ve started a series of Tips recently. The Tips

  2. Craig Stuntz says:
    1. Product.Category will still be null after setting CategoryId. CategoryReference.Load won’t work, either. You can write a custom helper that knows about EntityKeys, though.
  3. AlexJ says:

    Craig,

    Yes Product.Category will be null after you set CategoryID (unless you set the Product is attached and the Category in question is loaded too), but that doesn’t really matter, you can still form relationships, which you can’t do without this unless you manipulate the reference directly. That is the whole point of this. Forming relationships without reverting to using the EntityReference or Reference properties.

    As for CategoryReference.Load() that will work if the Product is attached.

    Alex

  4. dsoltesz says:

    Once EF for .NET 4.0 comes out with foreign key associations, will there be an easy way to upgrade your existing EF model built on .NET 3.5 sp1 to use the new associations?

  5. Craig Stuntz says:

    Let me explain why this matters to me:

    I have an ASP.NET MVC model binder which works in almost exactly the way you describe in the post. The web view contains HTML SELECTs which allow the user to change the "foreign key" values in the relationship. The resulting POST returns the selected IDs. So the model binder can take these IDs and assigned them similarly to how you do CategoryId. This is important, because the model binder cannot have a reference to the ObjectContext. That lives inside the repository, and the web app can’t see it.

    Inside the repository, however, there are validation rules for the updated instance. These validation rules work on the reference, rather than the ID. That’s just the natural way to write this sort of code, especially when the validation needs to access sub-properties of the referenced entity. In this case, the "Product" is almost guaranteed to NOT be attached, given the way that the product reference was set, and given that the ObjectContext only lives for the span of the Web request.

    So there are two different areas of code with different needs and different scope. One can see the ObjectContext and needs access to sub-properties of the referenced entity, the other cannot see the ObjectContext and is getting IDs from the POST anyway, so setting the ID directly is quite convenient.

    My solution is to use the following helpers inside the repository when I need access to the referenced entity:

           public static bool IsLoadable(EntityObject e)

           {

               return (((e.EntityState) & (System.Data.EntityState.Detached | System.Data.EntityState.Added)) == 0);

           }

           public static void LoadReference<T>(EntityObject e, EntityReference<T> reference, ObjectContext context) where T: EntityObject

           {

               if (context == null)

               {

                   throw new ArgumentNullException("context");

               }

               if (reference.Value == null)

               {

                   if (IsLoadable(e) && (!reference.IsLoaded))

                   {

                       reference.Load();

                   }

                   else

                   {

                       if (reference.EntityKey != null)

                       {

                           reference.Value = (T)context.GetObjectByKey(reference.EntityKey);

                       }

                   }

               }

           }

    Then I can write code like this inside the repository:

       if (Product.Category == null)

       {

           LoadReference (Product, Product.CategoryReference, context);

       }

    Now I know that Product.Category will be loaded if the key has been set in any fashion.

  6. AlexJ says:

    @dbstoltez,

    I don’t think we’ve done anything to make this easy.

    Mainly because Independent Associations are still valid.

    What would you like to see? A commandline tool? Or something built-in to VS?

    Alex

  7. AlexJ says:

    @Craig,

    Yeah I understand your problem. In fact the genesis of this post was my work playing with MVC and EF together.

    Here is my take:

    EF Entities are written to be able to operate both with services (attached) and without (detached). Given this world view an entity ‘property’ can never realistically guarantee it can LazyLoad().

    However a function can. Hence the code you wrote, which is pretty cool by the way*.

    On the other hand the goal of the code I wrote was somewhat more modest! The idea was not to provide services to un-attached entities, but rather to provide users a way to manipulate relationships without being attached.

    Cheers

    Alex

    *Although you should be aware that context.GetObjectByKey(EntityKey) can fail in .NET 4.0 with POCO entities if the EF hasn’t got an EntityType to CLRType mapping already loaded for the EntityType in question.

    This mapping generally happens as soon as you use one of the strongly typed methods on the ObjectContext i.e. ctx.People or something.

    Under the hood these methods call LoadFromAssembly(…) for the assembly of the Type in question.

    This means using GetObjectByKey() can be a little dangerous with POCO entities if it is the first thing you do with the context.

    Thankfully you can force that loading by putting something like this in your ObjectContext constructor

    this.MetadataWorkspace.LoadFromAssembly(this.GetType().Assembly);

    or something like that (can’t remember off the top of my head). This way your ObjectContext always has the necessary metadata.

    We will look at putting that line of code into the T4 template for POCO classes.

    So this probably usually won’t surface.

    Cheers

    Alex

  8. Craig Stuntz says:

    Alex, thanks for your comments.

    It doesn’t bother me that my function won’t work in 4.0 if I can have FK Associations instead. They are a better solution to this problem anyway. So, regarding dbstoltez’s point, yes, it would be nice to have some way to migrate. I don’t really care if it’s a commandline tool, a Visual Studio tool, or anything else. I probably will want to migrate, because my use fits the FK Associations pattern very well. Actually, I’m really happy to see this feature coming as it makes a whole bunch of things simpler for me.

    On the other hand, it seems a little disturbing that GetObjectByKey might stop working in certain cases. This method has always "just worked" in the past. It’s sort of a refreshing change from things like Load which work some of the time, but not all of the time.

    Honestly, POCO types are not an important feature to me, so I’m not sure this will impact me personally. I’m probably going to continue using the default EntityObject base type. But having to be aware of the state of the application before calling GetObjectByKey seems like a heavy price to pay.

    Anyway, thank you for your blogs. I appreciate the information.

    -Craig

  9. Meta-Me says:

    The Entity Framework is pretty big, so when the Entity Framework team talks about things internally we

  10. dsoltesz says:

    Yeah, something in vs would be nice to say, "convert model to fk relationships".

    As a side note, poco types are not that important to me either as I like using the default entity types.  The only thing I do now is i have to update my generated types to inherit from my custom base type but once T4 templates are supported, I won’t have to do this manual updating of the generated file, I will just create a custom template.

  11. Meta-Me says:

    Background and Motivation: In my last post on EF Jargon I introduced the concept of Relationship Span.

  12. VS2010学习 says:

    Background and Motivation: In my last post on EF Jargon I introduced the concept of Relationship Span

  13. VS2010学习 says:

    The Entity Framework is pretty big, so when the Entity Framework team talks about things internally we

  14. Ryan says:

    I have been using this in a similar way to set the FK relation in EF.

    Profile.UserReference.EntityKey = User.EntityKey;

    Obviously, you will need to get the User enity from the context first. But this will not throw state exceptions for those working in MVC or other "disconnected" environment.

  15. wtfChris says:

    I’m new to EF (this is my first EF project, and I’ve been trying to learn it by reading blogs, and watching webcasts).  So please forgive me if I bungle this along the way.

    I have an entity called Opportunity.  Opportunity has and FK (field name in the Opportunity table is SourceSystemID) into the NBOSourceSystem entity.  The EntityFramework model has created an association in my Opporunity entity to the NBOSourceSystem.

    in my UI the user designates the NBOSourceSystem to associate with the opportunity via a combo box.

    My combobox binds the selected item to the NBOSourceSystem association property on my Opportunity object.

    The entity framework then updates the value of the SourceSystemID property with the ID of the assocated NBOSourceSystem.

    So far, so good.  Here’s the problem.

    If the user selects a NBO Source System value from the combobox called "LOCAL", what gets stored in the SourceSystemID property is the phrase "NBOSourceSystem:LOCAL".  Its prefixing the value with association name.  When I attempt to persist this through the DomainDataControl, it attempts to update with the phrase "NBOSourceSystem:LOCAL", and not "LOCAL".  This is causing a FK Violation in my database, and the insert fails.

    What am I doing wrong?

    Thanks.

  16. AlexJ says:

    @wtfChris

    This sounds like a more general databinding problem, you simply need to figure out how to get the correct FK value IN and OUT and use that.

    It sounds like you are putting the wrong VALUE in the dropdown box in the first place?

    Why is the value "NBOSourceSystem:LOCAL" when it should be "LOCAL"?

    Probably because you are automatically binding or something, you might need to just change the binding expression.

    But this is not my area of expertise sorry.

    Alex

  17. wtfChris says:

    @AlexJ

    Thanks for the response.

    The values that are displayed in the dropdown are correct.  Its only when it binds the selecteditem to the assoication property (which in turn updates the FK property) that things go haywire.

    Can  you recommend a good resource for learning databinding.

    Thanks.

    • Chris
  18. What should "MyContainer" be?

    I’m not sure what the fully qualified name of the EntitySet that the target Entity belongs to is.

  19. AlexJ says:

    @Richard,

    MyContainer is usually the name of the strongly typed ObjectContext.

    Alex

  20. Manyam says:

    Hi,

    I am new to Entity Framework. I am not sure if my query related to the current topic.

    I have 3 entities County, City and Country_x_City. Lets say I dont have any data.

    Here country and City Id’s need to be inserted in Country_x_city

    I need to insert a record in country. (An Id will be generated)

    I need to insert a record in City. (An Id will be generated)

    I need to add these Id’s to Country_x_city table.

    All these should happen in a single transaction.

    How can I acheive this.

    My query is if i try to save data in entities how is the relation maintained. If we do it through Stored Procedures we will capture the identity column and place it in corresponding tables. How is it done in Entity FrameWork.

    Thanks in Advance

  21. AlexJ says:

    @Manyam,

    The EF is pretty clever about this sort of thing. It does things in the correct order, but only if it knows that the Ids of both Country and City are Identity columns.

    You tell the EF this via the SSDL (Storage Model) with a additional attribute on the Property (aka column) definition, namely: StoreGeneratedPattern=Identity

    With this in place the EF, will retrieve the new Ids (using @@Identity in SQL Server) and use them in the insert into the Country_x_city table.

    Hope this helps

    Alex