Entity Model and binding Label to an unrelated table

Question

I am trying to figure out the best way to bind to an unrelated table in .Net. I have an ADO .Net Entity data model which contains several related tables and a few unrelated tables.

For example, I have the following tables: KEY_VALUE_LKP with columns LKP_TEXT, LKP_VALUE and LKP_TYPE REQUEST_DET with columns REQUESTNO, USERID, and REQ_STATUS USER_DET with columns USERNAME, USERID

REQUEST_DET is related to KEY_VALUE_LKP where REQ_STATUS = LKP_VALUE and LKP_TYPE="REQUEST_CRITICALITY" but this relationship not defined in the database REQUEST_DET is has a foreign key relationship with USER_DET defined in the database where USERID=USERID

When I build my model I get the three tables and the fact that USER_DET is related to REQUEST_DET

Using a FORMVIEW I bind to the Request data (REQUEST_DET EntityDataSource) and can see the relevant information for the request for the related tables (USERNAME, REQUESTNO but I get the numeric value for the REQ_STATUS.

What I would like to do is retrieve the LKP_TEXT value but seeing there is no relationship defined between the KEY_VALUE_LKP table the "Include" attribute on the EntityDataSource will not work.

I added a second EntityDataSource to the WebForm and linked it to the KEY_VALUE_LKP table and added the where Where="it.LKP_TYPE='REQUEST_CRITICALITY'". I know I could bind this to a DropDown and in the code behind set the selected value, but I want this to be bound to a Label not a drop down.

So, from within a form view bound to REQUEST_DET EntityDataSource can I bind a lable to a different EntityDataSource?

Thanks in advance, -J

Answer 1

I found a method to do this with Entity SQL, below is a similar example:

string eSql = @"SELECT VALUE LKP FROM
Key_Value_LKP as LKP where
LKP.LKP_TYPE='REQUEST_CRITICALITY' and LKP.LkpValue=@lkpValue";

ObjectQuery<Key_Value_LKP> statusQuery = ctx.CreateQuery<Key_Value_LKP>(eSql);
ObjectParameter lkpValue = new ObjectParameter("lkpValue",lkpValue.ToString());
statusQuery.Parameters.Add(lkpValue);

string REQ_STATUS= statusQuery.First().LkpText;

Label lState = (Label)fvRun.Row.FindControl("REQ_STATUSLabel");

//set state to exisitng run state
lState.Text = REQ_STATUS;