This project is read-only.


  1. Introduction
  2. Basic LINQ operations
  3. Joins
  4. Grouping and aggregates
  5. Limitations and special cases in LINQ expressions
  6. Debugging queries - getting the generated SQL
  7. Include facility - loading related entities
  8. Search: building queries dynamically
  9. Update operations with LINQ
  10. Query parametetrization and caching
  11. LINQ and entity cache
  12. Query options
  13. Using LINQ to define DB Views
  14. Building Web API search methods


VITA provides a full-featured LINQ engine, capable of working with different database servers, with extra features on top of standard LINQ querying capabilities: query cache, querying cached data, dynamic query building, update operations with LINQ, etc.
In this guide we will be using the data model from sample BookStore app found in VITA download. The sample project defines entities/tables like books, authors, publishers, users, book orders, reviews, etc., with appropriate relations between them.

Basic LINQ operations

Any data operation in VITA starts with opening of an entity session:

  var session = entityApp.OpenSession(); 

To construct a LINQ query, you need an instance of IQueryable<TEntity> interface. Use the session.EntitySet<TEntity>() method to obtain it, and then construct the query:

  var qBooksByTitle = from b in session.EntitySet<IBook>()
                      where b.Title == "c# Programming"
                      select b;
  var lstBooksByTitle = qBooksByTitle.ToList();

You can specify ordering and cut the page of the result set using Skip/Take methods :

  var msbooks = from b in session.EntitySet<IBook>()
                where b.Publisher.Name == "MS Books"
                orderby b.Title
                select b;
var msBookList = msbooks.Skip(5).Take(10).ToList();

We used an entity-type property book.Publisher - the resulting SQL will have a join of Book and Publisher tables using the underlying foreign key. This is an easy way to express joins, by using entity-type properties in queries.
We can also use a plain, non SQL-like c# syntax to express the same query, with direct calls to the IQueryable methods:

  var msBookList = session.EntitySet<IBook>()
               .Where(b => b.Publisher.Name == "MS Books").OrderBy(b => b.Title)

You can use list-type properties like book.Authors, one-to-many or many-to-many:

  var query = from b in session.EntitySet<IBook>()
            where b.Authors.Any(a => a.FirstName == "Jack")
            select b;
  var booksByJack = query.ToList(); 

For many-to-many list properties like book.Authors the resulting query will have double-join of 3 tables: Book, Author and BookAuthor.
Queries can return auto-types :

  var query = from b in session.EntitySet<IBook>()
            where b.Publisher.Id == pubId
            orderby b.Price 
            select new {Title = b.Title, Price = b.Price};
  var bookPrices = query.ToList(); 

The query selects tuples {Title, Price} for books for a publisher with some known Id.
The returned auto-object can contain entity references:

  var query = from r in session.EntitySet<IBookReview>()
        where r.Rating == 5
        select new {Review = r, Book = r.Book, User = r.User};
  var topReviews = query.ToList();
  IList<IBookReview> topReviewEntities = topReviews.Select(o => o.Review).ToList(); 

The database query returns a list of triplets of entities: IBookReview, IBook, IUser. We then extract list of IBookReview entities from the result. The good side effect is that related books and users are loaded too and are cached in the entity session. So accessing a review's Book property would not cause an extra DB operation. The other way of pre-loading the related entities is to use Include facilities discussed in the section below.
You can page the results of the query, using Skip and Take methods:

  var query = from b in session.EntitySet<IBook>()
              where b.Category == BookCategory.Fiction
              select b;
  var fictionBooksPage2 = query.Skip(10).Take(10).ToList(); 

We have shown queries that return list of entities and list of auto objects. A query can also return a single entity, or a single scalar value:

  var query = from b in session.EntitySet<IBook>()
              where b.Category == BookCategory.Fiction
              orderby b.PublishedOn descending 
              select b;
  var latestFictionBook = query.First(); 
  var query2 = from b in session.EntitySet<IBook>()
               where b.Category == BookCategory.Fiction
               orderby b.PublishedOn descending 
               select b.Title;
  var latestFictionBookTitle = query.First(); 

The first query returns latest published fiction book; the second query returns its title only.
In all examples above the returned entities are attached to the entity session and tracked for changes, automatically, including the entities returned in auto-objects.


