Getting Foreign Key Values with Linq-to-Entities

A little more complicated that just calling the property...

Once again, L2E makes life a bit tougher. Let us say that we have a database record with a foreign key 'state_id' that links to a 'states' table. In this said record, we want to get the integer value of that 'state_id' column (which is the unique identifier/primary key of the states table). BUT, we don't want to pull the state information, we just want the indentifier. Once again, in L2S, this was trivial:

linq-to-sql
var a_record = 
  dataContext.records.First(r => r.record_id == 1);
int s_id = a_record.state_id;

Doesn't get much simpler than that, and this makes perfect sense. But L2E does not allow this... why I do not know, but you can't simply call on the property 'state_id' because it is not there.

linq-to-entities
// won't work
var a_record = 
  entities.records.First(r => r.record_id == 1);
int s_id = record.state_id; // doesn't exist

This will not work either:

linq-to-entities
// won't work
var a_record = 
  entities.records.First(r => r.record_id == 1);
int s_id = record.state.state_id; // not populated

Instead, the following *will* work, but it pulls the 'state' and that is an extra pull we don't need:

linq-to-entities
// this works
var a_record = 
  entities.Include("state").records
  .First(r => r.record_id == 1);
int s_id = record.state.state_id; // populated this time

Granted this example is small, but what if that was a table with a giant-sized record?

linq-to-entities
// the proper way
var a_record = 
  entities.records.First(r => r.record_id == 1);
int s_id = Convert.ToInt32(record.stateReference
  .EntityKey.EntityKeyValues.First().Value.ToString());

Wow, that is a lot of code to get a simple integer. This is so aggravating to me because that integer is clearly stored in the DB, but there is no clean way to get it, sp insteada you have to go through the references EntityKey.

Instead, I often write a static extension so I can simply call some_record.stated_id() to get the integer:

public static int isff_tracker_id(this state s)
{ 
  return Convert.ToInt32(record.stateReference.EntityKey
    .EntityKeyValues.First().Value.ToString()); 
}

Now, I am not L2E master, but if someone can point out a better way to do this, I would be greatful! This forced learning of L2E reminds me daily why I will stick with L2S next time.

Pingbacks and trackbacks (1)+

Add comment

Loading