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