ThroughAssociation Problem

Is there a known problem with ThroughAssociation where if you query both sides of the many-to-many relationship in the same method that it will return one of the through associations, but not the other?

Here's my method code:

            Penguin penguin = UnitOfWorkHolder.Current.FindOne<Penguin>(1);
            IList<Lion> lions = penguin.Lions;
            Debug.WriteLine(String.Format("Lion count is {0}", lions.Count));

            Lion lion = UnitOfWorkHolder.Current.FindOne<Lion>(1);
            IList<Penguin> penguins = lion.Penguins;
            Debug.WriteLine(String.Format("Penguin count is {0}", penguins.Count));

There's a table in the middle called PENGUINS_LIONS to maintain the through association. If I run each block separately, they return the appropriate through assocation collection. If I run them together, one after the other, the second through association returns a 0 count for penguins.

 I initially thought this had to do with the CRUD sp support, but I removed that and still see this behavior. Granted, this case may not make alot of sense in real world code, but I was just putting together a demo and ran into the issue.

Thanks.

Hmm, we're not seeing that behaviour, so this may be a glitch with load order.  Could you send us the definitions of the Penguin and Lion classes please so we can look into this?  Thanks!

Here ya go.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Mindscape.LightSpeed;
using Mindscape.LightSpeed.Validation;
using System.Text.RegularExpressions;

namespace LightSpeedCombinedSample
{
    //The table attribute attaches a class to a table in the case where you are not using stored procedures
    //The IdColumn is used to define the column name in the table that is used as the primary key id value
    [Table("PENGUINS", IdColumnName = "ID")]
    //The CrudProcedure attribute defines the stored procedure used to populate the entity
    //Currently, you need to assign all of the procedure names. In the future they may be inferred by LightSpeed.
    [CrudProcedure(
      Select = "SelectPenguins",
      SelectById = "SelectPenguin",
      Insert = "CreatePenguin",
      Update = "UpdatePenguin",
      Delete = "DeletePenguin")]
    public class Penguin : Entity<int>  //All LightSpeed entities must inherit from Entity<int>
    {
        //Define all columns in the table or stored procedure, except for the ID column, which is
        //a property of the base Entity<int> class
        #region Private Members       
        [Column("SPECIES")]         //Mapping of the private member to a column name in a table
        private string _species;
        [Column("NAME")]            //Mapping of the private member to a column name in a table
        [ValidatePresence]          //Required field validation
        [ValidateUnique]            //Ensures the value is unique in the table
        private string _name;
        [Column("AGE")]             //Mapping of the private member to a column name in a table
        [ValidateFormat(@"^\d+$")]  //Validates the format of a field based on a regular expression
        private int _age;
        [Column("PARENT")]          //Mapping of the private member to a column name in a table
        private int? _parentPenguinId;
        //If a table contains a CreatedOn field, it will automatically be populated with the Create Date by LightSpeed
        [Column("CREATEDON")]       //Mapping of the private member to a column name in a table
        private DateTime _createdOn;
        //If a table contains a UpdatedOn field, it will automatically be populated with the Create Date by LightSpeed
        [Column("UPDATEDON")]       //Mapping of the private member to a column name in a table
        private DateTime _updatedOn;
        #endregion

        //Relationships define the associations to other tables
        #region Relationships
        [ReverseAssociation("ChildPenguins")]   //A reverse association helps LightSpeed know the property that the association is linked to
        [CrudProcedure(Select = "SelectChildPenguins")] //When using stored procedures, the CrudProcedure attribute specifies the procuedure used to load the association
        private readonly EntityCollection<Penguin> _childPenguins = new EntityCollection<Penguin>();    //This EntityCollection holds an association, in this case it is the same table since the table has a self referencing association
        private readonly EntityHolder<Penguin> _parentPenguin = new EntityHolder<Penguin>();    //This EntityHolder is a back link to an associated table, in this case it is the same table since the table has a self referencing association
        [ReverseAssociation("Seals")]   //A reverse association helps LightSpeed know the property that the association is linked to
        private readonly EntityCollection<Seal> _seals = new EntityCollection<Seal>();
        private readonly EntityCollection<PenguinLion> _penguinLions = new EntityCollection<PenguinLion>(); //This Entity Collection holds the many-to-many table association
        private ThroughAssociation<PenguinLion, Lion> _lions;   //To bypass the many-to-many table, this ThroughAssociation will create a link directly
        #endregion

        #region Delegates
        private EventHandler<EntityEventArgs<Seal>> handler;    //This delegate is used to attach events to an entity, for example when an entity is modified
        #endregion

        #region Constructors
        public Penguin()
        {
            //When tracking entity changes (Added, Removed) you can attach to an event and handle special logic
            handler += new EventHandler<EntityEventArgs<Seal>>(_seals_EntityAdded);
            _seals.EntityAdded += handler;

            //When using a many-to-many relationship, you need to setup the ThroughAssociation when creating the instance so it can properly map across the many-to-many without using the middle class
            _lions = new ThroughAssociation<PenguinLion, Lion>(_penguinLions);
        }
        #endregion
        #region Destructors
        ~Penguin()
        {
            //When attaching to entity events, you need to clean up the event handlers in the destructor to make sure they go away gracefully
            if (handler != null)
                _seals.EntityAdded -= handler;
        }
        #endregion

