Maintaining both FK property and joined object in NPOCO C# model without memory leaks?

Question

Assuming I have the following classes I'm using to map objects from the db:

public class OwnedThingie
{
    public long Id {get;set;}
    public long OwnerId {get;set;}
    public UserMan Owner {get;set;}
}

public class UserMan
{
    public long Id {get;set;}
    public string Name {get;set;}
    public List<OwnedThingie> Thingies {get;set;}
}

and I use the following fluent mappings:

For<OwnedThingie>()
    .TableName("DbThingies")
    .PrimaryKey(o => o.Id, true)
    .Columns
    (
        c => 
        {
            c.Column(o => o.Id);
            c.Column(o => o.OwnerId);
            c.Column(o => o.Owner).WithName("OwnerId").Reference( um => um.Id, ReferenceType.OneToOne )
        },
        true
    );

For<UserMan>()
    .TableName("DbUsers")
    .PrimaryKey(u => u.Id, true)
    .Columns
    (
        c => 
        {
            c.Column(u => u.Id);
            c.Column(u => u.Name);
            c.Many(u => u.Thingies).Reference(o => o.OwnerId)
        },
                true
    );

This will create a gigantic memory leak as it will not recognize the owner <-> thingies as a looping relationship (specifically, it will map the OneToOne relationship from the Thingie to the User, then find a Many relationship to a Thingie, map again the Thingie, find the User and so on...)

We really need to have both the column OwnerId and the property Owner on the data model. Using a reference of OneToOne used to be the way to go, but unfortunately if you then want to establish proper relationships it will eat all of your memory as stated above.

To give a frame of reference, when I say "all of your memory" I mean a mappings configuration with 17 simple tables will cause the NPOCO-powered service to swallow anywhere between 1 and 3GB of memory.

What has been Tried:

• Conditionally stopping it from including too many nested layers in the map, by manually keeping counts or reference chains. Failed, at least so far, as there doesn't seem to be a way to correctly check the greater context.
• Using .Result() and .Computed() to overcome the limitation on same-field-name (you can't have a field OwnerId if your Foreign reference is named OwnerId). Failed as the names are saved in a Dictionary and there's no way you're going around that.
• Creating a different model that contained generic objects instead of actual data objects to stop the join propagation. To be clear, class SuperUserMan: UserMan with the offending relationships hidden by simple objects. Failed for reasons not yet clear, but likely treated all as dynamic by NPOCO. Memory usage went higher.
• Just pretending the FK properties are still there - trying to use them in our code, or rather NPOCO's LINQ expressions. Failed, as they are added to the dictionaries but cannot be used in any way.

Any suggestions are extremely welcome.

Using NPOCO latest, SQL Server 2019, .NET Core currently at 3.1.latest

This has also been opened as an Issue in the NPOCO GitHub - however here I'm asking if anyone has a solution that does not involve touching NPOCO in any way.

Answer 1

I think you may need to tune your model based on the queries which are needed, rather than trying to accommodate every possible scenario.

• You could remove the relationship (in your model) from Thingy to Owner so that when you query an owner you can bring back all of the thingies but not the other way around.

If for some reason you did need to retrieve all of the owners for a thingy then you may need a second model. I think this is a common problem which exists in all ORMs and you may have to use inline SQL or a procedure to get around the problem.