.NET MAUI has a great tutorial on how to add a local database to your .NET MAUI project. The tutorial shows how to add sqlite-net-pcl library to your NuGet packages. This provides a lightweight ORM that does extremely basic ORM capabilities. We won’t cover that here, but in this post we will be covering how to add extensions to the sqlit-net-pcl library which give you the ability to save whole object graphs. We will be adding SqliteNetExtensions library to your NuGet packages. This will give you the ability to do one-to-one, one-to-many, many-to-one, many-to-many, inverse and text-blobbed relations on top of the features of the sqlite-net-pcl library. Being able to read/write whole object graphs can save you a whole lot of time and coding.

See Working Example Project on GitHub

The tutorial that .NET MAUI provides says to install the two libraries below:
Sqlite-net-pcl
SQLitePCLRaw.bundle_green

We also have to install the following libraries to get the extended ORM functionality described above:
SQLiteNetExtensions
SQLiteNetExtensions.Async
You only have to install the SQLiteNetExtensions.Async if you want to do asynchronous database calls. For the examples described in this tutorial we will only use synchronous database access calls.

This tutorial’s object graph will be a Student that has a single address and multiple classes she is registered in. The Student class has a one-to-one relationship with the Address class and a one-to-many relationship with the Class class.

First we will create our data transfer objects:

First we create our Student class.
We have to use the Table annotation right above the class definition to define what our table will be called in the database. We set up our primary key using the PrimaryKey annotation and define what we want the primary ID column to be called with the Column annotation. We define our FirstName and LastName properties and define them using the Column annotation. We have a one-to-one relationship with the Address class so we have to define the foreign key of the Address class as a property using the ForeignKey annotation as seen below.
When we retrieve the Student from the database we want all associated objects to come with it (ie: the Address and all of the classes). To get the address to show up we define a property with the Address type. We use the [OneToOne(CascadeOperations = CascadeOperation.All)] annotation so that the Address is retrieved automatically when we get the student.
There can be more than one Class for a student so we create a List type Class property. Since this is a one-to-many relationship we use the [OneToMany(CascadeOperations = CascadeOperation.All)] annotation. In both this and the Address property the part that makes the retrieval automatic is the CascadeOperations = CascadeOperation.All property of the Annotation.

using System;
namespace DatabaseProject.DTO;

using SQLite;
using SQLiteNetExtensions.Attributes;

[Table("Student")]
public class Student
{

	[PrimaryKey, AutoIncrement, Column("ID")]
	public int ID { get; set; }

	[Column("FirstName")]
	public string FirstName { get; set; }

	[Column("LastName")]
	public string LastName { get; set; }

    [ForeignKey(typeof(Address))]
    public int AddressID { get; set; }

    [OneToOne(CascadeOperations = CascadeOperation.All)]
	public Address Address { get; set; }


    //one to many relationship with Class
    [OneToMany(CascadeOperations = CascadeOperation.All)]
	public List<Class> Classes { get; set; }

}

Next is our “Class” class. This will represent the class that the student is taking. This object has a many to one relationship with student. The student can have multiple classes. To set this up properly we need to define a foreign key for student for the Class class. We will also set up a many to one property.

using System;
using SQLite;
using SQLiteNetExtensions.Attributes;

namespace DatabaseProject.DTO;

[Table("Class")]
public class Class
{

	[PrimaryKey, AutoIncrement, Column("ID")]
	public int ID { get; set; }

	[Column("ClassName")]
	public string ClassName { get; set; }

	[ForeignKey(typeof(Student))]
	public int StudentId { get; set; }

	[ManyToOne]
	public Student Student { get; set; }
}

Finally we have our Address class. We set u the primary key using the PrimaryKey annotation as we described above. Address has a one-to-one relationship with Student so we create a foreign key that points to students and create a Student property of type Student with the OneToOne annotation. Doing so will allow us to reference the Student from the Address object.

using System;
using SQLite;
using SQLiteNetExtensions.Attributes;

namespace DatabaseProject.DTO;

[Table("Address")]
public class Address
{

    [PrimaryKey, AutoIncrement, Column("ID")]
    public int ID { get; set; }

	[Column("Street")]
	public string Street { get; set; }

	[Column("City")]
	public string City { get; set; }

	[Column("ZipCode")]
	public string ZipCode { get; set; }

    [ForeignKey(typeof(Student))]
    public int StudentId { get; set; }

    [OneToOne]
    public Student Student { get; set; }

}

Next we are going to create our data access objects.
First we have our StudentDataAccess class. This class an an Init method that creates the Database connection and creates the tables from our data transfer objects.
Most of the methods used in this data access object are methods that only come as “add ons” from the SQLiteNetExtensions library.
These include:
GetAllWithChildren<Class>();
InsertWithChildren(object);
UpdateWithChildren(object);

These extra methods allow us to save complete object graphs to the database.

using System;
using DatabaseProject.DTO;
using SQLite;
using SQLiteNetExtensions;
using SQLiteNetExtensions.Extensions;

