custom mapping of an edm based on a lookup table

Question

I had this question in another post and was asked to post it anew as it is fairly complex.  I have a table named CheckRequest, in this table I have about 5 fields that are integers and are all a foreign key relation to a table called Lookups.  Inside Lookups is another foreign key to a table called [Text] which has yet another foeigh key to a table called TextLanguages.  TextLanguages stores the description associated with the integer stored in the CheckRequest table.  I want to customize my CheckRequest entity to include the descriptions in the TextLanguages table.  My problem is that I can only map TextLanguages once which leaves the remaining 4 integer fields with no descriptions.  How might I accomplish this?

---

Adam

Answer 1

You can map the CheckRequest to an entity, and use a defining query or a db view to map your Text data to an entity, for example - you can build a view that joins all the needed table and produces a view with the integer value and the related text value. Once you have those entities, you shouldn't have any problem creating the necessary 5 navigations between the two entities (In EDM, there's no problem creating multiple navigations between the same entities).

If you encounter any problem, please specify the error you get, and any information that can help (edmx content etc..)

---

Please mark posts as answers/helpful if it answers your question

Answer 2

Can I do this with a stored procedure or does it have to be a view?  I am not that familiar with views, if I have a linq query that gets a specific row from the check request entity, how does the view also know to return the data for that 1 record?  Also if I used a Defining Query do I need to add the Id's and scalar properties even though they are already Naviagtio Properties?

---

Adam

Answer 3

Ok I created a view, imported it, mapped it, now  I am getting: 

Error 1 Error 3024: Problem in Mapping Fragment starting at line 2654: Must specify mapping for all key properties (CheckRequests.CheckRequestId) of the EntitySet CheckRequests.
 C:\Graebel2.0\RITS\FeatureReleases\CheckRequest\Source\Components\RitsEntityDal\RITSModel.edmx 2655 15 RITSComponents

<EntitySetMapping Name="CheckRequests">

<

 

EntityTypeMapping TypeName="IsTypeOf(RITSNetModel.CheckRequests)">

<

 

MappingFragment StoreEntitySet="CheckRequests">

<

 

ScalarProperty Name="CreatedBy" ColumnName="CreatedBy" />

<

 

ScalarProperty Name="StatusId" ColumnName="StatusId" />

<

 

ScalarProperty Name="ShipmentId" ColumnName="ShipmentId" />

<

 

ScalarProperty Name="RequestTypeId" ColumnName="RequestTypeId" />

<

 

ScalarProperty Name="PaymentMethodId" ColumnName="PaymentMethodId" />

<

 

ScalarProperty Name="DeliveryMethodId" ColumnName="DeliveryMethodId" />

<

 

ScalarProperty Name="CompanyEntityId" ColumnName="CompanyEntityId" />

<

 

ScalarProperty Name="CheckRequestId" ColumnName="CheckRequestId" />

<

 

ScalarProperty Name="ReturnCheckTo" ColumnName="ReturnCheckTo" />

<

 

ScalarProperty Name="Explanation" ColumnName="Explanation" />

<

 

ScalarProperty Name="CreatedDate" ColumnName="CreatedDate" />

</

 

MappingFragment>

<

 

MappingFragment StoreEntitySet="v_CheckRequests">

<

 

ScalarProperty Name="CheckRequestCreatedDate" ColumnName="CheckRequestCreatedDate" />

<

 

ScalarProperty Name="CreatedByName" ColumnName="CreatedByName" />

<

 

ScalarProperty Name="StatusDescription" ColumnName="StatusDescription" />

<

 

ScalarProperty Name="ShipmentNumber" ColumnName="ShipmentNumber" />

<

 

ScalarProperty Name="PaymentMethodDescription" ColumnName="PaymentMethodDescription" />

<

 

ScalarProperty Name="DeliveryMethodDescription" ColumnName="DeliveryMethodDescription" />

<

 

ScalarProperty Name="CompanyEntityName" ColumnName="CompanyEntityName" />

<

 

ScalarProperty Name="RequestTypeDescription" ColumnName="RequestTypeDescription" />

</

 

MappingFragment></EntityTypeMapping></EntitySetMapping>

What am I missing here?

---

Adam

Answer 4

Hi Adam,

 

Welcome to MSDN forums!

 

Based on your description, I suppose that you have the following four tables,

 

Tables	CheckRequest	Lookups	Text	TextLanguages
Columns	FKLookups1	id		id
	FKLookups2
	FKLookups3
	FKLookups4
	FKLookups5	FKTest	FKTextLanguages	Description

 

Is that your scenario?

However, I’m still not sure about your problem,

“I can only map TextLanguages once which leaves the remaining 4 integer fields with no descriptions”.

Do you mean you don’t to have the other four columns navigated to [TextLanguages]?

If you set the FKs in DB like the table I post above, you will have all the five properties in [CheckRequest] navigated to [Lookups], then to [Text], at last to [TextLanguages].

In logic, all FKLookups column have the rights to assess their Description in [TextLanguages], right?

 

If you want to prevent it in the EDM level, you can follow this workaround,

1)    Don’t set all the five FKs in [CheckRequest].

2)    In the EDM, set one 1:M between CheckRequest’s one field and Lookups’id.

You can refer to this blog,

http://cid-9f269c1994b33956.spaces.live.com/blog/cns!9F269C1994B33956!274.entry

 

 

By the way, I’ve received your edmx file from my colleague. However, it is too complex to get general idea. If you could provide a small scale table script related to this problem, we’ll have better understanding of it.

 

If you have any questions or concerns, please update the thread and we will have a further discussion.

 

 

Best Regards

Yichun Feng

MSDN Subscriber Support in Forum 

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 5

So I took the approach of creating a view and mapping the description fields to the view and it seems to be working. 

---

Adam