This is a migrated thread and some comments may be shown as answers.

Multiple associations to the same table

2 Answers 41 Views
Development (API, general questions)
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Paul
Top achievements
Rank 1
Paul asked on 03 Jun 2015, 04:01 PM

Hi,

I have a database that looks a little bit like this:

CREATE TABLE [dbo].[People](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](255) NOT NULL,
    CONSTRAINT [pk_People] PRIMARY KEY CLUSTERED ( [Id] )
)
CREATE TABLE [dbo].[Addresses](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Text] [varchar](255) NOT NULL,
    [Type] [varchar](255) NOT NULL,
    [PersonId] [int] NOT NULL,
    CONSTRAINT [pk_Addresses] PRIMARY KEY CLUSTERED ( [Id] )
    CONSTRAINT [fk_Addresses_People] FOREIGN KEY([PersonId]) REFERENCES [dbo].[People] ([Id])
)

For various reasons, I want to make a class hierarchy that looks like this:

public class Person
{
    public Person()
    {
        WorkAddresses = new List<WorkAddress>();
        HomeAddresses = new List<HomeAddress>();
    }
 
    public int Id { get; set; }
    public string Name { get; set; }
    public IList<WorkAddress> WorkAddresses { get; set; }
    public IList<HomeAddress> HomeAddresses { get; set; }
}
public class Address
{
    public int Id { get; set; }
    public string Text { get; set; }
}
public class HomeAddress : Address
{
}
public class WorkAddress : Address
{
}
 

My fluent mapping looks like this:

protected override IList<MappingConfiguration> PrepareMapping()
{
    List<MappingConfiguration> config = new List<MappingConfiguration>();
 
    var person = new MappingConfiguration<Person>();
    person.MapType().ToTable("People");
    person.HasProperty(p => p.Id).IsIdentity(KeyGenerator.Autoinc);
 
    person.HasAssociation(p => p.HomeAddresses)
        .ToColumn("PersonId");
    person.HasAssociation(p => p.WorkAddresses)
        .ToColumn("PersonId");
    config.Add(person);
 
    var address = new MappingConfiguration<Address>();
    address.MapType().ToTable("Addresses");
    address.HasProperty(a => a.Id).IsIdentity(KeyGenerator.Autoinc);
    address.HasDiscriminator().ToColumn("Type");
    config.Add(address);
 
    var homeAddress = new MappingConfiguration<HomeAddress>();
    homeAddress.MapType().Inheritance(InheritanceStrategy.Flat).ToTable("Addresses");
    homeAddress.HasDiscriminatorValue("HomeAddress");
    config.Add(homeAddress);
 
    var workAddress = new MappingConfiguration<WorkAddress>();
    workAddress.MapType().Inheritance(InheritanceStrategy.Flat).ToTable("Addresses");
    workAddress.HasDiscriminatorValue("WorkAddress");
    config.Add(workAddress);
 
    return config;
}
 

However, when I run this query:

var strategy = new FetchStrategy();
strategy.LoadWith<Person>(p => p.HomeAddresses);
strategy.LoadWith<Person>(p => p.WorkAddresses);
 
var results = context.GetAll<Person>().LoadWith(strategy).ToList();
 

I get this error:

Row for OID GenericOID@15caefab WorkAddress Id=2 NOTRES  is not in the hierarchy starting at TelerikFetchStrategy.Data.Entities.WorkAddress ('Addresses') (Type for row is HomeAddress)

Tracing the generated SQL, I see this:

SELECT a.[Id] AS COL1, a.[Id] AS COL2, a.[Name] AS COL3, b.[Id] AS COL4, b.[Type] AS COL5, b.[Text] AS COL6, b.[PersonId] AS xj2 FROM [People] a LEFT JOIN [Addresses] AS b ON (a.[Id] = b.[PersonId] AND b.[Type] = 'HomeAddress') ORDER BY COL1, xj2
go
SELECT a.[Id] AS COL1, b.[PersonId] AS COL2, b.[Id] AS COL3, b.[Type] AS COL4, b.[Text] AS COL5 FROM [People] a LEFT JOIN [Addresses] AS b ON (a.[Id] = b.[PersonId]) ORDER BY COL1, COL2
go
 

