Skip to content

DevGuide_Linq

Jump to bottom
[email protected] edited this page Jan 31, 2020 · 21 revisions

(UNDER CONSTRUCTION)

VITA Developer Guide - LINQ Operations

Contents

Introduction

VITA comes with the LINQ engine implementation for all supported database servers. LINQ technology allows you to formulate database queries as strongly-typed c# expressions, which are translated into SQL statements, then executed, and the results are delivered as entities to your code tier. We assume you are familiar with general LINQ concepts and have some knowledge of SQL.

The source code examples in this document are taken (adapted) from a small demo app available on github:

https://github.com/rivantsov/VitaGuideSample

The sample application models a very simple online bookstore with books, publishers, authors.

Basic LINQ operations

Any data operation starts with opening an entity session:

  var session = booksApp.OpenSession();

NOTE: We use a simplified way to open session, a shortcut. In majority of cases you will be using the OperationContext to open sessions - this object carries the identity of the current user (even if it is system or anonymous user), so that all data access operations are associated with the current user.

Let's write a non-trivial LINQ query, and then briefly comment on its elements. Let's find first 5 books with an author with first name "John", ordered by price, and return tuples (title, publisher name):

  var booksByJohn = session.EntitySet<IBook>()
          .Where(b => b.Authors.Any(a => a.FirstName == "John"))
          .OrderBy(b => b.Price)
          .Skip(skip).Take(take) //paging
          .Select(b => new { b.Title, Publisher = b.Publisher.Name })
          .ToList();

Comments:

  • Building a LINQ query always starts with getting an instance of IQueryable<TEntity> interface - the session.EntitySet<>() method creates this object for you.
  • You can use pretty much any of the facilities available on Queryable extension class: Where filters, OrderBy, Skip, Take, projection using Select and "new-auto-object", etc.
  • You can return entities, your custom objects or auto-objects; you can return many or a single object/entity using methods like First(), with optional filter; you can return simple values using methods like Count().
  • You can make joins in your query (SQL joins). One cool thing about VITA is that in many cases you do not need to specify the join explicitly - you can use the entity/entity-list properties instead. In this case the engine adds a SQL join automatically with proper join conditions. In this example we used two such properties:
    • book.Authors list to filter on author's first name
    • book.Publisher property to include publisher's name in the output.

Here is the resulting SQL:

SELECT  b0$."Title" AS "Title", p$."Name" AS "Publisher"
FROM "books"."Book" b0$
     INNER JOIN "books"."Publisher" p$ ON p$."Id" = b0$."Publisher_Id"
WHERE EXISTS 
  (SELECT  *
FROM "books"."BookAuthor" ba$
     INNER JOIN "books"."Author" a0$ ON a0$."Id" = ba$."Author_Id"
WHERE ba$."Book_Id" = b0$."Id" AND a0$."FirstName" = 'John')
ORDER BY b0$."Price"
OFFSET @P1 ROWS FETCH NEXT @P0 ROWS ONLY;

When this statement is executed, the results are converted into a list of auto-objects.

We used 'fluent' syntax to formulate the query (chain of Queryable calls), but we could use the SQL-like syntax:

  var booksByJohn2 = (
           from b in session.EntitySet<IBook>()
           where b.Authors.Any(a => a.FirstName == "John")
           orderby b.Price
           select new { b.Title, Publisher = b.Publisher.Name }
           )
           .Skip(0).Take(5)
           .ToList();

Both forms are equivalent, and the result is the same.

INNER vs OUTER join

In the previous example, all joins in SQL were INNER joins. When deriving joins from entity-type properties, the engine uses the following rule:

If the property linking two entities is nullable, then the resulting join is OUTER. Otherwise, for non-nullable property, the join is INNER.

For example, book.Publisher property is non-nullable (publisher is required for a book), so the join in the SQL is INNER. But if you put [Nullable] attibute on the Publisher property, you will see an OUTER join.

One interesting case that does not come up often, but is worth mentioning - explicit OUTER JOIN (explicit in the sense using join keyword in c# LINQ). The technique involving DefaultIfEmpty method is described here: [https://docs.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins], and it is fully supported by VITA LINQ engine.

Returning entities from a query

The previous query was returning tuples of strings. You can return entities as well:

  var cheapBooks = session.EntitySet<IBook>()
        .Where(b => b.Price < 10)
        .ToList();

There are a few points worth noting:

  • The returned entities are attached to the session - they are 'tracked' by the session. If you modify any of the entities, session 'knows' about it, and the session.SaveChanges() call will submit the updates to the database.
  • The session ensures 'single-instance' rule - there is only one entity, single instance of an entity mapped to a specific row in database table, within a given session. So if you run other queries, before or after, and they return books as well, and one or more of these is 'cheap book', then across all queries' results, this book is represented by the same object(.NET object). Same primary key - same instance.

Because of the 'single-instance' rule, you can always rely on comparing .NET entities as objects, instead of comparing primary key values - it is often easier.

Sometimes it is desirable to return more than one entity per query result row. In this case, we can just return a tuple (auto-object):

  var cheapBooksWithPubs = session.EntitySet<IBook>()
      .Where(b => b.Price < 10)
      .Select(b => new { Book = b, Pub = b.Publisher})
      .ToList();

All the IBook and IPublisher entities returned in tuples are 'normal' entities, tracked by the session instance. This method is useful when you are planning to update both books and their publishers, so you need to load both.

Group-by and aggregates

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

      var booksByCatQuery =
          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)
          };
      var booksByCat = booksByCatQuery.ToList();

Note that not all Queryable.GroupBy overloads are supported - those that have IEqualityComparer parameter are not supported. For other cases, you might eventually get 'groupby type non-supported' error for some non-trivial queries - this just means LINQ engine could not figure out the proper SQL, try to reformulate the query.

We can also use aggregate functions without group-by, over entire table, possibly with WHERE clause. For a single aggregate:

  var progrBooksCount = session.EntitySet<IBook>()
        .Where(b => b.Category == BookCategory.Programming)
        .Count();

If we need to return multiple aggregates, then we use fake group-by clause (it groups all in a single group):

  var progrBooksStats = session.EntitySet<IBook>()
        .Where(b => b.Category == BookCategory.Programming)
        .GroupBy(b => 0)
        .Select(g => new { BookCount = g.Count(), MaxPrice = g.Max(b => b.Price) })
        .ToList();

LINQ Includes - loading related entities

Insert/Update/Delete with LINQ



(to be completed)


              


          



  
          

            


            
Clone this wiki locally