So, I want to define a Many-to-Many association, but I also want to store additional field in intermediate table. Is that possible? Something like this:

<span class="syntaxdefault"><br /><br /></span><span class="syntaxkeyword">public class </span><span class="syntaxdefault">Link</span><span class="syntaxkeyword"><</span><span class="syntaxdefault">T</span><span class="syntaxkeyword">> : </span><span class="syntaxdefault">EntitySet</span><span class="syntaxkeyword"><</span><span class="syntaxdefault">T</span><span class="syntaxkeyword">> </span><span class="syntaxdefault">where T </span><span class="syntaxkeyword">: </span><span class="syntaxdefault">IEntity<br />    </span><span class="syntaxkeyword">{<br />        protected </span><span class="syntaxdefault">Link</span><span class="syntaxkeyword">(</span><span class="syntaxdefault">Entity owner</span><span class="syntaxkeyword">, </span><span class="syntaxdefault">FieldInfo field</span><span class="syntaxkeyword">) : </span><span class="syntaxdefault">base</span><span class="syntaxkeyword">(</span><span class="syntaxdefault">owner</span><span class="syntaxkeyword">, </span><span class="syntaxdefault">field</span><span class="syntaxkeyword">)<br />        {<br />        }<br /><br />        protected </span><span class="syntaxdefault">Link</span><span class="syntaxkeyword">(</span><span class="syntaxdefault">SerializationInfo info</span><span class="syntaxkeyword">, </span><span class="syntaxdefault">StreamingContext context</span><span class="syntaxkeyword">) : </span><span class="syntaxdefault">base</span><span class="syntaxkeyword">(</span><span class="syntaxdefault">info</span><span class="syntaxkeyword">, </span><span class="syntaxdefault">context</span><span class="syntaxkeyword">)<br />        {<br />        }<br /><br />        [</span><span class="syntaxdefault">Field</span><span class="syntaxkeyword">]<br />        public </span><span class="syntaxdefault">int Order </span><span class="syntaxkeyword">{ </span><span class="syntaxdefault">get</span><span class="syntaxkeyword">; </span><span class="syntaxdefault">set</span><span class="syntaxkeyword">; }<br />    }<br /><br />[</span><span class="syntaxdefault">HierarchyRoot</span><span class="syntaxkeyword">]<br />public class </span><span class="syntaxdefault">Book </span><span class="syntaxkeyword">: </span><span class="syntaxdefault">Entity<br /></span><span class="syntaxkeyword">{<br />    [</span><span class="syntaxdefault">Key</span><span class="syntaxkeyword">, </span><span class="syntaxdefault">Field</span><span class="syntaxkeyword">]<br />    public </span><span class="syntaxdefault">int Id </span><span class="syntaxkeyword">{ </span><span class="syntaxdefault">get</span><span class="syntaxkeyword">; private </span><span class="syntaxdefault">set</span><span class="syntaxkeyword">; }<br /><br />    [</span><span class="syntaxdefault">Field</span><span class="syntaxkeyword">]<br />    public </span><span class="syntaxdefault">Link</span><span class="syntaxkeyword"><</span><span class="syntaxdefault">Author</span><span class="syntaxkeyword">> </span><span class="syntaxdefault">Authors </span><span class="syntaxkeyword">{ </span><span class="syntaxdefault">get</span><span class="syntaxkeyword">; private </span><span class="syntaxdefault">set</span><span class="syntaxkeyword">; }<br />}<br /><br />[</span><span class="syntaxdefault">HierarchyRoot</span><span class="syntaxkeyword">]<br />public class </span><span class="syntaxdefault">Author </span><span class="syntaxkeyword">: </span><span class="syntaxdefault">Entity<br /></span><span class="syntaxkeyword">{<br />    [</span><span class="syntaxdefault">Key</span><span class="syntaxkeyword">, </span><span class="syntaxdefault">Field</span><span class="syntaxkeyword">]<br />    public </span><span class="syntaxdefault">int Id </span><span class="syntaxkeyword">{ </span><span class="syntaxdefault">get</span><span class="syntaxkeyword">; private </span><span class="syntaxdefault">set</span><span class="syntaxkeyword">; }<br /><br />    [</span><span class="syntaxdefault">Field</span><span class="syntaxkeyword">]<br />    public </span><span class="syntaxdefault">Link</span><span class="syntaxkeyword"><</span><span class="syntaxdefault">Book</span><span class="syntaxkeyword">> </span><span class="syntaxdefault">Books </span><span class="syntaxkeyword">{ </span><span class="syntaxdefault">get</span><span class="syntaxkeyword">; private </span><span class="syntaxdefault">set</span><span class="syntaxkeyword">; }<br />}<br /> </span><span class="syntaxdefault"></span>