namespace DatabaseProject.Database
{
    public class StudentDataAccess : IStudentDataAccess
    {
        SQLiteConnection Database;

        void Init()
        {
            if (Database is not null)
                return;

            Database = new SQLiteConnection(Constants.DatabasePath, Constants.Flags, false);
            Database.CreateTable<Student>();
            Database.CreateTable<Address>();
            Database.CreateTable<Class>();


        }
        public List<Student> GetStudentsWithChildren()
        {
            Init();
            return Database.GetAllWithChildren<Student>();
        }
        public Student GetStudent(int id)
        {
            Init();
            return Database.Get<Student>(id);

        }
        public void InsertStudentWithChildren(Student student)
        {
            Init();
            Database.InsertWithChildren(student);
        }

        public Student GetStudentWithChildren(int id)
        {
            Init();
            return Database.GetWithChildren<Student>(id);
        }

        public void UpdateStudentWithChildren(Student student)
        {
            Init();
            Database.UpdateWithChildren(student);
        }
    }
}

Next we define our ViewModel for our home page called HomePageViewModel.
We will use this view model to add some test data to the database using our extra methods that come with our library extension. We will be accessing the database through our database access objects. We inject these as interfaces into the constructor of the ViewModel. To see how to perform dependency injection see this CodeShadowHand tutorial!

In the below code we create a Student object, and fill out its fields. We also create an Address object and fill out its fields. We then associate the Address object with the Student object. We then create a Class object and fill out its fields. We then associate the Class object with the Student object. After that we call the data access object’s InsertStudentWithChildren(student) method which does a database call to Database.InsertWithChildren(student). This saves the whole object graph at one time.

Next we create a new Class and fill out its fields. We save the class in the database using the ClassDataAccess object. We then associate the new class with our existing student.
After that we do a call to UpdateStudentWithChildren(Student student) which makes a database call Database.UpdateWithChildren(student). This creates an association with our new Class we just created and the student so the foreign key is saved in the database.

Once we have completed that we do a call to our data access object’s method GetStudentsWithChildren() and we iterate over all of the students and print the first name, last name as well as the properties of the associated Address object. We then iterate over each of the classes associated with the student and print out their properties as well.

using System;
using DatabaseProject.Database;
using DatabaseProject.DTO;

namespace DatabaseProject.ViewModels;

public class HomeViewModel
{
    IStudentDataAccess _studentDataAccess;
    IClassDataAccess _classDataAccess;

    public HomeViewModel(IStudentDataAccess studentDataAccess, IClassDataAccess classDataAccess)
    {
        try
        {
            _studentDataAccess = studentDataAccess;
            _classDataAccess = classDataAccess;
            AddStudentAndStudentDetails();
            AddNewClassToExistingStudent();

            List<Student> students = GetStudents();

            foreach (Student student in students)
            {
                Console.WriteLine("**Student Name**");
                Console.WriteLine(student.FirstName);
                Console.WriteLine(student.LastName);
                Console.WriteLine("**Address**");
                Console.WriteLine(student.Address.Street);
                Console.WriteLine(student.Address.City);
                Console.WriteLine(student.Address.ZipCode);
                Console.WriteLine("**Classes**");
                foreach (Class currentClass in student.Classes)
                {
                    Console.WriteLine(currentClass.ClassName);
                }
            }
            string test = "We did it!";

        }
        catch(Exception e)
        {
            string message = e.Message;
            string test = "ouch!";
        }
    }
    private List<Student> GetStudents()
    {
        return _studentDataAccess.GetStudentsWithChildren();
    }
    private void AddNewClassToExistingStudent()
    {
        try
        {


            Student ourChemistryStudent = _studentDataAccess.GetStudentWithChildren(1);

            Class physicalChemistry = new Class();
            physicalChemistry.ClassName = "Chemistry 442";

            _classDataAccess.InsertClass(physicalChemistry);

            ourChemistryStudent.Classes.Add(physicalChemistry);

            _studentDataAccess.UpdateStudentWithChildren(ourChemistryStudent);
        }
        catch(Exception e)
        {

            string message = e.Message;
            string test = "ouch!";
        }
    }

    private void AddStudentAndStudentDetails()
    {
        try
        {
            Student student = new Student();
            student.FirstName = "Jim";
            student.LastName = "Jowl";

            Address address = new Address();
            address.Street = "123 Fake Street";
            address.City = "Springfield";
            address.ZipCode = "97409";

            Class chemistry = new Class();
            chemistry.ClassName = "Chemistry 421";

            student.Address = address;

            student.Classes = new List<Class> { chemistry };

            _studentDataAccess.InsertStudentWithChildren(student);

            Student ourChemistryStudent = _studentDataAccess.GetStudentWithChildren(student.ID);



        }
        catch(Exception e)
        {
            string message = e.Message;
            string test = "ouch!";

        }

    }
}

Here is the output that shows our object graph was inserted, updated, and retrieved properly thanks to our new extension methods!

Normally we would be using the object graph as a Property in our ViewModel for our ContentPage to populate controls in the XAML user interface, but that is beyond the scope of this tutorial.