Handle "join tables" in Database first scheme

4 posts, 0 answers
  1. Gag
    Gag avatar
    2 posts
    Member since:
    Aug 2012

    Posted 25 Feb 2013 Link to this post

    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.
  2. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 28 Feb 2013 Link to this post

    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
    OpenAccess ORM Q1 2013 is out featuring Multi-Diagrams, Persistent Data Stream Support and much more. Sign up for a free webinar to see all the new stuff in action.
  3. DevCraft banner
  4. Gag
    Gag avatar
    2 posts
    Member since:
    Aug 2012

    Posted 28 Feb 2013 Link to this post

    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.


  5. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 06 Mar 2013 Link to this post

    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
    OpenAccess ORM Q1 2013 is out featuring Multi-Diagrams, Persistent Data Stream Support and much more. Check out all of the latest highlights.
Back to Top