I want to be able to create my tables myself. Therefor i have a few questions.

Is there a way to define the name of the link-table a one-to-many relationship would go and seek a reference between for example a Project and a Tag. For example i have a Projects table which can have many Tags. So I create a Project_Tags table but DataObjects.Net will search for an Project-Tags-Tag table.

For a one-to-many relation, is it possible to have this done with an Column inside the Parent table, in stead of a link-table. For example: the table Projects has 1 Category. So I have a table Categories with a column ID (key) and Name. Projects would have a column CategoryID. But this doesn't look like possible with DataObjects.Net.

asked Oct 08 '10 at 04:38

Michiel%20Alders's gravatar image

Michiel Alders
506710

I'm writing an answer to your question (shortly, there are ways, but currently they aren't absolutely smooth) but could you describe why you need this feature?

Your point could help us to make right decision related to the implementation of this and similar features.

(Oct 08 '10 at 05:44) Alex Yakunin Alex%20Yakunin's gravatar image

I'm looking into migration to this great tool (DA04). But we have an existing database (over 5000 tables). So to implement these relations etc into objects I need this functionality.

(Oct 08 '10 at 05:46) Michiel Alders Michiel%20Alders's gravatar image

3 Answers:

For the first issue, this appears to be handled in the Manual as well. For an one-to-one relation I Managed to do the following in my Project Class:

    <FieldMapping("CategoryID")> _
    <Field()> _
    Public Property Category() As Category
        Get
            Return GetFieldValue(Of Category)("Category")
        End Get
        Set(ByVal value As Category)
            SetFieldValue(Of Category)("Category", value)
        End Set
    End Property

This works without an link-table, and the field in the Projects table is CategoryID.

answered Oct 08 '10 at 07:51

Michiel%20Alders's gravatar image

Michiel Alders
506710

There are 4 annoying issues related to full support of mappings:

1. Enable specifying schema in [TableMapping]

Currently you can map all the entities from a single Domain to a single schema only. This limitation can be important in Legacy mode.

2. IDENTITY column support

This feature is quite important for Legacy mode.

I recommend you to read all the comments about its planned implementation (the largest one was added soon - it explains why DO still doesn't support it); if you like puzzles, you can check this one (actually, it is related to the issue).

3. Mappings for columns of composite foreign keys

Currently unsupported.

4. Mappings for EntitySet with implicitly created item type (its EntitySetItem<t> instances)

Currently unsupported, but there is a workaround: never use EntitySet properties paired to other EntitySet properties (i.e. don't "push" DO to automatically create & map intermediate entity types); use EntitySet properties paired to regular reference properties instead (i.e. define an intermediate entity by your own - that's what other ORM tools usually offer).

Example of m-n relationship with automatically mapped intermediate entity:

[HierarchyRoot]
public class Book: Entity {
  ...
  [Field, Association(PairTo = "Books")]
  public EntitySet<Author> Authors { get; private set; }
}

[HierarchyRoot]
public class Author: Entity {
  ...
  public EntitySet<Book> Books { get; private set; }
}

The same relational model, but with explicitly defined intermediate entity:

[HierarchyRoot]
public class Book: Entity {
  ...
  [Field, Association(PairTo = "Author")]
  public EntitySet<BookAuthorRelation> Authors { get; private set; }
}

[HierarchyRoot]
public class Author: Entity {
  ...
  [Field, Association(PairTo = "Book")]
  public EntitySet<BookAuthorRelation> Books { get; private set; }
}

[HierarchyRoot]
public sealed class BookAuthorRelation: Entity {
  [Field, Key(0)]
  public Author Author { get; private set; }

  [Field, Key(1)]
  public Book Book { get; private set; }
}

I know, this is much less convenient (that's why we implemented pairing with auto-mapped entity ;) ), although you can map the intermediate entity in any way. Also, this is the approach you can use to describe ternary associations, associations with weighted edges and so on.

And finally,

Issues #2, #3 and #4 will be resolved in few months - they must be resolved to support this feature, that is quite important for us. Probably, #1 will be implemented as well, but it the least one important in this case.

Most likely, we'll add few attributes to support these cases ([EntitySetMapping], [CompositeMapping], etc.).

answered Oct 08 '10 at 10:03

Alex%20Yakunin's gravatar image

Alex Yakunin
29714412

edited Oct 08 '10 at 10:06

Would the following also be possible. 2 Tables, Person and Pets.

Pets Contains 2 fields, ID (key), Name. for example:

  1. Dog
  2. Cat
  3. Fish
  4. Horse
  5. Bird

And I would like connect a Person to a Pet but we want to add a numberOfPets to it. So Person 'John' has 2 dogs, 3 fishes, 1 horse and 1 bird.

So I would need a table: PersonPets: PersonID, PetID, NumberOfPets. How would you do this with DataObjects.NET

(Oct 11 '10 at 05:14) Michiel Alders Michiel%20Alders's gravatar image

Person class:

[HierarchyRoot]
public class Person : Entity {
  ...
  [Field, Association(PairTo="Person", 
    OnTargetRemove = OnRemoveAction.Clear,
    OnOwnerRemove = OnRemoveAction.Cascade)]
  public EntitySet<PersonPetItem> Pets { get; private set; }
  ...
}
(Oct 11 '10 at 05:45) Alex Yakunin Alex%20Yakunin's gravatar image

Pet class:

[HierarchyRoot]
public class Pet : Entity {
  ...
  [Field, Association(PairTo="Pet",
    OnTargetRemove = OnRemoveAction.Clear,
    OnOwnerRemove = OnRemoveAction.Cascade)]
  public EntitySet<PersonPetItem> Persons { get; private set; }
  ...
}
(Oct 11 '10 at 05:45) Alex Yakunin Alex%20Yakunin's gravatar image

An item describing the relationship:

[HierarchyRoot]
[KeyGenerator(KeyGeneratorKind.None)]
public sealed class PersonPetItem : Entity {
  [Field, Key(0),
    OnTargetRemove = OnRemoveAction.Cascade]
  public Person Person { get; private set; }
  [Field, Key(1),
    OnTargetRemove = OnRemoveAction.Cascade]
  public Pet Pet { get; private set; }
  [Field]
  public int PetCount { get; set; }

  public PersonPetItem(Person person, Pet pet, int petCount)
    : base(person, pet) // Defining PK
  {
    PetCount = petCount;
  }
}
(Oct 11 '10 at 05:46) Alex Yakunin Alex%20Yakunin's gravatar image

Usage:

var person = ...; // some Person

var oldItem = person.Pets
  .Where(i => i.PetCount==5) // EntitySet is IQueryable!
  .SingleOrDefault(); // Query is executed @ this line
if (oldItem!=null) {
  var oldPet = oldItem.Pet;
  oldItem.Remove(); // EntitySets are updated as well
  // Alternative:
  // person.Pets.Remove(oldItem); // Absolutely the same effect

  // Now restoring everything back
  var newItem = new PersonPetItem(person, oldPet, 5);
  // EntitySets are updated again
}
(Oct 11 '10 at 05:49) Alex Yakunin Alex%20Yakunin's gravatar image

I've done the following:

<TableMapping("ProjectTags")> _
<HierarchyRoot()> _
Public Class ProjectTag
    Inherits Entity

    Public Sub New(ByVal project As Project, ByVal tag As Tag)
        MyBase.New(project, tag)
    End Sub

    <Field(), Key(0)> _
    Public Property Project() As Project
        Get
            Return GetFieldValue(Of Project)("Project")
        End Get
        Private Set(ByVal value As Project)
            SetFieldValue(Of Project)("Project", value)
        End Set
    End Property

    <Field(), Key(1)> _
    Public Property Tag() As Tag
        Get
            Return GetFieldValue(Of Tag)("Tag")
        End Get
        Private Set(ByVal value As Tag)
            SetFieldValue(Of Tag)("Tag", value)
        End Set
    End Property

End Class

But i'm getting the problem with the key generator again: Default generator can serve hierarchy with exactly one key field.

answered Oct 11 '10 at 07:02

Michiel%20Alders's gravatar image

Michiel Alders
506710

Sorry, just noticed I forgot to add [HierarchyRoot, KeyGenerator(KeyGeneratorKind.None)] and sealed to PersonPetItem type - they should be there.

In your case you must add a constructor for ProjectTag type accepting Project and Tag as its arguments and passing them to base .ctor.

(Oct 11 '10 at 07:40) Alex Yakunin Alex%20Yakunin's gravatar image

If this isn't done, DO will try to generate ProjectTag.Key instead of building it from specified parts, but since there is no key generator for such keys (btw, you can create it), it will fail.

(Oct 11 '10 at 07:42) Alex Yakunin Alex%20Yakunin's gravatar image

Now i'm getting the exception: Paired field for field 'Project.Tags' should be assignable to type 'Project'. This is the Tags property of my Project class:

    <Field(), Association(PairTo:="Tag")> _
    Public Property Tags() As EntitySet(Of ProjectTags)
        Get
            Return GetFieldValue(Of EntitySet(Of ProjectTags))("Tag")
        End Get
        Private Set(ByVal value As EntitySet(Of ProjectTags))
            SetFieldValue(Of EntitySet(Of ProjectTags))("Tag", value)
        End Set
    End Property
(Oct 11 '10 at 10:16) Michiel Alders Michiel%20Alders's gravatar image

That's correct: use <Field, Association(PairTo:="Project")>

When you pair A.B to B.A, it's expected that:

  • Type of A.B is either B or EntitySet<B>
  • Type of B.A is either A or EntitySet<A>

So Project.Tags (of EntitySet<ProjectTags> type) must be paired to ProjectTags.Project (of Project type).

Btw, it's better to rename ProjectTags: class names must be singular (one of .NET converntions).

(Oct 11 '10 at 13:10) Alex Yakunin Alex%20Yakunin'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

Subscription:

Once you sign in you will be able to subscribe for any updates here

Tags:

×573
×2
×2
×2

Asked: Oct 08 '10 at 04:38

Seen: 3,475 times

Last updated: Oct 11 '10 at 13:10

powered by OSQA