The specified association foreign key columns "AffiliateId, Name" are invalid. The number of columns specified must match the number of primary key columns.
SQL Server diagram
Entity Developer diagram
Database DDL script:
Code: Select all
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Category](
[CategoryId] [int] IDENTITY(1,1) NOT NULL,
[ParentCategoryId] [int] NULL,
[RootCategoryId] [int] NULL,
[PictureId] [int] NULL,
[Name] [nvarchar](75) NOT NULL,
[SEOName] [nvarchar](75) NOT NULL,
[Description] [nvarchar](500) NULL,
[IsPublished] [bit] NOT NULL,
[IsDeleted] [bit] NOT NULL,
[CreatedOn] [datetime] NOT NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
[CategoryId] 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].[Category] WITH CHECK ADD CONSTRAINT [FK_Category_Category] FOREIGN KEY([ParentCategoryId])
REFERENCES [dbo].[Category] ([CategoryId])
GO
ALTER TABLE [dbo].[Category] CHECK CONSTRAINT [FK_Category_Category]
GO
ALTER TABLE [dbo].[Category] ADD CONSTRAINT [DF_Category_IsPublished] DEFAULT ((0)) FOR [IsPublished]
GO
ALTER TABLE [dbo].[Category] ADD CONSTRAINT [DF_Category_IsDeleted] DEFAULT ((0)) FOR [IsDeleted]
GO
ALTER TABLE [dbo].[Category] ADD CONSTRAINT [DF_Category_CreatedOn] DEFAULT (getutcdate()) FOR [CreatedOn]
GO
CREATE TABLE [dbo].[AdvertiserCategory](
[Name] [nvarchar](350) NOT NULL,
[AffiliateId] [int] NOT NULL,
[Vendor] [nvarchar](250) NOT NULL,
[Ignore] [bit] NOT NULL,
CONSTRAINT [PK_AdvertiserCategory] PRIMARY KEY CLUSTERED
(
[Name] ASC,
[AffiliateId] 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].[AdvertiserCategory] ADD CONSTRAINT [DF_AdvertiserCategory_Ignore] DEFAULT ((0)) FOR [Ignore]
GO
CREATE TABLE [dbo].[CategoryAdvertiserCategory](
[CategoryId] [int] NOT NULL,
[Name] [nvarchar](350) NOT NULL,
[AffiliateId] [int] NOT NULL,
CONSTRAINT [PK_CategoryAdvertiserCategory] PRIMARY KEY CLUSTERED
(
[CategoryId] ASC,
[Name] ASC,
[AffiliateId] 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].[CategoryAdvertiserCategory] WITH CHECK ADD CONSTRAINT [FK_CategoryAdvertiserCategory_AdvertiserCategory] FOREIGN KEY([Name], [AffiliateId])
REFERENCES [dbo].[AdvertiserCategory] ([Name], [AffiliateId])
GO
ALTER TABLE [dbo].[CategoryAdvertiserCategory] CHECK CONSTRAINT [FK_CategoryAdvertiserCategory_AdvertiserCategory]
GO
ALTER TABLE [dbo].[CategoryAdvertiserCategory] WITH CHECK ADD CONSTRAINT [FK_CategoryAdvertiserCategory_Category] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[Category] ([CategoryId])
GO
ALTER TABLE [dbo].[CategoryAdvertiserCategory] CHECK CONSTRAINT [FK_CategoryAdvertiserCategory_Category]
GO
Code: Select all
//------------------------------------------------------------------------------
// This is auto-generated code.
//------------------------------------------------------------------------------
// This code was generated by Devart Entity Developer tool using Entity Framework DbContext template.
// Code is generated on: 1/7/2014 6:27:29 AM
//
// Changes to this file may cause incorrect behavior and will be lost if
// the code is regenerated.
//------------------------------------------------------------------------------
using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.ModelConfiguration;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Linq;
using System.Runtime.Serialization;
using System.Xml.Serialization;
using System.Data.EntityClient;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
namespace EntityFrameworkFluentMapTestModel
{
#region EntityFrameworkFluentMapTestEntities
public partial class EntityFrameworkFluentMapTestEntities : DbContext
{
#region Constructors
/// <summary>
/// Initialize a new EntityFrameworkFluentMapTestEntities object.
/// </summary>
public EntityFrameworkFluentMapTestEntities() :
base(GetDefaultConnection(), true)
{
Configure();
}
private static DbConnection GetDefaultConnection() {
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = @"Data Source=localhost;Initial Catalog=EntityFrameworkFluentMapTest;Integrated Security=True;Persist Security Info=True;User ID=;Password=";
return connection;
}
/// <summary>
/// Initializes a new EntityFrameworkFluentMapTestEntities object using the connection string found in the 'EntityFrameworkFluentMapTestEntities' section of the application configuration file.
/// </summary>
public EntityFrameworkFluentMapTestEntities(string connectionString) :
base(connectionString)
{
Configure();
}
/// <summary>
/// Initialize a new EntityFrameworkFluentMapTestEntities object.
/// </summary>
public EntityFrameworkFluentMapTestEntities(DbConnection existingConnection, bool contextOwnsConnection) :
base(existingConnection, contextOwnsConnection)
{
Configure();
}
/// <summary>
/// Initialize a new EntityFrameworkFluentMapTestEntities object.
/// </summary>
public EntityFrameworkFluentMapTestEntities(ObjectContext objectContext, bool dbContextOwnsObjectContext) :
base(objectContext, dbContextOwnsObjectContext)
{
Configure();
}
/// <summary>
/// Initialize a new EntityFrameworkFluentMapTestEntities object.
/// </summary>
public EntityFrameworkFluentMapTestEntities(string nameOrConnectionString, DbCompiledModel model) :
base(nameOrConnectionString, model)
{
Configure();
}
/// <summary>
/// Initialize a new EntityFrameworkFluentMapTestEntities object.
/// </summary>
public EntityFrameworkFluentMapTestEntities(DbConnection existingConnection, DbCompiledModel model, bool contextOwnsConnection) :
base(existingConnection, model, contextOwnsConnection)
{
Configure();
}
private void Configure()
{
this.Configuration.AutoDetectChangesEnabled = true;
this.Configuration.LazyLoadingEnabled = true;
this.Configuration.ProxyCreationEnabled = true;
this.Configuration.ValidateOnSaveEnabled = true;
}
#endregion
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
#region AdvertiserCategory
modelBuilder.Entity<AdvertiserCategory>()
.HasKey(p => new { p.Name, p.AffiliateId })
.ToTable("AdvertiserCategory", "dbo");
// Properties:
modelBuilder.Entity<AdvertiserCategory>()
.Property(p => p.Name)
.IsRequired()
.HasMaxLength(350)
.HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None)
.HasColumnType("nvarchar");
modelBuilder.Entity<AdvertiserCategory>()
.Property(p => p.AffiliateId)
.IsRequired()
.HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None)
.HasColumnType("int");
modelBuilder.Entity<AdvertiserCategory>()
.Property(p => p.Vendor)
.IsRequired()
.HasMaxLength(250)
.HasColumnType("nvarchar");
modelBuilder.Entity<AdvertiserCategory>()
.Property(p => p.Ignore)
.IsRequired()
.HasColumnType("bit");
#endregion
#region Category
modelBuilder.Entity<Category>()
.HasKey(p => new { p.CategoryId })
.ToTable("Category", "dbo");
// Properties:
modelBuilder.Entity<Category>()
.Property(p => p.CategoryId)
.IsRequired()
.HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity)
.HasColumnType("int");
modelBuilder.Entity<Category>()
.Property(p => p.ParentCategoryId)
.HasColumnType("int");
modelBuilder.Entity<Category>()
.Property(p => p.RootCategoryId)
.HasColumnType("int");
modelBuilder.Entity<Category>()
.Property(p => p.PictureId)
.HasColumnType("int");
modelBuilder.Entity<Category>()
.Property(p => p.Name)
.IsRequired()
.HasMaxLength(75)
.HasColumnType("nvarchar");
modelBuilder.Entity<Category>()
.Property(p => p.SEOName)
.IsRequired()
.HasMaxLength(75)
.HasColumnType("nvarchar");
modelBuilder.Entity<Category>()
.Property(p => p.Description)
.HasMaxLength(500)
.HasColumnType("nvarchar");
modelBuilder.Entity<Category>()
.Property(p => p.IsPublished)
.IsRequired()
.HasColumnType("bit");
modelBuilder.Entity<Category>()
.Property(p => p.IsDeleted)
.IsRequired()
.HasColumnType("bit");
modelBuilder.Entity<Category>()
.Property(p => p.CreatedOn)
.IsRequired()
.HasColumnType("datetime");
// Associations:
modelBuilder.Entity<Category>()
.HasMany(p => p.Children)
.WithOptional(c => c.ParentCategory)
.HasForeignKey(p => new { p.ParentCategoryId })
.WillCascadeOnDelete(false);
modelBuilder.Entity<Category>()
.HasMany(p => p.AdvertiserCategories)
.WithMany(c => c.Categories)
.Map(manyToMany => manyToMany
.ToTable("CategoryAdvertiserCategory", "dbo")
.MapLeftKey("AffiliateId" ,"Name")
.MapRightKey("CategoryId"));
#endregion
#region Disabled conventions
#endregion
}
/// <summary>
/// There are no comments for AdvertiserCategory in the schema.
/// </summary>
public DbSet<AdvertiserCategory> AdvertiserCategories { get; set; }
/// <summary>
/// There are no comments for Category in the schema.
/// </summary>
public DbSet<Category> Categories { get; set; }
}
#endregion
}
namespace EntityFrameworkFluentMapTestModel
{
/// <summary>
/// There are no comments for EntityFrameworkFluentMapTestModel.AdvertiserCategory in the schema.
/// </summary>
public partial class AdvertiserCategory {
public AdvertiserCategory()
{
}
#region Properties
/// <summary>
/// There are no comments for Name in the schema.
/// </summary>
public virtual string Name
{
get;
set;
}
/// <summary>
/// There are no comments for AffiliateId in the schema.
/// </summary>
public virtual int AffiliateId
{
get;
set;
}
/// <summary>
/// There are no comments for Vendor in the schema.
/// </summary>
public virtual string Vendor
{
get;
set;
}
/// <summary>
/// There are no comments for Ignore in the schema.
/// </summary>
public virtual bool Ignore
{
get;
set;
}
#endregion
#region Navigation Properties
/// <summary>
/// There are no comments for Categories in the schema.
/// </summary>
public virtual ICollection<Category> Categories
{
get;
set;
}
#endregion
}
/// <summary>
/// There are no comments for EntityFrameworkFluentMapTestModel.Category in the schema.
/// </summary>
public partial class Category {
public Category()
{
}
#region Properties
/// <summary>
/// There are no comments for CategoryId in the schema.
/// </summary>
public virtual int CategoryId
{
get;
set;
}
/// <summary>
/// There are no comments for ParentCategoryId in the schema.
/// </summary>
public virtual global::System.Nullable<int> ParentCategoryId
{
get;
set;
}
/// <summary>
/// There are no comments for RootCategoryId in the schema.
/// </summary>
public virtual global::System.Nullable<int> RootCategoryId
{
get;
set;
}
/// <summary>
/// There are no comments for PictureId in the schema.
/// </summary>
public virtual global::System.Nullable<int> PictureId
{
get;
set;
}
/// <summary>
/// There are no comments for Name in the schema.
/// </summary>
public virtual string Name
{
get;
set;
}
/// <summary>
/// There are no comments for SEOName in the schema.
/// </summary>
public virtual string SEOName
{
get;
set;
}
/// <summary>
/// There are no comments for Description in the schema.
/// </summary>
public virtual string Description
{
get;
set;
}
/// <summary>
/// There are no comments for IsPublished in the schema.
/// </summary>
public virtual bool IsPublished
{
get;
set;
}
/// <summary>
/// There are no comments for IsDeleted in the schema.
/// </summary>
public virtual bool IsDeleted
{
get;
set;
}
/// <summary>
/// There are no comments for CreatedOn in the schema.
/// </summary>
public virtual global::System.DateTime CreatedOn
{
get;
set;
}
#endregion
#region Navigation Properties
/// <summary>
/// There are no comments for Children in the schema.
/// </summary>
public virtual ICollection<Category> Children
{
get;
set;
}
/// <summary>
/// There are no comments for ParentCategory in the schema.
/// </summary>
public virtual Category ParentCategory
{
get;
set;
}
/// <summary>
/// There are no comments for AdvertiserCategories in the schema.
/// </summary>
public virtual ICollection<AdvertiserCategory> AdvertiserCategories
{
get;
set;
}
#endregion
}
}Before purchasing Entity Developer I was manually writing the POCO's and created an entity for the junction table (CategoryAdvertiserCategory) and was able to successfully map everything with the fluent api like so.
Code: Select all
modelBuilder.Entity<CategoryAdvertiserCategory>().HasKey(x => new { x.CategoryId, x.Name, x.AffiliateId });
modelBuilder.Entity<CategoryAdvertiserCategory>().HasRequired(x => x.AdvertiserCategory).WithMany(x => x.Categories).HasForeignKey(x => new { x.Name, x.AffiliateId });
modelBuilder.Entity<CategoryAdvertiserCategory>().HasRequired(x => x.Category).WithMany(x => x.AdvertiserCategories).HasForeignKey(x => x.CategoryId);