Many to many table

I'm trying to link up to an external system in SQLSever2005.

There is a table linking the Accounts table with the Usergroups table that contains only the 2 columns with the references to each table it links to with no index of it's own.

I just cant get this to work, does not matter what I try, many2many links, a loose table that I query with a where clause, I just cant get it to work.....

It either crashes or the one column always comes back as a "1" and the opther is correctly filled.

Help pls...

Here is the create statement for the table in question.

regards Anthony

USE [ANTacc]
GO
/****** Object:  Table [dbo].[tb_XGT_UserGroupMembers]    Script Date: 05/03/2010 15:21:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb_XGT_UserGroupMembers](
    [UserGroupID_ref] [int] NOT NULL,
    [AccountID_ref] [int] NOT NULL,
 CONSTRAINT [PK_tb_XGT_UserGroupMembers] PRIMARY KEY CLUSTERED
(
    [UserGroupID_ref] ASC,
    [AccountID_ref] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[tb_XGT_UserGroupMembers]  WITH NOCHECK ADD  CONSTRAINT [FK_tb_CG_UserGroupMembers_tb_CG_UserGroups] FOREIGN KEY([UserGroupID_ref])
REFERENCES [dbo].[tb_XGT_UserGroups] ([ID])
GO
ALTER TABLE [dbo].[tb_XGT_UserGroupMembers] CHECK CONSTRAINT [FK_tb_CG_UserGroupMembers_tb_CG_UserGroups]

A through entity is still an entity and must have an ID.

In LightSpeed 2.x, this means your through table must have a separate identity column.

In LightSpeed 3.0 RTM, you could map your two composite key columns to a composite ID type, but we didn't support associations where the foreign keys were attributes of the ID type.

In LightSpeed 3.0 nightly builds, we do support associations keyed on attributes of composite ID types, but you have to perform some additional markup (which can't be done in the designer).

If you control the database, the simplest solution is to add an ID column to the through table, and make the ID column the PK.  If you have to stick with the existing database schema, see http://www.mindscape.co.nz/forums/Thread.aspx?PostID=8129 and http://www.mindscape.co.nz/forums/Post.aspx?ThreadID=2990&PostID=9499 (and you will need to install a recent nightly build of LightSpeed 3).

I have downloaded the latest version, still cant get it to work, I'm just after a simple example of howI can perform this, the 2 threads you give here dont give me a simple readable understandable example of how I can achieve this.

I still cant get it to work, please provide me with a simple example of how I can achieve this.

Thx Anthony

The key piece of magic is in the through class, where you need to use ForeignKeyFieldAttribute to say that the FKs are part of the PK rather than separate fields.  Here's my through class in full:

    public class UserGroupMember : Entity<UserGroupMemberId>
    {
      [ForeignKeyField("Id.UserGroupId")]
      private readonly EntityHolder<UserGroup> _userGroup = new EntityHolder<UserGroup>();

      public UserGroup UserGroup
      {
        get { return Get(_userGroup); }
        set { Set(_userGroup, value); }
      }

      [ForeignKeyField("Id.MemberId")]
      private readonly EntityHolder<Member> _myProperty = new EntityHolder<Member>();

      public Member Member
      {
        get { return Get(_myProperty); }
        set { Set(_myProperty, value); }
      }
    }

Note two things:

* The through class Id type is UserGroupMemberId.  I'll include the definition of this below, but basically this represents the composite key -- it's a two-member struct with UserGroupId and MemberId fields.

* The EntityHolder association fields are decorated with the ForeignKeyField attribute, with a path that traverses into the Id and identifies a member of the UserGroupMemberId struct.  Unlike a normal to-one association, there's no separate foreign key field in the class definition -- no int _memberId for example.

Everything else is standard LightSpeed associations.  So with that highlighting out the way, the full code should be pretty familiar:

    public struct UserGroupMemberId
    {
      private readonly int _userGroupId;
      private readonly int _memberId;

      public UserGroupMemberId(int userGroupId, int memberId)
      {
        _userGroupId = userGroupId;
        _memberId = memberId;
      }

      public int UserGroupId
      {
        get { return _userGroupId; }
      }

      public int MemberId
      {
        get { return _memberId; }
      }

      public override bool Equals(object obj)
      {
        if (obj is UserGroupMemberId)
        {
          UserGroupMemberId other = (UserGroupMemberId)obj;
          return other.UserGroupId == UserGroupId && other.MemberId == MemberId;
        }
        else
        {
          return false;
        }
      }

      public override int GetHashCode()
      {
        return 29 * UserGroupId.GetHashCode() + MemberId.GetHashCode();
      }
    }

    public class UserGroupMember : Entity<UserGroupMemberId>
    {
      [ForeignKeyField("Id.UserGroupId")]
      private readonly EntityHolder<UserGroup> _userGroup = new EntityHolder<UserGroup>();

      public UserGroup UserGroup
      {
        get { return Get(_userGroup); }
        set { Set(_userGroup, value); }
      }

      [ForeignKeyField("Id.MemberId")]
      private readonly EntityHolder<Member> _myProperty = new EntityHolder<Member>();

      public Member Member
      {
        get { return Get(_myProperty); }
        set { Set(_myProperty, value); }
      }
    }

    public class UserGroup : Entity<int>
    {
      private string _name;

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

      private readonly EntityCollection<UserGroupMember> _userGroupMembers = new EntityCollection<UserGroupMember>();

      public EntityCollection<UserGroupMember> UserGroupMembers
      {
        get { return Get(_userGroupMembers); }
      }

      private ThroughAssociation<UserGroupMember, Member> _members;

      public ThroughAssociation<UserGroupMember, Member> Members
      {
        get
        {
          if (_members == null)
          {
            _members = new ThroughAssociation<UserGroupMember, Member>(UserGroupMembers);
          }
          return Get(_members);
        }
      }
    }

    public class Member : Entity<int>
    {
      private string _name;

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

      private readonly EntityCollection<UserGroupMember> _userGroupMembers = new EntityCollection<UserGroupMember>();

      public EntityCollection<UserGroupMember> UserGroupMembers
      {
        get { return Get(_userGroupMembers); }
      }

      private ThroughAssociation<UserGroupMember, UserGroup> _userGroups;

      public ThroughAssociation<UserGroupMember, UserGroup> UserGroups
      {
        get
        {
          if (_userGroups == null)
          {
            _userGroups = new ThroughAssociation<UserGroupMember, UserGroup>(UserGroupMembers);
          }
          return Get(_userGroups);
        }
      }
    }

And here's the SQL (slightly edited for clarity, apologies if any typoes sneak in):

CREATE TABLE [dbo].[UserGroup](
    [Id] [int] NOT NULL PRIMARY KEY,
    [Name] [nvarchar](50) NOT NULL)

CREATE TABLE [dbo].[Member](
    [Id] [int] NOT NULL PRIMARY KEY,
    [Name] [nvarchar](50) NOT NULL)

CREATE TABLE [dbo].[UserGroupMember](
    [UserGroupId] [int] NOT NULL FOREIGN KEY REFERENCES [dbo].[UserGroup] ([Id]),
    [MemberId] [int] NOT NULL FOREIGN KEY REFERENCES [dbo].[Member] ([Id]),
 CONSTRAINT [PK_UserGroupMember] PRIMARY KEY CLUSTERED
 (
    [UserGroupId] ASC,
    [MemberId] ASC
 ))

I inserted two records into UserGroup, with Ids 1 and 2, and two into Member, with Ids 11 (Alice) and 12 (Bob).  Then in UserGroupMember I created records for 1 -> 11, 1 -> 12 and 2 -> 12.

And here's the test that exercises it:

    [Test]
    public void ThroughAssociationViaCompositeKey()
    {
      LightSpeedContext context = new LightSpeedContext
      {
        ConnectionString = "Data Source=localhost;Initial Catalog=scratch;Integrated Security=true",
      };

      using (IUnitOfWork u = context.CreateUnitOfWork())
      {
        UserGroup g1 = u.FindById<UserGroup>(1);
        Assert.AreEqual(2, g1.Members.Count);
        Assert.AreEqual("Alice", g1.Members[0].Name);
        Assert.AreEqual("Bob", g1.Members[1].Name);

        UserGroup g2 = u.FindById<UserGroup>(2);
        Assert.AreEqual(1, g2.Members.Count);
        Assert.AreEqual("Bob", g2.Members[0].Name);
      }
    }

Let me know if you need any more info.

Thank you, It's clear and I understand it :)

I have 1 other problem.

when I use the [ForeignKeyField( (Mindscape.Lightspeed.Dll version: 3.0.789.12667 2 or 3 days old) I get the following error:

[ForeignKeyField("Id.UserGroupId")]
private readonly EntityHolder<UserGroup> _userGroup = new EntityHolder<UserGroup>();


'Mindscape.Lightspeed.Migrations.ForeignKeyField'is not an attribute class

You've got a reference to the Mindscape.LightSpeed.Migrations DLL as well as to the Mindscape.LightSpeed DLL, and you've done a using Mindscape.LightSpeed.Migrations; instead of (or in addition to) using Mindscape.LightSpeed;.

Change the using Mindscape.LightSpeed.Migrations; to using Mindscape.LightSpeed;, or spell out the attribute name in full: [ForeignKeyFieldAttribute("Id.UserGroupId")].

Also, you may not have updated your project references to the nightly build: build 12667 is from December 2009.  You need build 13350 or above.