Thursday, 16 June 2011

MVC hacking for foreign keys

I have an unusual scenario for MVC entity framework 4. Firstly I have a table with a primary key (not used for foreign keys) and a unique constraint (which is used for foreign keys). Entity framework does not recognise unique constraints and therefore does not let you use them for foreign keys. The fix is to open the crm.edmx in an xml editor (you might be able to do it from the designer!) and then change the key to point to your unique field and not to your primary key. This allows EF4 to point foreign keys to your field. If you had already created the keys and then re-generated, you might have found that the associations have lost their referential settings which you will need to restore. Obviously if you regenerate, this change might be undone.
The second issue is that although one of my table columns has a theoretical foreign key to another table, the key does not exist which allows us to test the scenario where an item is orphaned (the foreign key does not exist on the customer system), therefore EF does not provide all the wiring up for a drop-down list on my view to edit this 'foreign' field. The answer is to use a ViewModel which is simply a class that contains your entity and any other supporting data, in my case a list of SelectItem which is used in the DropDown list. This is populated in the constructor. Then rather than creating the view against the entity, you specify the view model instead and link fields to Model.EntityPropertyName.Name rather than just Model.Name. This also means you can simply link your drop down to the list of SelectItem in the view model which does not exist in the entity itself:

public class AccountDetailViewModel
public AccountDetailViewModel()
AccountDetail = new AccountDetail();

public AccountDetailViewModel(AccountDetail source)
AccountDetail = source;
// Get list of 'Account' entities from db and add number and rowid to the list
var accounts = new Entities().Accounts;
Accounts = new List();
foreach (var acc in accounts)
Accounts.Add(new SelectListItem{ Text= acc.AccountNumber, Value= acc.RowId.ToString()});

public AccountDetail AccountDetail { get; set;}
public List Accounts { get; set; }

And then in the view:

<%: Html.DropDownListFor(model => model.AccountDetail.PrimeAccountInternalRefNo, Model.Accounts, Strings.DropDownListSelectItem)%>
<%: Html.ValidationMessageFor(model => model.AccountDetail.PrimeAccountInternalRefNo)%>
Post a Comment