InMemoryAdapter fails with 2 level joins

[sloncho]

Looks like the InMemoryAdapter fails to follow 2 level joins. In the following code, value.Group is null, while description.Group (one level join) and value.Description (also one level join) have the right values.

The same query against a real database with foreign keys works as expected.

            [Test]
            public void TwoLevelJoinTest()
            {
                var adapter = new InMemoryAdapter();
                Database.UseMockAdapter(adapter);
                adapter.SetKeyColumn("SettingValue", "Id");
                adapter.SetKeyColumn("SettingGroup", "Id");
                adapter.SetKeyColumn("SettingDescription", "Id");
                adapter.Join.Master("SettingGroup", "Id").Detail("SettingDescription", "SettingGroupId");
                adapter.Join.Master("SettingDescription", "Id").Detail("SettingValue", "SettingDescriptionId");

                var db = Database.Open();
                db.SettingGroup.Insert
                    (
                        Id: GROUP_ID,
                        Name: "Group"
                    );
                db.SettingDescription.Insert
                    (
                        Id: DESCRIPTION_ID,
                        SettingGroupId: GROUP_ID,
                        Name: "Description"
                    );
                db.SettingValue.Insert
                    (
                        Id: 100,
                        SettingDescriptionId: DESCRIPTION_ID,
                        Value: "zzz"
                    );
                var description = db.SettingDescription.All()
                                        .Select
                    (
                        db.SettingDescription.Name.As("Description"),
                        db.SettingDescription.SettingGroup.Name.As("Group")
                    ).First();
                var value = db.SettingValue.All()
                                  .Select
                    (
                        db.SettingValue.Id,
                        db.SettingValue.Value,
                        db.SettingValue.SettingDescription.Name.As("Description"),
                        db.SettingValue.SettingDescription.SettingGroup.Name.As("Group")
                    ).First();

                string descriptionGroup = description.Group;
                string valueDescription = value.Description;
                string valueGroup = value.Group;

                descriptionGroup.Should().Be.EqualTo("Group");
                valueDescription.Should().Be.EqualTo("Description");
                valueGroup.Should().Be.EqualTo("Group");
            }

[bjornsallarp]

I have the same problem. Seems development has stalled so I might have a crack at fixing this myself.

[bjornsallarp]

I had a look at this today. The join implementation has multiple problems and it's not easy to implement something that would work with any level of joins. I found an obvious bug that seems like a cut-and-paste bug where the relationships weren't set up properly. But the main problem is how relationships are handled and stored.

With your setup the stored object would be SettingsGroup.SettingsDescrption.SettingsValue. In SQL the result is always flat, the name of each column is TableName.Column. The InMemoryAdapter instead returns a list of dictionary that contain joined objects in a property. The result can have any depth. And in a one to many relationship you only get one row instead of row*relations. InMemoryAdapter should perhaps return a dictionary with all the relation properties (prefixed with table name) included in that one dict?

[jdscolam]

@bjornsallarp is the join logic broken in any of the other adapters?