"Unable to materialize field" Error

I am receiving the following error.  I would just like to confirm that eitehr a) I am donig something wrong, or b) it is not a supported configuration, rather than a bug.  The error message is:

Unable to materialize field [VendorCodeId] on type [ResortManager.Data.MarketListHeader]. Check your table has an Id column and that your mappings are correct. See

The scenario that generates this error is as follows.  A table called MarketListHeader, that contains a text column called VendorCode.  A second table called Vendor, that has a numeric key  Id column, and a Unique text column called VendorCode.  The two tables are joined using the VendorCode columns.

When I try to use the uow.Find<MarketListHeader>(); method I receive the error.  If I join the tables via the key Id field, there is no error message. 

However, doing it that way creates another problem, I need to join the tables via a known, controllable, text field, as the data is sync'd between different databases, and we need to ensure related records in different tables remain joined, something very difficult using the Id field.

Thanks in advance for your guidance.

Hi Mark,

There is nothing wrong with what you are doing, and joining on an arbitrary field other than Id is the primary case for using a join :)

The error above occurs when something doesnt reconcile between the internal view of the record set and the actual record set being returned from the server - would you be able to send through a small repro of this and we can have a look at how it is occuring as this will be somewhat specific to the model you are using. Perhaps if you can just distill it down to the two entities in question (and including any derived types) and fire through a model file and the sample data you are using that should be enough to recreate the issue here.

Jeremy

Hi Jeremy,

Thanks for the feedback.  After your suggestion that it might be an internal porblem, I have discovered something a little strange.  below is a copy of the class generated by the model.  What is strange is the _vendorId which is cast as an int, but linked to the column "VendorCode" which is a string.

Not sure if this helps, if not, I will try to put together a smaller version as soon as possible.

Thanks

public partial class MarketListHeader : Entity<int>
  {
    #region Fields
 
    [ValidatePresence]
    [ValidateLength(0, 50)]
    private string _marketList;
    private System.DateTime _marketListDate;
    private int _status;
    [ValidatePresence]
    [ValidateUnique]
    [ValidateLength(0, 10)]
    private string _orderNo;
    [Column("VendorCode")]
    private int _vendorId;

    #endregion
   
    #region Field attribute and view names
   
    /// <summary>Identifies the MarketList entity attribute.</summary>
    public const string MarketListField = "MarketList";
    /// <summary>Identifies the MarketListDate entity attribute.</summary>
    public const string MarketListDateField = "MarketListDate";
    /// <summary>Identifies the Status entity attribute.</summary>
    public const string StatusField = "Status";
    /// <summary>Identifies the OrderNo entity attribute.</summary>
    public const string OrderNoField = "OrderNo";
    /// <summary>Identifies the VendorId entity attribute.</summary>
    public const string VendorIdField = "VendorId";

    #endregion
   
    #region Relationships

    [EagerLoad]
    [ReverseAssociation("MarketList")]
    private readonly EntityCollection<MarketListDetail> _marketListDetails = new EntityCollection<MarketListDetail>();
    [ReverseAssociation("MarketListHeaders")]
    private readonly EntityHolder<Vendor> _vendor = new EntityHolder<Vendor>();

    #endregion
   
    #region Properties

    public EntityCollection<MarketListDetail> MarketListDetails
    {
      get { return Get(_marketListDetails); }
    }

    public Vendor Vendor
    {
      get { return Get(_vendor); }
      set { Set(_vendor, value); }
    }

    public string MarketList
    {
      get { return Get(ref _marketList, "MarketList"); }
      set { Set(ref _marketList, value, "MarketList"); }
    }

    public System.DateTime MarketListDate
    {
      get { return Get(ref _marketListDate, "MarketListDate"); }
      set { Set(ref _marketListDate, value, "MarketListDate"); }
    }

    public int Status
    {
      get { return Get(ref _status, "Status"); }
      set { Set(ref _status, value, "Status"); }
    }

    public string OrderNo
    {
      get { return Get(ref _orderNo, "OrderNo"); }
      set { Set(ref _orderNo, value, "OrderNo"); }
    }

    /// <summary>Gets or sets the ID for the <see cref="Vendor" /> property.</summary>
    public int VendorId
    {
      get { return Get(ref _vendorId, "VendorId"); }
      set { Set(ref _vendorId, value, "VendorId"); }
    }

Your problem is that the VendorCode column is not associated with the Id column of the Vendor table: it is associated with the unique Vendor.VendorCode column.  LightSpeed associations are *always* on the Id (the primary key).  Therefore, the designer is looking at the Id of the Vendor type, seeing that it is Int32, and concluding that the foreign key field must also be an Int32.  But you are then mapping the foreign key field to the MarketListHeader.VendorCode column, which is a string.

We don't support associations on non-Id unique keys (though it is on the wishlist).  Therefore, as Jeremy has suggested, you will need to implement this with a join or a helper method that performs an explicit query.  For example:

partial class MarketListHeader {
  public Vendor Vendor {
    get { return this.UnitOfWork.FindOne<Vendor>(Entity.Attribute("VendorCode") == this.VendorCode); }
    set { this.VendorCode = value.VendorCode; }
  }
}

and similarly at the other end for the Vendor.MarketListHeaders collection.  (I have ignored optimisations such as caching the query result.)

Hope this makes sense -- let me know if you need any further info.

Thanks for the feedback, I had a feeling this was going to be the case.  

I will try your suggested code, that looks like it may achieve what I need.  But I do hope you ccan implement this type of joins in a future release.

Many thanks