How to Use MVC.Net on the Dynamics NAV Database Structure

Please note that all DMS news and blog content has been moved to the main website at www.dynms.com for a categorized view.

All utilities and downloads are available at www.dmsiworks.com

For the example files associated with this tutorial, visit the Mibuso downloads.

So you’ve been asked to make a web portal for some Dynamics NAV data.  You also have a desire to stay current and would like to take advantage of some of the newer web development frameworks out there.  You’ve seen, or heard great things about how quickly you can get CRUD with some of the popular MVC frameworks out there like pages working with MVC.Net or Ruby on Rails.

MVC.Net and Ruby on Rails both would prefer that you have control over the database, and that the database follow industry standard naming conventions and patterns (the phrase “Convention over Configuration” is mentioned many places).  This allows you to make websites very quickly.  As a Dynamics NAV programmer you already know that the table names in NAV cause problems with these frameworks, specifically the prefixing of tables with “CompanyName$”.
  
This tutorial will show you how you can use the Entity Framework in an MVC.Net web application with Dynamics NAV.

Let’s start with a table that won’t cause problems.  The “Company” table in NAV is not company specific, and presents no problems using the "Table" data annotation attribute.

Happiness and butterflies all around, you get to your next table and now you want “Contact” data.  If you’ve got two companies, that now becomes two different SQL server tables.  “CRONUS Canada, Inc_$Contact” and “AnotherCompany$Contact”.

You could hard code the companies, but that will create deployment problems.  You could then get around the multiple companies by inheriting different classes, but that seems a waste.

Another possibility is to use a different table attribute that allows you to change companies.  In the below example we’re using a new table attribute class called “DynamicsNavTable”, that allows you to specify the NAV table portion name.

The company portion is then handled via the attribute.  They key parts to this attribute are as follows:

You’ll notice that we are deriving from the same "TableAttribute" that we used in the “Company” table example, where in our constructor we’re setting the actual table name to use ( via the GetCompanyTableName function described below ).

The current company is then determined by the current users chosen company, which if not yet defined, will bring it in from our settings.

We’ll show how the current company can change on the fly a bit further down in this example, but for the moment let’s just go with a configurable company name in the web.config.

 
And let’s set it as our default in the web application.

So far so good, so let’s use the default MVC.Net scaffold around the "Contact" model and let’s see what we have so far.   At this point all we really have to support table names is the special table helper data annotation attribute,  with a supporting configuration file. 
 

So at this point you have the ability to configure the company in the web.config, but that still doesn’t let you change the model (yet).  A complicating factor is the behavior of MVC.Net about how the database model gets compiled from your model.  Specifically, it will compile the model once the first time it’s used and after that it’s cached.  While this is great for performance, you seem to be stuck with that compiled table name of “CRONUS Canada, Inc_$Contact”, because it would have read in the model, and the table data annotation once, thus even if you were to create new contexts passing in the attributes that you wanted it would still use the previously compiled version.

The easy solution is to use the model when the DbContext is being constructed.  What we’ll do is make a base DynamicsNavDataContext, derived from DbContext, and then use that for our future implementations.

This approach to changing the table mapping at runtime is based off the example provided by bennymichielson.  In our solution specific data context we then just have create a helper method to create the appropriate DbModelBuilder.

With the last piece of the puzzle just to get the appropriate table name in the BuildModel() method.
   

So once you have this code established, an implementation looks very simple.  Your controller will look something like this (in our example you’ll notice we allow changing the company on the fly with as a URL parameter, that line isn’t necessary):

To take it a step further and allow changing the company per user ‘session’ instead of just on the URL we just store the current company per user.

Provide a mechanism to change the current company:

And then in our function that returns the current company we just return the current users selected company, where if not specified grabs the default out of our settings file.

 
We can then whip up a Razor page to allow changing the company.

We take a peek at our “Contacts” view while in “Another Company” (there are only two contacts)

Use our company changer to get back to "CRONUS Canada, Inc."

And we now see "CRONUS Canada, Inc." contacts.

Alternatively since we added the ability to change the company in the Index controller action we could change companies on the fly in the URL like this:

To summarize we’ve established how to use MVC.Net and connect a data entity that you can use with LINQ, to work with Dynamics NAV table naming convention, while providing the option to dynamically change the company name in a configuration file, on the URL, and with a “change” option.

It was a little bit of work, however you only need to build the supporting code once, and then you can re-use it in future projects.   We’ve also provided the code to support this as a download (see link above).   If you have a more complicated model you will probably want to cache the DBCompiledModel that is generated instead of re-building it with each DBContext.

Tim Dimsdale
Dynamic Manufacturing Solutions

CodeProject