        #region Event Handlers
        //Here you can perform special logic, like setting association Ids.
        void _seals_EntityAdded(object sender, EntityEventArgs<Seal> e)
        {
            // e is the Entity being added and this is the Entity that is getting the new Entity
            e.Entity.Name = String.Format("{0} - {1}", e.Entity.Name, this.Id);
        }
        #endregion

        //Each private member should have an associated property that is accessed to get the data
        //Always call the Get and Set methods in the property get/set so that LightSpeed can track
        //state and notifications
        #region Properties
        public int? ParentPenguinId
        {
            get { return Get(ref _parentPenguinId); }
            set { Set(ref _parentPenguinId, value, "ParentPenguinId"); }
        }
        public string Name
        {
            get { return Get(ref _name); }
            set { Set(ref _name, value, "Name"); }
        }
        public string Species
        {
            get { return Get(ref _species); }
            set { Set(ref _species, value, "Species"); }
        }
        public int Age
        {
            get { return Get(ref _age); }
            set { Set(ref _age, value, "Age"); }
        }
        public DateTime CreatedOn
        {
            get { return Get(ref _createdOn); }
            set { Set(ref _createdOn, value, "CreatedOn"); }
        }
        public DateTime UpdatedOn
        {
            get { return Get(ref _updatedOn); }
            set { Set(ref _updatedOn, value, "UpdatedOn"); }
        }
        public EntityCollection<Penguin> ChildPenguins
        {
            get { return Get(_childPenguins); }
        }
        public Penguin ParentPenguin
        {
            get { return Get(_parentPenguin); }
            set { Set(_parentPenguin, value); }
        }
        public EntityCollection<Seal> Seals
        {
            get { return Get(_seals); }
        }
        public EntityCollection<PenguinLion> PenguinLions   //This EntityCollection is the middle table in a many-to-many association
        {
            get { return Get(_penguinLions); }
        }
        public ThroughAssociation<PenguinLion, Lion> Lions  //This ThroughAssociation is what bypasses the middle table in a many-to-many association
        {
            get { return _lions; }
        }
        #endregion

        #region Methods
        //Using the Find method, it is possible to pass a predicate to simplfy the searching for records in a collection
        public static Predicate<Penguin> ByPenguinName(string penguinName)
        {
            return delegate(Penguin penguins)
            {
                return (penguins.Name == penguinName);
            };
        }
        #endregion

        #region Custom Validation
        //You can perform custom validations in the OnValidate event of the Entity if the Validation attributes do not cover what is needed
        protected override void OnValidate()
        {
          if ((Name == "P0") && (Species == "Species0"))
          {
            Errors.AddError("This is an invalid combination of fields.");
          }
        }
        #endregion

    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Mindscape.LightSpeed;
using Mindscape.LightSpeed.Validation;

namespace LightSpeedCombinedSample
{
    //The table attribute attaches a class to a table in the case where you are not using stored procedures
    //The IdColumn is used to define the column name in the table that is used as the primary key id value
    [Table("LIONS", IdColumnName = "ID")]
    public partial class Lion : Entity<int> //All LightSpeed entities must inherit from Entity<int>
    {
        //Define all columns in the table or stored procedure, except for the ID column, which is
        //a property of the base Entity<int> class
        #region Private Members
        [ValidatePresence]  //Required field validation
        [ValidateUnique]    //Ensures the value is unique in the table
        [Column("NAME")]    //Mapping of the private member to a column name in a table
        private string _name;
        #endregion

        //Relationships define the associations to other tables
        #region Relationships
        //EagerLoad when specified with dynamic SQL will automatically load the associations
        //Otherwise, the associations are not loaded until referenced
        [EagerLoad(AggregateName = "LoadAll")]
        private readonly EntityCollection<PenguinLion> _penguinLions = new EntityCollection<PenguinLion>(); //This EntityCollection holds an association
        //EagerLoad when specified with dynamic SQL will automatically load the associations
        //Otherwise, the associations are not loaded until referenced
        [EagerLoad]
        private ThroughAssociation<PenguinLion, Penguin> _penguins; //To bypass the many-to-many table, this ThroughAssociation will create a link directly
        #endregion

        #region Constructors
        public Lion()
        {
            //When using a many-to-many relationship, you need to setup the ThroughAssociation when creating the instance so it can properly map across the many-to-many without using the middle class
            _penguins = new ThroughAssociation<PenguinLion, Penguin>(_penguinLions);
        }
        #endregion

