(DEPRECATED documentation)

VITA Features Demo - Code Listing

The following code is part of download zip, demo project. It demonstrates the key features and capabilities of VITA framework:
  • Defining entities as .NET interfaces
  • Specifying database table keys and indexes using entity attributes or attributes on "companion" types
  • Creating database schema objects from entity model defined in c# code
  • Creating a data module with entities and custom stored procedures
  • Using the module and its entities in application code to create, load, update and delete entity records
  • Defining computed properties/columns
  • Performing built-in and custom validation on entities before submitting changes to database
  • Using direct LINQ queries with entity model
  • Using custom stored procedures defined in the model
The data model defines entities for a simplistic Books database, containing books, publishers and authors. Each book has a publisher (one-to-many relation from publisher to books), and zero or more authors (many-to-many relation).

Books entities:

  public enum BookType {

  [Entity, Validate(typeof(BooksModule), "ValidateBook")]
  public interface IBook {
    [PrimaryKey, Auto]
    Guid Id { get; set; }
    IPublisher Publisher { get; set; }
    string Title { get; set; } //uses default length
    [Size(250), Nullable]
    string Description { get; set; }
    int Quantity { get; set; } 
    BookType Type { get; set; }
    double Price { get; set; }
    IList<IAuthor> Authors { get; }

  public interface IPublisher {
    [PrimaryKey, Auto]
    Guid Id { get; set; }
    string Name { get; set; }
    IList<IBook> Books { get; }

  public interface IAuthor {
    [PrimaryKey, Auto]
    Guid Id { get; set; }
    string FirstName { get; set; }
    string LastName { get; set; }
    [Computed(typeof(BooksModule), "GetFullName")]
    string FullName { get; }
    IList<IBook> Books { get; }

  public interface IBookAuthor {
    IBook Book { get; set; }
    IAuthor Author { get; set; }

Books Keys and Indexes

  // This file contains companion types for Books entities. 
  // Companion types allow you to set some attributes (like DB keys and indexes) on companion types rather than
  // on entities themselves. You can put all database artifacts definitions in a separate c# file with companion types
  // and put the database admin in charge of this file, so he can fine-tune this stuff without clashing with other 
  // developers which work with entities in the middle tier.
  // You register companion types using the RegisterCompanionTypes method of EntityModelSetup class. 
  // There are 2 ways to link companion type to entity type
  //  1. Inheritance - companion type inherits from entity type; that's what we do here
  //  2. Using ForEntity attribute on companion type

  public interface IBookKeys : IBook {
    new string Title { get; set; }

  public interface IPublisherKeys : IPublisher {
    new string Name { get; set; }

  public interface IAuthorKeys : IAuthor {
    new string LastName { get; set; }

  public interface IBookAuthorKeys : IBookAuthor { }

Books module and extensions class

  //We could register Book entities directly at program startup. But for better code modularity, we create 
  // a data module that handles all book-related functionality - including entity registration, stored procedures, etc.
  // We also define a small static extension class to add handy entity-creation methods.
  public class BooksModule {

    string _schema;
    DbCommandInfo _getBooksByAuthorCommand, _changePriceCommand, _deleteBooksZeroQuantity;

    public BooksModule(EntityModelSetup modelSetup, string schema) {
      _schema = schema;
      // We need to register only one root type, the rest will be discovered automatically
      modelSetup.RegisterEntities(schema, typeof(IBook)); 
      //Register companion types that describe keys and indexes on entities
        typeof(IBookKeys), typeof(IAuthorKeys), typeof(IPublisherKeys), typeof(IBookAuthorKeys)); 
      modelSetup.CompilingCommands += Model_CompilingCommands;

    //Define and compile stored procedures for custom Select, Update, Delete operations using LINQ syntax.
    // This method will be called during model activation: by firing the CompilingCommands event 
    // the model suggests to custom modules to create stored procedures they will use.
    private void Model_CompilingCommands(object sender, CompileCommandsEventArgs e) {
      var compiler = e.Database.CommandCompiler;
      var books = compiler.EntitySet<IBook>();
      var pubs = compiler.EntitySet<IPublisher>();
      var authors = compiler.EntitySet<IAuthor>();
      var bookAuthors = compiler.EntitySet<IBookAuthor>();

      //Let's use 3-table join and find all books of an author identified by name
      var queryGetByAuthor = from bk in books
                             join ba in bookAuthors on bk equals ba.Book
                             join au in authors on ba.Author equals au
                             where au.LastName == "?"
                             select bk;
      _getBooksByAuthorCommand = compiler.CompileCommand<IBook>(CommandKind.Select, _schema, "BooksGetByAuthor", queryGetByAuthor, 1);

      //Update query must return auto-type containing two kinds of fields: primary key to identify the record, 
      // and fields to be updated in the record identified by PK
      var factor = 1.1d;
      var bkType = BookType.Hardcover; //does not matter, will turn into parameter
      var changePriceQuery = from p in books
                             where p.Type == bkType
                             select new { Id = p.Id, Price = p.Price * factor };
      _changePriceCommand = compiler.CompileCommand<IBook>(CommandKind.Update, _schema, "BooksChangePrice", changePriceQuery, 2);

      //Delete query must return ID's of books to be deleted
      var delQuery = from b in books
                     where b.Quantity == 0
                     select new { b.Id };
      _deleteBooksZeroQuantity = compiler.CompileCommand<IBook>(CommandKind.Delete, _schema, "BooksDeleteZeroQuantity", delQuery, 1);

    //Methods executing stored procedures 
    public IList<IBook> GetBooksByAuthor(IDataSession session, string lastName) {
      return session.QueryEngine.ExecuteSelect<IBook>(_getBooksByAuthorCommand, lastName).ToList();

    public void ChangePrice(IDataSession session, BookType bookType, double changePerc) {
      var factor = (100.0 + changePerc) / 100.0d;
      session.QueryEngine.ExecuteNonQuery(_changePriceCommand, bookType, factor);

    public void DeleteBooksWithZeroQuantity(IDataSession session) {
      session.QueryEngine.ExecuteNonQuery(_deleteBooksZeroQuantity, 0);

    // Static method computing FullName computed property for an Author
    public static string GetFullName(IAuthor author) {
      return author.FirstName + " " + author.LastName;

    // Static method validating Book entity
    public static void ValidateBook(DataValidator validator, IBook book) {
      validator.Check(book.Price > 0.01, book, "price", "PriceNegative", "Book price must be greater than 1 cent.");

    public void DeleteAll(Database database) {


  //Helper methods to create entities
  public static class BookExtensions {
    public static IBook NewBook(this IDataSession session, BookType type, string title, string description, 
         IPublisher publisher, double price, int quantity) {
      var book = session.NewEntity<IBook>() ;
      book.Type = type;
      book.Title = title;
      book.Description = description;
      book.Publisher = publisher; 
      book.Price = price;
      book.Quantity = quantity;
      return book;

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

    public static IAuthor NewAuthor(this IDataSession session, string firstName, string lastName) {
      var auth = session.NewEntity<IAuthor>();
      auth.FirstName = firstName;
      auth.LastName = lastName;
      return auth;


Code using the Books module:

      Write(" ");
      Write(" ");
      //Setup model, initialize Books module, create database model, update schema -------------------------------------------------
      Write("Constructing Entity model...");
      var modelSetup = new EntityModelSetup("Books");
      // The following settings are in fact defaults; 
      // change it to run without stored procs (direct SQL only) or without ref integrity in database;
      modelSetup.Settings.Options = ModelOptions.UseStoredProcs | ModelOptions.UseRefIntegrity;
      var booksModule = new BooksModule(modelSetup, "books");
      var model = modelSetup.CreateModel(); //created Entity Model
      var db = model.Connect(connectionString); //created Database object
      Write("Updating database schema...");

      //Delete old records so they do not impact record counts in assertions below 
      Write("Deleting all old data...");
      Write(" ");
      //Create some entities in database: publishers, authors, books -----------------------------------------------------
      var session = db.OpenSession();
      Write("Creating test data in database...");
      var msPub = session.NewPublisher("MS Books"); //we are using extension method here
      var kidPub = session.NewPublisher("Kids Books");
      var john = session.NewAuthor("John", "Sharp");
      var jack = session.NewAuthor("Jack", "Pound");
      //Books on programming from MS Books
      var csBook = session.NewBook(BookType.Paperback, "c# Programming", "Expert programming in c#", msPub, 20.0, 10);
      csBook.Authors.Add(john); //this is many-to-many
      var vbBook = session.NewBook(BookType.Paperback, "VB Programming", "Expert programming in VB", msPub, 25.0, 10);
      //Folk tale, no authors
      var kidBook = session.NewBook(BookType.Hardcover, "Three little pigs", "Folk tale", kidPub, 10.0, 15);
      //Let's remember some ID's, we'll use them later
      var msPubId = msPub.Id; 
      var csBookId = csBook.Id; 
      var vbBookId = vbBook.Id;
      var kidBookId = kidBook.Id;
      session.SaveChanges();  //Save all
      Write("  Done. Created 2 authors, 2 publishers and 3 books.");
      Write(" ");

      //Load all books and print titles 
      Write("Reading back all books:");
      session = db.OpenSession();
      var allBooks = session.GetAll<IBook>().ToList();
      AssertTrue(3 == allBooks.Count, "Invalid # of books");
      foreach (var bk in allBooks)
        Write("  Book: " + bk.Title + " from " + bk.Publisher.Name);
      Write(" ");
      //Loading entities by primary key --------------------------------------------------------
      // Verify that parent objects referenced by the same FK value result in the same object
      Write("Loading books by Book Id:");
      session = db.OpenSession();
      // load book by ID, check the title
      csBook = session.GetEntity<IBook>(csBookId);
      AssertTrue(csBook != null, "Failed to find the book by Id.");
      AssertTrue(csBook.Title == "c# Programming", "Loading book by Id: wrong book loaded, title mismatch.");
      vbBook = session.GetEntity<IBook>(vbBookId);
      AssertTrue(vbBook != null, "Failed to find the book by Id.");
      Write("  Done: Loaded and verified 2 programming books.");
      //Compare publishers as objects
      object csPubObj = csBook.Publisher;
      object vbPubObj = vbBook.Publisher;
      AssertTrue(csPubObj != null, "Publisher is null!");
      AssertTrue(csPubObj == vbPubObj, "c# and vb books publishers is not the same Publisher instance.");
      Write("  Verified: both loaded books reference the same Publisher instance.");
      Write(" ");

      // Validation  ------------------------------------------------------------------------------------
      Write("Entity validation: trying to save entities with errors.");
      session = db.OpenSession();
      var invalidAuthor = session.NewAuthor(null, "VeryLoooooooooooooooooooooooooooongLastName");
      var invalidBook = session.NewBook(BookType.EBook, "Not valid book", "Some invalid book", null, -5.0, 10);
      //We expect 4 errors: Author's first name should not be null; Author's last name is too long; Publisher cannot be null;
      // Price must be > 1 cent. The first 3 errors are found by built-in validation; the last error, price check, is added
      // by custom validation method.
      try {
      } catch (ValidationException vex) {
        AssertTrue(vex.Errors.Count == 4, "Found validation errors.");
        foreach (var err in vex.Errors)
          Write("  Error: " + err.ToString());
      Write(" ");

      // Entity lists, one-to-many -----------------------------------------------------------------------
      // For a publisher, get the books (Publisher.Books)
      Write("Entity Lists, one-to-many. ");
      Write("Loading a publisher and enumerating its Books property: ");
      session = db.OpenSession();
      msPub = session.GetEntity<IPublisher>(msPubId); 
      AssertTrue(2 == msPub.Books.Count, "Invalid # of books from MS Books");
      foreach (var bk in msPub.Books)
        Write("  Book: " + bk.Title);
      Write(" ");

      //Entity lists, many-to-many -----------------------------------------------------------------------
      Write("Entity Lists, many-to-many... ");
      Write("Loading a book (about c#) and enumerating its Authors property: ");
      session = db.OpenSession();
      csBook = session.GetEntity<IBook>(csBookId);
      AssertTrue(2 == csBook.Authors.Count, "Invalid authors count for c# book");
      foreach (var a in csBook.Authors)
        Console.WriteLine("  Author: " + a.FullName);
      Write(" ");

      //Direct LINQ query ----------------------------------------------------------------------------------
      Write("Direct LINQ query... ");
      Write("  Finding books by publisher's name 'MS Books':");
      session = db.OpenSession();
      var books = session.Linq.EntitySet<IBook>(); 
      //let's find books by publisher's name
      var msbooks = from b in books
                    where b.Publisher.Name == "MS Books"
                    orderby b.Title
                    select b;
      var msBookList = msbooks.ToList();
      foreach (var b in msBookList)
        Console.WriteLine("  Book: " + b.Title);
      //Records retrieved by LINQ are attached to session, they are updatable.
      // Change the price of c# book (from 20 to 10) and save it; read it again and check that the price has changed
      Write("Updating a book retrieved by LINQ query: ");
      Write("  Changing price of c# book from 20 to 10.");
      csBook = msBookList[0]; //we know it's the first book, we sort by title
      csBook.Price = 10.00; 
      session = db.OpenSession(); //start another session to make sure we load fresh version
      csBook = session.GetEntity<IBook>(csBookId);
      AssertTrue(Math.Abs(csBook.Price - 10) < 0.01, "Book price did not change to 10.");
      Write("  Done, verified. ");
      Write(" ");

      // Using SELECT custom stored proc ------------------------------------------------------------------
      Write("Using SELECT custom stored procedure BooksGetByAuthor.");
      // Get books by author name
      session = db.OpenSession();
      var booksByJack = booksModule.GetBooksByAuthor(session, "Pound");
      AssertTrue(2 == booksByJack.Count, "Found wrong # of books by Jack Pound.");
      Write("  Done, found 2 books by Jack.");
      Write(" ");

      // Using UPDATE custom stored proc ------------------------------------------------------------------
      Write("Using UPDATE custom stored procedure BooksChangePrice.");
      Write("  Reducing price by 20% on all hardcover books.");
      //Use stored proc to reduce the price by 20% on all hardcovers 
      // We have Kid book in Hardcovers, original price 10
      booksModule.ChangePrice(session, BookType.Hardcover, -20);
      //check that changes actually took place
      session = db.OpenSession();
      kidBook = session.GetEntity<IBook>(kidBookId);
      //Original price 10, now it should be 8
      AssertTrue(Math.Abs(8 - kidBook.Price) < 0.01, "Kids book price is not reduced after discount.");
      Write("  Done, verified - hardcover book price changed from 10 to 8.");
      Write(" ");

      // Using DELETE custom stored proc -------------------------------------------------------------------
      Write("Using DELETE custom stored procedure BooksDeleteZeroQuantity.");
      Write("  Preparation: load a book, change its quantity to 0, update it.");
      // Set kids book quantity to zero, then delete all books with zero quantity using stored procedure
      session = db.OpenSession();
      kidBook = session.GetEntity<IBook>(kidBookId);
      kidBook.Quantity = 0;
      Write("  Done.");
      Write("  Now delete books with zero quantity using stored proc BooksDeleteZeroQuantity.");
      //check it is deleted
      session = db.OpenSession();
      kidBook = session.GetEntity<IBook>(kidBookId);
      AssertTrue(kidBook == null, "Books with zero quantity were not deleted");
      Write("  Done, verified - book deleted.");

      Write(" ");
      Write("End Demo");
      Write("Have a look at the database populated by this demo - tables, indexes, stored procedures, data, etc.");

Last edited Feb 10, 2012 at 3:08 AM by rivantsov, version 21


No comments yet.