Closed riley-van-hengstum closed 5 years ago
Hello @Jaap-van-Hengstum ,
We tried on our side and everything works as expected.
However, the exception message gives us some hint. We have some idea to reproduce it but we want to make sure we will fix your case.
Do you think you could provide us the SQL generated by our library by using SQL Profiler
?
Here is the code we tried:
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using Z.BulkOperations;
namespace Z.EntityFramework.Extensions.Lab
{
public partial class Form_Request_ManyToMany_ReadOnly : Form
{
public Form_Request_ManyToMany_ReadOnly()
{
InitializeComponent();
// CLEAR
{
using (var context = new CurrentContext())
{
context.DiscountRules.RemoveRange(context.DiscountRules);
context.CostTypes.RemoveRange(context.CostTypes);
context.SaveChanges();
}
}
// SEED
{
using (var context = new CurrentContext())
{
for (int i = 0; i < 11; i++)
{
context.CostTypes.Add(new CostType() {ColumnInt = i});
}
context.SaveChanges();
}
}
// TEST
{
using (var context = new CurrentContext())
{
var costTypes = context.CostTypes.ToList();
var discountRules = new List<DiscountRule>();
{
// Create 11 rules
for (int i = 0; i < 11; i++)
{
discountRules.Add(new DiscountRule() { ColumnInt = 1, CostTypes = new List<CostType>()});
}
}
// Populate many to many list
{
for (int i = 0; i < costTypes.Count; i++)
{
var costType = costTypes[i];
var discountRuleToAdd = discountRules.Take(i).ToList();
costType.DiscountRules = discountRuleToAdd;
discountRuleToAdd.ForEach(x => x.CostTypes.Add(costType));
}
}
//{
// // MUST throw unique index exception
// context.BulkInsert(discountRules, options =>
// {
// options.IncludeGraph = true;
// });
//}
{
// WORK with and without proxy
var task = context.BulkInsertAsync(discountRules, options =>
{
options.IncludeGraph = true;
options.UnsafeMode = true;
options.IncludeGraphOperationBuilder = operation =>
{
if (operation is BulkOperation<CostType>)
{
var bulk = (BulkOperation<CostType>)operation;
bulk.IsReadOnly = true;
}
};
});
Task.WaitAll(task);
}
}
}
}
public class CurrentContext : DbContext
{
public CurrentContext()
: base("CodeFirstEntities")
{
}
public virtual DbSet<DiscountRule> DiscountRules { get; set; }
public virtual DbSet<CostType> CostTypes { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<DiscountRule>()
.HasMany(x => x.CostTypes)
.WithMany(x => x.DiscountRules)
.Map(c =>
{
c.MapLeftKey("DiscountRule");
c.MapRightKey("CostType");
c.ToTable("ImplicitLinkTable");
});
base.OnModelCreating(modelBuilder);
}
}
public class DiscountRule
{
public int Id { get; set; }
public int ColumnInt { get; set; }
public virtual List<CostType> CostTypes { get; set; }
}
public class CostType
{
public int Id { get; set; }
[Index(IsUnique = true)]
public int ColumnInt { get; set; }
public virtual List<DiscountRule> DiscountRules { get; set; }
}
}
}
Here is a trace: trace.zip
This seems like the offending query:
exec sp_executesql N' SELECT ''INSERT'' AS [$action], StagingTable.ZZZ_Index, DestinationTable.[Id] AS [Id_zzzinserted] FROM [dbo].[CostTypes] AS DestinationTable INNER JOIN (SELECT @0_0 AS [Id], @0_1 AS [Value], @0_2 AS [Name], @0_3 AS [Description], @0_4 AS ZZZ_Index UNION ALL SELECT @1_0 AS [Id], @1_1 AS [Value], @1_2 AS [Name], @1_3 AS [Description], @1_4 AS ZZZ_Index ) AS StagingTable ON ;',N'@0_0 bigint,@0_1 int,@0_2 nvarchar(max) ,@0_3 nvarchar(max) ,@0_4 int,@1_0 bigint,@1_1 int,@1_2 nvarchar(max) ,@1_3 nvarchar(max) ,@1_4 int',@0_0=1,@0_1=0,@0_2=N'Certificate',@0_3=N'certificaat',@0_4=0,@1_0=3,@1_1=2,@1_2=N'Examination',@1_3=N'examen',@1_4=1
Hello @Jaap-van-Hengstum ,
Thank you for the additional information,
It gives us a big hint to look at the primary key join.
We are currently investigating it to try to reproduce it.
Best Regards,
Jonathan
Hello @Jaap-van-Hengstum ,
We tried several scenarios to reproduce it but unfortunately, they all worked.
Your trace tells us that the key is not mapped correctly or is not found.
We have some additional question that might help us:
Id
an identity column?ISet
class?Obviously providing us a basic project sample with the issue will be the best for us since we will be able to fix it instead to find a way to reproduce it.
Best Regards,
Jonathan
Hello @Jaap-van-Hengstum ,
Do you think it will be possible on your side to provide the requesting information?
Best Regards,
Jonathan
Sorry for the delay.
Hello @Jaap-van-Hengstum ,
We tried several scenarios to reproduce it but unfortunately, they all worked.
Your trace tells us that the key is not mapped correctly or is not found.
We have some additional question that might help us:
* Which version of EFE are you using?
6.1.3
* Is the `Id` an identity column?
Yes (see below)
* Do you use some other options? Such as specifying ColumnInputExpression
No, just IncludeGraph
, IncludeGraphOperationBuilder
and UnsafeMode
.
* What's the `ISet` class?
The standard System.Collections.Generic.ISet<T>
interface.
Obviously providing us a basic project sample with the issue will be the best for us since we will be able to fix it instead to find a way to reproduce it.
Best Regards,
Jonathan
We use the following model classes:
DiscountRule.cs
public class DiscountRule : IIdentifiable, IExtendedAuditable
{
public static readonly DiscountRule Default = new DiscountRule
{
Id = 0,
EmployerId = null,
CalculationMethod = DiscountCalculationMethod.DiscountFirst,
DiscountFactor = decimal.Zero,
ResellerFeeFactor = decimal.Zero,
EmployerFeeFactor = decimal.Zero,
ProcessingFeeFactor = decimal.Zero,
EffectiveFromDate = DateTime.MinValue,
Tag = "Default"
};
public long Id { get; set; }
public string Tag { get; set; }
public virtual Employer Employer { get; set; }
public long? EmployerId { get; set; }
public virtual Catalog Catalog { get; set; }
public long CatalogId { get; set; }
[Column(TypeName = "date")]
public DateTime EffectiveFromDate { get; set; }
public decimal DiscountFactor { get; set; }
public decimal ResellerFeeFactor { get; set; }
public decimal EmployerFeeFactor { get; set; }
public decimal ProcessingFeeFactor { get; set; }
public DiscountCalculationMethod CalculationMethod { get; set; }
public virtual ISet<CostType> ExcludedCostTypes { get; set; }
= new HashSet<CostType>();
}
DiscountRule
has the following derived types:
public class CatalogDiscountRule : DiscountRule
{
}
public class ProductDiscountRule : DiscountRule
{
public virtual Product Product { get; set; }
public long ProductId { get; set; }
}
public class ProgramFormDiscountRule : DiscountRule
{
public virtual ProgramForm ProgramForm { get; set; }
public long ProgramFormId { get; set; }
}
CostType.cs
public class CostType : IIdentifiable
{
private CostType(CostTypeValue value)
{
Value = value;
Name = value.ToString();
Description = value.GetMember().Attributes.Get<DescriptionAttribute>().Description;
}
protected CostType()
{
}
public long Id { get; set; }
[Index("IX_CostType_Value", 0, IsUnique = true)]
public CostTypeValue Value { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public static implicit operator CostType(CostTypeValue value) => new CostType(value);
public static implicit operator CostTypeValue(CostType costType) => costType.Value;
public virtual ISet<DiscountRule> DiscountRules { get; set; }
= new HashSet<DiscountRule>();
}
We also use the following EF configuration for DiscountRule
and CostType
:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
...
modelBuilder.Entity<DiscountRule>()
.HasMany(e => e.ExcludedCostTypes)
.WithMany(e => e.DiscountRules)
.Map(m => m.ToTable("DiscountRules_CostTypes"));
modelBuilder.Entity<DiscountRule>()
.Property(_ => _.DiscountFactor)
.HasPrecision(18, 4);
modelBuilder.Entity<DiscountRule>()
.Property(_ => _.ResellerFeeFactor)
.HasPrecision(18, 4);
modelBuilder.Entity<DiscountRule>()
.Property(_ => _.EmployerFeeFactor)
.HasPrecision(18, 4);
modelBuilder.Entity<DiscountRule>()
.Property(_ => _.ProcessingFeeFactor)
.HasPrecision(18, 4);
...
}
Thank you for the additional information,
We will try it again on our side.
Hello @Jaap-van-Hengstum ,
We are still not able to reproduce the issue. We tried hard but everything seems to work for us.
For an unknown reason, our library cannot find the Id
key property. So the WHERE
part is wrongly created which lead to the issue.
We used your class to create our example but it still works for us ;(
You will find in attachment one project that we created. Could you try it and see if everything work as expected? If you can point us at what we are missing, that will greatly help us.
Attachment: Z.Lab.Issue_0034.zip
Hello @Jaap-van-Hengstum ,
This issue will be closed since we haven't heard back from you.
Feel free to reopen if you can provide further information.
Best Regards,
Jonathan
I have this entity class which has a many-to-many relation with an implicit link table:
I want to bulk insert a set of
DiscountRules
and include the implicit link table but not theCostType
target table.If I don't specifiy a
IncludeGraphOperationBuilder
option, like this:I get the error
"Cannot insert duplicate key row in object 'dbo.CostTypes' with unique index 'IX_CostType_Value'. The duplicate key value is (0)."
So I try to add a
IncludeGraphOperationBuilder
option to exclude theCostType
table:But then I get the exception:
Which I don't know what to do with.
Can
BulkInsert
handle a many-to-many relation and include the implicit link table but not the target table?