Dynamic Data – GUIDs/uniqueidentifiers as primary keys

I primarily use a GUID (uniqueidentifier) as my primary key data type. You never know when you need to synchronize your data, and I’d rather be prepared by using a GUID as a unique global identifier. Anyhow, it seems that the brand new Dynamic Data (released today as part of .net 3.5 Service Pack 1) does not really support a GUID as primary key.

The Entity Framework does not pass enough meta data to the Dynamic Data. When you use DD as a simple scaffold and add a record you will be greated with a textbox to enter the primary key for your record. Not sure about you, but I don’t have a GUIDGEN method in my brain, therefore I was looking for a solution.

There some talk of adding an attribute to the column in an inherited partial class in the forum, but that isn’t an option for large numbers of tables. So I though I’d come up with a simple solution with a little help of some jQuery goodness. (I’ve been doing a lot of jQuery lately and it’s I’m really starting to enjoy Javascript!)

My idea was to allow me to create a guid via a context menu in my dynamics data project. Like this:

You can implement the solution in your app in less than 5 minutes. You need to download

Then open your dynamic data project. You need to change 2 files:

1. Open Site.Master.cs and override the OnLoad method.

public partial class Site : System.Web.UI.MasterPage
{
    protected override void OnLoad(EventArgs e)
    {
        if (Request.QueryString["genguid"] != null)
        {
            Response.Clear();
            Response.Write(Guid.NewGuid());
            Response.End();
        }
        
        base.OnLoad(e);
    }
}

2. Then go to Site.master and insert the following directly after .

<!-- Guid Fix -->
<script src="/js/jquery-1.2.6.pack.js" type="text/javascript"></script>
<script src="/js/jquery.listen-1.0.3-min.js" type="text/javascript"></script>
<script src="/js/jquery.contextmenu.r2.js" type="text/javascript"></script>
<script language="javascript" type="text/javascript">
<!--
    $(document).ready(function() {
        // add a sticky listener to the click 
        // event of all input elements
        $.listen('click', 'input', function() {
            // add a ctx menu to each input box that 
            // creates a guid in the textbox
            $('input').contextMenu('ctxGuid',
            { bindings: { 'createGuid': 
                function(t) { genGuid(t); } } });    
        });
    });

    function genGuid(t) {
        $.ajax({
            url: location.href,
            type: 'GET',
            data: { genguid: 1 },
            dataType: 'text',
            timeout: 10000,
            error: function(XMLHttpRequest, textStatus, errorThrown) {
                alert('Error fetching guid: ' + textStatus + '/' + errorThrown);
            },
            success: function(guid) {
                $('#' + t.id).val(guid);
            }
        });
    }

-->
</script>

<div class="contextMenu" id="ctxGuid" style="display:none;">
    <ul>
        <li id="createGuid">Create GUID</li>
    </ul>
</div>
<!-- End Fix -->

Voila, you can now right-click on the primary key input textbox (or any input box for that matter) and let the server create a guid.

Hope it helps!

Advertisements

10 thoughts on “Dynamic Data – GUIDs/uniqueidentifiers as primary keys

  1. Keep in mind that using GUIDs as primary keys can, as they are not created in a sortable order, quickly lead to index fragmentation, especially affecting performance when the primary key is also the clustered index.

    A better solution than having to defragment or rebuild your indexes very often is to use SQL Server’s NEWSEQUENTIALID() instead of Guid.NewGuid() because it guarantees that a new GUID is always greater than one created by a previous call to this method on the same server.

  2. Hi Dave,

    you are right. I mentioned this in my sync framework post (https://alexduggleby.com/2007/12/16/sync-framework-tutorial-part-3-preparing-the-server-database/) that also points to a good explanation of the impacts in this article: http://www.sql-server-performance.com/articles/per/guid_performance_p1.aspx

    It is of course possible to extend my solution to call out to the database (slowing things down just a tick) and use NEWSEQUENTIALID.

    One other thing to remember when using NEWSEQUENTIALID is that the algorithm used is not the same as in NEWID (obviously, because they need to be in sequence). Details can be found here: http://www.yafla.com/dennisforbes/Sequential-GUIDs-in-SQL-Server/Sequential-GUIDs-in-SQL-Server.html
    What that means is that once you decide to use one algorithm (aka NEWSEQUENTIALID) you should never go back to the other. Otherwise you risk collisions. This means you shouldn’t use Guid.NewGuid() anymore, because it uses the NEWID version.

    There is a Windows API call you can make to get your sequential id in .NET aswell, but I don’t think it’s as easy as Guid.NewGuid yet.

    Basically you need to make a conscious decision about whether to use the sequential or non sequential version.

    Thanks for the comment!

  3. Hi Alex,

    (Sorry my bad english)
    I am Brazilian and work with disconected application. In our current application, we used COMPOSITE PRIMARY KEYS.

    We are a new application and think in GUID. But NEWSEQUENTIALID not is supported to SQL Server COmpact Edition. Ok, ok, run SQL Server in clientes stations. But and Mobile Applications?

    How can allow INSERTS offline with GUID NEWSEQUENTIALID in SQL Server Compact Edition? How can generate PK´s???

    Very good your blog!

    Thank´s

    []´s

  4. Hi,

    let me see if I understand this correctly. You are using newsequentialid on the server side and trying to insert records on a compact framework device. If that is right, then you have a bit of a problem situation.

    On Windows Systems (not Mobile) you can PInvoke out to the UUIDCreateSequential method (http://www.pinvoke.net/default.aspx/rpcrt4/UuidCreateSequential.html) but this is not available on Windows Mobile (as far as I know). Therefore this is not possible.

    The only solution I can think of is if you really need to use sequential Guids, is to leave the ids empty on the client side and update them after you synchronize with the server. This would mean a bit of manual work.

    Hope that helps,
    Alex

  5. You should just update the data model StoreGeneratedPattern=”Computed” // EF
    IsDbGenerated=”true” // L2S
    and let the DB generate the GUID and it does Identity.

  6. Using a GUID PK only fragments the table if you put a clustered index on the arbitrary key. Most tables shouldn’t be physically sorted by their arbitrary identifier anyway (perhaps an order table? with a reader friendly order #?). The problem isn’t the uniqueidentifier, it’s user error in not understanding clustered indices and the purpose of arbitrary primary keys.

  7. I have an issue where unique identifier fields are not shown in any of the forms. Any idea how to fix that?

  8. Great work around alex! I needed to quickly seed some reference data via some cheap labor and this was a fine work around.

    As for GUIDs versus identity versus sequential guid … just be smart about where you use them, if you value disconnected object hierarchy creation and don’t anticipate tables over ~1-2 million rows then GUID is fine. Place the clustered index on your most searched attribute and you are good to go. If you anticipate 100’s of millions of rows, if the application tier is just firing inserts (like a web hit tracking system for example) and the data will likely be rolled up and warehoused anyway then identity/bigint is fine.

    Personally I use uniqueidentifier on pretty much everything under 2 million records. Lets you easily migrate data back and forth, create easy clean up scripts for your QA environments, makes application tier code clean and consistent, reduces db trips.

  9. Thanks for this workarround. One point to note: ie will cache the creation of the guid sometimes and consequent upon that you will get an error while trying to commit. If somebody run into this issue – set the ajax cache option to false.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s