1:n with JoinTable FluentAPI

[Markus]

Hello,

I am trying to migrate a project from old api to fluent api. I have a class with a 1:n self- reference mapping. Referenced objects are ordered, so I have a join table with sequence column. How do I configure this in fluent api?

Join Table Location2Location:

ParentLocationID int,
seq int,
SubLocationID int

public class Location
{
     int _id;
     public int Id
     {
         get { return _id; }
     }

     string Name { get; set; }

     int ParentLocationId { get; }

     IList<Location> SubLocations { get; set; }
}

configuration.HasAssociation<Location>(x => x.Parent).WithOpposite(x => x.SubLocations).MapJoinTable("Location2Location", (subLocation,parentLocation ) => new
                {
                    ParentLocationID = parentLocation.Id,
                    SubLocationID = subLocation.Id
                }).HasSequenceColumn("seq");

[Zoran]

Hello Markus,

 I just double checked that the configuration that you sent in your letter is the correct one for this scenario. You should go ahead with it and have the desired behavior in your application. Have you had problems with this configuration so far? If yes, we would like to ask you to share more details with us regarding it, so we can take some actions in the direction of locating and fixing the potential bug.

Greetings,
Zoran
the Telerik team

[Markus]

Hello Zoran,

it does not work for me. When I try to load all locations context.GetAll<Location>() it throws exception:
Invalid column name  'parent_Location_id'.   bei Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery()
   bei OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery()
   bei OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute()
SQL:
SELECT a.[Location_id] AS COL1, a.[Name] AS COL2, a.[parent_Location_id] AS COL3, a.[ParentLocationID] AS COL4 FROM [Location] a ORDER BY COL1  Telerik.OpenAccess.RT.sql.SQLException: Ungültiger Spaltenname 'parent_Location_id'.

Do I need a class 'Location2Location' ?
Here is my complete class:

public class Location
    {
        int _id;
        public int Id
        {
            get { return _id; }
        }


        string _name;

        public string Name
        {
            get { return _name; }
            set { _name = value; }
        }


        int _parentId;

        public int ParentId
        {
            get { return _parentId; }
            set { _parentId = value; }
        }



        Location _parent;

        public Location Parent
        {
            get { return _parent; }
            set { _parent = value; }
        }



        IList<Location> _subLocations = new List<Location>();

        public IList<Location> SubLocations
        {
            get { return _subLocations; }
            set { _subLocations = value; }
        }



        public static MappingConfiguration GetMapping()
        {
            MappingConfiguration<Location > configuration = new MappingConfiguration<Location >();
            configuration.FieldNamingRules.AddPrefix = "_";

            configuration.MapType().WithDataAccessKind(DataAccessKind.ReadWrite).WithConcurencyControl(OptimisticConcurrencyControlStrategy.None).ToTable("Location");

            configuration.HasProperty(x => x.Id).IsIdentity(Telerik.OpenAccess.Metadata.KeyGenerator.HighLow).HasFieldName("_id").WithDataAccessKind(DataAccessKind.ReadWrite).ToColumn("Location_id").IsNotNullable().HasColumnType("int").HasPrecision(0).HasScale(0);
            configuration.HasProperty(x => x.Name).HasFieldName("_name").WithDataAccessKind(DataAccessKind.ReadWrite).ToColumn("Name").IsNotNullable().HasColumnType("varchar").HasLength(50);
            configuration.HasProperty(x => x.ParentId).HasFieldName("_parentId").WithDataAccessKind(DataAccessKind.ReadWrite).ToColumn("ParentLocationID").IsNullable().HasColumnType("int").HasPrecision(0).HasScale(0);

            configuration.HasAssociation<Location>(x => x.Parent).WithOpposite(x => x.SubLocations).MapJoinTable("Location2Location", (subLocation, parentLocation) => new
                {
                    ParentLocationID = parentLocation.Id,
                    SubLocationID = subLocation.Id
                }).HasSequenceColumn("seq");

            return configuration;
        }
    }

Regards,
Markus

[Zoran]

