Issue with Nested One-to-Many Relationships in SQLite-Net Extensions: LevelTwoModels Property Return
I am working on a model hierarchy using SQLite and SQLite-Net Extensions. The model structure consists of three levels: RootModel, LevelOneModel, and LevelTwoModel. Each model contains properties that establish relationships with the next level, as follows:
RootModel has a one-to-many relationship with LevelOneModel. LevelOneModel has a one-to-many relationship with LevelTwoModel. Here is my implementation:
public class RootModel
{
[PrimaryKey, AutoIncrement]
public Guid Id { get; set; }
public string? Name { get; set; }
[OneToMany(CascadeOperations = CascadeOperation.All)]
public List<LevelOneModel> LevelOneModels { get; set; }
}
public class LevelOneModel
{
[PrimaryKey, AutoIncrement]
public Guid Id { get; set; }
public string? Name { get; set; }
[ForeignKey(typeof(RootModel))]
public Guid RootModelId { get; set; }
[ManyToOne]
public RootModel RootModel { get; set; }
[OneToMany(CascadeOperations = CascadeOperation.All)]
public List<LevelTwoModel> LevelTwoModels { get; set; }
}
public class LevelTwoModel
{
[PrimaryKey, AutoIncrement]
public Guid Id { get; set; }
public string? Name { get; set; }
[ForeignKey(typeof(LevelOneModel))]
public Guid LevelOneModelId { get; set; }
[ManyToOne]
public LevelOneModel LevelOneModel { get; set; }
}
I am using SQLite-Net Extensions' InsertWithChildren and GetWithChildren methods for data manipulation. While the relationships between RootModel and LevelOneModel work as expected, the LevelTwoModels property in LevelOneModel always returns null when retrieved.
What could be causing this issue, and how can I resolve it to ensure all relationships are correctly handled and retrieved?
All three table is created.
For insert:
await SqliteDataStore.Instance.Database.InsertWithChildrenAsync(Data);
For get:
return await SqliteDataStore.Instance.Database.GetAllWithChildrenAsync<RootModel>();6 Replies
Any reason you're using this weird ORM with 16 stars on Github that's been last updated a decade ago, instead of Entity Framework?
EF has
.Include() and .ThenInclude() to load related data, or ideally you would use a .Select()GitHub
GitHub - media-tools/sqlite-net-extensions
Contribute to media-tools/sqlite-net-extensions development by creating an account on GitHub.
I'd tell you to maybe raise an issue on the repo... but the code hasn't been updated for ten years now
So slim chances
Well, this is a maui application and I did not find any other way to make this nested list one to many relation between the object. I wouldnt mind to get rid of it.
How does Maui prevent you from using EF?
And, honestly, the much more interesting question is, what in the hell did you google to get a "use this obscure ORM nobody ever heard of" instead of "just use EF" result?
I wanted to use SQLite and seemed legit to use the extension was built for. I was not expecting probably did not dig enough to realize it is possible to use ef on the device itself with migrations etc.
So based on your suggest this should be solvable with the following packages only:
<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" /> <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" >and just for the model:
public class RootModel
{
public Guid Id { get; set; }
public string? Name { get; set; }
public ICollection<LevelOneModel> LevelOneModels { get; } = new List<LevelOneModel>();
}
public class LevelOneModel
{
public Guid Id { get; set; }
public string? Name { get; set; }
public Guid RootModelId { get; set; }
public RootModel RootModel { get; set; }
}
Swiching from Sqlite and it's extension to Ef solved this issue.