Linq to Entities (EF): how to get the FK value without performing a connection

Question

I am using Linq for Entities. I have my main table, setting up Employee with a field named vendorID. Vendor ID is the foreign key in the Vendors table.

As of now, the Employee object does not directly expose the provider identifier. Instead, I can only access it like this:

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;

This is just fine and dandy, but it is an extra work on the database because it is a forced connection where there is no need. Is there a way to get this key value without having to make a connection to the tbl_vendors table?

Answer 1

.

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

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

. MSDN EntityReference EntityKey.

Answer 2

, :

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

, , L2E .

:

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

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

, (Microsoft).

Answer 3

Not sure about the names of the objects here, but you can grab the key from the property of the entity key without going to the database 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;