WARNING: Creating custom stored procedures from LINQ queries is temporarily disabled in VITA framework - it is being refactored and will be enabled soon.

VITA Tutorial

Part 5. Creating custom stored procedures using LINQ expressions.


VITA framework provides a robust way to create custom stored procedures for performing specialized operations. You can define the 'semantics' of custom SELECT, UPDATE, INSERT, DELETE operations using LINQ query syntax at application setup. These definitions are then translated into custom stored procedures in the database. There are several advantage of this approach, compared to directly coding the procedures in SQL:
  • it is coded in c#, and without switching the environment the developer gets full access to database-level data management features;
  • the procedures are automatically in-sync with the data model as it is expressed in entity definitions;
  • procedures are created/updated in the database automatically, whenever the procedure is not found in the database, or its definition does not match the 'current' version.

Custom commands

The 'custom command' is a VITA framework equivalent of a stored procedure in the database. We define custom commands using LINQ expressions, and they are translated into SQLs and then into stored procedures; they will be available to the application code as plain c# methods. Commands are defined in entity modules, in its constructors. The references to commands - IEntityCommand instances - are saved in module-level fields and then used in dedicated module-level methods to execute at runtime.
For our books entity model, we will create two commands:
  • A command that selects all books for author(s) with a given last name
  • A command that modifies the price of all books in a given category by a fixed factor.
First, we need to define two class-level fields to hold our commands (inside the BooksModule class):

    private IEntityCommand _getBooksByAuthorCommand, _changePriceCommand;

IEntityCommand is an interface that represents a custom command over entities. The details of the interface are not important - your code mostly passes it around as a reference to the 'real' command in the database. We create the commands directly in the constructor of the BooksModule :

      var books = this.EntitySet<IBook>();
      var authors = this.EntitySet<IAuthor>();
      var bookAuthors = this.EntitySet<IBookAuthor>();
      var qryBooksByAuthor = 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 == Param<string>(30)
                             select bk;
      _getBooksByAuthorCommand = this.CreateSelect<IBook>("BooksGetByAuthorLastName", 
              "Gets books for author(s) with a given last name.", qryBooksByAuthor);
      var qryChangePrice = from bk in books
                             where bk.Category == Param<BookCategory>()
                             select new { Id = bk.Id, Price = bk.Price * Param<double>() };
      _changePriceCommand = this.CreateUpdate<IBook>("BooksChangePrice", 
              "Changes prices of books in a given category.", qryChangePrice);

VITA supports creating custom methods for all four basic types: select, update, insert and delete. We start with defining custom LINQ queries using IQueryable sets returned by the generic EntitySet<TEntity>() method inherited from the base EntityModule class. For each procedure type the query must return a specific result type:
  • SELECT - the entity itself
  • UPDATE - an anonymous type containing all properties constituting the primary key plus all new values for the properties being updated.
  • INSERT - an anonymous type containing all values for the inserted entity
  • DELETE - an anonymous type containing the primary key of the entity to be deleted.

Notice the use of Param<T>() helper method - it allows you to define an explicit query parameter. For string parameters you can additionally specify the maximum length for SQL type, overriding the default 'nvachar(4000)' used by Linq2Sql implementation for MS SQL Server.
The query is then passed to one of the command construction methods: CreateSelect, CreateUpdate, etc. The returned IEntityCommand object is a handle for stored procedure that can be used for its actual execution. We save the returned objects in class-level fields.
Now we have the command ready to be used in the application code. Once the application startup is finished, two new stored procedures (part5.BooksGetByAuthorLastName, part5.BooksChangePrice) will be created in the database. To call them from application code, we define two methods in the BooksModule class:

    public IList<IBook> GetBooksByAuthor(IEntitySession session, string lastName) {
      return session.ExecuteQuery<IBook>(_getBooksByAuthorCommand, lastName);
    public void ChangePrice(IEntitySession session, BookCategory category, double changePerc) {
      var factor = (100.0 + changePerc) / 100.0d;
      session.ExecuteNonQuery(_changePriceCommand, category, factor);

Executing custom commands

We start with the standard VITA application setup:

      var entityModel = new BooksEntityModel();
      var entityStore = entityModel.CreateEntityStore( "<conn string>", (driver));

The entity store is the same entity store we had seen before - we will use it to open sessions and to query/update entities. The BooksEntityModel exposes MainModule property which is an instance of the BooksModule class that defines the custom commands.
The following code prints the titles of the books by John Sharp:

      session = entityStore.OpenSession(); 
      var booksByJohnSharp = entityModel.MainModule.GetBooksByAuthor(session, "Sharp");
      foreach (var book in booksByJohnSharp)
        Console.WriteLine("  Book: " + book.Title);

Now imagine we have a sale in our book store and we want drop prices on all programming books by 20%. The following code does the job:

      entityModel.MainModule.ChangePrice(session, BookCategory.Programming, -20);
Once you execute the code in the tutorial, the stored procedures will be created in the database - find them in the SQL Management Studio, in 'part5' schema in VitaTutorial database.


We created custom commands for querying and updating the data. These custom commands are translated into stored procedures in the database.

Tutorial Home

Last edited Nov 22, 2013 at 3:50 AM by rivantsov, version 14


No comments yet.