Multiple associations to the same table

[Paul]

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.

[Kristian Nikolov]

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

 

[Paul]

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