Many SQLMetal foreign keys pointing to a single table problem

Question

Edit - clear the question to better reflect the actual problem:

I am using SQLMetal to create database classes from our db sql server. Recently, I needed to add a table with several foreign keys pointing to the same table. Using LINQPad to play with the new tables, I was able to access the properties for both foreign keys:

• record.FK_AId
• record.FK_BId
• record.FKA
• record.FKB

... this is exactly how I expected it. The problem is that the classes created by SQLMetal produce these properties:

• record.FK_AId
• record.FK_BId
• record.FKA
• record.FKBTableNameGoesHere

Now I can simply compress the created classes, so FKBTableNameGoesHere will be FK_B, but the generated files are very often changed by different team members, so this will be a huge pain. Is there an easy solution for this?

Thanks in advance.

Edit 2 So, I thought the solution would be to simply create a partial class that had a property called the one I wanted it to, and let the getter / setter point to the poorly named property. This worked for choice, but not for its use in cases where clauses, etc. Anyone have a solution?

Answer 1

So my solution was to simply add another incomplete class and add the property using the get / set method, pointing to the property with the unusual name FKBTableNameGoesHere. Thus, we do not need to modify the modified classes. Not really a solution to the problem, but should make it more understandable for developers what the property means. Does anyone see any potential problems with this solution?

Change Thus, apparently, this only works for selecting data, and not for filtering on it. Not as easy to fix as I hoped. Anyone have any other suggestions?

Edit 2 - Jizz thought it would be some kind of common problem, but I think not. In any case, it turns out I was on the right track with this. I found this post:

Multiple foreign keys in one table

Which gave me the idea that I can't just bind directly to getter / setter for another property, as there is probably a lot more than this going on behind the scenes. The decision of these guys was not an exact answer, but he sent me in the right direction. Adding association attributes is what ended up being:

public partial class ProblemClass { [Association(Name = "FK__SomeLinkHere", Storage = "_OriginalPoorlyNamedStorageVariable", ThisKey = "FK_1_Id", OtherKey = "Id", IsForeignKey = true)] public FKType MyNewBetterName { get { return this._OriginalPoorlyNamedStorageVariable.Entity; } set { this.OriginalPoorlyNamedStorageVariable = value; } } } 

There is a need to leave generosity open to those who can still come up with a cleaner solution.

Answer 2

Well, I will offer a new answer (a little late, sorry) that will work even if the association name changes.

This method will look for the association property of the main object, and then it will look for the value in the main table. Imagine that:

Table: Orders with the Customers table on Orders.CustomerID is equal to Customers.Id . Therefore, we pass the meta-information of the main table, the CustomerID field (which is the reference field) and the Name field (which we want).

 /// <summary> /// Gets the value of "referencedValueFieldName" of an associated table of the "fieldName" in the "mainTable". /// This is equivalent of doing the next LINQ query: /// var qryForeignValue = /// from mt in modelContext.mainTable /// join at in modelContext.associatedTable /// on mt.fieldName equals at.associatedField /// select new { Value = at.referencedValueField } /// </summary> /// <param name="mainTable">Metadata of the table of the fieldName field</param> /// <param name="fieldName">Name of the field of the foreign key</param> /// <param name="referencedValueFieldName">Which field of the foreign table do you the value want</param> /// <returns>The value of the referenced table</returns> /// <remarks>This method only works with foreign keys of one field.</remarks> private Object GetForeignValue(MetaTable mainTable, string fieldName, string referencedValueFieldName) { Object resultValue = null; foreach (MetaDataMember member in mainTable.RowType.DataMembers) { if ((member.IsAssociation) && (member.Association.IsForeignKey)) { if (member.Association.ThisKey[0].Name == fieldName) { Type associationType = fPointOfSaleData.GetType(); PropertyInfo associationInfo = associationType.GetProperty(member.Name); if (associationInfo == null) throw new Exception("Association property not found on member"); Object associationValue = associationType.InvokeMember(associationInfo.Name, BindingFlags.GetProperty, null, fPointOfSaleData, null); if (associationValue != null) { Type foreignType = associationValue.GetType(); PropertyInfo foreignInfo = foreignType.GetProperty(referencedValueFieldName); if (foreignInfo == null) throw new Exception("Foreign property not found on assiciation member"); resultValue = foreignType.InvokeMember(foreignInfo.Name, BindingFlags.GetProperty, null, associationValue, null); } break; } } } return resultValue; } 

And the call:

  AttributeMappingSource mapping = new System.Data.Linq.Mapping.AttributeMappingSource(); MetaModel model = mapping.GetModel(typeof(WhateverClassesDataContext)); MetaTable table = model.GetTable(typeof(Order)); Object value = GetForeignValue(table, "CustomerId" /* In the main table */, "Name" /* In the master table */); 

The problem is that it only works with foreign keys with only one reference field. But switching to a few keys is pretty trivial.

This is a method to get the field value of the main table, which you can change to return the entire object.

PS I think that I am mistaken in my English, it is quite difficult for me.

Answer 3

This question arose a long time ago, but I ran into this problem. I am using DBML and I solved this problem by editing the relationship. If you extend ParentProperty, you can set the name of the property by changing the Name property.

Here is the XML from DBML (Member attribute changed):

  <Association Name="State_StateAction1" Member="DestinationState" ThisKey="DestinationStateId" OtherKey="Id" Type="State" IsForeignKey="true" /> 

Answer 4

Here is the version of Ocelot20:

 public partial class ProblemClass { partial void OnCreated() { this._MyNewBetterName = default(EntityRef<FKType>); } protected EntityRef<FKType> _MyNewBetterName; [Association(Name = "FK__SomeLinkHere", Storage = "_MyNewBetterName", ThisKey = "FK_1_Id", OtherKey = "Id", IsForeignKey = true)] public EntityRef<FKType> MyNewBetterName { get { return this._MyNewBetterName.Entity; } set { this.MyNewBetterName = value; } } } 

With this, you don’t even need to know the source name of the repository, however I'm not sure that the setter will work (I used only getter, but it worked like a charm). You can even pack this definition of a relationship into a base class and make it partially inherit (if you need to reuse the relationship between multiple models \ contexts, this should make it easier), but there is a catch, you will need to define OnCreated() inside each partial, since they cannot be inherited in C # ( see this ).