Updated at 15.03.2010 7:15:45

Thanks for your answer, but I've found an issue on googlecode http://code.google.com/p/dataobjectsdot ... ail?id=595 and it says this is not working

This thread was imported from our support forum. The original discussion may contain more detailed answer.

asked Mar 12 '10 at 10:19

xumix's gravatar image

xumix
425757682


One Answer:

Alex (Xtensive) wrote:

Yes, this is possible.

But inheriting from EntitySet<t> won't help here much - i.e. you can do this, but this won't help.

Declare:

  • Link<tl,tr> type with {TL, Left, TR Right, int Order} properties; // not really necessary, you can do the same in AuthorBookLink

  • Inherit AuthorBookLink from Link<author,book> marked as [HierarchyRoot];

  • Create EntitySet<authorbooklink> with [Association(PairTo = "Left")] in Book type

  • Create EntitySet<authorbooklink> with [Association(PairTo = "Right")] in Author type.

Actually I'm not sure if this will work - there might be issue, because the properties we pair to are inherited from base. If so, we'll fix this ASAP (as workaround, you can use AuthorBookLink w/o inheritance from Link<>).

In any case, I hope this explains the idea itself: you must create an intermediate entity and pair 2 EntitySets to its "ends".


Alex (Xtensive) wrote:

