inserting objects with a linking table

I have the following tables:

CREATE TABLE `site` (
  `id_site` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id_site`)
) ENGINE=InnoDB;

CREATE TABLE `user` (
  `id_user` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  PRIMARY KEY (`id_user`)
) ENGINE=InnoDB;

CREATE TABLE `site_user` (
  `id_site` int(11) NOT NULL,
  `id_user` int(11) NOT NULL,
  PRIMARY KEY (`id_site`,`id_user`),
  KEY `id_user` (`id_user`),
  KEY `id_site` (`id_site`),
  CONSTRAINT `site_user_ibfk_1` FOREIGN KEY (`id_site`) REFERENCES `site` (`id_site`),
  CONSTRAINT `site_user_ibfk_2` FOREIGN KEY (`id_user`) REFERENCES `user` (`id_user`)
) ENGINE=InnoDB;

I'm using the following code:

var _context = new Mindscape.LightSpeed.LightSpeedContext();
_context.ConnectionString = "server=xxxxx;User Id=xxxxx;Password=xxxxx;Persist Security Info=True;database=test";
_context.DataProvider = Mindscape.LightSpeed.DataProvider.MySql5;
_context.IdentityMethod = Mindscape.LightSpeed.IdentityMethod.IdentityColumn;

var ctx = _context.CreateUnitOfWork();

Site site = new Site
{
  Name = "foo"
};

User user = new User
{
  Username = "foo",
  Password = "bar"
};

ctx.Add(site);
ctx.Add(user);

SiteUser siteuser = new SiteUser();
siteuser.IdSite = site;
siteuser.IdUser = user;
                
ctx.SaveChanges();

a site object can have multiple users, and a user can exist at multiple sites.  the problem I'm having is that it doesn't want to let me create a site_user row referencing a site and a user that I have just created, but not committed to the database.  the exception I'm getting is as follows:

An error occurred when trying to assign a new Identity value to this entity. Check inner exception for details.

Inner Exception: Invalid cast from 'System.Int32' to 'LSTest.SiteUserId'.

Composite identities where the components are also foreign keys requires some special and slightly delicate handling.  See http://www.mindscape.co.nz/forums/Thread.aspx?PostID=9693 and the slightly more up-to-date http://www.mindscape.co.nz/forums/Thread.aspx?ThreadID=3179 for more info: if these don't provide enough info to get it working, please post your SiteUser class and the stack trace of the exception and we'll try to track down the problem.

I looked at the two threads you referenced, but didn't really understand how they help with my situation.  the following are the classes that make up the model:

[Serializable]
  [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")]
  [System.ComponentModel.DataObject]
  [Table("user", IdColumnName="id_user")]
  public partial class User : Entity<int>
  {
    [ValidatePresence]
    [ValidateLength(0, 255)]
    private string _username;
    [ValidatePresence]
    [ValidateLength(0, 255)]
    private string _password;

    /// <summary>Identifies the Username entity attribute.</summary>
    public const string UsernameField = "Username";
    /// <summary>Identifies the Password entity attribute.</summary>
    public const string PasswordField = "Password";

    [ReverseAssociation("IdUser")]
    private readonly EntityCollection<SiteUser> _siteUsers = new EntityCollection<SiteUser>();

    public EntityCollection<SiteUser> SiteUsers
    {
      get { return Get(_siteUsers); }
    }


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

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


  [Serializable]
  [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")]
  [System.ComponentModel.DataObject]
  [Table("site_user", IdColumnName="id_user")]
  public partial class SiteUser : Entity<SiteUserId>
  {
    private int _idUserId;
    private int _idSiteId;

    /// <summary>Identifies the IdUserId entity attribute.</summary>
    public const string IdUserIdField = "IdUserId";
    /// <summary>Identifies the IdSiteId entity attribute.</summary>
    public const string IdSiteIdField = "IdSiteId";

    [ReverseAssociation("SiteUsers")]
    private readonly EntityHolder<User> _idUser = new EntityHolder<User>();
    [ReverseAssociation("SiteUsers")]
    private readonly EntityHolder<Site> _idSite = new EntityHolder<Site>();

    public User IdUser
    {
      get { return Get(_idUser); }
      set { Set(_idUser, value); }
    }

    public Site IdSite
    {
      get { return Get(_idSite); }
      set { Set(_idSite, value); }
    }


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

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

  [Serializable]
  [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")]
  public partial struct SiteUserId
  {
    
    public SiteUserId(
      int idSite ,
      int idUser     )
    {
      _idSite = idSite;
      _idUser = idUser;
    }

    private readonly int _idSite;
    private readonly int _idUser;

    public int IdSite
    {
      get { return _idSite; }
    }
    public int IdUser
    {
      get { return _idUser; }
    }
    
    public override int GetHashCode()
    {
      int hashCode = 0;
      hashCode = 19 * hashCode + _idSite.GetHashCode();
      hashCode = 19 * hashCode + _idUser.GetHashCode();
      return hashCode;
    }
  }

  [Serializable]
  [System.CodeDom.Compiler.GeneratedCode("LightSpeedModelGenerator", "1.0.0.0")]
  [System.ComponentModel.DataObject]
  [Table("site", IdColumnName="id_site")]
  public partial class Site : Entity<int>
  {
    [ValidatePresence]
    [ValidateLength(0, 255)]
    private string _name;
    
    /// <summary>Identifies the Name entity attribute.</summary>
    public const string NameField = "Name";

    [ReverseAssociation("IdSite")]
    private readonly EntityCollection<SiteUser> _siteUsers = new EntityCollection<SiteUser>();

    public EntityCollection<SiteUser> SiteUsers
    {
      get { return Get(_siteUsers); }
    }

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

The call stack doesn't contain a lot of useful data:

LSTest.exe!LSTest.Program.Main(string[] args = {string[0]}) Line 88    C#

[Native to Managed Transition]    

[Managed to Native Transition]    

     Microsoft.VisualStudio.HostingProcess.Utilities.dll!Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() + 0x47 bytes    

     mscorlib.dll!System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state) + 0x9b bytes    

     mscorlib.dll!System.Threading.ThreadHelper.ThreadStart() + 0x4d bytes    

It looks like the problem is that SiteUser doesn't have a way to determine its ID.  Because SiteUser has a composite key, it derives from Entity<SiteUserId>, which means its ID is of type SiteUserId (which is in turn a little struct with a field for each primary key column).  However, your identity method (probably KeyTable) is generating IDs of type int.  So when you create a SiteUser, LightSpeed assigns an ID of type int, and tries to set SiteUser.Id to that int, which fails because an int is not a SiteUserId.

The solution to this is to create a partial class for SiteUser and override the GeneratedId() method so that it returns a SiteUserId instead of an int.  To learn how to do this, see:

http://www.mindscape.co.nz/blog/index.php/2009/12/22/composite-keys-in-lightspeed-3/

and in particular the section "Assigning Composite IDs."

Once you've got the composite key working, you'll then need to set up the foreign key mappings so that the associations are wired up to the Id.Xxx fields -- the previous links I posted should help with that.

Hope this helps -- let us know if you need further info.

When I add the ForeignKeyField attributes to the fields in the SiteUser class like so:

[ForeignKeyField("Id.IdUserId")]
[ReverseAssociation("SiteUsers")]
private readonly EntityHolder<User> _idUser = new EntityHolder<User>();
[ForeignKeyField("Id.IdSiteId")]
[ReverseAssociation("SiteUsers")]
private readonly EntityHolder<Site> _idSite = new EntityHolder<Site>();

it complains with the exception "Circular associations are not supported by LightSpeed: [SiteUser.IdUser and User.SiteUsers]"

it gives me this exception with or without the ReverseAssociation attributes.

That's a very misleading error message, and we'll see if we can get it to produce something more meaningful, but what it's telling you is that your foreign key mappings are invalid.  There are two issues that need to be addressed:

1. SiteUser shouldn't contain IdUserId and IdSiteId properties, because these columns are mapped into the composite Id (the SiteUserId struct).  You may already have removed these -- just going on your earlier code fragment here.

2. The ForeignKeyFieldAttribute paths don't correspond to the properties in the SiteUserId struct.  For example, your User association has ForeignKeyField("Id.IdUserId"); but SiteUserId doesn't have an IdUserId property, it has an IdUser property.  So your attributes (or column mappings in the designer) should be "Id.IdUser" and "Id.IdSite" i.e. without the additional Id suffix.

I've attached my updated version of your model, which works correctly for me -- you should be able to diff this against your code to see what I needed to change.  Hope this makes sense -- let me know if you want further clarification.

due to the amount of extra work involved in composite primary keys which are also foreign keys, I've decided to go a different route that will work just as well with less difficulty.  the new site_user table is as follows:

CREATE TABLE `site_user` (
  `id_site` int(11) NOT NULL,
  `id_user` int(11) NOT NULL,
  `id_site_user` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id_site_user`),
  UNIQUE KEY `uq_site_user` (`id_user`,`id_site`),
  KEY `fk_site_user_id_user` (`id_user`),
  KEY `fk_site_user_id_site` (`id_site`),
  CONSTRAINT `fk_site_user_id_site` FOREIGN KEY (`id_site`) REFERENCES `site` (`id_site`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_site_user_id_user` FOREIGN KEY (`id_user`) REFERENCES `user` (`id_user`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB;

this provides all the functionality that I am looking for, with no extra work.

with the new schema design, it's a simple matter to create an empty site_user and add it to the SiteUsers collection in both the user and the site, and then add the site and user objects to the database.  the id for site_user gets generated automatically as it should, and the foreign key values get hooked up as they should.

in my search for a viable .Net data provider for MySql, I also tried the Devart DotConnect system, which takes those composite key tables and converts it to a collection of site objects in the user, and a collection of user objects in the site.  the site_user table did not show up as an object in the model at all.  it might be worth looking into that sort of arrangement for Lightspeed in a future release.

Yes, if you have control over the database design then adding a separate PK column definitely makes linking tables easier to work with in LightSpeed.

Thanks for the suggestion on hiding the site_user table.  We can already do this in certain circumstances -- the Through Association arrow has an option for "auto through entity" which hides the link entity -- but it has certain limitations which mean it's not suitable for your schema.  (Specifically, an auto through entity doesn't support a custom table name mapping.)  More information at http://www.mindscape.co.nz/blog/index.php/2009/09/17/many-to-many-associations-in-the-lightspeed-designer/