c# - sqlite.net 表 where 子表上的条件

Question

我正在使用 Xamarin 表单、SQLite.net 和 SQLitenet 扩展，但我无法弄清楚为什么我期望简单的东西不起作用。

我有两个类(class)

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;
    }
}

和

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;}
}

我只是尝试执行以下查询，但它总是失败。

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

我得到这个异常

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 ---

正如您从堆栈跟踪中看到的那样，我在代码中添加了一些内容，例如排序方式。但这一切都可以正常工作，只要我在子表上没有条件。

Answer 1

SQLite-Net Extensions 没有添加任何查询功能(至少现在是这样)。这意味着您无法在查询时访问关系，因为该对象需要一个它未被执行的 JOIN。这就是您获得 NullReferenceException 的原因。

您需要手动执行 JOIN。替换这段代码:

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

有了这个:

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

自动创建此类查询非常复杂，并且不打算在不久的将来在 SQLite-Net Extensions 中支持它。

使用 SQLite-Net 扩展关系的另一个选项是使用 GetAllWithChildren 方法来过滤所需的主题，然后通过关系导航以获取实例:

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

这样您就不必手动输入 JOIN 并且结果完全相同，但是此选项会受到 N+1 issue 的影响。 ，因此它可能会遭受一些性能损失。

希望对您有所帮助。