Parent/Children self referenced entity with SQLite

[jfmary-storkcom]

Hello,

I am using Dapper v2.1.35 and FastCrud v3.1.46

I am testing FastCrud. I think it is great and documentation is really good.
I ran into an issue with parent/children in self referenced entity.
Here is my entity :

CREATE TABLE "location" (
"id" INTEGER NOT NULL ,
"code" varchar(45) NOT NULL,
"parent" INTEGER DEFAULT NULL,
PRIMARY KEY ("id")
CONSTRAINT "fk_location_location1" FOREIGN KEY ("parent") REFERENCES "location" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION,
)

[Table("location")]
class Location
{
[Key]
public long ID { get; set; }
public string Code { get; set; }
[ForeignKey(nameof(ParentObject))]
public long? Parent { get; set; }
public Location ParentObject { get; set; }

//[InverseProperty(nameof(Location.ParentObject))]
//public IEnumerable<Location> Children { get; set; }

}

As you see I expect ParentObject to be populated with the parent, but I got strange behaviour. here is how I call
var l = cnx.Find(s => s.WithAlias("l1").Include(s1 => s1.WithAlias("l2")));

You can see that parent is 4 but ParentObject id is 6 !

More over InverseProperty seems not to work.

Is there something I am doing wrong ?

[MoonStorm]

I think that this section of the wiki would apply to your situation: https://github.com/MoonStorm/FastCrud/wiki/JOINs#referencing-the-same-entity-multiple-times

So for this entity registration:

[Table("location")]
class Location
{
  [Key]
  [Column("id")]
  public long Id { get; set; }

  [ForeignKey(nameof(ParentLocation))]
  [Column("parent")]
  public long? ParentId { get; set; }

  public Location ParentLocation { get; set; }

  [InverseProperty(nameof(Location.ParentLocation))]
  public IEnumerable<Location> ChildrenLocations { get; set; }
}

then this call should work:

var locations = dataConnection.Find<Location>(statement => statement
  .WithAlias("locations")
  .Include<Location>(join => join.WithAlias("parents")
    .Referencing<Location>(relationship => relationship
      .FromAlias("locations").FromProperty(location => location.ChildrenLocations)
      .ToProperty(parentLocation => parentLocation.ParentLocation))
  )
  .OrderBy($"{nameof(Location.Id):of locations}")
).ToImmutableArray();

[jfmary-storkcom]

Thanks for your reply. I will try it ASAP.

[jfmary-storkcom]

And it works indeed.

[jfmary-storkcom]

One more thing that I don't understand : how to make it work without the InverseProperty. I still have the mismatch between parentId and the actual parentLocation. I think the inverse property is making the query run really slow.

Thanks again.