internal void RunAll()
{
ExampleRunner.RunQuery(
"Where() called without query command. db.Albums.Where(db.Albums.GenreId == 1)",
db => db.Albums.Where(db.Albums.GenreId == 1));
ExampleRunner.RunQuery(
"Where called without no parameters. db.Albums.All().Where()",
db => db.Albums.All().Where());
ExampleRunner.RunQuery(
"Where called with invalid SimpleExpression as parameter. FIeld name wrong",
db => db.Albums.All().Where(db.Albums.NonExistentColumn == 1));
ExampleRunner.RunQuery(
"Where called with two valid SimpleExpressions as parameters.",
db => db.Albums.All().Where(db.Albums.GenreId == 1, db.Albums.ArtistId == 120));
ExampleRunner.RunQuery(
"db.Albums.All().Where(db.Albums.GenreId == 1). Where clause added",
db => db.Albums.All().Where(db.Albums.GenreId == 1));
ExampleRunner.RunQuery(
"db.Albums.All().Where(db.Albums.GenreId == 1).Where(db.Albums.ArtistId == 120). Where clauses added and concatenated with AND",
db => db.Albums.All().Where(db.Albums.GenreId == 1).Where(db.Albums.ArtistId == 120));
ExampleRunner.RunQuery(
"db.Albums.FindAllByGenreId(1).Where(db.Albums.ArtistId == 120). Where clause concatenated with AND",
db => db.Albums.FindAllByGenreId(1).Where(db.Albums.ArtistId == 120));
ExampleRunner.RunQuery(
"Concatenate two SimpleExpressions using OR",
db => OrTwoExpressions(db));
ExampleRunner.RunQuery(
"Concatenate two ORed SimpleExpressions with a third. Use Where to concatenate",
db =>
db.Albums.FindAll(db.Albums.GenreId == 1 || db.Albums.GenreId == 2).Where(db.Albums.ArtistId == 120));
ExampleRunner.RunQuery(
"Concatenate two ORed SimpleExpressions with a third. Build SimpleExpression first",
db => OrAndThreeExpressions(db));
ExampleRunner.RunQuery(
"db.Albums.All().Where(db.Albums.Title == \"Dark Side of the Moon\"); String Comparison",
db => db.Albums.All().Where(db.Albums.Title == "Dark Side Of The Moon"));
ExampleRunner.RunQuery(
"Compare string field to production of StringBuilder. db.Albums.Title == sb.ToString()",
db => CompareStringBuilderProduction(db));
}
internal void RunAll()
{
ExampleRunner.RunQuery(
"Implicit Inner Join between two tables: albums and genre",
db => db.Albums.FindAllByGenreId(1)
.Select(
db.Albums.Title,
db.Albums.Genre.Name),
new List <string> {
"Title", "Name"
});
ExampleRunner.RunQuery(
"Implicit Inner Join between three tables: orderDetails, albums and genre",
db => db.OrderDetails.FindAllByOrderId(1)
.Select(
db.OrderDetails.OrderId,
db.OrderDetails.Albums.Title,
db.OrderDetails.Albums.Genre.Name),
new List <string> {
"Title", "Name"
});
ExampleRunner.RunQuery(
"Implicit 1:n Join between two tables: genre and albums",
db => db.Genre.FindAllByGenreId(1)
.Select(
db.Genre.Name,
db.Genre.Albums.Title),
new List <string> {
"Name", "Title"
});
ExampleRunner.RunQuery(
"Implicit m:n Join between two tables: order and albums",
db => db.Orders.FindAllByOrderId(4)
.Select(
db.Orders.OrderId,
db.Orders.OrderDetails.Albums.Title),
new List <string> {
"OrderId", "Title"
});
}
internal void RunAll()
{
ExampleRunner.RunQuery(
"Run db.Albums.Exists - returns true",
db => db.Albums.Exists());
ExampleRunner.RunQuery(
"Run db.Albums.Exists.Exists - returns true",
db => db.Albums.Exists().Exists());
ExampleRunner.RunQuery(
"Run db.AlbumCovers.Exists table exists. Throws UnresolvableObjectException",
db => db.AlbumCovers.Exists());
ExampleRunner.RunQuery(
"Run Exists(db.Albums.GenreId) - just the column name. Throws BadExpressionException",
db => db.Albums.Exists(db.Albums.GenreId));
ExampleRunner.RunQuery(
"Run Exists(1) - no column names. Throws BadExpressionException",
db => db.Albums.Exists(1));
ExampleRunner.RunQuery(
"Run Exists(1) - no column names. Throws BadExpressionException",
db => db.Albums.Exists(true));
ExampleRunner.RunQuery(
"Run Exists(two expressions). Throws BadExpressionException",
db => db.Albums.Exists(db.Albums.GenreId == 1, db.Albums.AlbumId == 1));
ExampleRunner.RunQuery(
"Albums.Exists(db.Albums.GenreId == \"a\"). Malformed Simple Expression. Throws FormatException",
db => db.Albums.Exists(db.Albums.GenreId == "a"));
ExampleRunner.RunQuery(
"Albums.Exists(db.Albums.GenreId != null).",
db => db.Albums.Exists(db.Albums.GenreId != null));
ExampleRunner.RunQuery(
"Albums.Exists(db.Albums.GenreId == 1)",
db => db.Albums.Exists(db.Albums.GenreId == 1));
ExampleRunner.RunQuery(
"Albums.Exists(db.Albums.GenreId > 3)",
db => db.Albums.Exists(db.Albums.GenreId > 3));
ExampleRunner.RunQuery(
"Albums.Exists(db.Albums.GenreId == 1 && db.Albums.ArtistId == 120)",
db => db.Albums.Exists(db.Albums.GenreId == 1 && db.Albums.ArtistId == 120));
ExampleRunner.RunQuery(
"Albums.Exists(db.Albums.GenreId == 1 & db.Albums.ArtistId == 120)",
db => db.Albums.Exists(db.Albums.GenreId == 1 & db.Albums.ArtistId == 120));
ExampleRunner.RunQuery(
"Albums.Exists(db.Albums.GenreId == 2 || db.Albums.ArtistId == 160)",
db => db.Albums.Exists(db.Albums.GenreId == 2 || db.Albums.ArtistId == 160));
ExampleRunner.RunQuery(
"Albums.Exists(db.Albums.GenreId == 2 | db.Albums.ArtistId == 160)",
db => db.Albums.Exists(db.Albums.GenreId == 2 | db.Albums.ArtistId == 160));
}
internal void RunAll()
{
// Throws NullReferenceException
ExampleRunner.RunQuery(
"Run OrderByDescending() without base command or column name.",
db => db.Albums.OrderByDescending());
// Runs All.OrderByArtistId()
ExampleRunner.RunQuery(
"Run OrderByArtistIdDescending() without base command.",
db => db.Albums.OrderByArtistIdDescending());
// Throws NullReferenceException
ExampleRunner.RunQuery(
"Run OrderByDescending(db.Albums.ArtistId) without base command.",
db => db.Albums.OrderByDescending(db.Albums.ArtistId));
// Throws NullReferenceException
ExampleRunner.RunQuery(
"Run All.OrderByDescending() without column name.",
db => db.Albums.All().OrderByDescending());
// Runs All.OrderByArtistId()
ExampleRunner.RunQuery(
"Run All.OrderByArtistIdDescending().",
db => db.Albums.All().OrderByArtistIdDescending());
// Runs All.OrderByArtistId()
ExampleRunner.RunQuery(
"Run All.OrderByDescending(db.Albums.ArtistId).",
db => db.Albums.All().OrderByDescending(db.Albums.ArtistId));
// Throws NullReferenceException
ExampleRunner.RunQuery(
"Run All.OrderByDescending(1) - no column names.",
db => db.Albums.All().OrderByDescending(1));
// Runs All.OrderByGenreId()
ExampleRunner.RunQuery(
"Run All.OrderByGenreIdDescending(1).",
db => db.Albums.All().OrderByGenreIdDescending(1));
// Runs All.OrderByGenreId()
ExampleRunner.RunQuery(
"Run All.OrderByGenreIdDescending(db.Albums.Title).",
db => db.Albums.All().OrderByGenreIdDescending(db.Albums.Title));
// Throws Ado.AdoAdapterException
ExampleRunner.RunQuery(
"Run All.OrderByDescending(db.Artists.Name) - column not related to query.",
db => db.Albums.All().OrderByDescending(db.Artists.Name));
// Throws NullReferenceException
ExampleRunner.RunQuery(
"Run All.OrderByDescending(db.Albums.GenreId == 1) - erroneous SimpleExpression.",
db => db.Albums.All().OrderByDescending(db.Albums.GenreId == 1));
// Throws UnresolvableObjectException - Column not found
ExampleRunner.RunQuery(
"Run All.OrderByArtistIdAndTitleDescending() - should use ThenBy() for second column.",
db => db.Albums.All().OrderByArtistIdAndTitleDescending());
// Throws NullReferenceException
ExampleRunner.RunQuery(
"Run All.OrderByDescending(db.Albums.ArtistId, db.Albums.Title) - should use ThenBy() for second column.",
db => db.Albums.All().OrderByDescending(db.Albums.ArtistId, db.Albums.Title));
// Runs All.OrderByDescending(ArtistId, Title)
ExampleRunner.RunQuery(
"Run All.OrderByDescending(db.Albums.ArtistId).OrderByDescending(db.Albums.Title) - should use ThenBy() for second column.",
db => db.Albums.All().OrderByDescending(db.Albums.ArtistId).OrderByDescending(db.Albums.Title));
// Joins and aliases and out params
// Runs All.OrderByDescending(Title)
ExampleRunner.RunQuery(
"Run All.OrderByDescending on explicit LeftJoin ordering on primary table field - named parameter",
db => db.Albums.All()
.Select(
db.Albums.Title,
db.Genre.Name)
.LeftJoin(db.Genre).On(db.Genre.GenreId == db.Albums.GenreId)
.OrderByDescending(db.Albums.Title),
new List <string> {
"Title", "Name"
});
// Runs All.OrderByDescending(Title)
ExampleRunner.RunQuery(
"Run All.OrderByDescending on explicit LeftJoin ordering on primary table field - fluid style",
db => db.Albums.All()
.Select(
db.Albums.Title,
db.Genre.Name)
.LeftJoin(db.Genre).On(db.Genre.GenreId == db.Albums.GenreId)
.OrderByTitleDescending(),
new List <string> {
"Title", "Name"
});
// Runs All.OrderByDescending(Name)
ExampleRunner.RunQuery(
"Run OrderByDescending on explicit LeftJoin ordering on secondary table field - named parameter",
db => db.Albums.All()
.Select(
db.Albums.Title,
db.Genre.Name)
.LeftJoin(db.Genre).On(db.Genre.GenreId == db.Albums.GenreId)
.OrderByDescending(db.Genre.Name),
new List <string> {
"Title", "Name"
});
// Throws UnresolvableObjectException
ExampleRunner.RunQuery(
"Run OrderByDescending on explicit LeftJoin ordering on secondary table field - fluid style",
db => db.Albums.All()
.Select(
db.Albums.Title,
db.Genre.Name)
.LeftJoin(db.Genre).On(db.Genre.GenreId == db.Albums.GenreId)
.OrderByNameDescending(),
new List <string> {
"Title", "Name"
});
// Runs All.OrderByDescending(Name)
ExampleRunner.RunQuery(
"Run OrderByDescending on explicit LeftJoin ordering on aliased column expression - named parameter",
db => db.Albums.All()
.Select(
db.Albums.Title,
db.Genre.Name.As("GenreName"))
.LeftJoin(db.Genre).On(db.Genre.GenreId == db.Albums.GenreId)
.OrderByDescending(db.Genre.Name),
new List <string> {
"Title", "GenreName"
});
// Runs All.OrderByGenreName
ExampleRunner.RunQuery(
"Run OrderByDescending on explicit LeftJoin ordering on aliased column expression - fluid style",
db => db.Albums.All()
.Select(
db.Albums.Title,
db.Genre.Name.As("GenreName"))
.LeftJoin(db.Genre).On(db.Genre.GenreId == db.Albums.GenreId)
.OrderByGenreNameDescending(),
new List <string> {
"Title", "GenreName"
});
// Runs All.OrderByDescending(g.Name)
ExampleRunner.RunQuery(
"Run OrderByDescending on explicit LeftJoin ordering on aliased table expression - named parameter",
db => TwoTableLeftJoinWithAliasUsingExpressionsNamed(db),
new List <string> {
"Title", "Name"
});
// Throws UnresolvableObjectException
ExampleRunner.RunQuery(
"Run OrderByDescending on explicit LeftJoin ordering on aliased table expression - fluid style",
db => TwoTableLeftJoinWithAliasUsingExpressionsFluid(db),
new List <string> {
"Title", "Name"
});
}
internal void RunAll()
{
// ToScalarList
ExampleRunner.RunQuery(
"Get a single album. Return null. Cast with ToScalarList(). Throws RuntimeBinderException",
db => db.Albums.Get(1000).ToScalarList(),
new List <string>(), "string");
ExampleRunner.RunQuery(
"Get a single album. Cast with ToScalarList(). Throws UnresolvableObjectException",
db => db.Albums.Get(1).ToScalarList(),
new List <string>(), "string");
ExampleRunner.RunQuery(
"Get a single album title. Cast with ToScalarList<string>(). Throws UnresolvableObjectException",
db => db.Albums.Select(db.Albums.Title).Get(1).ToScalarList <string>(),
new List <string>(), "string");
ExampleRunner.RunQuery(
"Get a list of albums. Return null. Cast with ToScalarList(). Returns empty List<dynamic>",
db => db.Albums.FindAllByGenreId(100).ToScalarList(),
new List <string> {
"Title"
}, "DynamicList");
ExampleRunner.RunQuery(
"Get a list of many albums. Cast with ToScalarList(). Returns List<dynamic> of integer AlbumIds",
db => db.Albums.FindAllByGenreId(1).ToScalarList(),
new List <string> {
"Title"
}, "DynamicList");
ExampleRunner.RunQuery(
"Get a list of many album titles. Cast with ToScalarList(). Returns List<dynamic>",
db => db.Albums.FindAllByGenreId(1).Select(db.Albums.Title).ToScalarList(),
new List <string> {
"Title"
}, "DynamicList");
ExampleRunner.RunQuery(
"Get a list of many album titles. Cast with ToScalarList<string>(). Returns List<string>",
db => db.Albums.FindAllByGenreId(1).Select(db.Albums.Title).ToScalarList <string>(),
new List <string> {
"Title"
}, "StringList");
ExampleRunner.RunQuery(
"Get a list of many album titles. Cast with ToScalarList<int>(). Throws InvalidCastException",
db => db.Albums.FindAllByGenreId(1).Select(db.Albums.Title).ToScalarList <int>(),
new List <string> {
"Title"
}, "StringList");
// ToScalarArray
ExampleRunner.RunQuery(
"Get a single album. Return null. Cast with ToScalarArray(). Throws RuntimeBinderException",
db => db.Albums.Get(1000).ToScalarArray(),
new List <string>(), "string");
ExampleRunner.RunQuery(
"Get a single album. Cast with ToScalarArray(). Throws UnresolvableObjectException",
db => db.Albums.Get(1).ToScalarArray(),
new List <string>(), "string");
ExampleRunner.RunQuery(
"Get a single album title. Cast with ToScalarArray<string>(). Throws UnresolvableObjectException",
db => db.Albums.Select(db.Albums.Title).Get(1).ToScalarArray <string>(),
new List <string>(), "string");
ExampleRunner.RunQuery(
"Get a list of albums. Return null. Cast with ToScalarArray(). Returns empty dynamic[]",
db => db.Albums.FindAllByGenreId(100).ToScalarArray(),
new List <string> {
"Title"
}, "DynamicArray");
ExampleRunner.RunQuery(
"Get a list of many albums. Cast with ToScalarArray(). Returns dynamic[] of integer AlbumIds",
db => db.Albums.FindAllByGenreId(1).ToScalarArray(),
new List <string> {
"Title"
}, "DynamicArray");
ExampleRunner.RunQuery(
"Get a list of many album titles. Cast with ToScalarArray(). Returns dynamic[]",
db => db.Albums.FindAllByGenreId(1).Select(db.Albums.Title).ToScalarArray(),
new List <string> {
"Title"
}, "DynamicArray");
ExampleRunner.RunQuery(
"Get a list of many album titles. Cast with ToScalarArray<string>(). Returns string[]",
db => db.Albums.FindAllByGenreId(1).Select(db.Albums.Title).ToScalarArray <string>(),
new List <string> {
"Title"
}, "StringArray");
ExampleRunner.RunQuery(
"Get a list of many album titles. Cast with ToScalarArray<int>(). Throws InvalidCastException",
db => db.Albums.FindAllByGenreId(1).Select(db.Albums.Title).ToScalarArray <int>(),
new List <string> {
"Title"
}, "StringArray");
}
internal void RunAll()
{
//select distinct orderid from OrderDetails
ExampleRunner.RunQuery(
"Get distinct order ids from OrderDetails using OrderId.Distinct()",
db => db.OrderDetails.All()
.Select(
db.OrderDetails.OrderId.Distinct()),
new List <string> {
"OrderId"
});
//select distinct orderid, albumid from OrderDetails
ExampleRunner.RunQuery(
"Get distinct order ids and albumid from OrderDetails using OrderId.Distinct",
db => db.OrderDetails.All()
.Select(
db.OrderDetails.OrderId.Distinct(),
db.OrderDetails.AlbumId),
new List <string> {
"OrderId", "AlbumId"
});
//select count(orderid) from OrderDetails
ExampleRunner.RunQuery(
"select count(orderid) from OrderDetails using OrderId.Count()",
db => db.OrderDetails.All()
.Select(
db.OrderDetails.OrderId.Count()),
new List <string> {
""
});
//select count(orderid) from OrderDetails
ExampleRunner.RunQuery(
"select count(*) from OrderDetails using db.OrderDetails.Count()",
db => db.OrderDetails.Count(),
new List <string> {
""
});
//select count(distinct orderid) from orderdetails using .distinct().count()
ExampleRunner.RunQuery(
"select count(distinct orderid) from orderdetails",
db => db.orderdetails.all()
.select(
db.orderdetails.orderid.distinct().count()),
new List <string> {
""
});
//select distinct count(orderid) from OrderDetails
ExampleRunner.RunQuery(
"select count(distinct orderid) from OrderDetails",
db => db.OrderDetails.All()
.Select(
db.OrderDetails.OrderId.Count().Distinct()),
new List <string> {
""
});
//select count(distinct orderid) from OrderDetails
ExampleRunner.RunQuery(
"select count(distinct orderid) from OrderDetails",
db => db.OrderDetails.All()
.Select(
db.OrderDetails.OrderId.CountDistinct()),
new List <string> {
""
});
}
internal void RunAll()
{
ExampleRunner.RunQuery(
"Comparison Operators. 1.Equals : Where(db.Albums.ArtistId == 120)",
db => db.Albums.All().Where(db.Albums.ArtistId == 120));
ExampleRunner.RunQuery(
"Comparison Operators. 2.Not Equals : Where(db.Albums.GenreId != 1)",
db => db.Albums.All().Where(db.Albums.GenreId != 1));
ExampleRunner.RunQuery(
"Comparison Operators. 3.Less Than : Where(db.Albums.Price < 8.99)",
db => db.Albums.All().Where(db.Albums.Price < 8.99));
ExampleRunner.RunQuery(
"Comparison Operators. 4.Less Than Or Equals : Where(db.Albums.Price <= 8.99)",
db => db.Albums.All().Where(db.Albums.Price <= 8.99));
ExampleRunner.RunQuery(
"Comparison Operators. 5.Greater Than : Where(db.Albums.Price > 7.99)",
db => db.Albums.All().Where(db.Albums.Price > 7.99));
ExampleRunner.RunQuery(
"Comparison Operators. 6.Greaer Than Or Equals : Where(db.Albums.Price >= 7.99)",
db => db.Albums.All().Where(db.Albums.Price >= 7.99));
ExampleRunner.RunQuery(
"Math Operators. 1.Add : Where(db.Albums.AlbumId + db.Albums.ArtistId > 120)",
db => db.Albums.All().Where(db.Albums.AlbumId + db.Albums.ArtistId > 120));
ExampleRunner.RunQuery(
"Math Operators. 2.Subtract : Where(db.Albums.AlbumId - db.Albums.ArtistId < 130)",
db => db.Albums.All().Where(db.Albums.AlbumId - db.Albums.ArtistId < 130));
ExampleRunner.RunQuery(
"Math Operators. 3. Multiply : Where(db.OrderDetails.Quantity * db.OrderDetails.UnitPrice >= 50)",
db => db.Albums.All().Where(db.OrderDetails.Quantity * db.OrderDetails.UnitPrice >= 50));
ExampleRunner.RunQuery(
"Math Operators. 4.Divide : Where(db.OrderDetails.UnitPrice / db.OrderDetails.Quantity <= 3)",
db => db.Albums.All().Where(db.OrderDetails.UnitPrice / db.OrderDetails.Quantity <= 3));
ExampleRunner.RunQuery(
"Math Operators. 5.Modulo : Where(db.OrderDetails.UnitPrice % db.OrderDetails.Quantity != 4)",
db => db.Albums.All().Where(db.OrderDetails.UnitPrice % db.OrderDetails.Quantity != 4));
ExampleRunner.RunQuery(
"Using LIKE : Where(db.Albums.Title.Like(\"%Side Of The%\")",
db => db.Albums.All().Where(db.Albums.Title.Like("%Side Of The%")));
ExampleRunner.RunQuery(
"Using NOT LIKE : Where(db.Albums.Title.NotLike(\"%a%\")",
db => db.Albums.All().Where(db.Albums.Title.NotLike("%a%")));
ExampleRunner.RunQuery(
"Using IN. 1.Embedded : db.Albums.FindAllByTitle(new[] {\"Nevermind\", \"Ten\"})",
db => db.Albums.FindAllByTitle(new[] { "Nevermind", "Ten" }));
ExampleRunner.RunQuery(
"Using IN. 2.In FindAll : db.Albums.FindAll(db.Albums.Title == new[] {\"Nevermind\", \"Ten\"})",
db => db.Albums.FindAll(db.Albums.Title == new[] { "Nevermind", "Ten" }));
ExampleRunner.RunQuery(
"Using IN. 3.In Where method : db.Albums.All().Where(db.Albums.Title == new[] {\"Nevermind\", \"Ten\"})",
db => db.Albums.All().Where(db.Albums.Title == new[] { "Nevermind", "Ten" }));
ExampleRunner.RunQuery(
"Using NOT IN. 1.In FindAll : db.Albums.FindAll(db.Albums.GenreId != new[] {1,3,7})",
db => db.Albums.FindAll(db.Albums.GenreId != new[] { 1, 3, 7 }));
ExampleRunner.RunQuery(
"Using NOT IN. 2.In Where method : db.Albums.All().Where(db.Albums.GenreId != new[] {1,3,7})",
db => db.Albums.All().Where(db.Albums.GenreId != new[] { 1, 3, 7 }));
ExampleRunner.RunQuery(
"Using BETWEEN. 1. Embedded : db.Albums.FindAllByAlbumId(400.to(410))",
db => db.Albums.FindAllByAlbumId(400.to(410)));
ExampleRunner.RunQuery(
"Using BETWEEN. 2. In FIndAll : db.Albums.FindAll(db.Albums.AlbumId == 400.to(410))",
db => db.Albums.FindAll(db.Albums.AlbumId == 400.to(410)));
ExampleRunner.RunQuery(
"Using BETWEEN. 3. In Where : db.Albums.All().Where(db.Albums.AlbumId == 400.to(410))",
db => db.Albums.All().Where(db.Albums.AlbumId == 400.to(410)));
ExampleRunner.RunQuery(
"Using NOT BETWEEN. 1. In FindAll : db.Orders.FindAll(db.Orders.OrderDate != DateTime.MinValue.to(DateTime.Now))",
db => db.Orders.FindAll(db.Orders.OrderDate != SqlDateTime.MinValue.Value.to(DateTime.Now)));
ExampleRunner.RunQuery(
"Using NOT BETWEEN. 2. In Where : db.Orders.All().Where(db.Orders.OrderDate != DateTime.MinValue.to(DateTime.Now))",
db => db.Orders.All().Where(db.Orders.OrderDate != SqlDateTime.MinValue.Value.to(DateTime.Now)));
ExampleRunner.RunQuery(
"Using IS NULL. 1. Embedded : db.Albums.FindAllByGenreId(null);",
db => db.Albums.FindAllByGenreId(null));
ExampleRunner.RunQuery(
"Using IS NULL. 2. Embedded : db.Albums.FindAllBy(GenreId:null);",
db => db.Albums.FindAllBy(GenreId: null));
ExampleRunner.RunQuery(
"Using IS NULL. 3. In FindAll : db.Albums.FindAll(db.Albums.GenreId == null);",
db => db.Albums.FindAll(db.Albums.GenreId == null));
ExampleRunner.RunQuery(
"Using IS NULL. 4. In Where : db.Albums.All().Where(db.Albums.GenreId == null);",
db => db.Albums.All().Where(db.Albums.GenreId == null));
ExampleRunner.RunQuery(
"Using IS NOT NULL. 1. In FindAll : db.Albums.FindAll(db.Albums.GenreId != null);",
db => db.Albums.FindAll(db.Albums.GenreId != null));
ExampleRunner.RunQuery(
"Using IS NOT NULL. 2. In Where : db.Albums.All().Where(db.Albums.GenreId != null);",
db => db.Albums.All().Where(db.Albums.GenreId != null));
}
internal void RunAll()
{
ExampleRunner.RunQuery(
"This works. Select all artist names and count of albums in system",
db => db.Albums.All()
.Select(db.Albums.Artists.Name, db.Albums.AlbumId.Count().As("NumberOfAlbums"))
.Having(db.Albums.AlbumId.Count() > 2),
new List <string> {
"Name", "NumberOfAlbums"
});
// Should throw NullReferenceException()
ExampleRunner.RunQuery(
"Having() called without base command.",
db => db.Albums.Having(db.Albums.Price.Avg() == 8.99));
// Throws MS.CS.RB.RuntimeBinderException. Should throw ArgumentException
ExampleRunner.RunQuery(
"Having called with no parameters",
db => db.Albums.All()
.Select(db.Albums.Artists.Name, db.Albums.AlbumId.Count().As("NumberOfAlbums"))
.Having(),
new List <string> {
"Name", "NumberOfAlbums"
});
// Throws MS.CS.RB.RuntimeBinderException. Should throw ArgumentException
ExampleRunner.RunQuery(
"Having called with invalid SimpleExpression .Having(123)",
db => db.Albums.All()
.Select(db.Albums.Artists.Name, db.Albums.AlbumId.Count().As("NumberOfAlbums"))
.Having(123),
new List <string> {
"Name", "NumberOfAlbums"
});
// Throws Simple.Data.Ado.AdoAdapterException
ExampleRunner.RunQuery(
"Having called with invalid SimpleExpression. Should use Where() instead .Having(db.Albums.GenreId == 1)",
db => db.Albums.All()
.Select(db.Albums.Artists.Name, db.Albums.AlbumId.Count().As("NumberOfAlbums"))
.Having(db.Albums.GenreId == 1),
new List <string> {
"Name", "NumberOfAlbums"
});
// Throws Simple.Data.UnresolvableObjectException
ExampleRunner.RunQuery(
"Having called with invalid SimpleExpression as parameter. Field name wrong .Having(db.Albums.NonExistentColumn.Max() > 1)",
db => db.Albums.All()
.Select(db.Albums.Artists.Name, db.Albums.AlbumId.Count().As("NumberOfAlbums"))
.Having(db.Albums.NonExistentColumn.Max() > 1),
new List <string> {
"Name", "NumberOfAlbums"
});
// Throws System.InvalidOperationException. Should throw ArgumentException?
ExampleRunner.RunQuery(
"Having called with two valid SimpleExpressions as parameters.",
db => db.Albums.All()
.Select(db.Albums.Artists.Name, db.Albums.AlbumId.Count().As("NumberOfAlbums"), db.Albums.Price.Sum().As("TotalCost"))
.Having(db.Albums.AlbumId.Count() > 2, db.Albums.Price.Sum() >= 16.99),
new List <string> {
"Name", "NumberOfAlbums", "TotalCost"
});
ExampleRunner.RunQuery(
"Having called with two valid SimpleExpressions ORed together as one parameter.",
db => db.Albums.All()
.Select(db.Albums.Artists.Name, db.Albums.AlbumId.Count().As("NumberOfAlbums"), db.Albums.Price.Sum().As("TotalCost"))
.Having(db.Albums.AlbumId.Count() > 2 || db.Albums.Price.Sum() >= 16.99),
new List <string> {
"Name", "NumberOfAlbums", "TotalCost"
});
// Concatenated using AND.
ExampleRunner.RunQuery(
"Having called twice with one valid SimpleExpression each. Should be concatenated with AND.",
db => db.Albums.All()
.Select(db.Albums.Artists.Name, db.Albums.AlbumId.Count().As("NumberOfAlbums"), db.Albums.Price.Sum().As("TotalCost"))
.Having(db.Albums.AlbumId.Count() > 2).Having(db.Albums.Price.Sum() >= 16.99),
new List <string> {
"Name", "NumberOfAlbums", "TotalCost"
});
}
internal void RunAll()
{
// ToScalar
// Throws RuntimeBinderException.
ExampleRunner.RunQuery(
"Get a single album. Return null. Cast with ToScalar()",
db => db.Albums.Get(1000).ToScalar(),
new List <string>(), "string");
ExampleRunner.RunQuery(
"Get a single album. Cast with ToScalar()",
db => db.Albums.Get(1).ToScalar(),
new List <string>(), "string");
// Throws InvalidCastException
ExampleRunner.RunQuery(
"Get a single album title. Cast with ToScalar<int>(). Throws InvalidCastException",
db => db.Albums.Select(db.Albums.Title).Get(1).ToScalar <int>(),
new List <string>(), "string");
ExampleRunner.RunQuery(
"Get a single album title. Cast with ToScalar<string>()",
db => db.Albums.Select(db.Albums.Title).Get(1).ToScalar <string>(),
new List <string>(), "string");
ExampleRunner.RunQuery(
"Get a list of album titles. Return null. Cast with ToScalar(). Throws Exception",
db => db.Albums.FindAllByGenreId(100).Select(db.Albums.Title).ToScalar());
ExampleRunner.RunQuery(
"Get a list of many album titles. Cast with ToScalar(). Throws Exception",
db => db.Albums.FindAllByGenreId(1).Select(db.Albums.Title).ToScalar());
ExampleRunner.RunQuery(
"Get a list of one album title. Cast with ToScalar(). Returns album title as string",
db => db.Albums.FindAllByAlbumId(1).Select(db.Albums.Title).ToScalar());
ExampleRunner.RunQuery(
"Get a list of one album. Cast with ToScalar(). Returns albumid as int",
db => db.Albums.FindAllByAlbumId(1).ToScalar());
ExampleRunner.RunQuery(
"Get a list of one album title. Cast with ToScalar<string>(). Returns album title as string",
db => db.Albums.FindAllByAlbumId(1).Select(db.Albums.Title).ToScalar <string>());
ExampleRunner.RunQuery(
"Get a list of one album. Cast with ToScalar<string>(). Throws InvalidCastException",
db => db.Albums.FindAllByAlbumId(1).ToScalar <string>());
// ToScalarOrDefault
ExampleRunner.RunQuery(
"Get a single album. Return null. Cast with ToScalarOrDefault(). Throws RuntimeBinderException",
db => db.Albums.Get(1000).ToScalarOrDefault(),
new List <string>(), "string");
ExampleRunner.RunQuery(
"Get a single album. Cast with ToScalarOrDefault(). Throws UnresolvableObjectException",
db => db.Albums.Get(1).ToScalarOrDefault(),
new List <string>(), "string");
ExampleRunner.RunQuery(
"Get a single album title. Cast with ToScalarOrDefault<int>(). Throws UnresolvableObjectException",
db => db.Albums.Select(db.Albums.Title).Get(1).ToScalarOrDefault <int>(),
new List <string>(), "string");
ExampleRunner.RunQuery(
"Get a single album title. Cast with ToScalarOrDefault<string>(). Throws UnresolvableObjectException",
db => db.Albums.Select(db.Albums.Title).Get(1).ToScalarOrDefault <string>(),
new List <string>(), "string");
ExampleRunner.RunQuery(
"Get a list of zero album titles. Return null. Cast with ToScalarOrDefault(). Returns null",
db => db.Albums.FindAllByGenreId(100).Select(db.Albums.Title).ToScalarOrDefault());
ExampleRunner.RunQuery(
"Get a list of many album titles. Cast with ToScalarOrDefault(). Throws SimpleDataException",
db => db.Albums.FindAllByGenreId(1).Select(db.Albums.Title).ToScalarOrDefault());
ExampleRunner.RunQuery(
"Get a list of one album title. Cast with ToScalarOrDefault(). Returns album title as string",
db => db.Albums.FindAllByAlbumId(1).Select(db.Albums.Title).ToScalarOrDefault());
ExampleRunner.RunQuery(
"Get a list of one album. Cast with ToScalarOrDefault(). Returns albumid as int",
db => db.Albums.FindAllByAlbumId(1).ToScalarOrDefault());
ExampleRunner.RunQuery(
"Get a list of one album title. Cast with ToScalarOrDefault<string>(). Returns album title as string",
db => db.Albums.FindAllByAlbumId(1).Select(db.Albums.Title).ToScalarOrDefault <string>());
ExampleRunner.RunQuery(
"Get a list of one album. Cast with ToScalarOrDefault<string>(). Throws InvalidCastException",
db => db.Albums.FindAllByAlbumId(1).ToScalarOrDefault <string>());
}
internal void RunAll()
{
ExampleRunner.RunQuery(
"Get a single album. Magic cast it to an album",
db => db.Albums.FindByGenreId(1), new List <string> {
"Title"
}, "Album");
// Throws Microsoft.CSharp.RuntimeBinder.RuntimeBinderException
// Cannot implicitly convert type 'Simple.Data.SimpleQuery' to 'SimpleDataSample.POCO.Album'
ExampleRunner.RunQuery(
"Get a list of albums. Magic cast it to a single album. Throws Exception",
db => db.Albums.FindAllByGenreId(1), new List <string> {
"Title"
}, "Album");
ExampleRunner.RunQuery(
"Get a list of albums. Magic cast it to a list of albums.",
db => db.Albums.FindAllByGenreId(1), new List <string> {
"Title"
}, "AlbumList");
ExampleRunner.RunQuery(
"Get a single album. Magic cast it to a list of albums",
db => db.Albums.FindByGenreId(1), new List <string> {
"Title"
}, "AlbumList");
// Throws Simple.Data.UnresolvableObjectException
ExampleRunner.RunQuery(
"Get a single album. Call Cast<Album>(). Throws Exception",
db => db.Albums.FindByGenreId(1).Cast <Album>(), new List <string> {
"Title"
}, "Album");
// Returns CastEnumerable<Album>
ExampleRunner.RunQuery(
"Get a list of albums. Call Cast<Album>(). Returns CastEnumerable<Album>",
db => db.Albums.FindAllByGenreId(1).Cast <Album>(), new List <string> {
"Title"
}, "AlbumEnumerable");
// Throws Simple.Data.UnresolvableObjectException
ExampleRunner.RunQuery(
"Get a single album. Call ToList(). Throws Exception",
db => db.Albums.FindByGenreId(1).ToList(), new List <string> {
"Title"
}, "AlbumList");
// Throws Simple.Data.UnresolvableObjectException
ExampleRunner.RunQuery(
"Get a single album. Call ToList<Album>(). Throws Exception",
db => db.Albums.FindByGenreId(1).ToList <Album>(), new List <string> {
"Title"
}, "AlbumList");
// Returns List<dynamic>
ExampleRunner.RunQuery(
"Get a list of albums. Call ToList(). Returns List<dynamic>",
db => db.Albums.FindAllByGenreId(1).ToList(), new List <string> {
"Title"
}, "DynamicList");
// Returns List<Album>
ExampleRunner.RunQuery(
"Get a list of albums. Call ToList<Album>(). Returns List<Album>",
db => db.Albums.FindAllByGenreId(1).ToList <Album>(), new List <string> {
"Title"
}, "AlbumList");
// Throws Simple.Data.UnresolvableObjectException
ExampleRunner.RunQuery(
"Get a single album. Call ToArray(). Throws Exception.",
db => db.Albums.FindByGenreId(1).ToArray(), new List <string> {
"Title"
}, "AlbumArray");
// Throws Simple.Data.UnresolvableObjectException
ExampleRunner.RunQuery(
"Get a single album. Call ToArray<Album>(). Throws Exception",
db => db.Albums.FindByGenreId(1).ToArray <Album>(), new List <string> {
"Title"
}, "AlbumArray");
// returns dynamic[]
ExampleRunner.RunQuery(
"Get a list of albums. Call ToArray(). Returns dynamic[]",
db => db.Albums.FindAllByGenreId(1).ToArray(), new List <string> {
"Title"
}, "DynamicArray");
// Returns Album[]
ExampleRunner.RunQuery(
"Get a list of albums. Call ToArray<Album>(). Returns Album[]",
db => db.Albums.FindAllByGenreId(1).ToArray <Album>(), new List <string> {
"Title"
}, "AlbumArray");
}
internal void RunAll()
{
#region Valid calls
ExampleRunner.RunQuery(
"Explicit Join between two tables using Join only (before Select method)",
db => db.Albums.FindAllByGenreId(1)
.Join(db.Genre, GenreId: db.Albums.GenreId)
.Select(
db.Albums.Title,
db.Genre.Name),
new List <string> {
"Title", "Name"
});
ExampleRunner.RunQuery(
"Explicit Join between two tables using Join only (after Select method)",
db => db.Albums.FindAllByGenreId(1)
.Select(
db.Albums.Title,
db.Genre.Name)
.Join(db.Genre, GenreId: db.Albums.GenreId),
new List <string> {
"Title", "Name"
});
ExampleRunner.RunQuery(
"Explicit Join between two tables using Join and On",
db => db.Albums.FindAllByGenreId(1)
.Select(
db.Albums.Title,
db.Genre.Name)
.Join(db.Genre).On(db.Genre.GenreId == db.Albums.GenreId),
new List <string> {
"Title", "Name"
});
ExampleRunner.RunQuery(
"Explicit Join between two tables using Join only (indexer style)",
db => db.Albums.FindAllByGenreId(1)
.Select(
db["Albums"]["Title"],
db["Genre"]["Name"])
.Join(db["Genre"], GenreId: db["Albums"]["GenreId"]),
new List <string> {
"Title", "Name"
});
ExampleRunner.RunQuery(
"Explicit Inner Join between three tables: orderDetails, albums and genre",
db => db.OrderDetails.FindAllByOrderId(1)
.Select(
db.OrderDetails.OrderId,
db.OrderDetails.Albums.Title,
db.OrderDetails.Albums.Genre.Name)
.Join(db.Albums, AlbumId: db.OrderDetails.AlbumId)
.Join(db.Genre, GenreId: db.Albums.GenreId),
new List <string> {
"Title", "Name"
});
ExampleRunner.RunQuery(
"Explicit Join between two tables using Join only and aliases using expression in the On clause",
db => TwoTableJoinWithAliasUsingExpressions(db),
new List <string> {
"Title", "Name"
});
ExampleRunner.RunQuery(
"Explicit Join between two tables using Join only and aliases using named parameters in the On clause",
db => TwoTableJoinWithAliasUsingNamedParameters(db),
new List <string> {
"Title", "Name"
});
ExampleRunner.RunQuery(
"Explicit Inner Join between three tables: orderDetails, albums and genre",
db => db.OrderDetails.FindAllByOrderId(1)
.Select(
db.OrderDetails.OrderId,
db.OrderDetails.Albums.Title,
db.OrderDetails.Albums.Genre.Name)
.Join(db.Albums, AlbumId: db.OrderDetails.AlbumId)
.Join(db.Genre, GenreId: db.Albums.GenreId),
new List <string> {
"Title", "Name"
});
#endregion
#region Invalid Calls
//Throws NullReferenceException
ExampleRunner.RunQuery(
"Explicit Join between two tables using Join only (trying to join the wrong table)",
db => db.Albums.FindAllByGenreId(1)
.Select(
db.Albums.Title,
db.Genre.Name)
.Join(db.Albums, GenreId: db.Genre.GenreId),
new List <string> {
"Title", "Name"
});
ExampleRunner.RunQuery(
"Explicit Join between two tables using Join only (target table key column name doesn't exist)",
db => db.Albums.FindAllByGenreId(1)
.Select(
db.Albums.Title,
db.Genre.Name)
.Join(db.Genre, NotPresent: db.Albums.GenreId),
new List <string> {
"Title", "Name"
});
ExampleRunner.RunQuery(
"Explicit Join between two tables using Join only (start table key column name doesn't exist)",
db => db.Albums.FindAllByGenreId(1)
.Select(
db.Albums.Title,
db.Genre.Name)
.Join(db.Genre, GenreId: db.Albums.NotPresent),
new List <string> {
"Title", "Name"
});
ExampleRunner.RunQuery(
"Explicit Join between two tables using Join only (key column types don't match)",
db => db.Albums.FindAllByGenreId(1)
.Select(
db.Albums.Title,
db.Genre.Name)
.Join(db.Genre, GenreId: db.Albums.Title),
new List <string> {
"Title", "Name"
});
// Throws System.InvalidOperationException
ExampleRunner.RunQuery(
"Explicit Join between two tables using Join and On. On not immediately after Join",
db => db.Albums.FindAllByGenreId(1)
.Join(db.Genre)
.Select(
db.Albums.Title,
db.Genre.Name)
.On(db.Genre.GenreId == db.Albums.GenreId),
new List <string> {
"Title", "Name"
});
// Throws IndexOutOfRangeException
ExampleRunner.RunQuery(
"Run Join with no arguments",
db => db.Albums.FindAllByGenreId(1)
.Join()
.Select(
db.Albums.Title,
db.Genre.Name),
new List <string> {
"Title", "Name"
});
// Throws Simple.Data.Ado.AdapterException
// The multi-part identifier "dbo.Genres.Name" could not be bound.
ExampleRunner.RunQuery(
"Run Join with only targetTable named",
db => db.Albums.FindAllByGenreId(1)
.Join(db.Genre)
.Select(
db.Albums.Title,
db.Genre.Name),
new List <string> {
"Title", "Name"
});
// Throws Simple.Data.Ado.AdapterException
// The multi-part identifier "dbo.Genres.Name" could not be bound.
ExampleRunner.RunQuery(
"Run Join with only targetTable and column named wrongly",
db => db.Albums.FindAllByGenreId(1)
.Join(db.Genre.GenreId)
.Select(
db.Albums.Title,
db.Genre.Name),
new List <string> {
"Title", "Name"
});
// Throws Simple.Data.Ado.AdapterException
// The multi-part identifier "dbo.Genres.Name" could not be bound.
ExampleRunner.RunQuery(
"Run Join with only startTable and column named",
db => db.Albums.FindAllByGenreId(1)
.Join(db.Albums.GenreId)
.Select(
db.Albums.Title,
db.Genre.Name),
new List <string> {
"Title", "Name"
});
//System.ArgumentOutOfRangeException
//Index was out of range. Must be non-negative and less than the size of the collection.
//Parameter name: index
ExampleRunner.RunQuery(
"Run Join with no arguments for Join or On",
db => db.Albums.FindAllByGenreId(1)
.Select(
db.Albums.Title,
db.Genre.Name)
.Join(db.Genre)
.On(),
new List <string> {
"Title", "Name"
});
//System.ArgumentOutOfRangeException
//Index was out of range. Must be non-negative and less than the size of the collection.
//Parameter name: index
ExampleRunner.RunQuery(
"Run Join with no arguments for On",
db => db.Albums.FindAllByGenreId(1)
.Select(
db.Albums.Title,
db.Genre.Name)
.Join(db.Genre)
.On(),
new List <string> {
"Title", "Name"
});
//System.ArgumentOutOfRangeException
//Index was out of range. Must be non-negative and less than the size of the collection.
//Parameter name: index
ExampleRunner.RunQuery(
"Run Join with just column name as argument for On",
db => db.Albums.FindAllByGenreId(1)
.Select(
db.Albums.Title,
db.Genre.Name)
.Join(db.Genre)
.On(db.Albums.GenreId),
new List <string> {
"Title", "Name"
});
//System.ArgumentOutOfRangeException
//Index was out of range. Must be non-negative and less than the size of the collection.
//Parameter name: index
ExampleRunner.RunQuery(
"Run Join with just literal as argument for On",
db => db.Albums.FindAllByGenreId(1)
.Select(
db.Albums.Title,
db.Genre.Name)
.Join(db.Genre)
.On(true),
new List <string> {
"Title", "Name"
});
ExampleRunner.RunQuery(
"Run Join with simpleexpression for On but not between two columns",
db => db.Albums.FindAllByGenreId(1)
.Select(
db.Albums.Title,
db.Genre.Name)
.Join(db.Genre)
.On(db.Albums.GenreId == null),
new List <string> {
"Title", "Name"
});
//Microsoft.CSharp.RuntimeBinder.RuntimeBinderException
//'Simple.Data.DynamicTable' does not contain a definition for 'GenreId'
ExampleRunner.RunQuery(
"Run Join with malformed simpleexpression for On",
db => db.Albums.FindAllByGenreId(1)
.Select(
db.Albums.Title,
db.Genre.Name)
.Join(db.Genre)
.On(db.Albums.GenreId = db.Genre.GenreId),
new List <string> {
"Title", "Name"
});
#endregion
}