Built small model in 2.0, synchronized to database, now getting "specified cast not valid"

 Model Code:

using System;

using Mindscape.LightSpeed;
using Mindscape.LightSpeed.Validation;
using Mindscape.LightSpeed.Linq;

namespace RationalPath.CMS.Data
{
  [Serializable]
  [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")]
  [Table(IdColumnName="Id")]
  public partial class Menu : Entity<int>
  {
    #region Fields
 
    private string _label;

    #endregion
   
    #region Relationships

    private readonly EntityCollection<MenuEntry> _menuEntries = new EntityCollection<MenuEntry>();

    #endregion
   
    #region Properties

    public EntityCollection<MenuEntry> MenuEntries
    {
      get { return Get(_menuEntries); }
    }

    public string Label
    {
      get { return _label; }
      set { Set(ref _label, value, "Label"); }
    }

    #endregion
  }

  [Serializable]
  [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")]
  [Table(IdColumnName="Id")]
  [OrderBy("Weight")]
  public partial class MenuEntry : Entity<int>
  {
    #region Fields
 
    private string _label;
    private string _path;
    private System.Nullable<int> _parent;
    private string _roles;
    private int _weight;
    private int _menuId;
    private int _parentEntryId;

    #endregion
   
    #region Relationships

    [EagerLoad]
    [OrderBy("Weight")]
    private readonly EntityCollection<MenuEntry> _childEntries = new EntityCollection<MenuEntry>();
    [ReverseAssociation("MenuEntries")]
    private readonly EntityHolder<Menu> _menu = new EntityHolder<Menu>();
    [ReverseAssociation("ChildEntries")]
    private readonly EntityHolder<MenuEntry> _parentEntry = new EntityHolder<MenuEntry>();

    #endregion
   
    #region Properties

    public EntityCollection<MenuEntry> ChildEntries
    {
      get { return Get(_childEntries); }
    }

    public Menu Menu
    {
      get { return Get(_menu); }
      set { Set(_menu, value); }
    }

    public MenuEntry ParentEntry
    {
      get { return Get(_parentEntry); }
      set { Set(_parentEntry, value); }
    }

    public string Label
    {
      get { return _label; }
      set { Set(ref _label, value, "Label"); }
    }

    public string Path
    {
      get { return _path; }
      set { Set(ref _path, value, "Path"); }
    }

    public System.Nullable<int> Parent
    {
      get { return _parent; }
      set { Set(ref _parent, value, "Parent"); }
    }

    public string Roles
    {
      get { return _roles; }
      set { Set(ref _roles, value, "Roles"); }
    }

    public int Weight
    {
      get { return _weight; }
      set { Set(ref _weight, value, "Weight"); }
    }

    public int MenuId
    {
      get { return _menuId; }
      set { Set(ref _menuId, value, "MenuId"); }
    }

    public int ParentEntryId
    {
      get { return _parentEntryId; }
      set { Set(ref _parentEntryId, value, "ParentEntryId"); }
    }

    #endregion
  }


  [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")]
  public partial class WebsiteUnitOfWork : Mindscape.LightSpeed.UnitOfWork
  {
    public System.Linq.IQueryable<Menu> Menus
    {
      get { return this.Query<Menu>(); }
    }
    public System.Linq.IQueryable<MenuEntry> MenuEntries
    {
      get { return this.Query<MenuEntry>(); }
    }
  }

}

Model Definition

<?xml version="1.0" encoding="utf-8"?>
<minimalLanguageDiagram dslVersion="1.0.0.0" absoluteBounds="0, 0, 11, 8.5" name="Website">
  <modelMoniker name="/Website" />
  <nestedChildShapes>
    <entityShape Id="5c3cbeb4-8458-4404-a47b-d81602b912c6" absoluteBounds="0.625, 0.5, 1.5, 0.70230143229166675">
      <entityMoniker name="/Website/Menu" />
      <nestedChildShapes>
        <elementListCompartment Id="a4b8c59c-3970-4ec6-a930-9f175fecf2e5" absoluteBounds="0.6399999999999999, 0.91000000000000014, 1.4700000000000002, 0.19230143229166666" name="Properties" titleTextColor="Black" itemTextColor="Black" />
      </nestedChildShapes>
    </entityShape>
    <entityShape Id="e66e43d5-6176-4e30-a1ae-75dbf2735f6f" absoluteBounds="2, 1.75, 1.5, 1.4715071614583333">
      <entityMoniker name="/Website/MenuEntry" />
      <nestedChildShapes>
        <elementListCompartment Id="3f474079-f9f8-4582-a059-031c87b00db5" absoluteBounds="2.015, 2.16, 1.4700000000000002, 0.96150716145833326" name="Properties" titleTextColor="Black" itemTextColor="Black" />
      </nestedChildShapes>
    </entityShape>
    <oneToManyAssociationConnector edgePoints="[(1.375 : 1.20230143229167); (1.375 : 2.86075358072917); (2 : 2.86075358072917)]" manuallyRouted="true" fixedFrom="Algorithm" fixedTo="Algorithm">
      <oneToManyAssociationMoniker Id="a5d09604-174c-4746-bcbd-febc70ebc396" />
      <relativeChildShapes />
      <nodes>
        <entityShapeMoniker Id="5c3cbeb4-8458-4404-a47b-d81602b912c6" />
        <entityShapeMoniker Id="e66e43d5-6176-4e30-a1ae-75dbf2735f6f" />
      </nodes>
    </oneToManyAssociationConnector>
    <oneToManyAssociationConnector edgePoints="[(2.20833333333333 : 3.22150716145833); (2.20833333333333 : 3.501923828125); (3.65602705829327 : 3.501923828125); (3.65602705829327 : 3.34404466145833); (2.97894372495994 : 3.34404466145833); (2.97894372495994 : 3.22150716145833)]" manuallyRouted="true" fixedFrom="Caller" fixedTo="Algorithm">
      <oneToManyAssociationMoniker Id="f9201fb2-b7aa-4db0-9581-e1cbe433ec41" />
      <relativeChildShapes />
      <nodes>
        <entityShapeMoniker Id="e66e43d5-6176-4e30-a1ae-75dbf2735f6f" />
        <entityShapeMoniker Id="e66e43d5-6176-4e30-a1ae-75dbf2735f6f" />
      </nodes>
    </oneToManyAssociationConnector>
  </nestedChildShapes>
</minimalLanguageDiagram>

I assume you are getting the "specified cast not valid" when you load data into the model from the database (i.e. not from the designer)?

If that is correct, could you post a copy of your database schema, and which database provider you are using?  Also it would be useful if you could post the stack trace of the InvalidCastException.  Thanks!

Actually it was going the other way, trying to update the database, though I guess it probably happens either way. Here is the schema though:

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for menuentries
-- ----------------------------
DROP TABLE IF EXISTS `menuentries`;
CREATE TABLE `menuentries` (
  `Id` int(11) NOT NULL,
  `Label` varchar(10) NOT NULL DEFAULT '',
  `Path` varchar(10) NOT NULL DEFAULT '',
  `Parent` int(11) NOT NULL DEFAULT '0',
  `Roles` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for menus
-- ----------------------------
DROP TABLE IF EXISTS `menus`;
CREATE TABLE `menus` (
  `Id` int(11) NOT NULL,
  `Label` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records
-- ----------------------------
 

I'm afraid I'm having difficulty reproducing your problem.

At the moment, your database schema as posted is out of sync with your model because it does not contain the parent-child relationship between MenuEntries (i.e. a ParentEntryId column and associated foreign key).  When I run Update Database, I get a message telling me that the MySQL provider doesn't support adding references (we don't have this feature for MySQL yet, sorry).

If I go ahead and run the LightSpeed code anyway, when I try to load or save records, I get a MySqlException that the ParentEntryId doesn't exist in the MenuEntries table.

If I add ParentEntryId (and the associated foreign key) to the database by hand, and then run, it seems to load the data okay for me, and I can add entries to the database successfully as well.

I did notice in your model that _parentEntryId is an int rather than a Nullable<int>, which will cause problems because _parentEntryId is a foreign key to the same table, and the parenting reference needs to top out somewhere.  (To fix this, set the association's IsNullable property to true.)  But LightSpeed throws a specific exception for this rather than a "specified cast is not valid."

Also the Parent column in the database is non-nullable, but the Parent property in the model is nullable.  But again, if I fail to set the Parent before saving, this causes a MySqlException rather than a "specified cast is not valid." 

Would it be possible for you to post the following for me:

* .lsmodel file (*not* just the generated .cs file)
* .lsmodel.diagram file
* .sql file for database schema
* a set of steps to reproduce the problem (e.g. "right-click the model and select Update Database" or "run the code and call the Find<MenuEntry> method" or "run the code, create a MenuEntry with the following properties, and call Add followed by SaveChanges").
* the exception message (and InnerException message if there is one) and stack trace if available

You can attach files via the Options tab.

Sorry to be a pain, but I'm not having any luck reproducing the problem so far and these will really help.  Thanks!

Oh, and if the error is occurring when you add entities to the DB, what IdentityMethod are you using?  The default is IdentityMethod.KeyTable which requires a table called KeyTable in the DB, but again if this is missing then the error is "missing table" rather than "invalid cast."  If you are using a KeyTable, can you include the schema for that?  Thanks!