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();
// 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);
}
}
private static void RenderSampleSelection(SampleSection section)
{
// Get all the samples exposed by the current assembly
KeyValuePair <SampleAttribute, Type>[] samples = SamplesHelper
.GetSectionSamples(section)
.ToArray();
// Calculate the selectable options
string[] options = samples
.Select(x => x.Key.Name)
.Concat(new[] { "Cancel" })
.ToArray();
// Read in the sample option
Console.Clear();
Console.WriteLine($"===== {section} Samples =====");
SamplesHelper.RenderOptions(options);
int index = SamplesHelper.ReadIntInRange(1, options.Length) - 1;
// Resolve the action
if (index < options.Length - 1)
{
RenderSample(samples[index].Key, samples[index].Value);
}
}
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);
}
}
private static bool RenderSectionSelection()
{
// Render the options
Console.Clear();
Console.WriteLine("===== System.Linq.Sql Samples =====");
Console.WriteLine("These samples demonstrate some of the common usages of this library.");
SamplesHelper.RenderOptions <SampleSection>();
// Read the desired section
SampleSection section = SamplesHelper.ReadInRange <SampleSection>();
if (section == SampleSection.Exit)
{
return(true);
}
// Render the section
RenderSampleSelection(section);
return(false);
}