        //Each private member should have an associated property that is accessed to get the data
        //Always call the Get and Set methods in the property get/set so that LightSpeed can track
        //state and notifications
        #region Properties
        public string Name
        {
            get { return Get(ref _name); }
            set { Set(ref _name, value, "Name"); }
        }
        public EntityCollection<PenguinLion> PenguinLions   //This EntityCollection is the middle table in a many-to-many association
        {
            get { return Get(_penguinLions); }
        }
        public ThroughAssociation<PenguinLion, Penguin> Penguins    //This ThroughAssociation is what bypasses the middle table in a many-to-many association
        {
            get { return _penguins; }
        }
        #endregion
    }
}

You may also want this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Mindscape.LightSpeed;
using Mindscape.LightSpeed.Validation;

namespace LightSpeedCombinedSample
{
    // The table attribute attaches a class to a table in the case where you are not using stored procedures
    // The IdColumn is used to define the column name in the table that is used as the primary key id value
    [Table("PENGUINS_LIONS", IdColumnName = "ID")]
    public partial class PenguinLion : Entity<int>
    {
        //Define all columns in the table or stored procedure, except for the ID column, which is
        //a property of the base Entity<int> class
        #region Private Members
        [ValidatePresence]      //Required field validation
        [Column("PENGUIN_ID")]  //Mapping of the private member to a column name in a table
        private int _penguinId;
        [ValidatePresence]      //Required field validation
        [Column("LION_ID")]     //Mapping of the private member to a column name in a table
        private int _lionId;
        #endregion

        //Relationships define the associations to other tables
        #region Relationships
        //EagerLoad when specified with dynamic SQL will automatically load the associations
        //Otherwise, the associations are not loaded until referenced
        [EagerLoad]
        private readonly EntityHolder<Penguin> _penguin = new EntityHolder<Penguin>();  //This EntityHolder is a back link to an associated table
        //EagerLoad when specified with dynamic SQL will automatically load the associations
        //Otherwise, the associations are not loaded until referenced
        [EagerLoad]
        private readonly EntityHolder<Lion> _lion = new EntityHolder<Lion>();   //This EntityHolder is a back link to an associated table
        #endregion

        //Each private member should have an associated property that is accessed to get the data
        //Always call the Get and Set methods in the property get/set so that LightSpeed can track
        //state and notifications
        #region Properties
        public int PenguinId
        {
            get { return _penguinId; }
            set { Set(ref _penguinId, value, "PenguinId"); }
        }
        public int LionId
        {
            get { return _lionId; }
            set { Set(ref _lionId, value, "LionId"); }
        }
        public Penguin Penguin
        {
            get { return Get(_penguin); }
        }
        public Lion Lion
        {
            get { return Get(_lion); }
        }
        #endregion

        #region Methods
        //Using the Find method, it is possible to pass a predicate to simplfy the searching for records in a collection
        public static Predicate<PenguinLion> ByPenguinName(string penguinName)
        {
            return delegate(PenguinLion PenguinLions)
            {
                return (PenguinLions.Penguin.Name == penguinName);
            };
        }
        #endregion
    }
}
 

Looks like you have a couple of misplaced EagerLoad attributes causing you some grief.

I have set up your model and some test data locally here and could see what you were meaning about it not fetching the other side. On investigation, I noticed you had some EagerLoad attributes on the Penguins_Lions entity - these are not required.

You also need to make sure that each entity is eager loading the Penguins_Lions, so that this is available when it initializes the ThroughAssociation in the constructor.

e.g. On Lions entity:

      [EagerLoad]
      [ReverseAssociation("Lions")]
      private readonly EntityCollection<PenguinLion> _penguinLions = new EntityCollection<PenguinLion>(); //This EntityCollection holds an association
      //EagerLoad when specified with dynamic SQL will automatically load the associations
      //Otherwise, the associations are not loaded until referenced
      private ThroughAssociation<PenguinLion, Penguin> _penguins;

On Penguins entity:

      [EagerLoad]
      [ReverseAssociation("Penguins")]
      private readonly EntityCollection<PenguinLion> _penguinLions = new EntityCollection<PenguinLion>(); //This Entity Collection holds the many-to-many table association
      private ThroughAssociation<PenguinLion, Lion> _lions;

On Penguins_Lions:

      #region Relationships
      //EagerLoad when specified with dynamic SQL will automatically load the associations
      //Otherwise, the associations are not loaded until referenced
      private readonly EntityHolder<Penguin> _penguin = new EntityHolder<Penguin>();  //This EntityHolder is a back link to an associated table
      //EagerLoad when specified with dynamic SQL will automatically load the associations
      //Otherwise, the associations are not loaded until referenced     
      private readonly EntityHolder<Lion> _lion = new EntityHolder<Lion>();   //This EntityHolder is a back link to an associated table
      #endregion

And that sorts it for me here - let us know how you get on :)

Jeremy

Good deal. Let me look at that and see if it helps.

On a side note. Should I be able to things like First<> and Where<> on a ThroughAssociation?

I get this error when trying:

ThroughAssociation<,> is IEnumerable<> so you should be able to use all the LINQ to Objects operators including First and Where, though note that these will run client-side over the collection rather than as a SQL query.  Have you done a using System.Linq; to bring the extension operators into scope?