Joins are in fact easy with VITA LINQ engine. Whenever you use a property that references an entity or entity list in a query (ex: book.Publisher, book.Authrs) - the LINQ engine creates a SQL with a JOIN. (There's an exception mentioned below). It does not matter where you use this property inside a query - in the output, WHERE clause, or in ORDER BY specification - the engine recognizes it needs to join to another table and injects a proper SQL code.
So some queries shown in the previous section are in fact joins. The type of JOIN (inner or outer) depends on the type of link between entities - nullable property references result in outer joins. Example:

  // Inner join
  var qInner = from b in session.EntitySet<IBook>()
          select new { Title = b.Title, Publisher = b.Publisher.Name};
  var listInner = qInner.ToList();
  // Outer join, author.User is nullable
  var qOuter = from a in session.EntitySet<IAuthor>()
      select new { First = a.FirstName, Last = a.LastName,
                            UserName = a.User.UserName };
  var listOuter = qOuter.ToList();

The second query results in outer join, and returns all authors, some of which are users, and some are not. Note that for non-users the expression 'a.User.UserName' does not result in null-reference exception - the engine correctly handles it and returns null.
In many cases the joins are created and handled automatically, using the information about entity relationships.
There are cases when you need to construct a join explicitly. One case is when you want an inner join for a nullable relation. For the previous case with authors and users, the inner join would look like this:

  var qInner2 = 
    from a in session.EntitySet<IAuthor>()
    from u in session.EntitySet<IUser>()
    where a.User == u
    select new {First = a.FirstName, Last = a.LastName,
                UserName = u.UserName};

The query results in an inner join and would return only authors that are users. This also works for 'sub-queries' - when you want to join two filtered subsets:

  var subUsers = users.Where(u => u.IsActive);
  var subAuthors = authors.Where(a => a.FirstName == "John");
  var qJoinSubQueries = 
    from u in subUsers
    from a in subAuthors
    where a.User == u 
    select new {First = a.FirstName, Last = a.LastName,
                UserName = u.UserName};
  var listSubQueries = qSubQueries.ToList(); 

The resulting query is a join with all sub-query filters and join condition in the WHERE clause.
The other case for explicit join is when there is no explicit relation between entities, but a column in an entity is in fact a foreign key storing the ID of a row in another table. In this case you simply match the IDs in the WHERE clause:

  var qLog = 
    from lg in session.EntitySet<ILogEntry>()
    from u in session.EntitySet<IUser>()
    where lg.UserId == u.Id 
    select new {Message = lg.Message, UserName = u.UserName};

This is an inner join. To make an outer join, we need a special construct with DefaultIfEmpty:

  var qLog = 
    from u in session.EntitySet<IUser>()
    join lg in session.EntitySet<ILogEntry>()
      on u.Id equals lg.UserId into tempLog
    from lg2 in tempLog .DefaultIfEmpty()
    select new {Message = lg2.Message, UserName = u.UserName};

Notice the reverse order, we start with IUser set, join to ILogEntry - that's the way it works in classic LINQ (see, and this style is reproduced in VITA LINQ engine.
One final note about cases when system does not add a join, although it supposed to. Using entity-reference property in a query does not always result in a join to the target table. When you use only a primary key value of the referenced entity, it is replaced internally by a foreign key value in the child table:

  var books = session.EntitySet<IBook>()
    .Where(b => b.Publisher.Id == someId).ToList();

The query will not have a join to the Publisher table. The translation engine recognizes that b.Publisher.Id is in fact the value of foreign key in the Book table, so no join is needed, it will use the foreign key column directly.
Note that table join in SQL for a particular query might (or might not) be based on actual JOIN keyword, or alternatively put join condition in WHERE clause. It depends on the detailed layout of the query, the engine will select an appropriate construct.

Grouping and aggregates

VITA LINQ engine supports grouping and aggregate functions, with some limitations. The following query groups books by category and returns triplets (Category, book count, max price):

  var booksByCat = 
      from b in session.EntitySet<IBook>()
      group b by b.Category into g
      orderby g.Key
      select new { Category = g.Key, BookCount = g.Count(), 
                   MaxPrice = g.Max(b => b.Price) };

All grouping and counting/calculations in this case happen on database side. This is true for all queries that return aggregates. VITA supports all standard SQL aggregate functions - Min, Max, Avg, Count, etc.
For queries that return groupings (groups of records), the actual grouping most often happens on the client side:

  var query = session.EntitySet<IBook>().GroupBy(b => b.Publisher.Id);
  var booksByPublisher = query.ToList();

The underlying SQL delivers plain Book records, which are grouped into IGrouping instances in the result-processing code (c#).
Note that not all Queryable.GroupBy overloads are supported. All overloads that have IEqualityComparer parameter are not supported. For other variants, it depends on particular query. Generally, all simple scenarios are supported (that have clear and obvious implementation in standard SQL); for fancier queries, you might get 'groupby type non-supported' exception from LINQ engine, so make sure to check early your complex group-by query against the LINQ engine.
Using Aggregates without grouping
Sometimes you need to compute aggregate functions (Max, Avg, etc.) over the entire output row set (possibly filtered), without any grouping.
For example, let's say we need to retrieve a number of user orders and compute the average total of orders. To formulate the query we can use a 'fake' group-by clause:
    var qUserStats = 
      from ord in session.EntitySet<IBookOrder>()
      where ord.User == someUser
      group ord by 0 into g
      select new { Count = g.Count(), Avg = g.Average(o => o.Total)};
    var statObject = qUserStats.ToList()[0]; //cannot use First() here 
    Console.WriteLine(statObject.Count + " orders, avg:" + statObject.Avg);
Notice '... group ord by 0 ...' clause - this is a fake group-by clause that essentially groups all output rows together, in one group, and the query returns a single row with aggregate values of the set.
We have to use ToList() to execute the query and then take the single returned object from the list; we cannot use First() directly here, as it results in a combination of COUNT and a paging clause (FETCH NEXT 1) which is not allowed in SQL for most servers.
The resulting SQL:
  SELECT COUNT_BIG(*) AS "Count", AVG("Total") AS "Avg"
   FROM "books"."BookOrder"
   WHERE ("User_Id" = @P0)

Limitations and special cases in LINQ expressions

You build LINQ queries using c# syntax. LINQ engine translates them into SQL equivalents. It is important to remember that this translation is not always possible, and even when it is, the semantics (meaning) of the SQL might be slightly different from original c# meaning.
As an example of c# expression that cannot be translated - any call to a c# method, except if a few standard system functions, is not translatable. LINQ engine will react with exception saying 'Facility is not supported, please reformulate the query'. Some methods like Math.Abs(...) are OK, they have direct equivalents in SQL for all DB servers.
Comparison operators (<, >, ==) are mostly supported - if values are comparable in c#, they are comparable in DB.
String comparison and string matching might work slightly different in DB, compared to c#. In c#, the == operator is case sensitive.
In the database, case sensitivity depends on current collation.
For MS SQL server, the default collation is case-insensitive, so a query for matches of a string might bring matches that are differently cased. PostgreSql is case sensitive by default. You might enforce case-insensitive matching by using query options:

  var qMsBooks = from b in session.EntitySet<IBook>()
                where b.Publisher.Name == 'ms books'
                select b; 
  var msBooks = qMsBooks.WithOptions(QueryOptions.ForceIgnoreCase).ToList();                

This query for case-sensitive server like PostgreSql will be translated into case-insensitive form. Note, you can apply WithOptions to any part of the query, like session.EntitySet<IBook>().WithOptions(...) - it is equivalent to applying it to the entire query. This is useful when the query result is a single entity or value, for ex, uses query.FirstOrDefault()_ .
Substring matching follows the same casing rules. The string class methods Contains, StartsWith, EndsWith are translated into LIKE operator in SQL. Be careful when querying big tables with Contains - unlike full match with == operator, search for substring does not use index seek (quick search by index), and may result in full table/index scan. One better case is string.StartsWith method with MS SQL Server - according to MSDN the engine uses index lookup, so make sure that the string column is indexed. Check your server documentation before using specific string match method.
You can compare entity references, they are translated into comparison of underlying keys:

  var query = from b in session.EntitySet<IBook>()
              where b.Editor == someEditor
              select b; 

There is a subtle problem here. book.Editor is a nullable property, and it might happen you set someEditor to null to search for books with no editor. The problem is that in the database the comparison of NULLs is handled differently from c#: in c#, 'null==null' is true, while in database 'NULL=NULL' is always false. To handle this situation and make it work in line with c# semantics, VITA LINQ engine translates a comparison of nullable values into something like:

  WHERE (bk.Editor_Id = @EditorId) OR (
     (bk.Editor_Id IS NULL) AND (@EditorId IS NULL) )

The same applies to nullable value types (ex: DateTime?) and nullable string properties.
There are cases when you have a list of values (IDs), and you need to retrieve entities with property value in the list. You can use IList.Contains(...) method:

  var qBooks = from b in session.EntitySet<IBook>()
               where idList.Contains(b.Id)
               select b;
  var books = qBooks.ToList();                

The translation (SQL) heavily depends on capabilities of particular database. For all servers the list.Contains expression is translated into SQL 'value IN (...)' statement. The list 'representation' varies depending on the server. Only two servers - MS SQL Server, PostgreSql - provide a way to send an array as query parameter. PostgreSql provides direct support for array parameters, so the expression looks simply like:

 WHERE b.Id IN @P2;

In MS SQL Server, you send array/list using a user-defined table type. On the client side, the list is placed into a ADO.NET DataTable object - this happens internally, LINQ engine does all the conversions. The SQL looks like this:

SELECT "Id", "CreatedOn", "Title", ...
FROM "books"."Book"
WHERE ("Id" IN (SELECT "Value" FROM @P0))

where @P0 is a table-type parameter containing the list. VITA uses a single custom table type dbo.Vita_ArrayAsTable for all queries and stored procedures in MS SQL Server that have an array-type parameter. VITA creates this user-defined type at startup:

CREATE TYPE [dbo].[Vita_ArrayAsTable] AS TABLE(
	[Value] [sql_variant] NULL

The default schema can be changed by setting value in MsSqlDbDriver.SystemSchema.
Important: If you are using the DB-first scenario with MS SQL Server, and disable database schema updates, then you have to run the SQL to create the type manually in the database.
Note: in version the type type is created in one of user's schemas. It is changed to 'dbo' in all later versions.
For all other database servers (SQL CE, MySql, SQLite) the handling of list.Contains is different. These databases do not support array parameters, so array values are directly embedded, as list of literals, into the 'IN' argument of the translated SQL.

Debugging queries - getting the generated SQL

You can easily retrieve the SQL and parameter values for the last executed query:

  var lastCmd = session.GetLastCommand(); 
  var sql = lastCmd.CommandText;
  var parameters = lastCmd.Parameters; 

This is usefull when debugging the code, to see what SQL was generated for a query. All SQLs are also logged into operation log (and log file if you specified entityApp.LogPath property).
You can also get the log for all previous operations for an entity session, up to 200 entries:

  var log = session.Context.LocalLog.GetAllAsText(); 

Include facility - loading related entities

Retrieving results using LINQ query is usually followed by some processing. It might be as simple as converting the result entities into 1-1 matching model objects to be returned over HTTP in the Web GET call. Or it might be iterating through entities, performing some calculations, and possibly modifying the entities. In any case, there is a big chance the code will touch some related entities (book -> book.Publisher) - which will cause automatic loading of the target entity(ies), and an extra query to the database. The result is what is commonly known as (N+1) queries problem - you run 1 query to select target entities, and then run N queries to retrieve related information, one per each returned entity. This results in unnecessary chatty db access, and inefficient use of database server.
The efficient approach would be to retrieve all related entities (Publisher) in one query, preferably right when we run initial query, so all related information is pre-loaded before processing. VITA provides Include facility that implements just that.
You can instruct the query engine to perform additional load of related entity using the Include extension method:

  var fictionBooks = 
        .Where(b => b.Category == BookCategory.Fiction)
        .Include(b => b.Publisher)

The Include method returns the same query instance that it receives as the first parameter ('this' object), but it adds an internal instruction to the query engine to retrieve the related publisher entity for every retrieved book entity. Right after executing the query, the engine collects all of publisher ID's values from the result book entities (from the underlying foreign key), removes the duplicates, and runs and extra SELECT against Publisher table with WHERE clause restricting IDs to the publisher IDs collected from books. Then the engine assigns the book.Publisher property for every book in initial 'main' query.
You can use list properties in the Include argument, and you can combine more than one property by using auto object:

  var fictionBooks = 
        .Where(b => b.Category == BookCategory.Fiction)
        .Include(b => new {b.Publisher, b.Authors})

book.Authors is a list property based on many-to-many relation. The LINQ engine will run 3 extra 'include' queries: one for publishers, one for IBookAuthor link table, and one for target IAuthor table. It then reconstructs the Authors list for every book retrieved in the main query and puts it into the book.Authors property slots in every book.
You can have more than one Include call for a query:

  var qOrders = session.EntitySet<IBookOrder>()
        .Where(o => o.Status != OrderStatus.Canceled)
        .Include(o => new { o.Lines, o.User })   
        .Include((IBookOrderLine l) => l.Book) 
        .Include((IBook b) => new { b.Publisher, b.Authors});  

The first Include instructs to load related order lines (one-to-many property list), and related User. The second Include is different - it defines an expression for an explicitly typed entity IBookOrderLine that is different from query result entity IBookOrder. This is in fact chaining of includes - the first Include produces a set of IBookOrderLine entities, and that's where the second include is applied: for each retrieved order line, the engine should retrieve the related IBook entity.
Finally, the third include, also explicitly typed, instructs the engine to load a publisher and list of authors for each book retrieved.
The order of includes does not matter, the engine runs through the list of Includes each time it gets a new entity set retrieved, trying to find another include to apply.
To avoid infinite loops, an Include expression is applied no more than 2 times for any entity type.
We could achieve the same effect with just 2 includes:

  var qOrders = session.EntitySet<IBookOrder>()
        .Where(o => o.Status != OrderStatus.Canceled)
        .Include(o => new { o.Lines, o.User })   
        .Include((IBookOrderLine l) => new {l.Book.Publisher, l.Book.Authors}); 

The LINQ engine unfolds the chained property reference like line.Book.Publisher, and runs two queries: first to retrieve related books, and another to get publishers for every book.
We use line.Book reference twice in the auto object, but it does not result in two queries for the related books - the engine keeps track of already processed entities, and when it encounters the 'books' expression again, it detects that all the related books are already loaded, and does not run the book query again.
We have shown so far how to add Include directives to a particular query.

You can also add an Include expression to the current OperationContext instance, available as session.Context.
In this case the directives apply to all queries executed through sessions associated with this context.

     .AddInclude((IBookOrder o) => new { o.Lines, o.User })
     .AddInclude((IBookOrderLine l) => l.Book.Publisher);
  var qOrders = session.EntitySet<IBookOrder>()
             .Where(o => o.Status != OrderStatus.Canceled);
  var qOrders2 = session.EntitySet<IBookOrder>()
             .Where(o => o.Status == OrderStatus.Open);

For both queries the includes from the context will be applied. You can remove include expressions from the context once you do not need them anymore:

  session.Context.RemoveInclude((IBookOrder o) => new { o.Lines, o.User });
  session.Context.RemoveInclude(((IBookOrderLine l) => l.Book.Publisher);

The RemoveInclude method matches the include expression first as objects (if you use the same instance of lambda saved in local variable to add and remove), or as strings - so their ToString() representations should match. In the latter case you should use the same lambda parameter name in both cases (in Add and in Remove expressions).
Adding Includes to OperationContext is useful in several situations:
  • To specify multiple Include instructions before running session.ExecuteSearch method (see below). The actual query is formed inside the method, so you cannot add Includes to it. The ExecuteSearch method accepts a single Include as an optional parameter, but you are limited to just one value and it must be typed to query result - so you cannot use more includes for types other than the entity being searched. In this case you can add Include expressions to the operation context of the session, and they will be applied right after the main search query is executed.
  • To specify include rules for queries in all methods of an API controller. Usually methods in a controller manipulate with the same objects, so most queries should have the same include instructions. Instead of adding them to every query in every method, you can override InitController() method and add common Include clauses to the operation context passed as a parameter.

Search: building queries dynamically

UI search forms for searching information by multiple criteria is a common thing in modern applications, Web and desktop. Implementing such search would require building a dynamic LINQ expression with WHERE condition composed of multiple optional conditions. Conditions are added to the query if user filled out the corresponding criteria value in the input search form. VITA supports this scenario with out-of-the-box helper methods that make it really easy to build queries on the fly.
We will now build a LINQ query to search books in a sample BookStore app. Let's assume that UI has a search form with multiple controls to enter the complex criteria. Once the user fills out the form and clicks Search button, the criteria is copied into a BookSearch object and passed to SearchBooks method:

  // Important for API methods: use properties (get/set), not fields.
  public class BookSearch : SearchParams {
    public string Title { get; set; }
    public double? MaxPrice { get; set; }
    public string Publisher { get; set; }
    public DateTime? PublishedAfter { get; set; }
    public DateTime? PublishedBefore { get; set; }
  public SearchResults<IBook> SearchBooks(BookSearch search) {
    var session = OpenSession();
    var where = session.NewPredicate<IBook>()
      .AndIfNotEmpty(search.Title, b => b.Title.StartsWith(search.Title))
      .AndIfNotEmpty(search.MaxPrice, b => b.Price <= search.MaxPrice.Value)
                       b => b.Publisher.Name.StartsWith(search.Publisher))
                       b => b.PublishedOn.Value >= search.PublishedAfter.Value)
                       b => b.PublishedOn.Value <= search.PublishedBefore.Value);
    return session.ExecuteSearch(where, searchParams, 
             include: b => b.Publisher, 
             nameMapping: _orderByMapping);
  static Dictionary<string, string> _orderByMapping = 
     new Dictionary<string, string>() 
       { {"pubname", "Publisher.Name"} };
  // SearchParams and SearchResults<T> base classes, 
  //   defined in VITA:
  public class SearchParams {
    public string OrderBy { get; set; }
    public int Skip { get; set; }
    public int Take { get; set; }

  public class SearchResults<T> {
    public long TotalCount;
    public IList<T> Results = new List<T>();

BookSearch class is a subclass of VITA-defined SearchParams class, a standard base class for search criteria. It defines basic common properties:
  • OrderBy (string) - sort order for results, a comma-delimited list of property names, with optional '-desc' suffix
  • Skip, Take (int) - page cut parameters.
You don't have to use this base class, but it makes things easier as some help methods expect an object derived from this class.
The method returns a generic SearchResults<> object containing actual book list (a page for take/count), and a total count of books matching the criteria if run without skip/take restrictions. This is useful in search pages, which usually show the total count of objects matching the criteria.
The next statement is a fluent-style call chain that actually constructs the dynamic WHERE clause. Each condition is added only if the corresponding search criteria is present (not empty). We used AndIfNotEmpty method but there are other overloads, including unconditional And() and Or() methods.
The end result of the chain is a complete WHERE clause for LINQ query based on the search parameters. It contains zero or more conditions, one for each search parameter, joined by AND operator.
The ExecuteSearch method actually runs the query and returns the result. It also figures out the total count of matching objects, optionally (but not always) running another query without page constraints. The method is smart about figuring out the total count - it tries to avoid running total query if possible:
  1. First it runs the query with page size (take) equal original Take value plus 1. If the query returns less than (take + 1) objects, it means we hit the last object, and we can figure out total without running the total Count query: it is a Skip value plus number of objects returned.
  2. If, alternatively, the first query returns exactly (take + 1) objects, the engine runs the additional total count query.

The ExecuteSearch accepts several optional parameters:

* include - an Include expression to run with the main query, see more here. You can provide only one include expression as parameter; to use more, you should attach them to the _session.Context_.
* nameMapping - optional, a mapping dictionary for OrderBy clause, discussed below.

Ordering of results is defined by the OrderBy property of SearchParams base class. It is a comma- or semicolon-delimited list of property names. A property in a list may optionally include a '-desc' prefix, to indicate the descending order. A property name should be either the name of the property in the base entity (Book), or dotted compound reference to a property in a related entity, for example:
OrderBy: "Price-desc;Publisher.Name;Title"
The LINQ engine will resolve the 'book.Publisher.Name' reference by adding a join to Publisher table, and will construct an appropriate OrderBy clause. Additionally, you can map long dotted specs to shorter aliases and provide mappings in a nameMapping parameter as shown in the code. For the mappings defined in the example you could use 'pubName' instead of 'Publisher.Name' in OrderBy property.
It is often convenient to use Search methods as universal lookups - not for search per se, with complex criteria, but to lookup a single object by a unique or almost unique key. It is especially handy for Web UI - a single search data API method can be used for various lookup-by-name/key cases from the UI code. It is important then that the Search method would run a single query in these lookup cases, without the total count query, as described above.

Update operations with LINQ

Normally LINQ is used for constructing queries that return data from the database using SELECT SQL statements. VITA provides a number of extensions that allow you to construct custom UPDATE, INSERT and DELETE statements against entities/tables using the familiar LINQ syntax.
Note: we will be using the term 'update LINQ' to mean all types of non-select commands: UPDATE, INSERT, DELETE.
For example, you can increment prices of all fiction books using the following update query:

  var booksQuery = from b in session.EntitySet<IBook>()
                   where b.Category == BookCategory.Fiction
                   select new { Id = b.Id, Price = b.Price + 1 }; //Id is optional 
  updateCount = booksQuery.ExecuteUpdate<IBook>();

The update query in general should return a list of auto objects each containing:
  • A primary key (ID) of the entity to be updated
  • Fields matching the properties of the entity type to be updated, with the new value of the property as field value

The query above returns a list of objects each containing book ID and new value of the Price property(column), derived from the old Price value. The query can be in fact simplified: for update queries that involve just one entity type (table) and not using other tables, the Id value in auto object may be skipped.
The general recipe for non-query LINQ commands is the following:
  • construct a base query returning an auto object (apply rules specific for each command type, see below)
  • execute the query using one of the extension methods: ExecuteUpdate, ExecuteInsert or ExecuteDelete.
Per command type, the output auto object must contain:
  • Update - primary key value of the target row; fields with names matching entity properties to update and values equal to new values. If the query uses only the entity type to be updated, the primary key value (ID) may be omitted.
  • Insert - the fields matching property names of the target entity and values to be inserted.
  • Delete - the primary key value of the entity to delete; alternatively you can also return the entity itself, instead of auto object.
Some specific situations:
  • To insert new Guid value in primary key column in INSERTs use Guid.NewGuid() method, it will be translated into a matching Guid-generating SQL function.
  • Setting entity references - you should use the name of the foreign key column and set it to the primary key value of the target entity.
Example - let's insert a 5-star review for all programming books:

  var insertReviewQuery = 
        from b in session.EntitySet<IBook>()
        where b.Category == BookCategory.Programming
        select new {Id = Guid.NewGuid(), CreatedOn = DateTime.Now,
                    Book_Id = b.Id, 
                    User_Id = internalId, //setting FK value
                    Rating = 5, Caption = "Recommended by our staff", 
                    Review = "We endorse it!"};
  var count = insertReviewQuery.ExecuteInsert<IBookReview>();

Now let's look at some advanced cases. The following query recalculates the totals of all book orders:
  // Note: MS SQL Server, PostgreSql only 
  var ordersTotalsQuery = 
      from bol in session.EntitySet<IBookOrderLine>()
      group bol by bol.Order.Id into orderUpdate
      select new { Id = orderUpdate.Key, 
                   Total = orderUpdate.Sum(line => line.Price * line.Quantity)};
  var updatedTotalsCount = ordersTotalsQuery.ExecuteUpdate<IBookOrder>();

The ExecuteUpdate method returns the number of rows updated. The generated SQL for the update:

  UPDATE "books"."BookOrder"
   SET "Total" = _from."Total"
  FROM (
  SELECT "Order_Id" AS "Id_", SUM(("Price" * CONVERT(numeric,"Quantity"))) AS "Total"
  FROM "books"."BookOrderLine"
  GROUP BY "Order_Id"
       ) AS _from
  WHERE "Id" = _from."Id_";

An example of delete query - deletes all reviews from users with username starting with 'xx':

  var reviewDelQuery = from r in session.EntitySet<IBookReview>()
                       where r.User.UserName.StartsWith("xx")
                       select r.Id; 
  countDel = reviewDelQuery.ExecuteDelete<IBookReview>();

Note: not supported by MySql, does not allow this kind of queries: "You cannot delete from a table and select from the same table in a subquery." (
The actual generated SQL for MS SQL Server:

  DELETE FROM "books"."BookReview"   WHERE "Id" IN (
  SELECT r$."Id"
  FROM "books"."BookReview" r$, "books"."User" t1$
  WHERE (t1$."Id" = r$."User_Id") AND (t1$."UserName" LIKE 'xx%' ESCAPE '\'));          

Executing update LINQ commands in a transaction
Usually database updates are executed in transactions. If you want to execute one or more non-query (update/insert/delete) LINQ operation in an explicit transaction, you can do it by using Direct DB Access facility in VITA. You need to obtain a direct DB connector instance - this will allow you to control transactions explicitly:

  var directDb = session.GetDirectDbConnector();
  //From this moment, the connection remains open and associated with the entity session
  //execute your queries/operations here, including session.SaveChanges(); 

You can call session.SaveChanges() in the middle of your operations, to submit all pending changes to entities.
An alternative and often better way to mix LINQ-based updates and regular entities updates is to schedule LINQ-based updates.

Scheduling update LINQ commands
Rather than executing LINQ-based updates immediately, you can schedule them to be executed with the next session.SaveChanges() call:

  // do some book updates, then create update-total command and schedule it 
  var ordersTotalsQuery = 
        from bol in session.EntitySet<IBookOrderLine>()
        group bol by bol.Order.Id into orderUpdate
        select new { Id = orderUpdate.Key, 
                     Total = orderUpdate.Sum(line => line.Price * line.Quantity) };
  session.ScheduleUpdate<IBookOrder>(orderTotalsQuery, CommandSchedule.TransactionEnd);

TransactionEnd specifies that the query should be executed AFTER submitting entities changes. It is a default value for optional parameter, so it might be skipped. Alternatively you can specify to execute the update query before the SaveChanges regular entity updates.

Query parametetrization and caching

(to be completed)

LINQ and entity cache

(to be completed)

Query options

(to be completed)

Using LINQ to define DB Views

(to be completed)

Building Web API search methods

Implementing search methods in RESTful APIs has some specifics, which we will touch in this section. VITA provides integration with ASP.NET Web API through SlimApi technology - a number of artifacts (attributes and base classes) that allow you to easily implement RESTful API controllers, with complete integration with VITA modules and facilities - logging, authentication, security, authorization, etc.
We will explain a typical Web-enabled search method equivalent to already discussed book search example:

  // Sample URL:
  [ApiGet, ApiRoute("books")] // 
  public SearchResults<Book> SearchBooks([FromUrl] BookSearch searchParams) {
    searchParams = searchParams.DefaultIfNull(defaultOrderBy: "title"); 
    var session = this.Context.OpenSecureSession();
    var where = . . . // construct WHERE  
    return session.ExecuteSearch(where, searchParams, 
             converter: ib => ib.ToModel(), 
             include: b => b.Publisher, 
             nameMapping: orderByMapping);

The ApiGet, ApiRoute attributes are defined by VITA, they are part of SlimAPI facility; they are equivalent to Web API's standard HttpGet and Route attributes.
They have similar effect, but using these attributes allows you to avoid referencing Web API packages, so controllers can be defined directly in you core business logic assembly.
Similarly, FromUrl attribute is equivalent to standard FromUri - it specifies that the parameter is an object whose properties are set from the parameters in the call URL. Search criteria has multiple fields, all optional, but Web API routing requires that all parameters of a handler method are present in the URL. The solution is using an object as a parameter with FromUri method. The FromUrl attribute you see is SlimApi equivalent.
The method returns SearchResults<Book> object, where Book is a model (aka Data Transfer Object, DTO) - a bag of properties representing IBook entity in HTTP transmissions, and probably used as JS objects in UI (with data binding). The exact structure of this object is not important, we just assume that there is a conversion extension method iBook.ToModel() that converts IBook -> Book model.
Our search method first calls the DefaultIfNull extension method; it checks if the search object is null - if yes, it creates a default instance. It also sets the default sort order if the OrderBy property is empty. Finally it sets the default paging parameters Skip, Take (0, 10), to limit the number of returned rows if none were provided.
The construction of WHERE condition is the same as we already discussed in the section above.
The ExecuteSearch method builds and runs the DB query. But now it returns a result set of Book model objects (rather than IBook entities), because of the converter parameter we provide - it is the ToModel() extension method.

Last edited Apr 26, 2016 at 8:09 PM by rivantsov, version 7


No comments yet.