CRM Development

Solutionist has designed the architecture for a new CRM system for use in the medical imaging industry.

Thursday, 29 December 2011

Primary Keys in Entity Framework and Sync Framework

Hold on - we're going for a walk on the wild side! It will take a few minutes to read - but it's a thriller...

This is a summary of what I've discovered after a bit of research - hopefully it will help other developers/architects out.

As part of a project I'm looking at creating a SQL server replicated architecture... and need to avoid clashes in the PK. The icing on the cake is that we're using Entity Framework heavily in the project for data access.

Exhibit 1 ... http://msdn.microsoft.com/en-us/library/bb726011.aspx

This article gives a few options about choosing a suitable Primary key in replication scenarios:

1. Using a GUID
2. Pk that includes a node ID
3. Natural keys

OK.... starting from the bottom up...

  • Natural Keys won't work for our project. Full stop
  • A PK that includes a Node ID - means adding a column and giving each client  a Node ID. Not the end of the world, but... doesn't feel right...
  • GUID... sounds good, or is it?

Enter
Exhibit 2 - http://leedumond.com/blog/using-a-guid-as-an-entitykey-in-entity-framework-4/
Yes, it's all true... I tried it and had to hack the EF model XML to include the correct attribute to get it to work. And, Yes, doing an update loses the change.
Yes, it's a feature and yes, it's not pleasant - it would mean after every update hacking the XML to find all the relevant GUID PKs and adding in the attribute. I'm sure we could automate it by some clever searching and replacing, but it's not right.

Also, Enter
Exhibits 3, 4, 5 and 6
http://www.eggheadcafe.com/tutorials/sql-server/d662b371-ed27-481c-aee0-ebe7cf2d9fad/why-guids-are-not-a-good-idea-for-sql-server-primary-keys.aspx

http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html

http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/

http://sqlblogcasts.com/blogs/martinbell/archive/2009/05/25/GUID-Fragmentation-in-SQL-Server.aspx


From this set of links you start to see that GUIDs are good, but come with a number of caveats.

Also using newsequentialid() - doesn't necessarily generate sequential GUIDs - I tried this. If you do a number of inserts close together then you do get them, but not always. 
So, you then can potentially have the performance issues.
Also, the space issue is relevant for us - since we're looking to replicate to SQL server compact.

Alternatives?

First off,
well we could use a normal ID (int) column and change the seed value for each client.
E.g. The Server has a seed of 10,000000
Client 1 has a seed of 20, 000000,
Client 2 has a seed of 30, 000000 etc

So, each client would be able to make 9,999,999 inserts before a conflict and we'd have a limit of 214(X2) users because we could have negative numbers as well.

Lets just do some maths....
The max value of an int is +/-2,147,483647. 
If you're really worried about conflicts you could go for using a bigint as the ID column, then you could have something like 9223327036850 clients! The seeds could then be something like 100,000000, 200000000, 300000000, etc so 99,999,999 inserts before a conflict - which should be enough for a lifetime - e.g. one insert a second would take you 1157 days. 100 inserts a day would take over 2500 years.
If you were using the the smaller int then 100 inserts a day would last over 250 years!

That means changing the seed on each client appropriately.

Now, there are ways of doing this:
http://www.techrepublic.com/blog/datacenter/how-do-i-reseed-a-sql-server-identity-column/406
but it seems there a some gotchas that you have to be aware of:
http://geekswithblogs.net/argot/archive/2009/10/18/sync-framework-common-practise-of-ado.net.aspx


Basically, you have to store and remember seed values before/after synchronisation.

You'd keep a list of seed values for each client. So any time you get a  new client the first sync would also download its seed value.

This solution has a certain appeal, because:

a. it leaves the PK alone as a single ID column
b. The replication mechanism takes care of the seeding/reseeding.
c. Entity Framework designer won't throw any wobblies.

Next alternative... 

Go back to a compound PK, ID plus a Node ID.
a. Entity Framework should cope with this.
b. Replication mechanism should be a bit simpler.
But... it means infecting the database with the replication details i.e. the Node ID and artificially creating a more complex PK. So, you can't just go
select * from Users where ID = 10.

Another thought....


This is horrible I think, but has a certain "something" about it!

Leave the PKs alone completely - simple ID column with an int.
Then code for the PK conflicts in the sync framework - as it will detect PK conflicts. 
So, we write logic that will do something like:
- determine of the inserted record exists on the server 
- if not, then add it (generating a new record/PK) then remove it from the client and
- resync the client to pull down the "new" record but with the updated PK.

You'd have to write this sort of thing to cover various scenarios and it may/would be table-specific - but what if we replicating a lot of tables?


Summing up...


We're looking at  either the compound PK or the different seed values.
We're going to try some stuff out - probably the different seed values first as that leaves the EF model alone completely - and I'll try and report back how it went.

Thursday, 8 September 2011

Entity Framework Tip #1 - Using MergeOption to get latest entities

Hopefully this is the first of various Entity Framework tips that I've gleaned along the way.
They'll serve as reminders for me and if they can help other people, they will have served their purpose.


Ok, what's wrong with this?

(We're using STEs because we're throwing things across the network). 
I was doing some testing and had code that looked like this.

 DBEntities context1 = new DBEntities();
 
 DBEntities context2 = new DBEntities();
 Site newSite = new Site();
 newSite.SiteName = "QAZ";
 
 context1.Sites.AddObject(newSite);
 context1.ApplyChanges("Sites", newSite);
 context1.SaveChanges();
 
 Site site2 = context2.Sites.Where(s => s.ID == newSite.ID).FirstOrDefault();
 
 // update the site...
 site2.ChangeTracker.ChangeTrackingEnabled = true; // Because they're STEs
 site2.SiteName = "QAZ QAZ";
 context2.ApplyChanges("Sites", site2);
 context2.SaveChanges();
 
 
 // I thought I'd use the first context to query for the updated site...
 
 var query = context1.Sites.Where(s => s.ID == newSite.ID);
 Site site3 = query.FirstOrDefault();

