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_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_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_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()));
}
}