public void RefIn()
{
var addBooksWithImage = DataMigrationHelper.AddBooksWithImage(250, 2, DbAccess);
foreach (var id in addBooksWithImage)
{
var countOfImages = -1;
if (DbAccess.DbAccessType.HasFlag(DbAccessType.MsSql))
{
countOfImages =
DbAccess.RunSelect <int>(
DbAccess.Database.CreateCommand(
$"SELECT COUNT(1) FROM {ImageMeta.TableName} WHERE {ImageMeta.TableName}.{ImageMeta.ForgeinKeyName} = {id}"))[0];
}
if (DbAccess.DbAccessType.HasFlag(DbAccessType.SqLite))
{
countOfImages =
(int)
DbAccess.RunSelect <long>(
DbAccess.Database.CreateCommand($"SELECT COUNT(1) FROM {ImageMeta.TableName} WHERE {ImageMeta.TableName}.{ImageMeta.ForgeinKeyName} = {id}"))[0];
}
Assert.That(countOfImages, Is.EqualTo(2));
var deSelect =
DbAccess.RunSelect <Image>(
DbAccess.Database.CreateCommand($"{ImageMeta.SelectStatement} AS b WHERE b.{ImageMeta.ForgeinKeyName} = {id}"));
Assert.That(deSelect, Is.Not.Empty);
Assert.That(deSelect.Length, Is.EqualTo(countOfImages));
var book = DbAccess.SelectSingle <Book>(id);
var forResult = CreateQuery().Select.Table <Image>().In(book).ExecutionMode(_asyncEnumeration).ToArray();
Assert.That(forResult, Is.Not.Empty);
Assert.That(forResult.Count, Is.EqualTo(countOfImages));
}
}
public void JoinChilds()
{
var addBooksWithImage = DataMigrationHelper.AddBooksWithImage(250, 10, DbAccess);
var books = Measure(() => CreateQuery()
.Select.Table <BookWithFkImages>()
.Join(nameof(BookWithFkImages.Images))
.ToArray());
Assert.That(books, Is.Not.Null);
for (var index = 0; index < books.Length; index++)
{
var bookWithFkImagese = books[index];
var addBook = addBooksWithImage[index];
Assert.That(bookWithFkImagese.BookId, Is.EqualTo(addBook));
Assert.That(bookWithFkImagese.Images, Is.Not.Null);
Assert.That(bookWithFkImagese.Images.Count, Is.EqualTo(10));
var bookOnId = DbAccess.SelectSingle <Book>(addBook);
Assert.That(bookWithFkImagese.Text, Is.EqualTo(bookOnId.Text));
var imagesOfThatBook = DbAccess.Query().Select.Table <Image>()
.Where
.Column(f => f.IdBook).Is.EqualsTo(addBook).ToArray();
foreach (var imageWithFkBook in bookWithFkImagese.Images)
{
var img = imagesOfThatBook.FirstOrDefault(f => f.ImageId == imageWithFkBook.ImageId);
Assert.That(imageWithFkBook, Is.Not.Null);
Assert.That(imageWithFkBook.Text, Is.EqualTo(img.Text));
}
}
}
public void JoinChildAndThenParentAsString()
{
DataMigrationHelper.AddBooksWithImage(250, 10, DbAccess);
var books = Measure(() => CreateQuery()
.Select.Table <BookWithFkImages>()
.Join("Images.Type.Book")
.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);
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 PagerWithReferencesCall()
{
var testUsers = DataMigrationHelper.AddBooksWithImage(250, 10, DbAccess);
var refSelect =
int.Parse(DbAccess.Database.Run(
s => s.GetSkalar(string.Format("SELECT COUNT(*) FROM {0}", BookMeta.TableName))).ToString());
Assert.That(testUsers.Length, Is.EqualTo(refSelect));
var pageSize = 25;
var currentPage = 1;
var countElements = DbAccess.Query().Select.Table <BookWithFkImages>().CountInt().First();
var maxPage = countElements / pageSize;
var bookWithFkImageses = DbAccess.Query()
.Select.AsPagedTable <BookWithFkImages>(currentPage, pageSize, f => f.By(e => e.BookId))
.Join(e => e.Images)
.ToArray();
Assert.That(maxPage, Is.EqualTo(Math.Ceiling((int)refSelect / 25D)));
Assert.That(bookWithFkImageses.Length, Is.EqualTo(pageSize));
foreach (var bookWithFkImagese in bookWithFkImageses)
{
Assert.That(bookWithFkImagese.Images.Count, Is.EqualTo(10));
}
}
public void SelectBooksWithXmlImages()
{
DataMigrationHelper.AddBooksWithImage(5, 10, DbAccess);
var bookXmls = DbAccess.Select <BookXml>();
Assert.That(bookXmls, Is.Not.Null);
Assert.That(bookXmls.Length, Is.EqualTo(5));
}
public void ForginKeyTest()
{
var addBooksWithImage = DataMigrationHelper.AddBooksWithImage(1, 20, DbAccess);
var array = CreateQuery().Select.Table <Image>().Where.ForginKey <Book>().Is.Not.Null.ExecutionMode(_asyncEnumeration).ToArray();
Assert.That(array, Is.Not.Empty);
Assert.That(array[0].IdBook, Is.EqualTo(addBooksWithImage[0]));
}
public void JoinParent()
{
DataMigrationHelper.AddBooksWithImage(250, 10, DbAccess);
var books = Measure(() => CreateQuery()
.Select.Table <ImageWithFkBooks>()
.Join(nameof(ImageWithFkBooks.Book))
.ToArray());
foreach (var imageWithFkBookse in books)
{
Assert.That(imageWithFkBookse.Book, Is.Not.Null);
Assert.That(imageWithFkBookse.Book.Text, Is.Not.Null);
Assert.That(imageWithFkBookse.Book.BookId, Is.Not.Zero);
}
Assert.That(books, Is.Not.Null);
}
public void AddBooksWithImage()
{
Assert.That(() => DataMigrationHelper.ClearDb(DbAccess), Throws.Nothing);
Assert.That(() => DbAccess.Database.Run(s => s.GetSkalar("SELECT COUNT(1) FROM " + BookMeta.TableName)),
Is.Zero);
Assert.That(() => DbAccess.Database.Run(s => s.GetSkalar("SELECT COUNT(1) FROM " + ImageMeta.TableName)),
Is.Zero);
Assert.That(DataMigrationHelper.AddBooksWithImage(200, 5, DbAccess), Is.Not.Empty.And.Unique);
Assert.That(() => DbAccess.Database.Run(s => s.GetSkalar("SELECT COUNT(1) FROM " + BookMeta.TableName)),
Is.EqualTo(200));
Assert.That(() => DbAccess.Database.Run(s => s.GetSkalar("SELECT COUNT(1) FROM " + ImageMeta.TableName)),
Is.EqualTo(200 * 5));
Assert.That(DataMigrationHelper.AddBooksWithImage(200, 5, DbAccess), Is.Not.Empty.And.Unique);
Assert.That(() => DbAccess.Database.Run(s => s.GetSkalar("SELECT COUNT(1) FROM " + BookMeta.TableName)),
Is.EqualTo(400));
Assert.That(() => DbAccess.Database.Run(s => s.GetSkalar("SELECT COUNT(1) FROM " + ImageMeta.TableName)),
Is.EqualTo(400 * 5));
}
public void SelectFkRelation()
{
var addBooks = DataMigrationHelper.AddBooksWithImage(10, 2, DbAccess);
foreach (var bookId in addBooks)
{
var images = CreateQuery().Select.Table <Image>()
.Where
.ForginKey <Book>().Is.EqualsTo(bookId)
.ToArray();
Assert.That(images, Is.Not.Null);
Assert.That(images.Length, Is.EqualTo(2));
foreach (var image in images)
{
Assert.That(image.IdBook, Is.EqualTo(bookId));
}
}
}
public void JoinInCte()
{
DataMigrationHelper.AddBooksWithImage(250, 10, DbAccess);
var books = Measure(() => CreateQuery()
.WithCte(e => e
.Select
.Table <ImageWithFkBooks>()
.Join(f => f.Book),
out var cteId)
.Select
.Identifier <ImageWithFkBooks>(cteId)
.ToArray());
foreach (var imageWithFkBookse in books)
{
Assert.That(imageWithFkBookse.Book, Is.Not.Null);
Assert.That(imageWithFkBookse.Book.Text, Is.Not.Null);
Assert.That(imageWithFkBookse.Book.BookId, Is.EqualTo(imageWithFkBookse.IdBook));
}
Assert.That(books, Is.Not.Null);
}
public void RefIn()
{
var addBooksWithImage = DataMigrationHelper.AddBooksWithImage(250, 2, DbAccess);
foreach (var id in addBooksWithImage)
{
var countOfImages = -1;
if (DbAccess.DbAccessType == DbAccessType.MsSql)
{
countOfImages =
DbAccess.RunPrimetivSelect <int>(
string.Format("SELECT COUNT(1) FROM {0} WHERE {0}.{1} = {2}",
ImageMeta.TableName, ImageMeta.ForgeinKeyName,
id))[0];
}
if (DbAccess.DbAccessType == DbAccessType.SqLite)
{
countOfImages =
(int)
DbAccess.RunPrimetivSelect <long>(
string.Format("SELECT COUNT(1) FROM {0} WHERE {0}.{1} = {2}",
ImageMeta.TableName, ImageMeta.ForgeinKeyName,
id))[0];
}
Assert.That(countOfImages, Is.EqualTo(2));
var deSelect =
DbAccess.SelectNative <Image>(string.Format("{2} AS b WHERE b.{0} = {1}",
ImageMeta.ForgeinKeyName, id, ImageMeta.SelectStatement));
Assert.That(deSelect, Is.Not.Empty);
Assert.That(deSelect.Length, Is.EqualTo(countOfImages));
var book = DbAccess.Select <Book>(id);
var forResult = CreateQuery().Select.Table <Image>().In(book).ForResult(_asyncEnumeration).ToArray();
Assert.That(forResult, Is.Not.Empty);
Assert.That(forResult.Count, Is.EqualTo(countOfImages));
}
}
public void JoinParentAndThenChild()
{
DataMigrationHelper.AddBooksWithImage(250, 10, DbAccess);
var images = Measure(() => CreateQuery()
.Select.Table <ImageWithFkBooks>()
.Join(f => f.Book.Images)
.ToArray());
Assert.That(images, Is.Not.Null);
var allBooks = DbAccess.Select <Book>();
var allImages = DbAccess.Select <Image>();
foreach (var imageWithFkBookse in images)
{
Assert.That(imageWithFkBookse.Text, Is.Not.Null);
Assert.That(imageWithFkBookse.Book, Is.Not.Null);
var book = allBooks.FirstOrDefault(e => e.BookId == imageWithFkBookse.IdBook);
Assert.That(imageWithFkBookse.Book.Text, Is.EqualTo(book.Text));
Assert.That(imageWithFkBookse.Book.Images, Is.Not.Null);
Assert.That(imageWithFkBookse.Book.Images, Is.Not.Empty);
var imgs = allImages.Where(f => f.IdBook == imageWithFkBookse.IdBook);
foreach (var image in imageWithFkBookse.Book.Images)
{
var img = imgs.FirstOrDefault(e => e.ImageId == image.ImageId);
Assert.That(image.Book, Is.Null);
Assert.That(image.Text, Is.Not.Null);
Assert.That(image.IdBook, Is.EqualTo(imageWithFkBookse.Book.BookId));
Assert.That(img, Is.Not.Null);
Assert.That(img.Text, Is.EqualTo(image.Text));
}
}
}