单元测试不返回联接表的结果

Question

我鼓励在编写单元测试时检查函数在连接两个表时是否返回有效结果时出现问题。我有以下场景:我有2个表，命名为文件夹和项目，文件夹可以有多个项目。他们的DDL是这样的:

create table Folder
(   varchar(max) Name,
    int User_Id,
    int Id primary key
);
create table Item
(   int Id primary key,
    varchar(max) Data,
    int User_Id,
    int Folder_id foreign key Folder(Id)
}

我想从属于文件夹的项目中收集所有的"数据"，这些文件夹的名称以某些前缀开头。因此，我编写了以下函数，它工作良好并返回有效结果。

string GetDatas(string prefix, int userId) //_db is injected, this function is a part of a DatasProvider class
{
    dynamic folder;
    return _db.dbo.Item.FindAllByUser_Id(userId)
            .Join(_db.dbo.Folder, out folder)
            .On(_db.dbo.Item.Folder_id == folder.Id)
            .Where(_db.dbo.Folder.Name.Like(prefix))
            .Select(_db.dbo.Item.Data)
            .ToScalarList<string>();
}

我写了一个单元测试来检查函数是否工作正常，我的规范是这样的:

public class Spec
{
    private static dynamic _db;
    private static IList<string> result;
    private const int userId = 14;
    private const string validPrefix = "dd";
    private const int validFolderId = 22;
    private const int invalidFolderId = 33;
    private static readonly List<string> validData = new List<string>()
    {
        "1",
        "2",
        "3",
        "4"
    };
    private static readonly List<string> invalidData = new List<string>()
    {
        "a",
        "b",
        "z"
    };
    public class When_getting_data : Observes<DatasProvider>
    {
        public Establish context = () =>
        {
            Database.UseMockAdapter(new InMemoryAdapter());
            _db = Database.Open();
            _db.dbo.Folder.Insert(Id: validFolderId, User_Id: userId, Name: validPrefix + "4231142");
            _db.dbo.Folder.Insert(Id: invalidFolderId, User_Id: userId, Name: "4321" + validPrefix + "4231142");
            for (int i = 0; i < validData.Count; ++i)
            {
                _db.dbo.Item.Insert(Id: i, User_Id: userId, Folder_Id: validFolderId, Data: validData[i]);
            }
            for (int i = 0; i < invalidData.Count; ++i)
            {
                _db.dbo.Item.Insert(Id: i + validData.Count, User_Id: userId, Folder_Id: invalidFolderId, Data: invalidData[i]);
            }
            depends.on<dynamic>(_db);
        };
        private Because of = () => result = sut.GetDatas(userId, validPrefix);
        private It should_return_valid_datas_count = () => result.Count.ShouldEqual(validData.Count);
        private It should_return_valid_datas = () => result.ShouldContainOnly(validData);
        private It should_not_return_invalid_datas = () => result.ShouldNotContain(invalidData);
    }
}

但是规格失败，因为函数getdata返回0结果。当我调试代码时，我发现它为用户找到所有项目，但是当我想获得与属于用户的项目相关的所有文件夹名称时，我得到一个具有有效数量结果的列表，但每个结果内部都有空值。我还需要做些什么来让测试正常工作吗?

<标题>编辑

我也尝试在db适配器中指定master/detail，但它什么也不做。

var adapter = new InMemoryAdapter();
adapter.Join.Master("Folder", "Id").Detail("Item", "Folder_Id");
Database.UseMockAdapter(adapter);

Answer 1

我解决了我的问题，通过改变代码的一部分来收集数据:

dynamic folder;
return _db.Item.FindAllByUser_Id(userId)
    .Join(_db.Folder, out folder)
    .On(_db.Item.Folder_Id == folder.Id)
    .With(folder)
    .Where(folder.Name.Like(prefix))
    .Select(_db.Item.Data)
    .ToScalarList<string>();

，并通过在测试

中指定具有关系的适配器

var adapter = new InMemoryAdapter();
adapter.Join.Master("Folder", "Id").Detail("Item", "Folder_Id");
Database.UseMockAdapter(adapter);
_db = Database.Open();