What's the SiteName of site3?

No, it's not "QAZ QAZ" - it's "QAZ"

Why is that? 

Because the site already exists in context1 - so even though a database query is actually executed, EF will not overwrite what's in context1 because its cache already contains an entity with that EntityKey.

How to make sure that I actually get the latest values back?

Set the MergeOption on the objectSet before doing the call - as follows...
context1.Sites.MergeOption = MergeOption.OverwriteChanges;

The default option is AppendOnly which won't update the context if it finds an entity with the
same entityKey present.

Another tip from the twighlight world of entity framework...

One thing I've yet to investigate is how delayed execution would affect MergOption settings.
So, if I reset the MergeOption after the query to AppendOnly and then do further queries before finally executing the query - would the different MergeOptions be applied at the right time... hmmm...


 

Tuesday, 7 June 2011

.NET IoC - part 3 (Spring)

It's been some time, but this is continuing my previous posts about .NET IoC.

As I'm familiar with Spring from the Java world, I thought I'd transfer that knowledge across to .NET. It turned out to be less painful than I expected.

I was building a service-based project and used spring on both the client and server sides of the divide. For this post, I'll initially stick to it's use on the client side, but server-side has a very similar set-up. Maybe I'll come to full WCF integration later on.

For basic IoC you just need the Spring.Core.dll from the distribution - add this as a reference to your project.

Initially I'm following the KISS principle, so I created a basic application context file - spring.config.xml and put that at the top level of my project.

Basic format is as follows:

<?xml version="1.0" encoding="utf-8" ?>
<objects xmlns="http://www.springframework.net">

<object id="Object1"
type="<Assembly name>.namespace.classname1, <assembly name>" singleton="false">

<property name="Property1"  ref="Object2" />
</object>


<object id="Object2"
type="<Assembly name>.namespace.classname2, <assembly name>" singleton="false">

</object>

</objects>


The singleton="false" will ensure that Spring gives you a new instance every time you request the particular object.

As a minimum in your code, you'll need to create an application context somewhere.
I've put it in a singleton Locator class as follows:

private static IApplicationContext _context = null;
_context = new XmlApplicationContext("spring-config.xml");

where spring-config.xml is the name of your config file. This is sitting at the top-level of the project.

Then you'll want a method to grab hold of an object instance as follows:

public object getObjectInstance(string name)
{
  return _context.GetObject(name);
}

or, if you need to pass arguments in to the constructor...

public object getObjectInstance(string name, object[] args)
{
  return _context.GetObject(name, args);
}

In both cases "name" is the object's ID in the configuration file.
Passing arguments in like this is "OK", but can be a pain, since there's no compile time checking - but we're staying simple.

At some point you could call:

Locator.Instance.getObjectInstance("Object1");


This will:
  • create an instance of Object1
  • create an instance of Object2
  • set Property1 on Object1 to be Object2
  • return you a "ready-to-go" instance of Object1.
 These are your absolute basics. But hopefully this will put you on the road.

Wednesday, 12 January 2011

.Net IoC - part 2 (StructureMap)

It's been some time since I've last posted anything up, but I thought I'd put up a bit more about my
working with IoC in .NET.

In a previous post I mentioned that I'd come across Spring.NET, Unity and StructureMap
I wanted something that I could pick up easily and which focused on IoC. 


Starting to using it fairly simple. 
  1. You reference the structuremap dll
  2. Add something looking like this into your code... I wrapped it up in a Singleton to give me more flexibility.. 
public sealed class Locator     {
        private static volatile Locator _instance = null;         private static object syncRoot = new Object();


        private static Container objectFactory = null; 
        private Locator()
        {
            objectFactory = new Container(
                 x =>
                 {

                    x.For<myservice>().Use<myserviceclient>();                        </myserviceclient></myservice>
 
                     x.For<ihelper>().Use<realhelper>();
  
                     x.For<iinterface1>().Use<implementation1>()
                         .Setter().IsTheDefault();
                 });
        }
 
        public static Locator Instance
        {
            get 
            {         
                if (_instance == null) 
                {            
                    lock (syncRoot)             
                    {               
                        if (_instance == null)                   
                            _instance = new Locator();            
                    }         
                }         
                return _instance;      
            }
        }
 
        public object getObjectInstance()
        {

            return objectFactory.GetInstance();
        }
 

        public object getObjectInstance(Dictionary items)
        {

            var args = new ExplicitArguments();
 
            foreach (var item in items)
            {
                //args.Set(item);
                args.SetArg(item.Key, item.Value);
            }
            return objectFactory.GetInstance(args);
        }


In code I'm calling:
Locator.Instance.getObjectInstance()


The second method allow you to pass in constructor parameters.


All this means I can remove references to StructureMap from my Code and, if need be,
swap out IoC containters without affecting the rest of the code. In fact, this happened eventually.

StructureMap looked a good candidate, the fluent interface is nice and there's no XML if you don't want it! But, I did find the that the lack of decent documentation was an issue.
There was a lot of information - but scattered around. I didn't want to spent ages hunting stuff down.


Unity, I'm afraid, seemed over complicated. I'm still getting my head round other .Net stuff and I wanted to minimise the amount to learn. 


That left Spring.... which, all being well, I'll cover in part 3.