How join metadata works in SQLIte

[pfencer]

Hi.
I am testing your library but I have problems managing joins.
I defined a simple model Author-Book from SQLite database, as follows

[Table("Authors")]
public class Author
{
[Key, Identity]
public int AuthorId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
[LeftJoin("Books", "AuthorId", "AuthorId")]
public List Books { get; set; }
}

[Table("Books")]
public class Book
{
[Key, Identity]
public int BookId { get; set; }
public string Title { get; set; }
public int AuthorId { get; set; }
[LeftJoin("Authors", "AuthorId", "AuthorId")]
public Author Author { get; set; }
}

Then I defined the following repository

public class AuthorRepository : DapperRepository
{
public AuthorRepository(IDbConnection connection, ISqlGenerator sqlGenerator)
: base(connection, sqlGenerator)
{

 }

}

and I wrote this code to get author with all related books:

private void GetAllAuthors(string conn)
{
SQLiteConnection sqlLiteConnection = new SQLiteConnection(conn);

SqlGenerator<Author> sqlGenAuthorModel = new SqlGenerator<Author>();
AuthorRepository authorRepo = new AuthorRepository(sqlLiteConnection, sqlGenAuthorModel);
var authorEnum = authorRepo.FindAll();

}

At this point when I get query result, the authorEnum variable contains author but the Book list is null.

What am I doing wrong?

Thanks a lot for your support.
Best regards.

[phnx47]

var authorEnum = authorRepo.FindAll(null, x => x.Books));

Look at BaseRepositoriesTests.cs

[pfencer]

Thanks for your quick reply...
It works !!! (there is just an additional close parenthesis at the end)

But now I have another question:
as first example in your description you stated

Find by ID:
var user = await userRepository.FindAsync(x => x.Id == 5);

I tried a similar xode for my DB with

var author = authorRepo.Find(x => x.AuthorId == 2);

and I got the following exception

System.Data.SQLite.SQLiteException
HResult=0x87AF001F
Message=SQL logic error near "1": syntax error
Source=System.Data.SQLite

while using

var author = authorRepo.FindById(2, x => x.Books);

I got the required row from table.

Am I missing any reference to make working the first syntax?

Thanks again for your patience and support. I really appreciate it.
Best regards.

[phnx47]

Can you post here the generated SQL query?

[pfencer]

Sorry, but I don't know how to get it...

[phnx47]

Look at BaseRepositoriesTests.cs. These tests are run with SQLite too. Play with unit tests, put breakpoints and look at generated SQL query.

dapper-repositories/tests/Repositories.Base/BaseRepositoriesTests.cs

Lines 77 to 83 in 78fce0d

	[Fact]
	public void Find()
	{
	var user = Db.Users.Find(x => x.Id == 2);
	Assert.Null(user.Deleted);
	Assert.Equal("TestName1", user.Name);
	}