MS Dynamics365 - generate custom serial number, ensure uniqueness

Question

I am developing a Dynamics365 CRM based solution, and for a custom entity we've defined, we need to create custom serial numbers.

For various business reasons, neither a GUID nor a sequential numbering scheme will do - business insists on a format of

99-9999-9999

where each group of numbers is basically a random group of numbers.

Creating those in a plugin is easy - but how do I ensure uniqueness?? I'm a C#/SQL Server developer at heart, and in SQL Server / T-SQL, I'd just create an unique index on my "Entity" table on this column. Checking if a newly created number exists would be easy-peasy - just do an

IF EXISTS (SELECT * FROM dbo.MyEntity WHERE SerialNum = @SerialNumGenerated)

check, and since that single column is indexed and NOT NULL, it would be quite fast, too.

But how do I do the same (at least as "same" as possible) in Dynamics365? How can I programmatically check (inside my C# plugin) if a newly created "serial number" has not yet been used - and do so it's quick enough not to slow down the save process too much? Can I somehow also "index" that property on my custom entity and do something similar to the IF EXISTS() check in T-SQL ?

Thanks for any hints or pointers!

Answer 1

You want to do this numbering within a pre-operation plugin. This is the only way to completely guarentee uniqueness, it is quite performant, and the number will immediately exist upon creation of the record.

1) Generate the characters
You can use whatever method you want, as a database developer I usually just default to what I am familiar with, a substring of Guid.NewGuid().ToString();

2) Check uniqueness

    QueryExpression query = new QueryExpression("[prefix_yourentityname]")
    query.Criteria.AddCondition("prefix_yourfieldname", ConditionOperator.Equal, [IDNumber]);
    query.TopCount = 1;
    bool isUnique = Service.RetrieveMultiple(query).Entities.Count = 0;

3) Set the ID number on the target so that it is saved with the transaction

Performance:
This check obviously results in a small read operation against the database but you don't have a better option. If you are using on-premise CRM you can add an index against this table including the ID field. Indexing the CRM database is supported by Microsoft.

If you are using CRM online, the only thing you can do is add your ID field to the Quick Find View "View Columns" and "Search Columns" for this entity. The CRM application generates an index on each table based on the configuration within the Quick Find view, so adding your ID column to this view will result in that field being added to the index.