[Solved] Handle "join tables" in Database first scheme

[Gag]

I am facing a situation where the Open Access ORM is not generating correct code.

I have three tables in my database named "Provider" (primary key "ProviderID"), "Languages" (primary key "LanguageID")
and "ProviderLanguages" (with columns "ProviderID", "LanguageID" as FKey from Provider and Languages) which acts as a join table. One provider can have multiple languages.

When I generate "Data Access Layer"(DAL) through ORM, "Languages" lands as a non-updatable (into DB) property of "Provider". As a result, I am not able to update "Languages" when I am editing Provider through UI. 

I get "Languages" as property of "Provider" type in DAL as:

private IList<Language> _languages = new List<Language>();
[Collection(InverseProperty = "Providers")]
[Storage("_languages")]
public virtual IList<Language> Languages
{
    get
    {
        return this._languages;
    }
}

whereas "Providers" lands as a property of "Language" type in DAL as:

private IList<Provider> _providers = new List<Provider>();
[JoinTableAssociation(TableName = "ProviderLanguages", OwnerColumns = "LanguageID", TargetColumns = "ProviderID", SourceConstraint = "ref_PrvdrLnggs_Bwy_Lngages", TargetConstraint = "ref_PrvdrLnggs_Prvders")]
[Column("LanguageID", OpenAccessType = OpenAccessType.Int32, IsPrimaryKey = true, Length = 0, Scale = 0, SqlType = "int")]
[Column("ProviderID", OpenAccessType = OpenAccessType.Int32, IsPrimaryKey = true, Length = 0, Scale = 0, SqlType = "int")]
[Storage("_providers")]
public virtual IList<Provider> Providers
{
    get
    {
        return this._providers;
    }
}

As you can see in code above, "Providers" property has the [Column()] metadata but not "Languages" property. I was expecting the opposite.

If I go by "Model first" scheme and generate the "Join table" (ProviderLanguages) through Open Access ORM tool using:
http://www.telerik.com/help/openaccess-orm/developer-guide-domain-model-working-associations-many-to-many-custom-table.html, I am able to generate correct property exactly as I expect.

But how to solve this in "Database First" scenario?

TIA.

[PetarP]

Hi Gag,

 Indeed this appears to be a problem on our side that is only happening when you use attribute mapping.  If you use another mapping type (Fluent or xml) the same problem should not occur.
We will try to fix that for our next public build. Thank you for pointing that out. Please find your Telerik points updated.

Kind regards,
Petar
the Telerik team

[Gag]

Hi Petar,

Thanks for your response.

I quickly tried to see what code is generated when using mapping option as "XML".
I see that most of the join table associations are now created correctly (which was not the case when using "Attributes" option). But it does not generate correct for at least one that I noticed.

("Providers" can have multiple "Languages" and "Cities" associated with them)  

As you can see in the code below that it does not generate correctly for "Languages" (first block of code) but does it correct for Cities" (second block of code). Infact, it generates the property "Providers" on "Language" class with association "LanguagesHaveProviders", which of course is not correct.

<orm:field name="_languages" property="Languages" behavior="readwrite" uniqueId="2495a3e7-8304-46c8-9e42-5a3732f18040" type="OpenAccessModel2.Language">
  <orm:collection element-class="OpenAccessModel2.Language" inverse-field="_providers" order-by="" uniqueId="3aba3dc3-94bc-4107-bd8a-365b9db7b02b" />
</orm:field>



<orm:field name="_cities" property="Cities" behavior="readwrite" uniqueId="6da27750-84eb-4db6-8fe7-57c588abdb0d" type="OpenAccessModel2.City">
  <orm:join-table uniqueId="4b1d9ba6-1f99-4581-bda7-5b5d637e4ca1" association-name="ProvidersHaveCities">
    <orm:table name="ProviderJobCityPreferences" join-table="true" />
    <orm:owner>
      <orm:constraint name="ref_PrvdrJbCtyPrfrncs_" destination-table="Providers">
        <orm:column name="ProviderID" sql-type="int" nullable="false" length="0" scale="0" primary-key="true" ado-type="Int32" target-class="OpenAccessModel2.Provider" target-field="_providerID" />
      </orm:constraint>
    </orm:owner>
    <orm:value>
      <orm:constraint name="ref_PrvdrJbCtyPrfrncs_2" destination-table="Cities">
        <orm:column name="CityID" sql-type="int" nullable="false" length="0" scale="0" primary-key="true" ado-type="Int32" target-class="OpenAccessModel2.City" target-field="_cityID" />
      </orm:constraint>
    </orm:value>
  </orm:join-table>
</orm:field>

So there might be more of such a case. Analyzing the EntityModel.cs is quite a eye-straining process :)

I worked around this issue by changing my approach to "Model First" approach and generate the "Join tables" through ORM interface within Visual Studio, and then creating the "Associations" for join tables in ORM interface. That is the best way to make Open Access ORM know which field is from the "Source class" and which from the "Target class" (basically "Provider" as Source class and "Languages" as Target class in the "Create Association" interface of Provider model).

Thanks.

[PetarP]

Hello Gag,

 It does seem pretty strange. I have not been able to reproduce the same behaviour on my side and I believe that the problem might actually be caused by us misreading something in your database schema. Please let me know if you project allows you to share the part of your database schema that is causing the problem and I will convert the thread to one that allows attachments.

All the best,
Petar
the Telerik team