sqlite.net table where condition on a child table

Question

I'm using Xamarin forms, SQLite.net and SQLitenet extensions and I'm unable to figure out why something that I would expect to be simple just doesn't work.

I have two classes

public class MeasurementInstanceModel
{
    public MeasurementInstanceModel ()
    {
    }

    [PrimaryKey]
    [AutoIncrement]
    public int Id {
        get;
        set;
    }

    [ForeignKey(typeof(MeasurementDefinitionModel))]
    public int MeasurementDefinitionId {
        get;
        set;
    }

    [ManyToOne(CascadeOperations = CascadeOperation.CascadeRead)]
    public MeasurementDefinitionModel Definition {
        get;
        set;
    }

    [ForeignKey(typeof(MeasurementSubjectModel))]
    public int MeasurementSubjectId {
        get;
        set;
    }

    [ManyToOne(CascadeOperations = CascadeOperation.CascadeRead)]
    public MeasurementSubjectModel Subject {
        get;
        set;
    }

    public DateTime DateRecorded {
        get;
        set;
    }

    [OneToMany(CascadeOperations = CascadeOperation.All)]
    public List<MeasurementGroupInstanceModel> MeasurementGroups {
        get;
        set;
    }
}

and

public class MeasurementSubjectModel
{


    [PrimaryKey]
    [AutoIncrement]
    public int Id {
        get;
        set;
    }
    public string Name {
        get;
        set;
    }

    [OneToMany (CascadeOperations = CascadeOperation.All)]
    public List<MeasurementInstanceModel> MeasurementInstances {get;set;}
}

I'm simply trying to do the following query and it always fail.

db.Table<MeasurementInstanceModel>().Where(w => w.Subject.Name == avariable);

I get this exception

System.Diagnostics.Debugger.Mono_UnhandledException (ex={System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.NullReferenceException: Object reference not set to an instance of an object at SQLite.Net.TableQuery1[MeasureONE.MeasurementInstanceModel].CompileExpr (System.Linq.Expressions.Expression expr, System.Collections.Generic.List1 queryArgs) [0x00000] in :0 at SQLite.Net.TableQuery1[MeasureONE.MeasurementInstanceModel].CompileExpr (System.Linq.Expressions.Expression expr, System.Collections.Generic.List1 queryArgs) [0x00000] in :0 at SQLite.Net.TableQuery1[MeasureONE.MeasurementInstanceModel].CompileExpr (System.Linq.Expressions.Expression expr, System.Collections.Generic.List1 queryArgs) [0x00000] in :0 at SQLite.Net.TableQuery1[MeasureONE.MeasurementInstanceModel].GenerateCommand (System.String selectionList) [0x00000] in <filename unknown>:0 at SQLite.Net.TableQuery1[MeasureONE.MeasurementInstanceModel].GetEnumerator () [0x00000] in :0 at System.Collections.Generic.List1[MeasureONE.MeasurementInstanceModel].AddEnumerable (IEnumerable1 enumerable) [0x00000] in :0 at System.Collections.Generic.List1[MeasureONE.MeasurementInstanceModel]..ctor (IEnumerable1 collection) [0x00000] in :0 at System.Linq.Enumerable.ToList[MeasurementInstanceModel] (IEnumerable1 source) [0x00000] in <filename unknown>:0 at MeasureONE.Repository1[MeasureONE.MeasurementInstanceModel].GetAll[DateTime] (System.Linq.Expressions.Expression1 predicate, System.Linq.Expressions.Expression1 orderBy, Nullable1 descending, Nullable1 skip, Nullable1 count) [0x00094] in /Users/jean-sebastiencote/MeasureONE/MeasureONE/Models/Repository/Repository.cs:48 at MeasureONE.Repository1[MeasureONE.MeasurementInstanceModel].GetAllWithChildren[DateTime] (System.Linq.Expressions.Expression1 predicate, System.Linq.Expressions.Expression1 orderBy, Nullable1 descending, Nullable1 skip, Nullable1 count) [0x00009] in /Users/jean-sebastiencote/MeasureONE/MeasureONE/Models/Repository/Repository.cs:54 at MeasureONE.MeasurementListViewModel.Load (System.Linq.Expressions.Expression1 pred, Nullable1 skip, Nullable1 count) [0x00049] in /Users/jean-sebastiencote/MeasureONE/MeasureONE/ViewModels/MeasurementListViewModel.cs:42 at MeasureONE.MeasurementListViewModel.Load (MeasureONE.FilterViewModel filter) [0x000cf] in /Users/jean-sebastiencote/MeasureONE/MeasureONE/ViewModels/MeasurementListViewModel.cs:34 at MeasureONE.MeasurementListViewModel.m__1 (GalaSoft.MvvmLight.Messaging.NotificationMessage`1 msg) [0x00007] in /Users/jean-sebastiencote/MeasureONE/MeasureONE/ViewModels/MeasurementListViewModel.cs:21 at (wrapper managed-to-native) System.Reflection.MonoMethod:InternalInvoke (System.Reflection.MonoMethod,object,object[],System.Exception&) at System.Reflection.MonoMethod.Invoke (System.Object obj, BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) [0x00000] in :0 --- End of inner exception stack trace ---

As you can see from the stack trace, I have a few more things in the code, such as an order by. But this all work fine, as long as I don't have a condition on the child table.

Answer 1

SQLite-Net Extensions doesn't add any query functionality (at least by now). This means that you cannot access a relationship at query time, because that object requires a JOIN that it's not being performed. This is why you are obtaining a NullReferenceException.

You'll need to perform the JOIN manually. Replace this code:

db.Table<MeasurementInstanceModel>().Where(w => w.Subject.Name == avariable);

With this one:

var result = conn.Query<MeasurementInstanceModel>(
    "SELECT * " +
    "FROM MeasurementInstanceModel AS it " +
    "JOIN MeasurementSubjectModel AS sb " +
    "ON it.MeasurementSubjectId == sb.Id " +
    "WHERE sb.Name == ?", avariable);

Creating this kind of queries automatically is quite complex and it's not planned to be supported in a near future in SQLite-Net Extensions.

Another option using SQLite-Net Extension relationships is using GetAllWithChildren method to filter the desired subjects and then navigate through the relationships to obtain the instances:

var subjects = conn.GetAllWithChildren<MeasurementSubjectModel>(s => s.Name == avariable);
var result = subjects.Select(s => s.MeasurementInstances).Distinct().ToList();

This way you don't have to manually type the JOIN and the result is exactly the same, however this option suffers from the N+1 issue, so it may suffer some performance penalty.

Hope it helps.