How to access foreign key related column values using LinqToSql?

Question

I'm trying to wire-up LinqToSql to be my data access layer and running into a few issues, probably due to my lack of experience with LinqToSql.

I have two tables, one called Project and one called Employee. The Project has fields for OpenedBy and ClosedBy which are foreign key references to the Employee table, which has fields for EmployeeId and Name.

When I fetch a Project I would like for it to fetch the EmployeeName for the OpenedBy and ClosedBy. I would like to access these like the following:

// assuming data is of type project

this.OpenedByName.Text = data.OpenedByName;
this.ClosedByName.Text = data.ClosedByName;

Is it also possible to set these values whenever OpenedBy or ClosedBy changes? Is this possible? Sample code would be much appreciated!

Clarification

I would like to do this without having to use stored procedures.

Answer 1

If you have 2 relationships coming from the Employee table, I think you'll have 2 child properties, project.Employee, and project.Employee1 in each Project entity.

You can change the name of the association, just go to the relationship properties, select Child Property and there change the name of each child Employee to be more descriptive.

You can name the child properties as you want, for example you could:

this.OpenedByName.Text = data.OpenedByEmployee.Name;
this.ClosedByName.Text = data.ClosedByEmployee.Name;