Hi
Can anyone tell me if the following issue is caused by misconfiguration or if it is a bug in DA?
We were able to reproduce this issue with all recent and current DA NuGet versions on SQL Server 10.50.2550.0 and 12.0.2000.8. The queries and their results where analyzed with SQL profiler and are correct.
The data model is a Code-entity with m-to-m relationship on itself. The m-to-m relationship is defined by the table "CodeScope" which consists of two Code-Id FKs columns forming a primary key cluster.
Code entity:
int Id
string Name
IList<Code> RestrictedFor
IList<Code> Restricts
MappingConfiguration:
configuration.HasProperty(x => x.Id).IsIdentity(KeyGenerator.Default).WithDataAccessKind(DataAccessKind.ReadWrite).ToColumn("Id").IsNotNullable().HasColumnType("int").HasPrecision(0).HasScale(0);
configuration.HasProperty(x => x.Name).WithDataAccessKind(DataAccessKind.ReadWrite).ToColumn("Name").IsNotNullable().HasColumnType("nvarchar").HasLength(255);
configuration.HasAssociation(x => x.RestrictedFor).IsManaged().WithOpposite(x => x.Restricts).WithDataAccessKind(DataAccessKind.ReadWrite).MapJoinTable("CodeScope", (x, y) => new { Code_Id = x.Id, Scope_Id = y.Id });
Now the thing is to a certain number of entries in the code table both collections (Restricted and RestrictedFor) on all Code-entities represent the defined relationships correctly. After adding the 693th entry in the Code-table, without adding a new relationship, one collection is suddenly empty:
var codes = db.GetAll<Code>().Include(x => x.RestrictedFor).Include(x => x.Restricts);
codes.Count() // 662
codes .SelectMany(x => x.RestrictedFor).Count() // 169
codes .SelectMany(x => x.Restricts).Count() // 169
// adding a new Code to db manually, without defining new CodeScope relationship
var codes = db.GetAll<Code>().Include(x => x.RestrictedFor).Include(x => x.Restricts); // in a new context, restarted program
codes.Count() // 663
db.GetAll<Code>().SelectMany(x => x.RestrictedFor).Count() // 0 (!)
db.GetAll<Code>().SelectMany(x => x.Restricts).Count() // 169
// when loading all codes without Include, it works again..
var codes = db.GetAll<Code>();
codes.Count() // 663
db.GetAll<Code>().SelectMany(x => x.RestrictedFor).Count() // 169
db.GetAll<Code>().SelectMany(x => x.Restricts).Count() // 169
Code and SQL scripts are on GitHub: https://github.com/mkubicek/DataAccessSelfReference
Really appreciate all forms of feedback as this is a major showstopper for us. Thank you in advance.