Multiple associations to the same table

3 posts, 1 answers
  1. Paul
    Paul avatar
    17 posts
    Member since:
    May 2011

    Posted 03 Jun 2015 Link to this post

    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. Answer
    Kristian Nikolov
    Admin
    Kristian Nikolov avatar
    206 posts

    Posted 08 Jun 2015 Link to this post

    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.
  3. DevCraft banner
  4. Paul
    Paul avatar
    17 posts
    Member since:
    May 2011

    Posted 09 Jun 2015 in reply to Kristian Nikolov Link to this post

    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

Back to Top