Why is a varchar(10) field in db being translated to an int field in the generated code

The result is when a query is performed, the following exception is thrown:

Unable to materialize field [StopId] on type [LbsServices.DataAccessLayer.Entities.StopTimes]. Check your table has an Id column and that your mappings are correct. See inner exception for details.

I have to manually modify this property to make it a string for it to work. Field name is StopId.

TIA.

My guess would be that StopId is a foreign key, and that the Id type of the target table is int. When the LightSpeed designer sees a foreign key, it maps that to an association rather than a separate field, and the field type of the association is always assumed to be the Id type of the associated entity.

You are correct in that this field is a foreign key. However field is not an int. Is there a way I can override this default behavior so I do not manually change generated code every time?

The generated type of the field is the identity type of the associated entity. If the foreign key field is meant to be a string, the identity type of the associated entity should also be a string.

Change the identity type of the associated entity to a string. This will automatically change the foreign key field to a string.

If you run into any problems, please post a minimal repro (a .lsmodel file containing only the two associated entities) and we'll look into it.

You lost me here:

Change the identity type of the associated entity to a string. This will automatically change the foreign key field to a string.

The generated property being referenced as foreign is of type string. This constraint was generated in the Schedules table as follows:

CONSTRAINT [FK_Schedules_Subscriber] FOREIGN KEY
    ( [AgentName] ) REFERENCES [dbo].[Subscribers] ( [Name] )
                ON DELETE NO ACTION
                ON UPDATE NO ACTION 

while the Name field is of type varchar(30) created in the Subscribers table as follows:

[Name] [varchar](30) NOT NULL CONSTRAINT [NN_Unique_Subscribers] UNIQUE,

When I look at the Subscriber entity, it correctly has a Property called Name of type string. However, the Schedule entity has an AgentNameId property of type int with the field attributed to the AgentName column as follows:

 [Column("AgentName")]
    private int _agentNameId;

I looked at the properties of the association and could not find where on specifies types.

In LightSpeed, a foreign key always references the Id of the associated entity -- in your case Subscriber. So I'm guessing that Subscribers.Id is an INT, and that Subscribers.Name is not the ID column. Because LightSpeed presumes the Agent association to be using Subscribers.Id, rather than Subscribers.Name, it presumes that the foreign key must be an int, as per the Subscriber identity type.

There's nothing you can do about this at the association level, because LightSpeed doesn't have any way of representing an association through a non-ID column. Instead, you'll have to remove the association and hand-code queries in the partial class to perform the lookup on the non-ID field, e.g.

public Subscriber Agent {
  get { return this.UnitOfWork.Find<Subscriber>(Entity.Attribute("Name") == this.AgentName); }
}

(Alternatively, if you have control over the database, change the foreign key to be the AgentID rather than the AgentName, and to reference Subscribers.Id instead of Subscribers.Name. This enables LightSpeed to map it as a proper association, but obviously has compatibility implications for an existing database.)