Many-to-Many relationships

[Allan Nielsen]

Not sure what I'm doing wrong, but when using the ManyToMany attribute as outlines in the integration tests within my own code, I am having issues saving new related records when the tables are already populated. The issue manifests itself after I have added the initial records, and then add a new parent record with one or more children which may or may not exist already.

I have rectified the issue within my own version of the code as a test for my specific scenario.

Within the WriteOperations class, you need to modify the private method: private static void UpdateManyToManyForeignKeys(this SQLiteConnection conn, object element, PropertyInfo relationshipProperty)

        // Obtain the list of children keys
        var childList = (IEnumerable)relationshipProperty.GetValue(element, null);
        var childKeyList = (from object child in childList ?? new List<object>()
                            select otherEntityPrimaryKeyProperty.GetValue(child, null)).ToList();

** if (!childKeyList.Any()) return;

Add the last line which checks the childKeyList. If this is empty it prevents the Delete and Insert statements running later in that method. The delete statement was the one causing me problems.

I'm not sure why the method is being called twice with empty child keys before the actual call that is needed is made, and do not have the time to tackle that issue.

Basically my classes are as follows:

public class Category
{
    public int Id {get;set;}
    public string Title {get;set;}
    [ManyToMany(typeof(CategoryHeader),CascadeOperations=CascadeOperation.All)]
    public ObservableCollection<Header> Headers { get; set; }
}

public class Header
{
    public int Id {get;set;}
    public string Title {get;set;}
    [ManyToMany(typeof(CategoryHeader),CascadeOperations=CascadeOperation.All)]
    public ObservableCollection<Category> Categories { get; set; }
}

And in my data service call Db.InsertOrReplaceWithChildren(header, true);

Note:- This code is an abbreviation of the actual code.

[Guillermo Gutiérrez]

None of your classes have a PrimaryKey. I guess that it's a copy-paste issue, but just make sure that it's not the case.

Maybe the CategoryHeader class doesn't have an AutoIncrement primary key? Can you post its code?

[Allan Nielsen]

Thanks for your response but I wanted to keep the post reasonably short so didn't post all the code. But since you asked:

public class CategoryHeader
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    [ForeignKey(typeof(Category))]
    public Guid CategoryId { get; set; }
    [ForeignKey(typeof(Header))]
    public Guid HeaderId { get; set; }
}

All entity types inherit from a base class defined in part as follows:

public abstract class ClientModel<TKey> : BindableBase, IValidatable, ISupportReplication, IHasKey
{
    [PrimaryKey]
    [NotNull]
    [RestorableState]
    public TKey Id
    {
        get { return _id; }
        set { SetProperty(ref _id, value); }
    }

The issue has nothing really to do with Primary keys or AutoIncrement settings, this has to do with relationships, and entities that match parameters. If none exist (as is the case on the first two passes through UpdateManyToManyForeignKeys the ChildKeyList will be empty making the SQL statements invalid as there will be no "In" portion. Hope this clarifies things a little.

[Guillermo Gutiérrez]

Hey Allan, I'm not able to reproduce the issue. Empty 'in' statements work as expected for me. Can you check if this test works for you?

public class Category
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public string Title { get; set; }
    [ManyToMany(typeof(CategoryHeader), CascadeOperations = CascadeOperation.All)]
    public ObservableCollection<Header> Headers { get; set; }
}

public class Header
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public string Title { get; set; }
    [ManyToMany(typeof(CategoryHeader), CascadeOperations=CascadeOperation.All)]
    public ObservableCollection<Category> Categories { get; set; }
}

public class CategoryHeader
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    [ForeignKey(typeof(Category))]
    public int CategoryId { get; set; }
    [ForeignKey(typeof(Header))]
    public int HeaderId { get; set; }
}

[Test]
public void TestIssue40() {
    var conn = Utils.CreateConnection();
    conn.DropTable<CategoryHeader>();
    conn.DropTable<Category>();
    conn.DropTable<Header>();
    conn.CreateTable<CategoryHeader>();
    conn.CreateTable<Category>();
    conn.CreateTable<Header>();

    var category1 = new Category {
        Title = "Category1"
    };

    var category2 = new Category {
        Title = "Category2"
    };

    var category3 = new Category {
        Title = "Category3"
    };

    var header1 = new Header {
        Title = "Header1",
        Categories = new ObservableCollection<Category>{ category1, category2 }
    };

    var header2 = new Header {
        Title = "Header2",
        Categories = new ObservableCollection<Category>{ category2, category3 }
    };

    var header3 = new Header {
        Title = "Header3",
        Categories = new ObservableCollection<Category>{ category3 }
    };

    var header4 = new Header {
        Title = "Header4",
        Categories = new ObservableCollection<Category>()
    };

    // Set inverse relationships
    // (required because they have cascade insert operation and are not ReadOnly)
    category1.Headers = new ObservableCollection<Header>{ header1 };
    category2.Headers = new ObservableCollection<Header>{ header1, header2 };
    category3.Headers = new ObservableCollection<Header>{ header2, header3 };

    // Insert all elements recursively
    conn.InsertOrReplaceAllWithChildren(new List<Header>{ header1, header2, header3, header4 }, recursive: true);

    // Insert element 2 again (will replace all elements recursively but header4)
    conn.InsertOrReplaceWithChildren(header2, recursive: true); // This will update the complete tree but header4

    var header5 = new Header {
        Title = "Header5",
        Categories = new ObservableCollection<Category> {
            new Category{ Title = "Category4" },
            new Category{ Title = "Category5" }
        }
    };

    // Insert a new, detached header
    conn.InsertOrReplaceWithChildren(header5, recursive: true);


    var header6 = new Header {
        Title = "Header6",
        Categories = new ObservableCollection<Category>{ category1, category3 }
    };
    category1.Headers.Add(header6);
    category3.Headers.Add(header6);

    // Insert a new header connected to previous elements
    conn.InsertOrReplaceWithChildren(header5, recursive: true);
}

[Guillermo Gutiérrez]

Closing this issue. Feel free to re-open it if you experience this error again.