This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hi, I have 2 tables: ApplicantDetails and Titles Foreign key on ApplicantDetails('titleCode' ) references titles(code) TitleCode and Code are Varchar I have a View which is based upon the Model: ApplicantDetails. This View has a dropdown select list @html.DropDownListFor() which contains the code and description from the Titles table i.e. Mr => Mister etc, plus 2 other text items: @Html.TextBoxFor(), named Surname and Forenames The controller has the method: updateForm(ApplicantDetails appDets) called by the @Ajax.BeginForm in the View In the method I am able to retrieve the forename and surnames data using: appDets.surname and appDets.forename, however, I'm unable to retrieve the TitleCode - Debug show the TitleCode to be null and {Mindscape.LightSpeed.EntityHolder<myModels>.Title} - which seems to be the Titles table???? (guess) So I would like to know how I retrieve the TitleCode drop down list Code (i.e. Mr) from the model passed into the Controller method as I would expect the TitleCode to be available seeing that the other are fields are. I'm not sure if this a Lightspeed issue or to do with passing a value from a Select List, any help would be appreciated. Thanks |
|
|
I'm not sure from your post exactly where the issue is occurring. Is it: 1. You are loading an ApplicantDetails from the database and trying to set up the view to display this ApplicantDetails. The appdets.Forename and appdets.Surname fields are correct, but appdets.TitleCode is null? Or: 2. You have a form on which the user fills out their applicant details. When the user submits this form, you are copying the data from the form into an entity (using the default binder? or the LightSpeed binder?). However, the value copied into TitleCode is null. I think it is scenario 1, but not sure. If it is scenario 1, I'm not sure why TitleCode is not being loaded from the database. My guess is that it might be a type conversion issue e.g. the database TitleCode column is VARCHAR but the LightSpeed _titleCodeId field is Nullable<int>. Could you post the ApplicantDetails and Title classes and the corresponding database CREATE TABLE statements? It would also be useful to see the value of the database TitleCode column for a row that is not loading correctly. Notice by the way that foreign key fields in LightSpeed always have the suffix Id, so there needs to be a column mapping from _titleCodeId to TitleCode. I think that is in place because otherwise you should get a 'no such column TitleCodeId' error from the database, but it may be worth double-checking. |
|
|
Hi Ivan, unfortunately it is scenario 2 The view has an Ajax Form with TitleCode drop list and forename surname field. I tightly bind the Vire to the Model: @model TensMVC3.Models.ApplicatnDetailsViewModel
where ApplicatnDetailsViewModel =
public class ApplicationDetailsViewModel { }
Press submit button in form and the method: UpdateForm in the Home Controller is called: public string UpdateForm(ApplicationDetailsViewModel appDets) { return myDets.ApplicatDetails.titleCode + " " + myDets.ApplicatDetails.Forenames + " " +myDets.ApplicatDetails.Surname; }
Forenames and Surname a return but titleCode is null - TitleCodeId = 0, does the Foreign Key need to be an int, as mine are Varchar? Thanks
Doug
|
|
|
ApplicationDetailsViewModel:
namespace TensMVC3.Models
{ public class ApplicantDetailsViewModel
{ public ApplicantDetail applicantDetails { get; set; }
} }
|
|
|
The foreign key can certainly be a VARCHAR, but I'm a bit confused about the relationship between TitleCodeId (which appears to be an Int32), TitleCode, the Title entity and the data you want to display. Could you post the ApplicantDetails and Title classes, and the CREATE TABLE statements for the corresponding tables? Which field/column in which entity/table is the one in the SELECT list? Also, how is myDets.ApplicantDetails (the ApplicantDetails entity) getting populated? Is it being populated from the form or from the database? You imply it is being populated from the form in which case you need to check whether form is setting the TitleCode property correctly -- this would be outside the scope of LightSpeed unless you are using the LightSpeed model binder. |
|
|
Ok, Done some further investigation: Converted my Foreign Key field to be Int, and problem persists. Removed Foreign Key reference and I can now view the Select Box value in the Controller method - so it would seem that the Foreign Key is the problem. Help!!!! |
|
|
Ok, after some more tests I've found that Lightspeed only allows you to use Primary Key fields in Foreign Keys:
So this doesn't work - Scenaion 1 Database Tables: Person Title ------- ----- PersonId (PK) TitleId (PK) Name (varchar) Code (varchar) (Unique Index on this field) TitleCode (varchar) (Indexed) Description (varchar)
Foreign Key: person.TitleCode -> Title.Code Cannot retrieve the varchar Code in call to Home controller method.
This does work - Scenario 2 Database Tables: Person Title ------- ----- PersonId (PK) TitleId (PK) Name (varchar) Code (varchar) (Unique Index on this field) TitleCode (varchar) (Indexed) Description (varchar) TitleId
Foreign Key: person.TitleId -> Title.TitleId Retrieves the TitleId in call to Home controller method.
So it would seem that only Primary Key fields can be used in a Foreign Key reference within Lightspeed. The first table/database design scenario is an often followed practice as codes can be more meaningful than the primary key id in certain circumstances. It also allows for more complex reference tables: i.e. Reference_Codes - Reference_Code_Domains - this scenario allows for a single reference table to contain all reference codes for the whole system.
If my assumption is correct about Lightspeed, this could cause a large amount of database redesign of a system that has already been developed and is being moved to lightspeed for an n-tier architecture solution. If my assumption is correct, is lightspeed 4 able to handle this scenario somehow, and if not will future releases be able to handle this scenario?
Thanks |
|
|
That is correct. The foreign key field always refers to the the ID (primary key) of the target entity. To traverse a foreign key that refers to a non-ID field in the target entity, write a custom property in the partial class that performs a query: using Mindscape.LightSpeed.Linq; partial class ApplicationDetails { |
|
|
Hi Ivan,
That's fine, I can do that. Is it possible to add this functionality to a future release of Lightspeed, rather than having to code around the issue?
Regards
Doug |
|
|
Hi Doug, It's on the backlog, but it's relatively hard for us to do and there are currently relatively few customers asking for it, so it will depend on more customers letting us know they want it. |
|