Error "Unknown column 't1.ProductId' in 'on clause'"

I get this error when trying to perform the following query. "Unknown column 't1.ProductId' in 'on clause'"

Snippet

                    var categorys = from c in GruntMageUow.MageCatalogProductEntities
                                    join d in GruntMageUow.MageCatalogCategoryProducts on c.Id equals d.ProductId
                                    where c.Sku == sku.ProductNumber
                                    select new
                                    {
                                        catId = d.Id
                                    };

I have also tried this way and get the same error
Snippet
                    var categorys = from c in GruntMageUow.MageCatalogProductEntities
                  //                where (from d in GruntMageUow.MageCatalogCategoryProducts
                    //                       where c.Sku == sku.ProductNumber
                    //                       select d.ProductId).Contains(c.Id)
                    //                select new { catId = c.Id };

trace log output is below

Query(Grunt.Data.Magento.MageCatalogProductEntity).Join(Query(Grunt.Data.Magento.MageCatalogCategoryProduct), c => c.Id, d => d.ProductId, (c, d) => new <>f__AnonymousType0`2(c = c, d = d)).Where(<>h__TransparentIdentifier17 => (<>h__TransparentIdentifier17.c.Sku == value(Grunt.NaturalOrderCommerceServerMapper.Magento.MagentoExportManager+<>c__DisplayClass20).sku.ProductNumber)).Select(<>h__TransparentIdentifier17 => new <>f__AnonymousType7`1(catId = <>h__TransparentIdentifier17.d.Id))

A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll

SELECT
  t1.product_id AS "t1.product_id"
FROM
  mage_catalog_product_entity t0
INNER JOIN
  mage_catalog_category_product t1
ON
  t0.entity_id = t1.ProductId
WHERE
  t0.Sku = 'HLMT3'

--> Time:   0 ms

Does the ProductId property have a defined column name which is not ProductId? (e.g. I notice your Id field has the column name of entity_id)

Jeremy

It has a defined column name of product_id

This is the code generated by lightspeed for that table

Snippet

 [Serializable]
  [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")]
  [System.ComponentModel.DataObject]
  [Table("mage_catalog_category_product", IdColumnName="product_id")]
  public partial class MageCatalogCategoryProduct : Entity<MageCatalogCategoryProductId>
  {
    #region Fields
  
    private int _position;
    private int _productId;
    private int _categoryId;
 
    #endregion
    
    #region Field attribute and view names
    
    /// <summary>Identifies the Position entity attribute.</summary>
    public const string PositionField = "Position";
    /// <summary>Identifies the ProductId entity attribute.</summary>
    public const string ProductIdField = "ProductId";
    /// <summary>Identifies the CategoryId entity attribute.</summary>
    public const string CategoryIdField = "CategoryId";
 
 
    #endregion
    
    #region Relationships
 
    [ReverseAssociation("MageCatalogCategoryProductsProduct")]
    private readonly EntityHolder<MageCatalogProductEntity> _product = new EntityHolder<MageCatalogProductEntity>();
    [ReverseAssociation("MageCatalogCategoryProductsCategory")]
    private readonly EntityHolder<MageCatalogCategoryEntity> _category = new EntityHolder<MageCatalogCategoryEntity>();
 
 
    #endregion
    
    #region Properties
 
    public MageCatalogProductEntity Product
    {
      get { return Get(_product); }
      set { Set(_product, value); }
    }
 
    public MageCatalogCategoryEntity Category
    {
      get { return Get(_category); }
      set { Set(_category, value); }
    }
 
 
    public int Position
    {
      get { return Get(ref _position, "Position"); }
      set { Set(ref _position, value, "Position"); }
    }
 
    /// <summary>Gets or sets the ID for the <see cref="Product" /> property.</summary>
    public int ProductId
    {
      get { return Get(ref _productId, "ProductId"); }
      set { Set(ref _productId, value, "ProductId"); }
    }
 
    /// <summary>Gets or sets the ID for the <see cref="Category" /> property.</summary>
    public int CategoryId
    {
      get { return Get(ref _categoryId, "CategoryId"); }
      set { Set(ref _categoryId, value, "CategoryId"); }
    }
 
    #endregion
  }
 
  [Serializable]
  [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")]
  public partial struct MageCatalogCategoryProductId
  {
    
    public MageCatalogCategoryProductId(
      uint categoryId ,
      uint productId     )
    {
      _categoryId = categoryId;
      _productId = productId;
    }
    
    #region Fields
 
    private readonly uint _categoryId;
    private readonly uint _productId;
 
    #endregion
    
    #region Properties
 
    public uint CategoryId
    {
      get { return _categoryId; }
    }
    public uint ProductId
    {
      get { return _productId; }
    }
 
    #endregion
    
    #region Dictionary support
    
    public override int GetHashCode()
    {
      int hashCode = 0;
      hashCode = 19 * hashCode + _categoryId.GetHashCode();
      hashCode = 19 * hashCode + _productId.GetHashCode();
      return hashCode;
    }
 
    #endregion
  }

Thanks - that looks like a bug so Ill look at setting up a repro here and look at getting this fixed. We are at TechEd for the next few days so it wont be until next week that we can have a look at this and get something up for you in the nightlies.

Jeremy

Thanks con't wait to get the fix

Hi Kerry,

Have just come to having another look at this and in setting up a repro case here I notice that in your model you are actually missing a declaration on the ProductId column which indicates that it is called product_id in the database. Since this is not in place this leads LightSpeed to generate the column as ProductId.

So I dont think there is any actual bug, rather you just need to set a specific column name for that property. You can do this through the designer by right clicking on the ProductId property and setting the column name field.

Jeremy

Just to follow on from this, I notice you have the Id field in that table already mapped to product_id, so you are not going to be able to map both. Is ProductId going to be the same column? If so, you should remove that property and then update your join to join on Id rather than ProductId.

Jeremy

When I drag the table from the database in the designer it gives an image that specifies

Identity

   categoryid

   productid

Properties

   position

Even if i add a column name the lightspeed generated code does not change. I can add it manually but when I recompile it changes it back. I think this is because the database primary key is both the categoryid and the productid combined. which map to category_id and product_id columns

If the ProductId is part of a composite key, then you can refer to it using entity.Id.ProductId.  You still shouldn't have separate ProductId and CategoryId fields in the Properties section, as this would result in those columns being mapped twice (once to the Identity properties and once to the 'normal' properties).