This is a migrated thread and some comments may be shown as answers.

Issues with self-referencing many-to-many relationship (when eager-loading with include)

12 Answers 67 Views

General Discussions

This is a migrated thread and some comments may be shown as answers.

This question is locked. New answers and comments are not allowed.

RIT asked on 23 Feb 2016, 03:57 PM

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.

Product Bundles

DevCraft

Web

Mobile

Document Management

Desktop

Reporting & Mocking

Automated Testing

CMS

UI/UX Tools

Debugging

Free Tools

Issues with self-referencing many-to-many relationship (when eager-loading with include)

12 Answers, 1 is accepted