How to create a non-nullable CRM basic type like int or double?

Question

When we create a new field in Microsoft Dynamics CRM, it will create it in the database as NULL allowed. How can you create a field in CRM that is NON-NULL in the backing database, like they do use for some of their own fields?

Selecting the Mandatory flag doesn't make it NON-NULL.

Reason for asking: when programming against CRM using LINQ, many queries become relatively difficult when you have to take care of nullables, and some even become impossible, like aggregate queries (try Max(), it will give a InvalidCastException when the underlying db type is nullable).

score 2 · Answer 1 · edited May 23 '17 at 12:12

2

Sorry to say that there is simply no supported way of doing this ("supported" == "present in the SDK").

You can't even do anything at the SQL level without a lot of terrible hacking about (to remove the attribute and its dependencies, recreate it to disallow NULL then plumb it back in), which may not even work anyway.

I think the correct way to go is to overcome your LINQ issues. Is this not a solution?

edited May 23 '17 at 12:12

Community

1
1

answered Jun 15 '12 at 09:20

Greg Owens

3,878
1
18
42

For some reason, all those solutions still throw the same error. So, the problem is partially in the LINQ to CRM (FetchXML) translation, or v.v. Since I haven't managed to debug that part, I hoped I could fix it at the root. – Abel Jun 15 '12 at 12:41
Btw, you say "present in the SDK". Does that imply that it isn't possible to do from the CRM front-end either? – Abel Jun 15 '12 at 12:44
Correct - no way in the front end. Your only entry point in when you create the attribute and nothing there drives the NULLABLE property. – Greg Owens Jun 15 '12 at 13:19

score 1 · Accepted Answer · answered Jun 15 '12 at 19:41

Though not exactly what you're asking for, you can achieve a similar effect via the API. Write a plugin against the Create and Update in the pre-validation stage (to catch the null value before the database transaction). If the non-nullable attribute is ever null, throw an exception, which will cancel the transaction altogether.

If you register the plugin after records have already been created, then you'd just have to make sure to populate those pre-existing records with values.

public void Execute(IServiceProvider serviceProvider)
{
    IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));

    if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity)
    {
        var target = ((Entity)context.InputParameters["Target"]);
        if (target.LogicalName == Appointment.EntityLogicalName)
        {
            IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
            IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);
            var entity = target.ToEntity<Xrm.Appointment>();

            if (entity.Description == null && entity.Attributes.Contains("description"))
            {
                throw new Microsoft.Xrm.Sdk.SaveChangesException("Fill in the field, buddy.");
            }
        }
    }
}

Just realized this code is targeting a CRM 2011 instance, but I believe it should be portable to CRM 4. — Peter Majeed, Jun 15 '12 at 19:43
This is certainly something to look for, however, I don't think it will make the auto-creation of the C# abstraction layer create non-nullable types, because the underlying type in the database is still nullable. — Abel, Jun 16 '12 at 12:53
@Abel: Ah, I see - I think in that case, I partly misunderstood your question. In CRM 2011, `Max()` isn't even supported (but using fetchXml gets you the desired results while ignoring nulls). — Peter Majeed, Jun 16 '12 at 17:25

How to create a non-nullable CRM basic type like int or double?

2 Answers2