Hello Markus,

 Please let me apologize for misleading you in my previous response. I must admit that I misunderstood you and and verified a case where the Parent property was also a List<Location> objects and not a single Location reference. We just verified that there is a bug in this scenario and that bug will be fixed by our next major release. At the moment, you can workaround the bug though by overriding the CreateModel() method in you FluentMetadataSource derived class. Here is a sample of what you should do in order to workaround the reported issue:

public class FluentSource : FluentMetadataSource
{
    protected override IList<MappingConfiguration> PrepareMapping()
    {
        return new List<MappingConfiguration>() { };
    }

    protected override Telerik.OpenAccess.Metadata.MetadataContainer CreateModel()
    {
        MetadataContainer container = base.CreateModel();
        MetaAssociation association = container.Associations
.First(a => a.Source.FullName == typeof(Location).FullName && a.Target.FullName == typeof(Location).FullName);

        association.AssociationType = AssociationType.OneToManyJointable;

        return container;
    }
}

All the best,
Zoran
the Telerik team

[Markus]

Hello Zoran,

I tried your workaround, but I get same error. Error: invalid column 'parent_location_id'. I think the problem is that in my existing Location- table (not JoinTable) the column for the Parent- poperty is named as 'ParentID'. But OpenAccess expects 'parent_location_id.
How can I configure the name for this column?

I tried:

configuration.HasAssociation<Location>(x => x.Parent).ToColumn("ParentLocationID");

 

- and -
configuration.HasAssociation<Location>(x => x.Parent).ToColumn("ParentLocationID")
.WithOpposite(x => x.SubLocations).MapJoinTable( ...

Regards,
Markus

 

 

 

 

[Zoran]

Hello Markus,

 The problem with the current setup is that you have a join table that contains the data about the self reference and you also have a column called PrentLocationId in your Locations table. Can you confirm that this is the case? If that is really the scenario, then does that mean that you have doubled data in your ParentColumnId table in the Locations table that points to the parent location and the join table that also takes care about the referenced data?

I would like to ask you about your database schema, is it possible for you to send us the script for the involved tables in your database?  We are interested about what is the join table actually referencing there, does it reference the Id column in it ParentLocationId and SubLocationId columns? This would explain why OpenAccess tries to create a parent_location_id column. This column is created because there is no mapping in this way for the Parent property which is of type Location. Do you actually have two associations in your model, one tracking the parent and another one tracking the SubLocations? For me this really seems as you are having two associations in your model, one that tracks the parent storing a value in the ParentLocationId property of the Locations table and another one tracking the children in the join-table. In this case, the following mapping should do the job for you:

public static MappingConfiguration GetMapping()
{
    MappingConfiguration<Location> configuration = new MappingConfiguration<Location>();
    configuration.FieldNamingRules.AddPrefix = "_";

    configuration.MapType().WithDataAccessKind(DataAccessKind.ReadWrite).WithConcurencyControl(OptimisticConcurrencyControlStrategy.None).ToTable("Location");

    configuration.HasProperty(x => x.Id).IsIdentity(Telerik.OpenAccess.Metadata.KeyGenerator.HighLow).HasFieldName("_id").WithDataAccessKind(DataAccessKind.ReadWrite).ToColumn("Location_id").IsNotNullable().HasColumnType("int").HasPrecision(0).HasScale(0);
    configuration.HasProperty(x => x.Name).HasFieldName("_name").WithDataAccessKind(DataAccessKind.ReadWrite).ToColumn("Name").IsNotNullable().HasColumnType("varchar").HasLength(50);
    configuration.HasProperty(x => x.ParentId).HasFieldName("_parentId").WithDataAccessKind(DataAccessKind.ReadWrite).ToColumn("ParentLocationID").IsNullable().HasColumnType("int").HasPrecision(0).HasScale(0);

    configuration.HasAssociation(x => x.SubLocations).MapJoinTable("Location2Location", (subLocation, parentLocation) => new
    {
        ParentLocationID = parentLocation.Id,
        SubLocationID = subLocation.Id
    }).HasSequenceColumn("seq");


    configuration.HasAssociation(x => x.Parent).ToColumn("ParentLocationID");


    return configuration;
}

Regards,
Zoran
the Telerik team

[Markus]

Hello Zoran,

thank you for your answer, your last code example together with overriding of CreateModel work for me. My model is not designed very well in this case. Normally I would not need a join table. But now it works. Thanks!