public void Run()
{
using (BloggingContext context = new BloggingContext())
{
DbConnection connection = context.GetDbConnection();
context.Database.Migrate();
context.Seed();
// Declare the fields that we want to query.
// Note: Under normal usage of this library you would normally store the field definitions in the database.
string[] fields = Blog.Fields;
// Create the query from a DbConnection.
// Note: We use the connection provided by the Entity Framework DbContext. There is no requirement to use a connection provided by Entity Framework.
// If you are using an Entity Framework DbContext in your code, we recommend making a method on your context exposing the provider.
// We will only be explicit in this sample.
IQueryable <Record> query = new SqliteQueryable(connection, "Blogs", fields);
// Traditional linq syntax can be used to predicate your queries
// When comparing a field value you must specify the table and field with this square bracket style:
// ["table"]["field"]
// Note: The casts are required for the comparisions to be valid C#
query = query.Where(x => (int)x["Blogs"]["BlogId"] == 1 || (int)x["Blogs"]["BlogId"] == 2);
// Executing the query can be achieved with methods like ToArray, ToList, FirstOrDefault etc.
// Note: Helper methods exist to flatten results which we will cover in other samples
Record[] results = query.ToArray();
SamplesHelper.RenderQuery("select * from Blogs where BlogId = 1 or BlogId = 2");
SamplesHelper.RenderRecords(results);
}
}
public void SqlQueryable_SingleOrDefault()
{
// Prepare the test data
string[] fields = new[] { "Id", "Name" };
IQueryable <Record> queryable = new SqliteQueryable(connection, "Course", "Alias", fields);
// Perform the test operation
Record record = queryable.SingleOrDefault();
}
public void SqlQueryable_Properties()
{
// Prepare the test data
SqlQueryable item = new SqliteQueryable(connection, "Table", "Alias", new string[] { "FieldA" });
// Check the test result
Assert.AreEqual(typeof(Record), item.ElementType);
Assert.IsInstanceOfType(item.Provider, typeof(SqlQueryableProvider));
Assert.IsInstanceOfType(item.Expression, typeof(TableExpression));
}
public void SqlQueryable_Contains_Chained()
{
// Prepare the test data
IQueryable <Record> query = new SqliteQueryable(connection, "Course", new[] { "Id", "Name" });
// Perfor the test operation
bool result = query.Contains(x => x["Course"]["Id"], 1);
// Check the test result
Assert.IsTrue(result);
}
public void SqlQueryable_Count()
{
// Prepare the test data
IQueryable <Record> query = new SqliteQueryable(connection, "Course", new[] { "Id", "Name" });
// Perform the test operation
int count = query.Count();
// Check the test result
Assert.AreEqual(ConnectionTestHelper.CountCourses, count);
}
public void SqlQueryable_Min()
{
// Prepare the test data
IQueryable <Record> query = new SqliteQueryable(connection, "Course", new[] { "Id", "Name" });
// Perform the test operation
int count = query.Min(x => (int)x["Course"]["Id"]);
// Check the test result
Assert.AreEqual(1, count);
}
public void SqlQueryable_Simple()
{
// Prepare the test data
string[] fields = new[] { "Id", "Name" };
IQueryable <Record> queryable = new SqliteQueryable(connection, "Course", "Alias", fields);
// Perform the test operation
Record[] records = queryable.ToArray();
// Check the test result
Assert.AreEqual(ConnectionTestHelper.CountCourses, records.Length);
}
public void SqlQueryable_LastOrDefault()
{
// Prepare the test data
string[] fields = new[] { "Id", "Name" };
IQueryable <Record> queryable = new SqliteQueryable(connection, "Course", "Alias", fields);
// Perform the test operation
Record record = queryable.LastOrDefault(x => x["Alias"]["Id"]);
// Check the test result
Assert.AreEqual((long)ConnectionTestHelper.CountCourses, record["Alias"]["Id"]);
}
public void SqlQueryable_First()
{
// Prepare the test data
string[] fields = new[] { "Id", "Name" };
IQueryable <Record> queryable = new SqliteQueryable(connection, "Course", "Alias", fields);
// Perform the test operation
Record record = queryable.First();
// Check the test result
Assert.AreEqual(1L, record["Alias"]["Id"]);
}
public void SqlQueryable_SingleOrDefault_Predicate()
{
// Prepare the test data
string[] fields = new[] { "Id", "Name" };
IQueryable <Record> queryable = new SqliteQueryable(connection, "Course", "Alias", fields);
// Perform the test operation
Record record = queryable.SingleOrDefault(x => (int)x["Alias"]["Id"] == 2);
// Check the test result
Assert.AreEqual(2L, record["Alias"]["Id"]);
}
public void SqlQueryable_Where_Comparison()
{
// Prepare the test data
string[] fields = new[] { "Id", "Name" };
IQueryable <Record> queryable = new SqliteQueryable(connection, "Course", "Alias", fields);
// Perform the test operation
Record[] records = queryable
.Where(x => (int)x["Alias"]["Id"] == 1)
.ToArray();
// Check the test result
Assert.AreEqual(1, records.Length);
}
public void SqlQueryable_Contains_SubQuery()
{
// Prepare the test data
IQueryable <Record> outer = new SqliteQueryable(connection, "Course", new[] { "Id", "Name" });
IQueryable <Record> inner = new SqliteQueryable(connection, "CourseStudent", new[] { "Id", "CourseId" });
// Perfor the test operation
Record[] records = outer
.Where(x => inner.Contains(y => y["CourseStudent"]["CourseId"], x["Course"]["Id"]))
.ToArray();
// Check the test result
Assert.AreEqual(ConnectionTestHelper.CountCourses, records.Length);
}
public void SqlQueryable_Sum()
{
// Prepare the test data
IQueryable <Record> query = new SqliteQueryable(connection, "Course", new[] { "Id", "Name" });
// Perform the test operations
int sumInt = query.Sum(x => (int)x["Course"]["Id"]);
long sumLong = query.Sum(x => (long)x["Course"]["Id"]);
decimal sumDecimal = query.Sum(x => (decimal)x["Course"]["Id"]);
float sumFloat = query.Sum(x => (float)x["Course"]["Id"]);
double sumDouble = query.Sum(x => (double)x["Course"]["Id"]);
// Check the test results
Assert.AreEqual(10, sumInt);
Assert.AreEqual(10L, sumLong);
Assert.AreEqual(10m, sumDecimal);
Assert.AreEqual(10f, sumFloat);
Assert.AreEqual(10, sumDouble);
}
public void SqlQueryable_Contains_StringArray()
{
// Prepare the test data
string[] values = new[] { "1", "3" };
string[] fields = new[] { "Id", "Name" };
IQueryable <Record> queryable = new SqliteQueryable(connection, "Course", "Alias", fields);
// Perfor the test operation
Record[] records = queryable
.Where(x => values.Contains((string)x["Alias"]["Id"]))
.ToArray();
// Check the test result
Assert.AreEqual(values.Length, records.Length);
foreach (Record record in records)
{
Assert.IsTrue(values.Contains(Convert.ToInt32(record["Alias"]["Id"]).ToString()));
}
}
public void SqlQueryable_Average()
{
// Prepare the test data
IQueryable <Record> query = new SqliteQueryable(connection, "Course", new[] { "Id", "Name" });
// Perform the test operations
int averageInt = query.Average(x => (int)x["Course"]["Id"]);
long averageLong = query.Average(x => (long)x["Course"]["Id"]);
decimal averageDecimal = query.Average(x => (decimal)x["Course"]["Id"]);
float averageFloat = query.Average(x => (float)x["Course"]["Id"]);
double averageDouble = query.Average(x => (double)x["Course"]["Id"]);
// Check the test results
Assert.AreEqual(2, averageInt);
Assert.AreEqual(2L, averageLong);
Assert.AreEqual(2.5m, averageDecimal);
Assert.AreEqual(2.5f, averageFloat);
Assert.AreEqual(2.5, averageDouble);
}
public void SqlQueryable_Take()
{
// Prepare the test data
IQueryable <Record> query = new SqliteQueryable(connection, "Course", new[] { "Id", "Name" });
// Perform the test operation
Record[] records = query
.Take(2)
.ToArray();
// Check the test result
Assert.AreEqual(2, records.Length);
foreach (Record record in records)
{
Assert.AreEqual(1, record.Count);
Assert.IsTrue(record["Course"].ContainsKey("Id"));
Assert.IsTrue(record["Course"].ContainsKey("Name"));
}
}
public void SqlQueryable_Join_Predicate_SelectOuter()
{
// Prepare the test data
IQueryable <Record> outer = new SqliteQueryable(connection, "Course", new[] { "Id", "Name" });
IQueryable <Record> inner = new SqliteQueryable(connection, "CourseStudent", new[] { "Id", "CourseId", "StudentId" });
// Perform the test operation
Record[] records = outer
.Join(inner, (o, i) => o["Course"]["Id"] == i["CourseStudent"]["CourseId"], (o, i) => o)
.ToArray();
// Check the test result
Assert.AreEqual(8, records.Length);
foreach (Record record in records)
{
Assert.AreEqual(1, record.Count);
Assert.IsTrue(record["Course"].ContainsKey("Id"));
Assert.IsTrue(record["Course"].ContainsKey("Name"));
}
}
public void SqlQueryable_OrderByDescending()
{
// Prepare the test data
IQueryable <Record> query = new SqliteQueryable(connection, "Course", new[] { "Id", "Name" });
// Perform the test operation
Dictionary <string, object>[] result = query
.OrderByDescending(x => x["Course"]["Id"])
.Flatten()
.ToArray();
// Check the test result
Assert.AreEqual(ConnectionTestHelper.CountCourses, result.Length);
long last = ConnectionTestHelper.CountCourses + 1;
foreach (Dictionary <string, object> record in result)
{
Assert.IsTrue((long)record["Id"] < last);
last = (long)record["Id"];
}
}
public void SqlQueryable_ThenByDescending()
{
// Prepare the test data
IQueryable <Record> query = new SqliteQueryable(connection, "SortTest", new[] { "Id", "Alpha", "Beta" });
// Perform the test operation
Dictionary <string, object>[] result = query
.OrderBy(x => x["SortTest"]["Alpha"])
.ThenByDescending(x => x["SortTest"]["Beta"])
.Flatten()
.ToArray();
// Check the test result
Assert.AreEqual(ConnectionTestHelper.CountSortTestAlphas * ConnectionTestHelper.CountSortTestBetas, result.Length);
Dictionary <string, object> last = result[0];
for (int i = 1; i < result.Length; i++)
{
Dictionary <string, object> record = result[i];
Assert.IsTrue((long)record["Alpha"] > (long)last["Alpha"] || (long)record["Beta"] < (long)last["Beta"]);
last = record;
}
}
