How can I map foreign key field to the associated table

Hi There

I am a beginner in Lightspeed. I create my model from our real database. I find there is a problem when I tried to map the foreign key field to the right one since the system automatically create a one with the sufix id of the referring table. Could you let me know how can I reset the mapping from Table JobSpType to the right one: JobType field in JobSp? I have attached a screen shot of part the diagram. Many Thanks!

Are you asking how to have the JobSpType association based off the JobType column? If so, the way to do this is to select the association arrow, go to the Properties grid, and set the Column Name to JobType. This will still create a JobSpTypeId field, because LightSpeed requires the FK field to be the association name plus "Id", but it will map it to the JobType column.

Note that you will then no longer need the JobType property in the entity (because the JobType column is now mapped to the JobSpTypeId field) and you should delete it.

For more info about database mapping see http://www.mindscapehq.com/documentation/lightspeed/Controlling-the-Database-Mapping and in particular http://www.mindscapehq.com/documentation/lightspeed/Controlling-the-Database-Mapping/Overriding-the-Default-Mapping.

An alternative approach is to use the Key Property Reference option to override LightSpeed's automatic backing field, but this is recommended only for specialised scenarios such as when the FK is a subfield of a composite key. See http://www.mindscapehq.com/documentation/lightspeed/Working-with-Legacy-Databases/Using-Composite-Keys for more information about this.

If I've misunderstood the problem, let me know!

It's works! Thank you so much for that. However when I run the project I got another error:

Unable to materialize field [JobSpTypeId] on type [CallManagementApp.JobSp]. Check your table has an Id column and that your mappings are correct. See inner exception for details.

I only do a quite simple call:

            var jobsp = UnitOfWork.JobSps.Single(j => j.Id == jobID);

it crashed and gave the excetion. Any idea to fix it?

Per the error message, "see inner exception for details." This error usually means that the data type is wrong (for example, the field is an int but the database column is a string; unlikely in your case), or that the database column contains NULLs but the field is non-nullable. The inner exception should give you (or us!) a better idea.

Thanks for your prompt reply! I found the problem, you are right. the mapping field in JobSpType is not actually the key filed:JobSPTypesID to join the table JobSp Jobtype, it use another filed: Abbreviation (string) instead. How can I solve this problem - join the table not use the key field - since I really don't want to change the real database?

Select j.JobId, jt.Description as JobType,
from JobSP j inner join JobSPTypes jt on j.JobType = jt.Abbreviation where j.JobId = 12115

You would need to write an explicit query instead of using an association. LightSpeed associations are always to the ID, never to a 'normal' field.

You can encapsulate the query in a property or method in the partial class e.g.

partial class Job {
  public JobType JobType {
    get {
      return this.UnitOfWork.FindOne<JobType>(Entity.Attribute("Abbreviation") == this.JobTypeAbbreviation);
    }
  }
}

Hi ivan

Thanks for your reply. Sorry I am still a little confused. It's that meant I have to delete the association in model and create the partial classes outside the auto_generated code and manually set the reference and collections for both classes: JobSp and JobSpType? many Thanks!

Yes. You can't keep the association because it doesn't use the ID for lookup. Therefore you have to delete it out of the model [1].

But this leaves you with the problem of how to provide a friendly API for application code to get the JobType for a Job, or the collection of Jobs for a JobType. Because LightSpeed can't automate this for you, you have to implement these manually, which means creating a partial class outside the auto-generated code, and implementing the properties there. (Handy tip: to quickly create a partial class file, right-click the entity and choose Refactor > Create Partial Class.) In the Job partial class, implement a JobType property; in the JobType class, implement a Jobs property. (You can leave out either of these if you don't need it; LightSpeed requires associations to be bidirectional, but since yours is no longer a LightSpeed association, just a pair of queries, you're not obliged to stick to this. For example, if JobType is reference data and you don't need the JobType.Jobs collection, just don't implement it.)

[1] Actually, to be exact, what's important is that it doesn't appear in the generated code. So if you wanted to keep the arrow on the diagram to help readers visualise the relationship, you could do, provided you set its Generation option to None. But this could be confusing when you come back to it later!

It's seems work! Thank you very much for your detail information.