This project is read-only.

VITA Tutorial

Part 2. More attributes, entity lists, computed attributes, paging

Outline

In this part of the tutorial we will see how to create and use list-type properties of entities mapping to either one-to-many or many-to-many relationships in the database; we will use Enum-typed properties; computed properties; we will use various VITA-defined attributes in our entities to invoke some automatic behavior; we will introduce a convenient practice of using extension methods for manipulating entities; finally, we will see how to perform paged queries.

Extending the Books model: more entities and attributes

Let's start with an expanded definition of the IPublisher entity:

  [Entity, OrderBy("Name")]
  public interface IPublisher {
    [PrimaryKey, Auto]
    Guid Id { get; } // no need for setter with Auto attribute
    string Name { get; set; }
    IList<IBook> Books { get; }
  }

There are a few new things in this in this definition.
  • Auto attribute on Id property - this simply tells the framework to auto-generate the Id value for new publisher instances - we no longer need to assign it in the code.
  • OrderBy on the interface itself - this specifies the default ordering when we return entity lists using 'session.GetEntities<IPublisher>()' method. You can specify more than one property name here (names separated by comma), each property name may be followed by ':DESC' specification.
  • Books property, a list of IBook entities. It returns a list of books published by a publisher. It all happens automatically - when the system 'sees' this property during initial model analysis, it tries to find a back reference from IBook to IPublisher - there is such property, IBook.Publisher. So it puts an automatic query action that is invoked when application code first time reads the Books property - the returned list is filled up with related IBook entities.

Note on entity list properties. If there are two or more foreign keys on the target entity (like IBook) pointing back to parent - then you should use OneToMany attribute and explicitly specify which back-reference property to use.
Next, the IBook entity. We define two enum types that we use as types for new properties:

  [Flags]
  public enum BookEdition {
    Paperback = 0x01,
    Hardcover = 0x02,
    EBook = 0x04,
  }

  public enum BookCategory {
    Programming,
    Fiction,
    Kids,
  }

  [Entity, OrderBy("PublishedOn:DESC"), Paged]
  public interface IBook {
    [PrimaryKey, Auto]
    Guid Id { get; } 
    [Size(50)]
    string Title { get; set; } 
    DateTime PublishedOn { get; set; }
    [Size(250), Nullable]
    string Description { get; set; }
    [Memo, Nullable]
    string Abstract { get; set; }
    BookCategory Category { get; set; }
    BookEdition Editions { get; set; }
    double Price { get; set; }
    IPublisher Publisher { get; set; }

    [ManyToMany(typeof(IBookAuthor))]
    IList<IAuthor> Authors { get; }
  } 
New things in this entity definition:
  • Enum-typed properties - VITA supports these just like other basic .NET types.
  • We had seen already the OrderBy attribute; here the property name is appended with ':DESC' to indicate that the default sort order for books is by descending publishing date.
  • Paged attribute instructs the framework to execute paging in the database. With this attribute or not, any query can use 'take' and 'skip' paging parameters. But for entities without Paged attribute the paging will be performed outside the database. This might be reasonable if the target table contains limited number of rows, so it is simpler to get all and then cut the page in .NET code, rather than running more complex query with paging.
  • Size attribute - we use it to specify the size of a string (nvarchar) column in the database. Without this attribute the framework assumes the default size specified in the EntityModelSetup class (50) - you can change this default when you start up your application.
  • Nullable attribute specifies that the value for the property is optional. It has two effects: first, it specifies that the corresponding column in the database allows NULL values. Secondly, the validation routine for the value (run before saving the data) checks each property value; with Nullable attribute it would allow a null or empty value to be submitted to the database. Without it, the value is required and if not set, the SaveChanges() call will be aborted and the database operation is not even started.
  • Memo attribute identifies a property as an "unlimited string" type. In the database, the corresponding column would have a Memo data type (nvarchar(Max) for MS SQL Server).
  • Authors property is decorated with ManyToMany attribute. We now have multiple authors for a book. The Authors property obviously returns the authors which is a list of IAuthor entities, linked to IBook through many-to-many relationship implemented through IBookAuthor 'linking' entity:

  [Entity, Paged, OrderBy("LastName,FirstName")]
  public interface IAuthor {
    [PrimaryKey, Auto]
    Guid Id { get; }
    string FirstName { get; set; }
    string LastName { get; set; }
    [ManyToMany(typeof(IBookAuthor))]
    IList<IBook> Books { get; }

    [Computed(typeof(BookExtensions), "GetFullName"),
        DependsOn("FirstName,LastName")] 
    string FullName { get; }
  }

  [Entity, PrimaryKey("Book,Author")]
  public interface IBookAuthor {
    IBook Book { get; set; }
    IAuthor Author { get; set; }
  }
  • The OrderBy attribute has two field names separated by comma to specify multiple columns for default sorting order of IAuthor entities.
  • The PrimaryKey attribute on IBookAuthor entity is a bit different from what we had seen before. First, it is placed at entity level, not property. Second, it has a parameter that lists two property names. This is the way to specify composite primary key for entities that have them. Note that actual key in the database will contain the ID columns - foreign keys referencing the Book and Author tables - the framework will make the substitute automatically.
  • Computed FullName property - see section below for a detailed discussion of this facility.
  • Another many-to-many list property Books on IAuthor entity, also decorated with ManyToMany attribute.