I can see that the discriminator is not being included in the second query, which means when it tries to resolve "WorkAddress" records, it is finding "HomeAddress" records and is complaining.  How can I fix this?

Thanks,
Paul S.

2 Answers, 1 is accepted

Sort by
0
Accepted
Kristian Nikolov
Telerik team
answered on 08 Jun 2015, 09:21 AM
Hi Paul,

Thank you for contacting us.

The setup you are attempting to implement requires some specific mapping. Since Person has two separate associations - one for HomeAddress and one with WorkAddress, the properties for those associations should be defined in the child classes HomeAddress and WorkAddress. Furthermore the full mapping for the associations should be used as shown in this article.

The following mapping setup illustrates those specifics:
//Mapping configuration
protected override IList<MappingConfiguration> PrepareMapping()
{
    List<MappingConfiguration> config = new List<MappingConfiguration>();
 
    var person = new MappingConfiguration<Person>();
    person.MapType().ToTable("People");
    person.HasProperty(p => p.Id).IsIdentity(KeyGenerator.Autoinc);
    person.HasProperty(p => p.Name).ToColumn("Name");
 
    //full association mapping
    person.HasAssociation(p => p.WorkAddresses)
        .WithOpposite(wa => wa.Person)
        .HasConstraint((p, wa) => p.Id == wa.PersonId);
    person.HasAssociation(p => p.HomeAddresses)
        .WithOpposite(ha => ha.Person)
        .HasConstraint((p, ha) => p.Id == ha.PersonId);
    config.Add(person);
 
    var address = new MappingConfiguration<Address>();
    address.MapType().ToTable("Addresses");
    address.HasProperty(a => a.Id).IsIdentity(KeyGenerator.Autoinc);
    address.HasProperty(a => a.Text).ToColumn("Text");
    address.HasDiscriminator().ToColumn("Type");
    config.Add(address);
 
    var homeAddress = new MappingConfiguration<HomeAddress>();
    homeAddress.MapType().Inheritance(InheritanceStrategy.Flat).ToTable("Addresses");
    homeAddress.HasDiscriminatorValue("HomeAddress");
    //Map the association specific property to the configuration of the child class
    homeAddress.HasProperty(a => a.PersonId).ToColumn("PersonIdHA");
 
    config.Add(homeAddress);
 
    var workAddress = new MappingConfiguration<WorkAddress>();
    workAddress.MapType().Inheritance(InheritanceStrategy.Flat).ToTable("Addresses");
    workAddress.HasDiscriminatorValue("WorkAddress");
    //Map the association specific property to the configuration of the child class
    workAddress.HasProperty(a => a.PersonId).ToColumn("PersonIdWA");
 
    config.Add(workAddress);
 
    return config;
}
 
//Entities
public class Person
{
    public Person()
    {
        WorkAddresses = new List<WorkAddress>();
        HomeAddresses = new List<HomeAddress>();
    }
 
    public int Id { get; set; }
    public string Name { get; set; }
    public IList<WorkAddress> WorkAddresses { get; set; }
    public IList<HomeAddress> HomeAddresses { get; set; }
}
 
public class Address
{
    public int Id { get; set; }
    public string Text { get; set; }
}
 
//Moved the association specific properties to the child classes
public class HomeAddress : Address
{
    public int PersonId { get; set; }
    public Person Person { get; set; }
}
 
public class WorkAddress : Address
{
    public int PersonId { get; set; }
    public Person Person { get; set; }
}

Please note that when using this mapping configuration you should update your database to match the model.
Using this mapping configuration and entities we successfully executed the query you have specified.

I hope this helps. Feel free to get back to us in case you have additional questions.

Regards,
Kristian Nikolov
Telerik
 
Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
0
Paul
Top achievements
Rank 1
answered on 09 Jun 2015, 06:04 PM

Hi Kristian,

Thanks for the pointer - I wouldn't have guessed that I needed separate foreign key columns for the two relationship types.  My query is working as expected.

Paul

Tags
Development (API, general questions)
Asked by
Paul
Top achievements
Rank 1
Answers by
Kristian Nikolov
Telerik team
Paul
Top achievements
Rank 1
Share this question
or