Unit test doesn't return result for joined tables

Question

I encourage a problem when writing a unit tests which checks if function returns valid results while joining two tables. I have the following scenario: I have 2 tables, named they Folder and Item, Folder can has multiple items. Theirs DDL looks like this:

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

And I want to gather all 'data' from items which belongs to folders which names starts with some prefix. So I write following function which works fine and returns valid results.

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

And I write a unit test to check if the function works fine, my spec looks like this:

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

But the specs failes because function GetDatas return 0 results. When I debbug code I find out that It finds all Items for a user, but when I want to get all folder names associated with this items which belongs to a user I get a list with a valid number of results, but with a null values inside for each result.. Is there something I should do extra to make the tests works like it should?

Edit

I also try to specify master/detail in db adapter, but it does nothing..

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

Answer 1

I fix my problem, by changing a portion of code to gather data to this:

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>();

and via specifing an adapter with relationship in a test

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