Entity lists: one-to-many and many-to-many relations

Both one-to-many and many-to-many relations in the database are expressed as entity references and entity-list typed properties in the entity model. Notice the difference in the definition.
The one-to-many IPublisher.Books property did not use any extra attributes . For one-to-many, the system is able to deduce automatically all information for property implementation. For many-to-many relation, there is an extra entity/table involved - linking entity (IBookAuthor). We have to specify it explicitly using ManyToMany attribute.
There is another difference between these two relations - in the way we add/remove links at runtime. For one-to-many, we simply set the reference property on child entity to target parent; for many-to-many, we add the child to the list in the property; the system will add the link entity automatically:

  book.Publisher = pub; //one-to-many
  book.Authors.Add(johnSharp); //many-to-many - creates link entity automatically

Important: For entity list properties, always use IList<> -based type (based on interface), not List<> generic type.
Another important thing about entity lists: all entity lists in VITA implement INotifyCollectionChanged interface, which is used in WPF binding engine, so the lists are readily bindable. This includes list-type properties on entities, and lists returned by IEntitySession.GetEntities() method.

Explicitly ordered lists

Sometimes child entity lists are not ordered using some property, but the order is specified explicitly by the application code. Usually the solution is to define an additional property on child entity like LineNumber, and use it to save the order. VITA defines an attribute [PersistOrderIn(propertyName)] - just add it to the list property, and VITA will take care of assigning proper numbers whenever your code rearranges entities in the list.

Computed properties

It is a very common situation for an entity to have a property with the value that is derived from other properties of the entity. For example, we may associate a FullName property with a person, and its value is a combination of the first and last names. While you can define a helper method GetFullName() to compute full name, it is often convenient and natural to have a property FullName on the entity itself. For some UI binding implementations it might be a requirement to have a property, not a method as a binding target.
VITA provides an easy way to define a computed property on an entity. The Computed attribute identifies a property as computed and specifies the method that performs the computation.
The method GetFullName is static and is defined in BookExtensions class:

    public static string GetFullName(IAuthor author) {
      return author.FirstName + " " + author.LastName;
    }

The method used for computed property must be a function returning a value, with single parameter matching the type of the entity. We can now use this property as any other "real" property:

  Console.WriteLine("  Author: " + author.FullName);

The optional attribute DependsOn on FullName property lists the properties that this property depends on. Entity instances at runtime are objects implementing the standard .NET INotifyPropertyChanged interface. Each time you modify a property, the entity fires the property-changed event. For complex binding scenarios, it is convenient to have the object fire property-changed for computed property whenever any of its 'parts' change - this behavior is provided using the DependsOn attribute. Each time you change FirstName or LastName, the entity raises 2 PropertyChanged events: one for the property you assign like FirstName, and the other one for the FullName.

Extension methods

For complex entity models, it is convenient to define various extension methods for creating new entities, or for some other complex operations on entities. This way the code for a new entity collapses into a single line. Let's define a static extension class for entities in our model - we will use these methods in our sample code:

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

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

    public static IBook NewBook(this IEntitySession session, BookEdition editions, 
         BookCategory category, string title, string description, IPublisher publisher, 
         DateTime publishedOn, double price) {
      var book = session.NewEntity<IBook>();
      book.Editions = editions;
      book.Category = category; 
      book.Title = title;
      book.Description = description;
      book.Publisher = publisher;
      book.PublishedOn = publishedOn;
      book.Price = price;
      return book;
    }

  }

