public void TestLinqBasics()
{
Startup.BooksApp.LogTestStart();
var app = Startup.BooksApp;
IDbCommand lastCmd;
//Init
var session = app.OpenSession();
var books = session.EntitySet <IBook>();
var authors = session.EntitySet <IAuthor>();
var users = session.EntitySet <IUser>();
var pubs = session.EntitySet <IPublisher>();
var csTitle = "c# Programming";
var vbTitle = "VB Programming";
// new + new
var idTitles = books.Select(b => new { Id1 = b.Id, Title1 = b.Title })
.Select(b => new { Id2 = b.Id1, Title2 = b.Title1 }).ToList();
Assert.IsTrue(idTitles.Count > 0, "Expected Id-Title pairs");
// book by title
var qBooksByTitle = from b in books
where b.Title == csTitle
select b;
var lstBooksByTitle = qBooksByTitle.ToList();
Assert.IsTrue(lstBooksByTitle.Count > 0, "Books by title failed.");
// Books by publisher's name; skip, take test
var msbooks = from b in books
where b.Publisher.Name == "MS Books"
orderby b.Title.ToUpper() //ToUpper for Oracle, case-sensitive
select b;
var msBookList = msbooks.ToList();
Assert.AreEqual(3, msBookList.Count, "Invalid number of MS books.");
var msbook0 = msBookList[0]; //c# book should be the first book when sorted by title
Assert.AreEqual("c# Programming", msbook0.Title, "Invalid title of c# book.");
//Same with skip, take
var qSkipTake = msbooks.Skip(1).Take(1);
var lstSkipTake = qSkipTake.ToList();
Assert.AreEqual(1, lstSkipTake.Count, "Invalid # of books in skip/take.");
// Just some sanity check, ran into strange behavior here with session.LastCommand
lastCmd = session.GetLastCommand();
Assert.IsTrue(lastCmd.CommandText.Contains("'MS Books'"), "Investigate: LastCommand contains wrong command: " + lastCmd.CommandText);
// Test bitwise op in SQL. Finding books by editions - we use bit-wise operation on book.Editions property - which is a flagset enum
session = app.OpenSession();
books = session.EntitySet <IBook>();
var eBooks = from b in books
where (b.Editions & BookEdition.EBook) != 0
select b;
var eBooksList = eBooks.ToList();
Assert.IsTrue(eBooksList.Count == 1, "Invalid number of e-books");
// Using == for entity objects in queries
var progCat = BookCategory.Programming;
var msPub = session.EntitySet <IPublisher>().Single(p => p.Name == "MS Books");
var msbooks2 = from b in books
where b.Publisher == msPub && b.Category == progCat
orderby b.Title
select b;
msBookList = msbooks2.ToList();
Assert.IsTrue(msBookList.Count > 0, "Query with entity object == comparison failed.");
//BookAuthors link table
var bookAuthors = session.EntitySet <IBookAuthor>();
var aJack = session.EntitySet <IAuthor>().Where(a => a.FirstName == "Jack").First();
var qBA = from ba in bookAuthors
where ba.Author == aJack && ba.Book.Title == "c# Programming"
select ba;
var lstBAs = qBA.ToList();
Assert.IsTrue(lstBAs.Count > 0, "Failed to find book-author record by author and book title.");
// LINQ with FirstOrDefault() - these are executed slightly differently from queries that return result sets
session = app.OpenSession();
var msp = (from p in session.EntitySet <IPublisher>()
where p.Name == "MS Books"
select p).FirstOrDefault();
Assert.IsTrue(msp != null, "Query with FirstOrDefault failed.");
Assert.AreEqual("MS Books", msp.Name, "Query with FirstOrDefault failed, invalid name.");
//Check that entity is attached to session
var mspRec = EntityHelper.GetRecord(msp);
Assert.IsTrue(mspRec.Session != null, "FirstOrDefault query failed: the result record is not attached to session.");
Assert.IsTrue(msp.Books.Count > 0, "Failed to read publisher's books: entity is not attached to session.");
// Query that returns a derived entity, different from the original 'EntitySet' entity
session = app.OpenSession();
//Return publishers of books in Kids category - we have one kids book, so expect one pub record
var kidPubs = from b in session.EntitySet <IBook>()
where b.Category == BookCategory.Kids
select b.Publisher;
var listPubs = kidPubs.ToList();
Assert.AreEqual(1, listPubs.Count, "Unexpected pub count in special LINQ query.");
// The same but with FirstOrDefault
var firstKidPub = (from b in session.EntitySet <IBook>()
where b.Category == BookCategory.Kids
select b.Publisher).FirstOrDefault();
Assert.IsNotNull(firstKidPub, "First kid Publisher is null in special LINQ query.");
// Test FirstOrDefault with null result - special case for EntityCache.CloneEntity method
session = app.OpenSession();
var none = session.EntitySet <IBook>().Where(b => b.Title == "123").FirstOrDefault();
Assert.IsNull(none, "FirstOrDefault with null result failed.");
// Query returning anonymous type
// Return pairs book-title, price
session = app.OpenSession();
var someId = Guid.Empty;
var qryBooksAnon = from b in session.EntitySet <IBook>()
where b.Category == BookCategory.Programming
select new { Id = b.Id.ToString(), Title = b.Title, Price = b.Price }; //testing how ToString() works with Guids - only in select output!
var listBooksAnon = qryBooksAnon.ToList();
Assert.IsTrue(listBooksAnon.Count > 0, "Failed to retrieve anon type.");
Assert.IsTrue(!string.IsNullOrEmpty(listBooksAnon[0].Id), "ToString query translation failed");
//Same but with parameter in output
var someKey = "someKey"; //some value to include into results - testing how such field initialized from parameter is handled
var qryBooksAnon2 = from b in session.EntitySet <IBook>()
where b.Category == BookCategory.Programming
select new { b.Title, b.Price, Key = someKey };
var listBooksAnon2 = qryBooksAnon2.ToList();
Assert.IsTrue(listBooksAnon2.Count > 0, "Failed to retrieve anon type.");
// Return pairs of (book, publisher)
session = app.OpenSession();
var bpPairs = from b in session.EntitySet <IBook>()
where b.Category == BookCategory.Programming
select new { Book = b, Publisher = b.Publisher };
var bpPairsList = bpPairs.ToList();
Assert.IsTrue(bpPairsList.Count > 0, "Failed to retrieve anon type.");
// Return anon type with pair of entities, one of them is nullable
session = app.OpenSession();
var qAuthorUser = from a in authors
select new { Author = a, User = a.User };
var listAuthorUser = qAuthorUser.ToList();
Assert.IsTrue(listAuthorUser.Count > 0, "Author-User query failed.");
// Author Jim Hacker is not a user, so its User prop should be null
var jimInfo = listAuthorUser.First(au => au.Author.LastName == "Hacker");
Assert.IsTrue(jimInfo.User == null, "User prop is not null for Author Jim Hacker");
// Some odd query returning list of constants
books = session.EntitySet <IBook>();
var numberQuery = from b in books
where b.Publisher.Name == "MS Books"
select 1;
var numbersFromQuery = numberQuery.ToList();
Assert.IsTrue(numbersFromQuery.Count > 0, "Number query did not return rows");
// Join thru nullable reference - it should work even with cache!
var qAuthorsJohn = from a in authors
where a.User.UserName == "John"
select a;
var lstAuthorsJohn = qAuthorsJohn.ToList();
Assert.AreEqual(1, lstAuthorsJohn.Count, "Failed to find author by user name");
// Aggregate methods
session = app.OpenSession();
books = session.EntitySet <IBook>();
var maxPrice = books.Max(b => b.Price);
Assert.IsTrue(maxPrice > 0, "Max price is invalid.");
// Queryable.Average method is special in some way - it is not a generic on result type, but has a number of overloads
// for specific numeric types. This test checks how fwk handles this
var avgPrice = books.Average(b => b.Price);
Assert.IsTrue(avgPrice > 0, "Average price is invalid.");
// Test 'locally evaluatable' pieces in queries
session = app.OpenSession();
pubs = session.EntitySet <IPublisher>();
var msBooksName = "local var value";
_someClassField = "field value";
// NOTE: a bug in PostGres npgsql (version v2.1.3/Sept 2014); in the following query, if we put literal "Literal'string" before
// any expr involving parameter, then provider/postgres fails with error 42703 'Column P0 does not exist'
// But if value with quote is after all parameters, everything works fine! To see it, just uncomment the clause below
var pq = from p in pubs
where
p.Name == msBooksName // param in query
//|| p.Name != "Postgres'fails"
|| p.Name == _someClassField || //param
p.Name == SomeClassProp || //param
p.Name == PublisherNameConst || // const in query, not parameters
p.Name == "Literal'string" || //const (literal)
p.Name != "blah" //const
select p;
var pqList = pq.ToList();
Assert.IsTrue(pqList.Count > 0, "Query with local variables failed.");
//Using extra container object
var qparams = new QueryParamsContainer()
{
Field = csTitle, Prop = vbTitle
};
var qBooksWithParamObj = from b in books
where b.Title == qparams.Field || b.Title == qparams.Prop
select b;
var lstBooksWithParamObj = qBooksWithParamObj.ToList();
Assert.AreEqual(2, lstBooksWithParamObj.Count, "Query with param object failed");
// Test using predicates comparing with null. It is special case - must be translated to 'IS NULL' in SQL
var bq = from b in books
where b.Abstract == null
select b;
var booksWithoutAbstract = bq.ToList();
Assert.IsTrue(booksWithoutAbstract.Count > 0, "Query with predicate checking for null failed.");
// testing entity reference null check (foreign key != null)
var qAuthUsers = from a in authors
where a.User != null
select a;
var lstAuthUsers = qAuthUsers.ToList();
Assert.IsTrue(lstAuthUsers.Count > 0, "Query with check for non null failed.");
// Test using calculations over local values directly into the query. Did not work initially, now fixed.
// The date value should be calculated before running the query and supplied as parameter. Same with price
var priceCutOff = 5;
Func <decimal, decimal> getDiscountedPrice = (decimal p) => p * 0.8m;
var queryWithCalc = session.EntitySet <IBook>().Where(b => b.PublishedOn > DateTime.Now.AddYears(-3) && b.Price > getDiscountedPrice(priceCutOff));
var lstFromCalc = queryWithCalc.ToList();
lastCmd = session.GetLastCommand(); //to check SQL in debugger
Assert.IsTrue(lstFromCalc.Count > 0, "Query with local calc values failed.");
// Test for MySql handling of Guid IDs
// Must use an entity that is not in full entity cache: like IBookOrder, not IBook.
session = app.OpenSession();
var someOrder = session.GetEntities <IBookOrder>(take: 10).First();
var someOrderId = someOrder.Id;
var qGetOrderById = from bo in session.EntitySet <IBookOrder>()
where bo.Id == someOrderId
select bo;
var listOrders = qGetOrderById.ToList();
Assert.AreEqual(1, listOrders.Count, "Failed to get order by Id.");
Assert.AreEqual(someOrderId, listOrders[0].Id, "Failed to get order by Id.");
// Linq query against entity with computed column
// Testing loading entity with computed column; I've encountered trouble with computed columns in new linq engine, so verifying the fix here.
// Important - we should use entity that is NOT in full cache, like IBookOrder (it has computed field Summary).
var orders = session.EntitySet <IBookOrder>();
var qOrders = from ord in orders
where ord.Status == OrderStatus.Completed
select ord;
var lstOrders = qOrders.ToList();
Assert.IsTrue(lstOrders.Count > 0, "Failed to retrieve orders.");
var summ0 = lstOrders[0].Summary;
Assert.IsFalse(string.IsNullOrWhiteSpace(summ0), "Failed to read order summary");
//Test string concatenation
var authFullNames = session.EntitySet <IAuthor>().Select(a => a.LastName + ", " + a.FirstName).ToList();
Assert.IsTrue(authFullNames.Count > 0, "Expected non-empty list.");
Assert.IsTrue(authFullNames.Contains("Sharp, John"), "Expected john sharp in the list.");
//Test string Length methods
var authNameLens = session.EntitySet <IAuthor>().Select(a => a.LastName.Length + a.FirstName.Length).ToList();
Assert.IsTrue(authNameLens.Count > 0, "Expected non-empty list of name lengths.");
lastCmd = session.GetLastCommand();
// decimal linq params
decimal pr = 5.0m;
var expensiveBooks = session.EntitySet <IBook>().Where(b => b.Price > pr).ToList();
lastCmd = session.GetLastCommand();
Assert.IsTrue(expensiveBooks.Count > 0, "Query with decimal param failed.");
}
public void TestLinqBasics()
{
var app = SetupHelper.BooksApp;
SetupHelper.InvalidateCache();
//Init
var session = app.OpenSession();
var books = session.EntitySet<IBook>();
var authors = session.EntitySet<IAuthor>();
var users = session.EntitySet<IUser>();
var pubs = session.EntitySet<IPublisher>();
var csTitle = "c# Programming";
var vbTitle = "VB Programming";
// book by title
var qBooksByTitle = from b in books
where b.Title == csTitle
select b;
var lstBooksByTitle = qBooksByTitle.ToList();
Assert.IsTrue(lstBooksByTitle.Count > 0, "Books by title failed.");
// Books by publisher's name; skip, take test
var msbooks = from b in books
where b.Publisher.Name == "MS Books"
orderby b.Title
select b;
var msBookList = msbooks.ToList();
Assert.AreEqual(3, msBookList.Count, "Invalid number of MS books.");
var msbook0 = msBookList[0]; //c# book should be the first book when sorted by title
Assert.IsTrue(msbook0.Title == "c# Programming", "Invalid title of c# book.");
//Same with skip, take
var qSkipTake = msbooks.Skip(1).Take(1);
var lstSkipTake = qSkipTake.ToList();
Assert.IsTrue(lstSkipTake.Count == 1, "Invalid # of books in skip/take.");
// Test bitwise op in SQL. Finding books by editions - we use bit-wise operation on book.Editions property - which is a flagset enum
session = app.OpenSession();
books = session.EntitySet<IBook>();
var eBooks = from b in books
where (b.Editions & BookEdition.EBook) != 0
select b;
var eBooksList = eBooks.ToList();
Assert.IsTrue(eBooksList.Count == 1, "Invalid number of e-books");
// Using == for entity objects in queries
var progCat = BookCategory.Programming;
var msPub = session.EntitySet<IPublisher>().Single(p => p.Name == "MS Books");
var msbooks2 = from b in books
where b.Publisher == msPub && b.Category == progCat
orderby b.Title
select b;
msBookList = msbooks2.ToList();
Assert.IsTrue(msBookList.Count > 0, "Query with entity object == comparison failed.");
//BookAuthors link table
var bookAuthors = session.EntitySet<IBookAuthor>();
var aJack = session.EntitySet<IAuthor>().Where(a => a.FirstName == "Jack").First();
var qBA = from ba in bookAuthors
where ba.Author == aJack && ba.Book.Title == "c# Programming"
select ba;
var lstBAs = qBA.ToList();
Assert.IsTrue(lstBAs.Count > 0, "Failed to find book-author record by author and book title.");
// LINQ with FirstOrDefault() - these are executed slightly differently from queries that return result sets
session = app.OpenSession();
var msp = (from p in session.EntitySet<IPublisher>()
where p.Name == "MS Books"
select p).FirstOrDefault();
Assert.IsTrue(msp != null, "Query with FirstOrDefault failed.");
Assert.AreEqual("MS Books", msp.Name, "Query with FirstOrDefault failed, invalid name.");
//Check that entity is attached to session
var mspRec = EntityHelper.GetRecord(msp);
Assert.IsTrue(mspRec.Session != null, "FirstOrDefault query failed: the result record is not attached to session.");
Assert.IsTrue(msp.Books.Count > 0, "Failed to read publisher's books: entity is not attached to session.");
// Query that returns a derived entity, different from the original 'EntitySet' entity
session = app.OpenSession();
//Return publishers of books in Kids category - we have one kids book, so expect one pub record
var kidPubs = from b in session.EntitySet<IBook>()
where b.Category == BookCategory.Kids
select b.Publisher;
var listPubs = kidPubs.ToList();
Assert.AreEqual(1, listPubs.Count, "Unexpected pub count in special LINQ query.");
// The same but with FirstOrDefault
var firstKidPub = (from b in session.EntitySet<IBook>()
where b.Category == BookCategory.Kids
select b.Publisher).FirstOrDefault();
Assert.IsNotNull(firstKidPub, "First kid Publisher is null in special LINQ query.");
// Test FirstOrDefault with null result - special case for EntityCache.CloneEntity method
session = app.OpenSession();
var none = session.EntitySet<IBook>().Where(b => b.Title == "123").FirstOrDefault();
Assert.IsNull(none, "FirstOrDefault with null result failed.");
// Query returning anonymous type
// Return pairs book-title, price
session = app.OpenSession();
var someId = Guid.Empty;
var qryBooksAnon = from b in session.EntitySet<IBook>()
where b.Category == BookCategory.Programming
select new { Id = b.Id.ToString(), Title = b.Title, Price = b.Price }; //testing how ToString() works with Guids - only in select output!
var listBooksAnon = qryBooksAnon.ToList();
Assert.IsTrue(listBooksAnon.Count > 0, "Failed to retrieve anon type.");
Assert.IsTrue(!string.IsNullOrEmpty(listBooksAnon[0].Id), "ToString query translation failed");
//Same but with parameter in output
//SQL CE does not allow this (query like: SELECT Title, Price, @P3)
if (SetupHelper.ServerType != DbServerType.SqlCe) {
var someKey = "someKey"; //some value to include into results - testing how such field initialized from parameter is handled
var qryBooksAnon2 = from b in session.EntitySet<IBook>()
where b.Category == BookCategory.Programming
select new { b.Title, b.Price, Key = someKey };
var listBooksAnon2 = qryBooksAnon2.ToList();
Assert.IsTrue(listBooksAnon2.Count > 0, "Failed to retrieve anon type.");
}
// Return pairs of (book, publisher)
session = app.OpenSession();
var bpPairs = from b in session.EntitySet<IBook>()
where b.Category == BookCategory.Programming
select new { Book = b, Publisher = b.Publisher };
var bpPairsList = bpPairs.ToList();
Assert.IsTrue(bpPairsList.Count > 0, "Failed to retrieve anon type.");
// Return anon type with pair of entities, one of them is nullable
session = app.OpenSession();
var qAuthorUser = from a in authors
select new { Author = a, User = a.User };
var listAuthorUser = qAuthorUser.ToList();
Assert.IsTrue(listAuthorUser.Count > 0, "Author-User query failed.");
// Author Jim Hacker is not a user, so its User prop should be null
var jimInfo = listAuthorUser.First(au => au.Author.LastName == "Hacker");
Assert.IsTrue(jimInfo.User == null, "User prop is not null for Author Jim Hacker");
// Some odd query returning list of constants
books = session.EntitySet<IBook>();
var numberQuery = from b in books
where b.Publisher.Name == "MS Books"
select 1;
var numbersFromQuery = numberQuery.ToList();
Assert.IsTrue(numbersFromQuery.Count > 0, "Number query did not return rows");
// Join thru nullable reference - it should work even with cache!
var qAuthorsJohn = from a in authors
where a.User.UserName == "John"
select a;
var lstAuthorsJohn = qAuthorsJohn.ToList();
Assert.AreEqual(1, lstAuthorsJohn.Count, "Failed to find author by user name");
// Aggregate methods
session = app.OpenSession();
books = session.EntitySet<IBook>();
var maxPrice = books.Max(b => b.Price);
Assert.IsTrue(maxPrice > 0, "Max price is invalid.");
// Queryable.Average method is special in some way - it is not a generic on result type, but has a number of overloads
// for specific numeric types. This test checks how fwk handles this
var avgPrice = books.Average(b => b.Price);
Assert.IsTrue(avgPrice > 0, "Average price is invalid.");
// Test 'locally evaluatable' pieces in queries
session = app.OpenSession();
pubs = session.EntitySet<IPublisher>();
var msBooksName = "local var value";
_someClassField = "field value";
// NOTE: a bug in PostGres npgsql (version v2.1.3/Sept 2014); in the following query, if we put literal "Literal'string" before
// any expr involving parameter, then provider/postgres fails with error 42703 'Column P0 does not exist'
// But if value with quote is after all parameters, everything works fine! To see it, just uncomment the clause below
var pq = from p in pubs
where
p.Name == msBooksName // param in query
//|| p.Name != "Postgres'fails"
|| p.Name == _someClassField //param
|| p.Name == SomeClassProp //param
|| p.Name == PublisherNameConst // const in query, not parameters
|| p.Name == "Literal'string" //const (literal)
|| p.Name != "blah" //const
select p;
var pqList = pq.ToList();
Assert.IsTrue(pqList.Count > 0, "Query with local variables failed.");
//Using extra container object
var qparams = new QueryParamsContainer() { Field = csTitle, Prop = vbTitle };
var qBooksWithParamObj = from b in books
where b.Title == qparams.Field || b.Title == qparams.Prop
select b;
var lstBooksWithParamObj = qBooksWithParamObj.ToList();
Assert.AreEqual(2, lstBooksWithParamObj.Count, "Query with param object failed");
// Test using predicates comparing with null. It is special case - must be translated to 'IS NULL' in SQL
var bq = from b in books
where b.Abstract == null
select b;
var booksWithoutAbstract = bq.ToList();
Assert.IsTrue(booksWithoutAbstract.Count > 0, "Query with predicate checking for null failed.");
// testing entity reference null check (foreign key != null)
var qAuthUsers = from a in authors
where a.User != null
select a;
var lstAuthUsers = qAuthUsers.ToList();
Assert.IsTrue(lstAuthUsers.Count > 0, "Query with check for non null failed.");
// Test using calculations over local values directly into the query. Did not work initially, now fixed.
// The date value should be calculated before running the query and supplied as parameter. Same with price
var priceCutOff = 5;
Func<decimal, decimal> getDiscountedPrice = (decimal p) => p * 0.8m;
var queryWithCalc = session.EntitySet<IBook>().Where(b => b.PublishedOn > DateTime.Now.AddYears(-3) && b.Price > getDiscountedPrice(priceCutOff));
var lstFromCalc = queryWithCalc.ToList();
var cmd = session.GetLastCommand(); //to check SQL in debugger
Assert.IsTrue(lstFromCalc.Count > 0, "Query with local calc values failed.");
// Test for MySql handling of Guid IDs
// Must use an entity that is not in full entity cache: like IBookOrder, not IBook.
session = app.OpenSession();
var someOrder = session.GetEntities<IBookOrder>(take: 10).First();
var someOrderId = someOrder.Id;
var qGetOrderById = from bo in session.EntitySet<IBookOrder>()
where bo.Id == someOrderId
select bo;
var listOrders = qGetOrderById.ToList();
Assert.AreEqual(1, listOrders.Count, "Failed to get order by Id.");
Assert.AreEqual(someOrderId, listOrders[0].Id, "Failed to get order by Id.");
// Linq query against entity with computed column
// Testing loading entity with computed column; I've encountered trouble with computed columns in new linq engine, so verifying the fix here.
// Important - we should use entity that is NOT in full cache, like IBookOrder (it has computed field Summary).
var orders = session.EntitySet<IBookOrder>();
var qOrders = from ord in orders
where ord.Status == OrderStatus.Completed
select ord;
var lstOrders = qOrders.ToList();
Assert.IsTrue(lstOrders.Count > 0, "Failed to retrieve orders.");
var summ0 = lstOrders[0].Summary;
Assert.IsFalse(string.IsNullOrWhiteSpace(summ0), "Failed to read order summary");
//Test string concatenation
var authFullNames = session.EntitySet<IAuthor>().Select(a => a.LastName + ", " + a.FirstName).ToList();
Assert.IsTrue(authFullNames.Count > 0, "Expected non-empty list.");
Assert.IsTrue(authFullNames.Contains("Sharp, John"), "Expected john sharp in the list.");
//Test string Length methods
var authNameLens = session.EntitySet<IAuthor>().Select(a => a.LastName.Length + a.FirstName.Length).ToList();
Assert.IsTrue(authNameLens.Count > 0, "Expected non-empty list of name lengths.");
cmd = session.GetLastCommand();
}
