Fluent api mapping not working in specific scenario

Discussion of open issues, suggestions and bugs regarding Entity Developer - ORM modeling and code generation tool
Post Reply
strandedpirate
Posts: 21
Joined: Tue 07 Jan 2014 12:38

Fluent api mapping not working in specific scenario

Post by strandedpirate » Tue 07 Jan 2014 12:55

I'm getting the following error when I run my application and narrowed down the issue to three tables. Is this a defect in the code generator or the entity framework fluent API?
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
Image

Entity Developer diagram
Image

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
Entity Developer generated code:

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
    }

}
Notes:
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);

strandedpirate
Posts: 21
Joined: Tue 07 Jan 2014 12:38

Re: Fluent api mapping not working in specific scenario

Post by strandedpirate » Tue 07 Jan 2014 19:35

This is looking like a fluent API bug. I flip-floped the MapLeftKey and MapRightKey calls and the error went away and the application loaded. Looks like composite keys with more than two fields are mishandled internally by the EF. Probably a simple copy/paste defect.

non-working:

Code: Select all

modelBuilder.Entity<SSISAdvertiserCategory>()
	.HasMany(p => p.Categories)
		.WithMany(c => c.SSISAdvertiserCategories)
	.Map(manyToMany => manyToMany
		.ToTable("SSISCategoryAdvertiserCategory", "dbo")
		.MapLeftKey("CategoryId")
		.MapRightKey("AffiliateId", "Name"));
working:

Code: Select all

modelBuilder.Entity<SSISAdvertiserCategory>()
	.HasMany(p => p.Categories)
		.WithMany(c => c.SSISAdvertiserCategories)
	.Map(manyToMany => manyToMany
		.ToTable("SSISCategoryAdvertiserCategory", "dbo")
		.MapRightKey("CategoryId")
		.MapLeftKey("AffiliateId", "Name"));

strandedpirate
Posts: 21
Joined: Tue 07 Jan 2014 12:38

Re: Fluent api mapping not working in specific scenario

Post by strandedpirate » Wed 08 Jan 2014 20:07

Ok, after looking into this a lot more it's clear that this is a defect in the DBContext template. MapLeftKey should be built using association.AssociationMapping.Parent and MapRightKey should be built using association.AssociationMapping.Child

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Re: Fluent api mapping not working in specific scenario

Post by Shalex » Fri 10 Jan 2014 09:18

Thank you for your report and a detailed description of the problem. We will post here when the issue is fixed in DbContext template shipped with Entity Developer installation.

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Re: Fluent api mapping not working in specific scenario

Post by Shalex » Wed 15 Jan 2014 14:51

The bug with association mapping in the DbContext template is fixed. We will notify you when the corresponding build of Entity Developer is available for download.

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Re: Fluent api mapping not working in specific scenario

Post by Shalex » Thu 16 Jan 2014 15:06

New build of Entity Developer 5.7.276 is available for download!
It can be downloaded from http://www.devart.com/entitydeveloper/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=32&t=28723.

Post Reply