The mistake shown in issue 595 was fixed before v4.2 release (as far as I can judge by provided exception message - we didn't try to clone the whole sample in our tests). But after today's review of our support forum I'm almost fully sure this won't work because of issue 619.

We're working on bugfix.


stefmen wrote:

This is the linkclass that works:

[KeyGenerator(KeyGeneratorKind.None)]
public class Link<TL,TR> : Entity where TL : IEntity where TR : IEntity
{
    [Key(Position=0), Field]
    public TL Left { get; private set; }

    [Key(Position = 1), Field]
    public TR Right { get; private set; }

    public Link(TL left, TR right)
        : base(left, right)
    {
    }
}

This is the class that inherits:

[Serializable]
[HierarchyRoot]
public class Class1 : Link<ClassA, ClassB>
{
    public ClassA Classa { get { return Left; } }

    public ClassB Classb { get { return Right; } }

    [Field]
    public Int32 AdditionalField { get; set; }

    /// <summary>
    /// Constructor to make the association.
    /// </summary>
    public AssociationClass(ClassA classa, ClassB classb)
        : base(classa, classb)
    {

    }
}

psulek wrote:

[quote="Alex (Xtensive)":37iubond]Hmm... And what's the issue? No issue, just sharing! :P[/quote:37iubond] I think alex means, what is purpose (what that class is for)? I'm also interesting in what it is for, some example maybe?


stefmen wrote:

The classes I posted are the implementation of how to achieve a many-to-many association with additional table fields (topic title.. :wink:) which Alex explained in pseudocode here:

But inheriting from EntitySet<t> won't help here much - i.e. you can do this, but this won't help. Declare: - Link<tl,tr> type with {TL, Left, TR Right, int Order} properties; // not really necessary, you can do the same in AuthorBookLink - Inherit AuthorBookLink from Link<author,book> marked as [HierarchyRoot]; - Create EntitySet<authorbooklink> with [Association(PairTo = "Left")] in Book type - Create EntitySet<authorbooklink> with [Association(PairTo = "Right")] in Author type. I was having the same question as xumix, and thought: let's post some code for all the people who have the same question.


jonepolvora wrote:

//I have the following scenario:

//A table Company:

[HierarchyRoot]
    public class Company : Entity
    {
        [Key, Field]
        public int CompanyId { get; private set; }
        [Field(Length = 50)]
        public string CompanyName { get; set; }
    }

//A table Customer

[HierarchyRoot]
    public class Customer : Entity
    {
        [Key, Field]
        public int CustomerId { get; private set; }
        [Field(Length = 50)]
        public string FullName { get; set; }
    }

//A table CompanyCustomer that do the association from both tables implemented using the hints of this topic:

[HierarchyRoot]
    public class CompanyCustomer : Link<Company, Customer>
    {
        public Company Company { get { return Left; } }
        public Customer Customer { get { return Right; } }

        //my additional field
        [Field(Length = 36)]
        public string RegisterId { get; set; }

        public CompanyCustomer(Company company, Customer customer)
            : base(company, customer) { }
    }

the Link Class:

[KeyGenerator(KeyGeneratorKind.None)]
    public class Link<TL, TR> : Entity where TL : IEntity where TR : IEntity
    {
        [Key(Position = 0), Field]
        public TL Left { get; private set; }
        [Key(Position = 1), Field]
        public TR Right { get; private set; }

        public Link(TL left, TR right)
            : base(left, right) { }
    }

/* Everything is working like I was expecting.

My question is:
How can I do like the following:

*/

//get the customer by CPF field
    var customer = Query.All<Customer>().Where(c => c.CPF == "123").SingleOrDefault();
    //
    if (customer != null)
    {
        //get a list of companies relationed to this customer
        //this is my question => how get a list using Association("Company")
        IList<Company> companies = customer.Companies.ToList();
    }

//I'd tried to insert a field at the Customer Class like this:

[Field]
        [Association("Customer")]
        public EntitySet<Company> companies { get; set; }

//But DataObjects created some other reduntant relationship tables that is not what I wish.

//Any suggestions to get the Companies related to a given customer?


otto wrote:

CompanyCustomer in your model is an entity (not just a association), you should treat it that way.

So your query:

Customer cust=...; var custCompanies = (from cc in Query.All<companycustomer>() where cc.Customer==cust select cc.Company).ToList();


Alex (Xtensive) wrote:

Yes, exactly. Since the association type you're going to use is custom one, you'll anyway see \ traverse it in queries.

Few more advices:

1) Turn Link<tl, tr=""> into an abstract type, or, even better, get rid of it completely (i.e. use just CompanyCustomer). You need it just because of Left and Right properties, but there is no any generality except presence of these two properties here. On the other hand, this will make you using .Left and .Right instead of .Customer and .Company, which turns the code into much less readable. So it's nearly like using Tuple<tl,tr> as base class of any type having two fields.

2) Use pairing to its properties from Customer & Company to make this more usable:

public class Company {
  ...
  [Association(PairTo = "Left")]
  public EntitySet<CompanyCustomer> CustomerRelationships { get; private set; }
  ...
}

public class Customer {
  ...
  [Association(PairTo = "Right")]
  public EntitySet<CompanyCustomer> CompanyRelationships { get; private set; }
  ...
}

In this case your query could be:

var customerCompanies = customer.CompanyRelationships.Select(rel => rel.Left);

answered Mar 12 '10 at 18:53

Editor's gravatar image

Editor
46156156157

Issue 619 was fixed.

v4.2.0 installers were updated to the latest stable revision.

(Mar 12 '10 at 18:53) Alex Yakunin Alex%20Yakunin's gravatar image

Hmm... And what's the issue?

(Mar 12 '10 at 18:53) Alex Yakunin Alex%20Yakunin's gravatar image

stefmen wrote: > Hmm... And what's the issue? No issue, just sharing! :P

(Mar 12 '10 at 18:53) Editor Editor's gravatar image

otto wrote: The query

customer.Companies.ToList()

would only work if there were a simple EntitySet-EntitySet paired assiciation with no intermediate link entity.

(Mar 12 '10 at 18:53) Editor Editor's gravatar image
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA