Skip to content

DevGuide

Jump to bottom
Roman Ivantsov edited this page Feb 12, 2020 · 32 revisions

Developer Guide: Step-by-step Walkthrough

Contents

Introduction

In this guide we will show how to create a simple data connected application with VITA ORM framework. We will follow the code-first approach, when you start with the data model defined in c# code and an empty database. As you go, the database 'follows' the app - database tables and other objects are created and modified following the changes in c#-expressed model. db-first scenario is also supported.

We build a simple bookstore, with books, publishers, authors, etc. The extended version of this simple app is a sample Bookstore app in VITA source repo. We use MS SQL Server as a target database, but you can use any of the supported database servers with just minor adjustments.

The full source code for the final version of the demo application is available here: https://github.com/rivantsov/VitaGuideSample

Creating a Data Model

Start Visual Studio (2019 or later) and create a new class library project; language c#, platform .NET Standard 2.0; name it BookStore. Open Nuget package manager, find and install Vita nuget package.

Rename the automatically created class file to Entities.cs, add using Vita.Entities; at the top, delete the class created by wizard.

Entities, in ORM world, are models of database tables - their properties closely match the columns of corresponding tables. They also serve as containers for actual data when reading/writing data from/to database. VITA is a bit different from other ORMs in regards to the choice of the basic entity concept. In other ORMs, entities are usually classes. Sometimes entity class must derive from some special base class to be recognized as an entity, sometimes entities are POCOs (Plain Old c# objects). In VITA, entities are interfaces. Plain old c# interfaces.

The reasons for this design choice are beyond the scope of this guide, but one thing to say - it works pretty well in the end.

You may be wondering - how this can work? With interfaces but not classes, how you create objects? - you cannot create an instance of an interface, at least directly. The answer is that there are classes behind the interfaces - they are generated by VITA on the fly, at application startup (using Proxemity nuget package). You create instances by using factory methods provided by the framework: var bk = session.NewEntity<IBook>();

We start with a very simple entity IPublisher :

  [Entity]
  public interface IPublisher {

    [PrimaryKey, Identity]
    int Id { get; }

    [Size(100)]
    string Name { get; set; }
  }

It has just 2 properties. The entity will be mapped into database table Publisher ('I' is cut-off by default), with Id column set as identity primary key, and column Name - nvarchar(100), non-nullable. Table names, column names and types are inferred, but you can set any of these explicitly using attributes.

We use identity primary keys in this sample app - a common pattern - but you can use GUIDs if you want to (which is actually our recommended way) or any other type. Composite keys are also supported, we will introduce one later.

Once we have a publisher, we can introduce a book entity:

  [Entity]
  public interface IBook {

    [PrimaryKey, Identity]
    int Id { get; }

    [Size(100)]
    string Title { get; set; }

    BookCategory Category { get; set; }

    [Nullable, Size(200)]
    string Description { get; set; }

    int PageCount { get; set; }
    
    decimal Price { get; set; }

    IPublisher Publisher { get; set; }
  }

  public enum BookCategory {
    Programming,
    Fiction,
    Kids,
  }

This entity is a bit more complex. The Nullable attribute indicates 'allow nulls' on the mapped column in the database. Strings are not nullable by default, and need either Size (max size for nvarchar column), or Unlimited to mark as 'nvarchar(max)'.

Value types (int, DateTime) are mapped to non-nullable columns; to make it nullable in database, use nullable c# types like int?. We also have a property of enum type BookCategory. This property is mapped to the 'underlying' int type of the enum - Int32 in this case.

Finally, notice the Publisher property of type IPublisher. It is a reference to another entity, which will be translated into a column Publisher_Id and a foreign key constraint pointing to IBook.Id. At runtime, the property will return the corresponding publisher instance, loaded lazily on the first touch.

Naturally, books have authors. Authors/books is a many-to-many relation, so we use a link table/entity:

  [Entity]
  public interface IAuthor {
    [PrimaryKey, Identity]
    int Id { get; set; }

    [Size(50)]
    string FirstName { get; set; }

    [Size(50)]
    string LastName { get; set; }
  }

  [Entity, PrimaryKey("Book,Author")] 
  public interface IBookAuthor {
    IBook Book { get; set; }
    IAuthor Author { get; set; }
  }

Notice a composite primary key on IBookAuthor table - the actual key on the table will be based on foreign key columns: (Book_id, Author_Id).

We have a data model with four entities. Let's see how it works - let's create the application and run it. We will see our entities turn into database tables.

Entity App

The entities in VITA must be registered with a custom EntityApp -derived class, but not directly. Entities are first grouped into a custom entity module, and then the module is placed into the entity app:

  public class BooksEntityModule: EntityModule {
    public BooksEntityModule(EntityArea area): base(area, "BooksModule") {
      this.RegisterEntities(typeof(IPublisher), typeof(IBook), 
                            typeof(IAuthor), typeof(IBookAuthor));
    }
  }

  public class BooksEntityApp : EntityApp {
    BooksEntityModule _booksModule; 

    public BooksEntityApp() {
      EntityArea area = base.AddArea("books"); // area is equivalent of schema
      _booksModule = new BooksEntityModule(area); 
    }
  }

The BooksEntityApp constructor instantiates our Books module in the context of EntityArea named 'books'. EntityArea is a representation of database schema (ex: dbo). While the entity app represents the whole database, you create one or more schemas and place modules (all its entities/tables) into a particular schema.

The areas/schemas is the first reason for the multi-level entity registration process. You group entities into modules, and then place modules into areas/schemas, which are contained by the entity app. So entities/tables are already pre-grouped, and you can place the groups (modules) into different schemas.
The other reason is to provide a mechanism for componentization. The entity module is essentially a reusable component. You can pack a number of entities and related code into an entity module, and then re-use across multiple applications. An example is VITA's Login module - a complete implementation of login functionality, available as a library/nuget package.

Connect and Run the App

Let's now give our app its first run. We need an executable application to do this. So let's create a new console application project BookStore.Demo, and let's make it .NET Framework 4.6.2 (or higher if you prefer).

Open the Package Manager and install packages Vita, Vita.Data.MsSql. Add reference to the project BookStore we built already.
Next, open Program.cs source file, and put the following code there:

using Vita.Entities;
using Vita.Data;
using Vita.Data.MsSql;

namespace BookStore.Demo {
  class Program {
    static string ConnString = 
       @"Data Source=.;Initial Catalog=VitaGuideApp;Integrated Security=True";
    static BooksEntityApp _app; 

    static void Main(string[] args) {
      try {
        Console.WriteLine("Initializing...");
        Init();
        Console.WriteLine("Done. Connected sucessfully, tables created.");
      } catch(Exception ex) {
        Console.WriteLine("Error!!!");
        Console.WriteLine(ex.ToString());
      } finally {
        Console.WriteLine(); 
        Console.WriteLine("Demo completed, press any key...");
        Console.ReadKey();
      }
    }

    static void Init() {
      _app = new BooksEntityApp();
      var driver = new MsSqlDbDriver();
      var dbSettings = new DbSettings(driver, 
           MsSqlDbDriver.DefaultMsSqlDbOptions, ConnString);
      _app.ConnectTo(dbSettings);
    }
  }
}

We are almost ready to run it - we just need to create the target database. Open SQL Management Studio (SMS) and create a new database VitaGuideApp. Adjust the connection string in ConnString variable if necessary.

Compile and run the console app. If you see an exception printed out, see what's wrong and fix it. When the application runs successfully, it should print the success message. The message says that database tables are created. Let's check it.

Back in SMS, find the Tables node in the database, refresh and expand it. You should see something like this:

SMS tables

We now have tables; as you see, column names and types are derived from the properties of corresponding entities. We can now try some data operations.

Simple read/write operations

Let's create a book and a publisher, save them in the database, and read back. The first step is opening an entity session. Think of a session like an active connection to the database; it is responsible for all read/write operations, and tracking the entities that had been modified.

    var session = _app.OpenSession();
    
    var pub = session.NewEntity<IPublisher>();
    pub.Name = "MS Publishing";
    var bk = session.NewEntity<IBook>();
    bk.Title = "c# programming";
    bk.Category = BookCategory.Programming;
    bk.PageCount = 350;
    bk.Price = 19.99m;
    bk.Publisher = pub;
    session.SaveChanges();
    
    var bkId = bk.Id; // Identity-generated value
    
    //check book counts
    var bkCount = session.EntitySet<IBook>().Count();
    Console.WriteLine($"Done. Create publisher and a book. Book count in db: {bkCount} ");

Once we execute this code, we can go to SMS and verify that records are actually there in the database. The code uses dynamic LINQ statement to retrieve the book count at the end of the snippet. The EntitySet<>() method returns IQueryable<> which can be used to build LINQ expressions that will be translated in SQL and sent to the database. Let's see how to load a book by Id, update it, and delete it:

      var session = _app.OpenSession();
      
      // load 
      bk = session.GetEntity<IBook>(bkId);
      
      // update
      bk.Description = "Hacker's handbook";
      session.SaveChanges();

      // delete
      session.DeleteEntity(bk);
      session.SaveChanges();

Useful extensions for creating entities

The code creating entities in the last snippet has one visual flaw - it takes a lot of lines. If we have to create the same entity in multiple places, that would be ugly. Plus, we might forget to assign something in one of the copies, and will not discover it until it fails at runtime. It would be nice to have a 'constructor' - a function that takes all properties of the new entity and fills it, possibly checking for valid values.

Unfortunately interfaces do not have constructors. But we can do a very close thing using extension methods. We can create an extension of the IEntitySession interface for the entities we define, for example:

  public static class BookExtensions {
    public static IPublisher NewPublisher(this IEntitySession session, string name) {
      var pub = session.NewEntity<IPublisher>();
      pub.Name = name;
      return pub; 
    }
  }

Now we can create a publisher with one line:

  var pub = session.NewPublisher("MS Publishing");

We can do exactly the same for IBook entity, but there is one more trick we can use. A book requires publisher, it is a required non-nullable property. Which means we can implement the book creator method as an extension of IPublisher interface, not IEntitySession. Even better - we do not need to pass the session, we can retrieve it from the publisher entity.

Entity instances are tracked by the originating session, and any entity keeps an internal reference to the parent session. There is a helper method that will get the session from an entity. So here is the book creator method:

  public static IBook NewBook(this IPublisher publisher, string title, 
         BookCategory category, int pageCount, 
         decimal price, string description = null) {
    var session = EntityHelper.GetSession(publisher);
    var book = session.NewEntity<IBook>();
    book.Title = title;
    book.Category = category;
    book.PageCount = pageCount;
    book.Price = price;
    book.Description = description;
    book.Publisher = publisher; 
    return book; 
  }

Then we can create a book in one line:

    var bk = pub.NewBook("c# programming", BookCategory.Programming, 350, 19.9m);

We recommend creating extensions like these for all entities you define. This will make your code much shorter and cleaner.

List properties

VITA provides direct support for entity lists that result from foreign key relations. Let's look again at IBook entity and its Publisher property:

  [Entity]
  public interface IBook {
    // ... ... other properties 

    IPublisher Publisher { get; set; }
  }

The property establishes a foreign key relation between IBook and IPublisher entities. On the publisher side, the relation results in a list of books related to each publisher - naturally, books by the publisher.

Now let's add the following property to IPublisher entity:

    // in IPublisher
    
    [OrderBy("Title:desc")]
    IList<IBook> Books { get; }

VITA understands automatically what this means - a list of books related to the publisher, ordered by Title (descending). The engine examines the target entity ( IBook ) and finds a property/column pointing back to 'this' entity. It then sets up all necessary machinery to handle it for you.

At runtime, when the app reads this property for the first time on some publisher instance, the code behind would load the list and return it:

    var pub = session.GetEntity<IPublisher>(pubId);
    var pubBooks = pub.Books; // lazy load

The loaded list is cached, so on consequitive reads the cached value is returned.

You may ask: what if I have more than one property linking the child to parent? This scenario is supported - all you need to do is specify which back-link property to use with additional EntityRef attribute.

Publisher-books is a one-to-many relation: one publisher - many books. VITA also supports many-to-many relations like books-authors. We can define list properties on any side of the relation.

Loading authors for a book would require building a join. VITA provides direct support for this case, making it much easier to manage many-to-many relations. We can expose the target list (authors) as a property on the other entity (book). And the other way around. We add both list properties:

    // in IBook entity 
    [ManyToMany(typeof(IBookAuthor)), OrderBy("LastName,FirstName")]
    IList<IAuthor> Authors { get; }

    // in IAuthor entity 
    [ManyToMany(typeof(IBookAuthor)), OrderBy("Title")]
    IList<IBook> Books { get; }

For many-to-many lists we have to explicitly specify the link entity using the ManyToMany attribute.

Now when you read the book.Authors property ...

  var bkAuthors = bk.Authors;

... VITA engine automatically runs the join query to fill up the list, something like:

SELECT  ba$."Author_Id", ba$."Book_Id", a$."Id", a$."FirstName", a$."LastName"
FROM "books"."BookAuthor" ba$
     INNER JOIN "books"."Author" a$ ON a$."Id" = ba$."Author_Id"
WHERE ba$."Book_Id" = @P0
ORDER BY a$."LastName", a$."FirstName";

Adding an author to a book is simple - just add the author to the book.Authors list. VITA will take care of the link record IBookAuthor. Let's create two authors and add them to the book:

  var john = session.NewAuthor("John", "Sharp");
  var jack = session.NewAuthor("Jack", "Hacker");
  bk.Authors.Add(john);
  bk.Authors.Add(jack); 
  session.SaveChanges();

If you want to 'break' the relation between a book and an author, you remove the author entity from book.Authors list. You can use the list on either side of the relation, but only one of them.

Logging and diagnostics

For a complex system like an ORM, it is important to be able to see what's going on inside - like seeing SQLs executed for entity-level actions in your code. VITA can produce a detailed log file - just assign the entityApp.LogPath property. It can be either a file name (then it will be in 'bin' folder), or full file path:

  bookEntityApp.LogPath = "booksLog.txt";

The resulting log will mostly contain SQL statements executed, with stats (time, # of records), and errors if any. If you want a separate errors-only file, you can assign the ErrorLogPath property - this file will contain errors only, without verbose output.

To retrieve the last executed SQL in code (while testing or debugging), get the last Db command like this:

  var book = session.GetEntity<IBook>(bookId); 
  IDbCommand cmd = session.GetLastCommand();

cmd.CommandText contains the SQL executed, and parameters collection has parameter values if any. When debugging the code and stopped on breakpoint you can always reach this last command by expanding the 'session' variable in Watch or Locals window in VS Debugger.

Use these facilities to inspect SQLs and other data about operations under the hood, check timing of certain complex queries, etc. Even if VITA builds SQLs for you, and does it well in most cases, you need to check regularly what happens - just review to logs after execution, you may spot something that needs fixing.

You can also direct the logs to a database. Vita.Modules.Logging package provides this functionality, and WebTests project in source repo demonstrates it. The tests write the log of all REST calls coming to the server, with timing and stats, along with all SQL that were executed when processing the request.

Querying with LINQ

You can use LINQ to formulate your SELECT query as a strongly-typed expression over entities, and let VITA translate it into SQL and handle everything involved in actual database operation, including materializng entities that are returned as a result.

Let's query for inexpensive programming books, order by Price:

  var cheapBooks = session.EntitySet<IBook>()
            .Where(b => b.Price < 20 && b.Category == BookCategory.Programming)
            .OrderBy(b => b.Price)
            .ToList();

The query returns a list of IBook entities that match the criteria. Here is the SQL that was executed:

SELECT  "Id", "Title", "Category", "Description", "PageCount", "Price", "Publisher_Id"
FROM "books"."Book"
WHERE "Price" < 20 AND "Category" = 0
ORDER BY "Price";

Any LINQ query starts with the call session.EntitySet() - it returns IQueryable<> instance which is then used to build a query.

In general VITA LINQ engine supports all functionality available the static methods of the Queryable class in System.Linq namespace: with filters, joins, ordering, paging (take/skip), projecting into auto-objects, etc. But note, that not all LINQ translations are possible, not all c# functions or conversions have equivalent in SQL. VITA may reject some queries, or they may fail at the server.

There is one nice aspect about VITA's implementation of LINQ - it makes joins easier in many cases. When you use entity properties that are references to entities (ex: book.Publisher), or entity lists (ex: book.Authors), the translation engine adds necessary joins automatically. For example:

  var booksByJohn = session.EntitySet<IBook>()
        .Where(b => b.Authors.Any(a => a.FirstName == "John"))
        .Select(b => new { b.Title, Publisher = b.Publisher.Name})             
        .ToList();

We are selecting books with an author with first name John, and return tuples (title, publisher name). Here is the 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');

As you see, the engine adds a join to publisher to return its name, and a sub-query for authors and link tables. We did not have to use join syntax to produce this query.

This was a quick overview of LINQ functionality in VITA. See more in a separate document:

Developer guide: LINQ

One really useful feature - you can create not only SELECTs, but also insert/update/delete statements using LINQ expressions. So for example, you can make a LINQ-based command to change price of some books based on some criteria.

Database indexes

When we first executed our demo app, VITA created all database objects - tables, primary and foreign keys. VITA provides ways to control and manage the database schema right from the c# code. We will not go into all details here, this is a subject of another document. We will just show how to add one important thing that was missing in our data model - indexes.

The following attributes control database indexes:

[Index] - creates a non-unique index

[Unique] - creates a unique index

[ClusteredIndex] - creates a clustered index

When one of these attributes without parameters is placed on a property, it identifies an index based on a single column. There is another form - with property name list as a parameter. We place it on entity and it identifies a composite index. For example, let's make publisher name unique, so no two publishers with the same name:

  [Size(100), Unique]
  string Name { get; set; }

Let's add a composite clustered index on a book:

  [Entity, ClusteredIndex("Category,Id")]
  public interface IBook {

and also an index on Price:

    [Index]
    decimal Price { get; set; }

Now if you run the app again, you will see these indexes created in the database. Pretty easy! Notice that there are some extra indexes there - for example, index on book.Publisher_id column. This is an index on foreign key column. By default, MS SQL Server does not index foreign key columns. VITA adds this index automatically if there is a list property on the target entity. There is a publisher.Books list property, so probably the app will retrieve books by publisher Id quite often - so VITA creates an index automatically.

Read more about database schema control in a separate document:

Developer guide: database schema.

Conclusion

That concludes our step-by-step walk through the process of creating a simple app with VITA. There is more to explore - read other guides (under construction) or browse the source code of tests and samples in the project repository.

Clone this wiki locally