public void JoinChildAndThenParentAndThenChilds()
{
DataMigrationHelper.AddBooksWithImageAndUser(250, 10, DbAccess);
var books = Measure(() => CreateQuery()
.Select.Table <BookWithFkImages>()
.Join(f => f.User1)
.Join(f => f.Images.Type.Book.Images.Type.Book)
.Join(f => f.User)
.ToArray());
Assert.That(books, Is.Not.Null);
var allBooks = DbAccess.Select <Book>();
var allImages = DbAccess.Select <Image>();
foreach (var book in books)
{
Assert.That(book, Is.Not.Null);
Assert.That(book.Text, Is.Not.Null);
Assert.That(book.Images, Is.Not.Null);
Assert.That(book.Images, Is.Not.Empty);
Assert.That(book.IdUser, Is.EqualTo(book.User.User_ID));
var imgsOfBook = allImages.Where(f => f.IdBook == book.BookId);
foreach (var image in imgsOfBook)
{
var imageInBook = book.Images.FirstOrDefault(f => f.ImageId == image.ImageId);
Assert.That(imageInBook, Is.Not.Null);
Assert.That(imageInBook.Text, Is.EqualTo(image.Text));
}
}
}
public void JoinMultipleOnCteParent()
{
DataMigrationHelper.AddBooksWithImageAndUser(250, 10, DbAccess);
var books = Measure(() => CreateQuery()
.WithCte(e => e
.Select.Table <BookWithFkImages>(),
out var cteAlias)
.Select
.Identifier <BookWithFkImages>(cteAlias)
.Join(nameof(BookWithFkImages.User))
.Join(nameof(BookWithFkImages.User1))
.ToArray());
foreach (var book in books)
{
Assert.That(book.User, Is.Not.Null);
Assert.That(book.User.UserName, Is.Not.Null);
Assert.That(book.User.User_ID, Is.Not.Zero);
Assert.That(book.User1, Is.Not.Null);
Assert.That(book.User1.UserName, Is.Not.Null);
Assert.That(book.User1.User_ID, Is.Not.Zero);
}
Assert.That(books, Is.Not.Null);
}
public void JoinParentsMultiple()
{
DataMigrationHelper.AddBooksWithImageAndUser(250, 10, DbAccess);
var books = Measure(() => CreateQuery()
.Select.Table <ImageWithFkBooks>()
.Join(f => f.Book.User1)
.Join(f => f.Book.User)
.ToArray());
Assert.That(books, Is.Not.Null);
foreach (var imageWithFkBookse in books)
{
Assert.That(imageWithFkBookse.Book, Is.Not.Null);
Assert.That(imageWithFkBookse.Book.User, Is.Not.Null);
Assert.That(imageWithFkBookse.Book.User1, Is.Not.Null);
Assert.That(imageWithFkBookse.Book.User.User_ID, Is.EqualTo(imageWithFkBookse.Book.User1.User_ID));
}
}
public void JoinChildWithAdditionalCondition()
{
DataMigrationHelper.AddBooksWithImageAndUser(250, 10, DbAccess);
var books = Measure(() =>
{
var bookWithFkImageses = CreateQuery()
.Select.Table <BookWithFkImages>()
.Join(e => e.Images.Type.Book, JoinMode.Left)
.Join(f => f.User1, null, e => e.And.Column(f => f.Text).Is.Not.Null);
var compiled = bookWithFkImageses.ContainerObject.Compile(out _);
var debugger =
new QueryDebugger(
DbAccess.Database.CreateCommandWithParameterValues(compiled.Query,
compiled.Parameters.ToArray()), DbAccess.Database);
Console.WriteLine(debugger);
return(bookWithFkImageses
.ToArray());
});
}