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 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. The second argument defines an alias that will be used throughout the query.
// Note: This is only an extension method, look at the Query definition to see how it creates the queryable.
IQueryable <Record> query = context.Query("Blogs", "BlogsAlias", fields);
// The declared alias (BlogsAlias) is now used in the place of the table name (Blogs).
Record[] results = query
.Where(x => (int)x["BlogsAlias"]["BlogId"] == 1 || (int)x["BlogsAlias"]["BlogId"] == 2)
.ToArray();
SamplesHelper.RenderQuery("select * from Blogs as BlogsAlias where BlogId = 1 or BlogId = 2");
SamplesHelper.RenderRecords(results);
}
}
public void Run()
{
using (BloggingContext context = new BloggingContext())
{
DbConnection connection = context.GetDbConnection();
context.Database.Migrate();
context.Seed();
// Create the inner query to join.
IQueryable <Record> posts = context.Query("Posts", Post.Fields);
// Query Blogs and left join Posts onto it.
// Note: This query demonstrates the overloaded Linq Join method. The normal Linq Join methods are also compatible.
Record[] results = context
.Query("Blogs", Blog.Fields)
.Join(posts, // The inner sequence to join
(x, y) => (int)x["Posts"]["BlogId"] == (int)x["Blogs"]["BlogId"], // The correlation predicate of the join
(outer, inner) => outer | inner, // Allows you to select fields from the outer, inner or both
JoinType.Left) // The type of join
.ToArray();
SamplesHelper.RenderQuery("select * from Blogs left join Posts on Posts.BlogId = Blogs.BlogId");
SamplesHelper.RenderRecords(results);
}
}
public void Run()
{
using (BloggingContext context = new BloggingContext())
{
DbConnection connection = context.GetDbConnection();
context.Database.Migrate();
context.Seed();
// The Linq extensions OrderBy, OrderByDescending, ThenBy and ThenByDescending are supported
Record[] results = context
.Query("Posts", Post.Fields)
.OrderBy(x => x["Posts"]["PostId"])
.ThenByDescending(x => x["Posts"]["Content"])
.ToArray();
SamplesHelper.RenderQuery("select * from Posts order by PostId asc, Content desc");
SamplesHelper.RenderRecords(results);
}
}
