Linq to Entities (EF): How to get the value of a FK without doing the join

Question

I'm using the Linq to Entities. I've got my main table, Employee setup with a field named vendorID. Vendor ID is a foreign key into the Vendors table.

As it is right now, the Employee object does not directly expose the vendorID. Instead, I can only access it this way:

var employee = (from e in context.Employees.Include("tbl_vendors")
               where e.employeeID = 1
               select e).FirstOrDefault();

//this gets the vendor ID
int vendorID = employee.tbl_vendors.vendorID;

That is just fine and dandy, but it is extra work on the database because it is forcing a join where none is needed. Is there a way to get that key value without being forced to do a join to the tbl_vendors table?

Answer 1

Not sure about your object names here but you can grab the key from the entity key property without going to the database something like this:

var employee = (from e in context.Employees
               where e.employeeID = 1
               select e).FirstOrDefault();

//this gets the vendor ID
int vendorID = (int)employee.tbl_vendorsReference.EntityKey.EntityKeyValues[0].Value;

Answer 2

You can access the foreign key via the entity reference.

Employee employee = context.Employees.Single(e => e.employeeID == 1);

Int32 vendorID = (Int32)employee.tbl_vendorsReference.EntityKey.
   EntityKeyValues[0].Value;

See MSDN for reference on the EntityReference and EntityKey classes.

Answer 3

Actually this is very simple you basically do this:

var tblVendorID = (from e in context.Employees
                  select e.tbl_vendors.ID).FirstOrDefault();

Even though this looks like you are doing a join L2E will optimize out the join.

Which you can confirm with code like this:

var results = from e in ctx.Employees
              select e.tbl_vendors.ID;

var query = results as ObjectQuery<int>;
string sql = query.ToTraceString();

Hope this helps Alex (Microsoft).