Composite Key Not Loading All Related Entities

Question

I've been trying various solutions to this for a couple of hours now and I just cannot figure it out. I'm using a custom membership provider in a system so have a roles table defined using code first like this:

public class UsersInRole
{
    [Key]
    public long UserId { get; set; }

    [Required]
    public UserProfile UserProfile { get; set; }

    [Key]
    public long RoleId { get; set; }

    [Required]
    public webpages_Role Role { get; set; }
}

When I script out this table all seems correct:

CREATE TABLE [dbo].[UsersInRoles](
[UserId] [bigint] NOT NULL,
[RoleId] [bigint] NOT NULL,
CONSTRAINT [PK_dbo.UsersInRoles] PRIMARY KEY CLUSTERED 
(
[UserId] ASC,
[RoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[UsersInRoles]  WITH CHECK ADD  CONSTRAINT [FK_dbo.UsersInRoles_dbo.UserProfile_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[UserProfile] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[UsersInRoles] CHECK CONSTRAINT [FK_dbo.UsersInRoles_dbo.UserProfile_UserId]
GO

ALTER TABLE [dbo].[UsersInRoles]  WITH CHECK ADD  CONSTRAINT [FK_dbo.UsersInRoles_dbo.Roles_RoleId] FOREIGN KEY([RoleId])
REFERENCES [dbo].[Roles] ([RoleId])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[UsersInRoles] CHECK CONSTRAINT [FK_dbo.UsersInRoles_dbo.Roles_RoleId]
GO

The strange behaviour is only the UserProfile property is ever populated. If I load the UsersInRole objects directly through the context I see the same behaviour.

var userInRoles = context.UsersInRoles.ToList();

The UserProfile properties are loaded but the Role property is always null.

The UsersInRole entity has this composite key mapped:

HasKey(compKey => new { compKey.UserId, compKey.RoleId });

I've checked over and over the relationships and as far as I can tell this should be working fine. The UserProfile id field is Id and the role id field is RoleId as the relationships state. I can't see the wood for the trees now!!

Any ideas would be appreciated.

if you do context.UsersInRoles.Include(ur => ur.UserProfile).Include(ur => ur.Role).ToList() is Role still null? — Mones, Nov 03 '12 at 01:54
I couldn't find the exact method you mention but I did this: `context.UsersInRoles.Include("UserProfile").Include("Role").ToList();` And the Role property is now populated! However, the UserProfile object is a proxy but the role is a concrete object ... — Jammer, Nov 03 '12 at 10:31
For the method's signature I used above you have to add System.Data.Entity 'using' clause. — Mones, Nov 03 '12 at 13:23

score 0 · Accepted Answer · answered Nov 03 '12 at 12:23

Seems I had made a mistake with the Roles object definition. I have now fixed this be defining a foreignkey attribute on the roles object like:

public class webpages_UsersInRole
{
    public long UserId { get; set; }

    public virtual UserProfile UserProfile { get; set; }

    public long RoleId { get; set; }

    [ForeignKey("RoleId")]
    public virtual webpages_Role Role { get; set; }
}

This class is also configured with a composite key like:

HasKey(compKey => new { compKey.UserId, compKey.RoleId });

From here on it, just loading a UserProfile is now correctly populated with the Roles objects as desired.

Composite Key Not Loading All Related Entities

1 Answers1