We now can use these methods to create entities in one line:

  var john = session.NewAuthor("John", "Sharp"); 

Opening an entity store and creating sample entities

The definition of entity model is very similar to the code in Part 1, except we now register more entities in the entity module constructor:

  public class MainBooksModule : EntityModule {
    public MainBooksModule(EntityArea area)  : base(area, "Main") {
      RegisterEntities(typeof(IBook), typeof(IPublisher), typeof(IAuthor), typeof(IBookAuthor));
    }
  }

The startup code to open an entity store is the same. Let's create a few sample entities using the extension methods:

      var session = entityStore.OpenSession();
      var msPub = session.NewPublisher("MS Publishing");
      var john = session.NewAuthor("John", "Sharp");
      var jack = session.NewAuthor("Jack", "Pound");
      var csBook = session.NewBook(BookEdition.Hardcover, BookCategory.Programming, 
        "c# Programming", "Expert c# programming", msPub, DateTime.Today.AddYears(-1), 25); 
        csBook.Authors.Add(john);
      var vbBook = session.NewBook(BookEdition.EBook | BookEdition.Paperback, 
          BookCategory.Programming, "VB Programming", "Expert VB programming", msPub, 
          DateTime.Today, 15); 
      vbBook.Authors.Add(jack);
      vbBook.Authors.Add(john);
      session.SaveChanges(); //Submit to database
      //let's remember some IDs, we'll use them later
      var msPubId = msPub.Id;
      var vbBookId = vbBook.Id;

Notice how we link books and authors - we simply add an author to the book.Authors list property - the framework automatically creates the link entity IBookAuthor. Could not be easier!
Let's see now how the relations work. First let's load a publisher and print out all its books using the publisher.Books property:

      session = entityStore.OpenSession(); 
      var pub = session.GetEntity<IPublisher>(msPubId);
      Console.WriteLine("  Loaded publisher: " + pub.Name);
      foreach(var bk in pub.Books)
        Console.WriteLine("  Book: " + bk.Title);

The above code prints 2 books from MS publisher. Next, let's try the many-to-many relation. The following code loads a book and prints all its authors:

      var book = session.GetEntity<IBook>(vbBookId);
      Console.WriteLine("  Loaded a book: " + book.Title);
      foreach(var author in book.Authors)
        Console.WriteLine("  Author: " + author.FullName);

The code prints a book title and its two authors. Just like in the previous case for publisher.Books list, the book.Authors list is loaded "on-demand", in lazy fashion - when we read the property, the system runs a query loading all book authors. Notice that we used a computed property FullName to print a full name of the author.

Paging entity lists

In this section I would like to show how to perform paging of the query results. In the real-world applications, most of the tables in the database contain a huge number of rows. It is not possible to load the entire table into memory for processing - so we need some paging mechanism, to load entities page-by-page. VITA framework supports a simple and convenient paging mechanism. All you need to do is specify two optional parameters skip and take when you call the session.GetEntities<T>() method.
Let's look at the example:

      var allBooks = session.GetEntities<IBook>();
      // Books are ordered by Published date, desc. 
      // CS book is published a year ago, VB book is published today. 
      // If we query with skip=1, we should get CS book only.
      var pagedBooks = session.GetEntities<IBook>(skip: 1, take: 1);
      var title = pagedBooks[0].Title;
      Console.WriteLine("  Loaded book: " + title);
      // the code should print the title "c# Programming"

There are in fact two paging mechanisms supported by VITA.
If you specify the Paged attribute on the entity definition (as we did for the IBook entity), then paging is performed in the database. VITA generates a SELECT stored procedure with paging parameters - have a look at the 'BookSelectAllPaged' stored procedure to see how it is implemented.
Some tables are expected to be small and contain a limited number of rows. For example, we may expect a limited number of publishers in our model. For these entities running a complex paging query does not bring any benefits. So we did not put the Paged attribute on the IPublisher entity. As a result, queries against this entity/table do not use paged SQL. You can still use skip and take parameters in the GetEntities<>() call, but cutting off a page will be performed in c# code, after retrieving all entities.

Conclusion

The following image shows the sample code output:

tut_part2_output.jpg


Tutorial Home

Last edited Mar 29, 2013 at 4:34 AM by rivantsov, version 45

Comments

No comments yet.