i-nercya EntityLite: A Lightweight, Database First, Micro ORM

• Download source

Introduction

i-nercya EntityLite is an open source, lightweight, database first, Micro ORM written in C# for use in C# applications that access relational databases.

EntityLite has been designed to accomplish the following objectives:

• Simplicity. The core has only about 2,600 lines of code and has a maintainability index of 85.
• Easy of use and understand. EntityLite provides T4 templates to generate an easy to use data layer. Queries are easy to write using the fluent interface provided by EntityLite.
• High performance. The processing time spent to generate SQL statements isn't noticeable. EntityLite uses DynamicMethod to generate, at runtime, high performance methods to load entities from datareaders.
• Fulfill application query needs. Using EntityLite, you can base your queries on a table, a view or a table valued function, then add a filter and order. Stored procedures are also supported. That covers most of your query needs. The rest of your query needs are covered by highly flexible and dynamic runtime T4 template based queries.
• Leverage database power. Relational database servers have great processing power and features that you can take advantage of by using views, functions and stored procedures.
• Support multiple database systems. EntityLite works, out of the box, with SQL Server, Oracle, Postgre-SQL, MySQL and SQLite. But it can be easily extended to others RDMSs as well.

Background

Back in the year 2009 when Entity Framework was young we started using it, but soon we felt the need of a simpler, better performing ORM. So I started working on EntityLite, ORMLite was its name, I changed it recently because there is others micro ORM's with that name. EntityLite has evolved a lot since then, and now I decided to publish it as open source project.

Yes, there are several full ORMs like Entity Framework, NHibernate and LLBLGen Pro, and several micro ORM's such as PetaPoco, Massive and Dapper. So EntityLite is just another micro ORM. But I hope you like it and love it as much as I do.

Although EntityLite seems like a brand new micro ORM, it has been in production several years. All applications we have developed use it.

Entities, Tables and Views

In EntityLite, entities are POCO objects that map to database tables and views. You can see an entity as a denormalized projection or view of the database. An entity can map to zero or one table, and zero or more views. Of course, an entity must map, at least, to one table or view. If an entity maps to a table, the entity is updatable, you can use that entity to insert, update and delete rows on that table. If an entity doesn't map to any table, the entity is read only, you can query it, but you cannot save it to the database. For example, the entity Product maps to table Products and view Product_Detailed. That means you can insert, update and delete products. That also means you can query for products based on either Products table or Product_Detailed view.

Entities are simple. They don't have complex properties nor collection properties. Relations are implemented as foreign keys. Entity properties map to table columns and view columns. There is one property for each distinct column of the mapping table and views.

Instead of abstracting the database and considering it as an object graph persistence store, EntityLite embraces the relational model, providing little abstraction, and looking at it as what it really is: a relational database.

If an entity maps to a table, its entity views should accomplish the following rules regarding the primary key:

• The primary key of the base table should be present in the entity view
• The entity view should not return more than one row with the same value of the primary key

In EntityLite, views that map to entities are called entity views. They must follow a specific naming convention. An entity view name must start with the entity name, followed by an underscore and ended with a projection name. For example, Product_Detailed is an entity view of Product entity and Detailed is the projection name.

EntityLite encourages the use of entity views. They help you to build your read model, they provide different ways to query for your entity and they allow you to load different sets of properties. For example, an employee of Northwind Traders needs to review the product catalog and you want to display the product catalog on a grid. You can query the Products table, but this is not enough because the employee wants to see the category name and the supplier name of the product. To solve this problem, you create the Product_Detailed entity view that includes these columns:

CREATE VIEW [dbo].[Product_Detailed]
AS
SELECT
    P.ProductID, P.ProductName, P.SupplierID, P.CategoryID, P.QuantityPerUnit, P.UnitPrice,
    P.UnitsInStock, P.UnitsOnOrder, P.ReorderLevel, P.Discontinued,
    C.CategoryName, S.CompanyName AS SupplierName
FROM
    [dbo].[Products] P
    LEFT OUTER JOIN [dbo].[Categories] C
        ON P.CategoryID = C.CategoryID
    LEFT OUTER JOIN [dbo].Suppliers S
        ON P.SupplierID = S.SupplierID    

Similary, when you show orders (just the headers) on a grid, you may want to display the order total, the number of order details, and the name of the customer and the name of the shipper. To do that, you create the following two views. The first view is just a helper view, the second one is an entity view.

CREATE VIEW [dbo].[OrderDetailsSummary]
WITH SCHEMABINDING
AS
    SELECT
        OD.OrderID, SUM(OD.Quantity * OD.UnitPrice * (1 - OD.Discount)) AS OrderTotal, COUNT_BIG(*) AS LineCount
    FROM
        [dbo].[OrderDetails] OD
    GROUP BY
        OD.OrderID    

To make the above view more efficient, you can index it:

CREATE UNIQUE CLUSTERED INDEX [UK_OrderDetailsSummary_OrderID] 
ON [dbo].[OrderDetailsSummary]([OrderID])    

And here, you have the entity view:

CREATE VIEW [dbo].[Order_Extended]
AS
SELECT 
    O.OrderID, O.CustomerID, O.EmployeeID, O.OrderDate, O.RequiredDate, O.ShippedDate, O.ShipVia, 
    O.Freight, O.ShipName, O.ShipAddress, O.ShipCity, O.ShipRegion, O.ShipPostalCode, O.ShipCountry,
    C.CompanyName AS CustomerCompanyName,
    E.FirstName AS EmployeeFirstName, E.LastName AS EmployeeLastName,
    S.CompanyName AS ShipperCompanyName,
    OS.OrderTotal, OS.LineCount
FROM
    [dbo].[Orders] O
    LEFT OUTER JOIN dbo.Customers C
        ON O.CustomerID = C.CustomerID
    LEFT OUTER JOIN dbo.Employees E
        ON O.EmployeeID = E.EmployeeID
    LEFT OUTER JOIN dbo.Shippers S
        ON O.ShipVia = S.ShipperID
    LEFT OUTER JOIN  dbo.OrderDetailsSummary OS WITH (NOEXPAND)
        ON O.OrderID = OS.OrderID    

During the development process, you might be tempted to do the following: you create an entity view, when you need another column you modify the view, and then, every time you need more columns you add them to the view. At the end, you have "The Universal View". Don't be confused, this is a monster, 12 or more table joins, inefficient and a difficult to maintain view. Please don't do that, you can create as many views per entity as you want, so create another view for the new use case. Don't use a view in a use case when that view access tables that the use case doesn't need, just create another view.

Views have an advantage over application generated code, DBAs have an opportunity to optimize them. Experienced database developers can also optimize views. They can rewrite them to make them more efficient, include query hints and other tricks that are impossible with application generated code.

There are view designers for most of database servers, and if you are a beginner on SQL, use them. They can help you to create the views. However view designers don't support all SQL language features, you can create all possible views by writing SQL code, but you can't using designers.

The EntityLite Nuget Package

To include EntityLite in your project, you need to install EntityLite Nuget package. It includes the T4 code generator and the core library (The EntityLite.Core Nuget package). Just type the following in the Package Manager Console to install the latest pre-release version:

PM>Install-Package EntityLite -Pre    

To install the latest stable version, type the following:

PM>Install-Package EntityLite    

When you install the EntityLite Nuget package, the following is added to your project:

• A reference to inercya.EntityLite.dll. This is the core library. It requires .NET 3.5 or later
• A folder named EntityLite.ttinclude. This folder includes several T4 include files
• The DataLayer.tt T4 template. This is where you define the entities you want to generate and several properties to access the database and control the code generation process.

You might want to create a library class project to put your data layer in, and a UI project that references the library class project. Then the UI project must reference the EntityLite.Core assembly:

PM>Install-Package EntityLite.Core

Attaching The Sample Database

Before generating the data layer, you need a database. EntityLite is a database first micro ORM after all. The sample code includes a SQL Server 2012 database that you need to attach. It also includes AttachDb.SQL script. Execute the script from Visual Studio or SQL Sever Management Studio on a local SQL Server 2012 LocalDb or regular instance to attach the database. Change the Northind.mdf database file path as appropriate. You can also attach the database to a remote SQL Server 2012 instance. In that case, you need to copy the database files (*.mdf and *.ldf) to the remote machine, and change the connection string to point to the remote SQL Server 2012 instance.

CREATE DATABASE Northwind
ON (FILENAME = 'C:\Projects\EntityLite\Samples\Northwind.mdf') 
FOR ATTACH;

Generating the Data Layer

To generate the data layer, you need to modify the DataLayer.tt file. Change the connection string and include the entities and procedures you want.

Below you have a DataLayer.tt sample. It uses SqlClient to connect to the Northwind database on the SQL Server LocaDb default instance. Generated classes are placed on the Samples.Entities namespace. It generates a data service class named "NorthwindDataService", several entities and a method for easily calling the RaiseProductPrices stored procedure.

<#@ include file ="EntityLite.ttinclude\EntityLite.ttinclude" #>
<#
var generation = new DataLayerGeneration
{
    ProviderName = "System.Data.SqlClient",
    ConnectionString = @"Data Source=(LocalDb)\V11.0;Initial Catalog=Northwind",
    DefaultSchema = "dbo",
    RootNamespace = "Samples.Entities",
    DataServiceName = "NorthwindDataService",
    EntitySettings = new List<EntitySetting>
    {
        new EntitySetting 
        {
            BaseTableName = "Products",
            EntityName = "Product"  
        },
        new EntitySetting 
        {
            BaseTableName = "Categories",
            EntityName = "Category"  
        },
        /* ....................... */
        new EntitySetting 
        {
            BaseTableName = "Orders",
            EntityName = "Order"  
        }
    },
    ProcedureSettings = new List<ProcedureSetting>
    {
        new ProcedureSetting 
        {
            ProcedureName = "RaiseProductPrices",
            ResultSetKind = ProcedureResultSetKind.None,
            RelatedEntityName = "Product"
        }
    }
};
Render(generation);
#>    

If everything is OK, when you save the DataLayer.tt file or right click on it and select Run Custom Tool, the DataLayer.cs file is generated.

The following image shows you the generated data layer:

As you can see, there are three classes for each entity:

• The POCO class that represents the entity. For example Category
• The Repository class. For example CategoryRepository, which allows you to query, save and delete Categories
• The Fields class. For example, CategoryFields which has a constant string field for each Category property.

The NorthwindDataService generated class is the entry point to the data layer. It manages the connection to the database as well as transactions. It allows you to perform all supported data access operations. To make this easier, NorthwindDataService has one property for each repository class.

Things That Might Go Wrong Generating the Data Layer

There are a few things that might go wrong generating the data layer. Typically:

• The ADO.NET provider you are using is not properly registered. The assemblies must be in the Global Assembly Cache and the provider must be registered in the DbProviderFactories section of Machine.config.
• The ADO.NET provider you are using works on 64 bits but it doesn't on 32 bits. You need the provider to work on 32 bits because Visual Studio runs on 32 bits.
• You have problems connecting to the database. The connection string might be wrong or you might not have permissions to connect.
• Typos in database objects (tables and stored procedures).
• Some Views and stored procedures might be invalid. For example, you dropped or renamed a table column, but forgot to update a view that references it.

Querying

There are several ways in EntityLite to perform queries:

• Getting an entity by the primary key. This is done by using the Repository.Get method.
• Create a query based on a table or view and then add ordering and filtering. You can do it by using the Repository.Query method.
• Create a query based on a table valued function and then add ordering and filtering. The FunctionQueryLite class is used for this.
• Create a query based on a T4 runtime template and then add ordering and filtering. This is performed by using the TemplatedQueryLite class.
• Execute an stored procedure that returns a result set. You can call the stored procedures Repository auto-generated methods
• Execute a query built from a T4 runtime template that returns a result set. The TemplatedCommand class is used in this case.

Getting an Entity by the Primary Key

To get an entity by the primary key, you use the Get method of the repository. The Get method has the projection as the first parameter, it can be either one of the inercya.EntityLite.Projection enumeration values (standard projections) or the projection name.

The following code snippet illustrates the use of the Get method:

// "Norhtwind" is the application configuration file connection string name
using (var ds = new NorthwindDataService("Northwind"))
{
    // reaads a category from the database by CategoryId
    // SELECT * FROM dbo.Categories WHERE CategoryId = 1
    Category c = ds.CategoryRepository.Get(Projection.BaseTable, 1);
    // Loads the product with ProductId = 2 from the database
    // SELECT CategoryName, ProductName FROM Product_Detailed WHERE ProductId = 2
    Product p = ds.ProductRepository.Get(Projection.Detailed, 2, ProductFields.CategoryName, ProductFields.ProductName);
}    

The QueryLite Object

To query entities based on base tables and entity views, you use the Query method of the repository. This method returns a QueryLite object and has the projection as the first parameter, it can be either one of the inercya.EntityLite.Projection enumeration values (standard projections) or the projection name.

In the following example, you can see how to create QueryLite objects using different projections:

using (var ds = new NorthwindDataService("Northwind"))
{
    // this query is based on the dbo.Categories table
    IQueryLite<Category> query1 = ds.CategoryRepository.Query(Projection.BaseTable);

    // this query is based on the dbo.Product_Detailed view
    IQueryLite<Product> query2 = ds.ProductRepository.Query(Projection.Detailed);

    // this query is based on the dbo.ProductSale_Quarter view
    IQueryLite<ProductSale> query3 = ds.ProductSaleRepository.Query("Quarter");
}

You can specify the columns to retrieve from the entity base table or entity view using the Fields extension method. If you don't, all columns are retrieved. You can also filter and order. To filter, you use Where, And and Or extension methods. To order, you use OrderBy and OrderByDesc extension methods. To execute the query, you call ToEnumerable(), ToList(), or FirstOrDefault() methods. The following code snippet shows you an example:

using (var ds = new NorthwindDataService("Northwind"))
{
    IEnumerable<Product> products = ds.ProductRepository.Query(Projection.Detailed)
        .Fields(ProductFields.CategoryName, ProductFields.ProductName)
        .Where(ProductFields.Discontinued, false) // equals is the default operator
        .And(ProductFields.SupplierId, OperatorLite.In, new int[] {2, 3}) // the value for OperatorLite.In is an enumerable
        .And(ProductFields.UnitsInStock, OperatorLite.Greater, 0)
        .OrderBy(ProductFields.CategoryName, ProductFields.ProductName)
        .ToEnumerable();

    foreach (Product p in products)
    {
        Console.WriteLine("CategoryName: {0}, ProductName: {1}", p.CategoryName, p.ProductName);
    }
}

Parentheses are implemented by subfilters. For example:

using (var ds = new NorthwindDataService("Northwind"))
{
    var subFilter = new FilterLite<product>()
                .Where(ProductFields.SupplierId, 1) 
                .Or(ProductFields.SupplierId, OperatorLite.IsNull);

    // SELECT * FROM dbo.Products WHERE CategoryId = 1 AND (SupplierId = 1 OR SupplierId IS NULL)
    IList<Product> products = ds.ProductRepository.Query(Projection.BaseTable)
                    .Where(ProductFields.CategoryId, 1)
                    .And(subFilter)
                    .ToList();
}    

To perform query pagination, ToList() and ToEnumerable() methods have an overload that includes fromRowIndex and toRowIndex arguments, both starting at zero. These methods use specific database features such as LIMT OFFET for MySQL, SQLite and Postgre-SQL. ROW_NUMBER() is used for SQL Server, and rownum for Oracle.

The following code snippet shows the product list in a paged way:

using (var ds = new Entities.NorthwindDataService("Northwind"))
{
    const int PageSize = 10;
    var query = ds.ProductRepository.Query(Projection.Detailed)
        .Fields(ProductFields.CategoryName, ProductFields.ProductName)
        .OrderBy(ProductFields.CategoryName, ProductFields.ProductName);

    // SELECT COUNT(*) FROM ....
    var productCount = query.GetCount();

    var fromRowIndex = 0;
    var toRowIndex = PageSize - 1;
    while (fromRowIndex < productCount)
    {
        foreach (var product in query.ToEnumerable(fromRowIndex, toRowIndex))
        {
            Console.WriteLine("{0}\t{1}", product.CategoryName, product.ProductName);
        }
        Console.WriteLine("Press enter to view the next product page ...");
        Console.ReadLine();
        fromRowIndex = toRowIndex + 1;
        toRowIndex += PageSize;
    }
}    

EntityLite has partial support for subqueries. You can pass a QueryLite object as the value argument for OperatorLite.In and OperatorLite.NotIn operators as shown in the following example:

using (var ds = new NorthwindDataService("Northwind"))
{
    IQueryLite<OrderDetail> orderDetailSubQuery = ds.OrderDetailRepository.Query(Projection.BaseTable)
        .Fields(FieldsOption.None, OrderDetailFields.OrderId)
        .Where(OrderDetailFields.ProductId, 11);

    // SELECT OrderId, OrderDate, CustomerId
    // FROM dbo.Orders
    // WHERE OrderId IN (
    //       SELECT OrderId
    //       FROM dbo.OrderDetails
    //       WHERE ProductId = 11
    //    )
    IQueryLite<Order> orderQuery = ds.OrderRepository.Query(Projection.BaseTable)
        .Fields(OrderFields.OrderId, OrderFields.OrderDate, OrderFields.CustomerId)
        .Where(OrderFields.OrderId, OperatorLite.In, orderDetailSubQuery);

    foreach(var order in orderQuery.ToEnumerable())
    {
        Console.WriteLine("OrderId {0}, OrderDate {1}, CustomerId {2}", 
            order.OrderId, order.OrderDate, order.CustomerId);
    }
}     

Table Valued Functions

EntityLite supports SQL Server table valued functions. To use them, you create a FunctionLite object. FunctionLite implements IQueryLite interface, therefore you can specify columns and add filtering and ordering as usual. Query pagination is also available.

Given the following inline table valued function that returns a employee subtree by using a recursive CTE:

CREATE FUNCTION GetEmployeeSubTree(@EmployeeId int)
RETURNS TABLE
AS
RETURN
WITH H
AS
(
    SELECT E.EmployeeID, E.LastName, E.FirstName,  E.ReportsTo, E.City
    FROM
        [dbo].[Employees] E
    WHERE
        E.EmployeeID = @EmployeeId
    UNION ALL
    SELECT E.EmployeeID, E.LastName, E.FirstName,  E.ReportsTo, E.City
    FROM
        [dbo].[Employees] E
        INNER JOIN H ON E.ReportsTo = H.EmployeeID
)
SELECT * FROM H

You can get all employees from London that report directly or indirectly to Mr. Andrew Fuller using the following code snippet:

using (var ds = new NorthwindDataService("Northwind"))
{
    // Andrew Fuller EmployeeId is 2
    // SELECT FirstName, LastName
    // FROM GetEmployeeSubTree(2)
    // WHERE City = 'London'
    // ORDER BY FirstName, LastName
    IQueryLite<Employee> query = new FunctionQueryLite<Employee>(ds, "dbo.GetEmployeeSubTree", 2)
        .Fields(EmployeeFields.FirstName, EmployeeFields.LastName)
        .Where(EmployeeFields.City, "London")
        .OrderBy(EmployeeFields.FirstName, EmployeeFields.LastName);

    foreach(var emp in query.ToEnumerable())
    {
        Console.WriteLine("FirstName: {0}, LastName: {1}", emp.FirstName, emp.LastName);
    }
}

You might want to include the FunctionQueryLite object creation in the EmployeeRepository class for homogenising and reusing purposes. It is easy to do because all generated classes are partial. Here you have an example:

public partial class EmployeeRepository 
{
    public IQueryLite<Employee> EmployeeSubtreeQuery(int employeeId)
    {
        return new FunctionQueryLite<Employee>(this.DataService, "dbo.GetEmployeeSubTree", employeeId);
    }
}

Template-Based Queries

EntityLite provides a highly dynamic and flexible way to perform queries based on runtime text templates. For a brief introduction to runtime text templates, please see Preprocessed T4 Templates.

Template-based queries are implemented by the TemplatedQueryLite class. To understand how it works, let's start with the following entity view that returns all product sales by quarter.

CREATE VIEW [dbo].[ProductSale_Quarter]
AS
    SELECT
        P.CategoryID, C.CategoryName, P.ProductID, P.ProductName,
        DATEPART(year, O.OrderDate) AS [Year],
        DATEPART(quarter, O.OrderDate) AS [Quarter],
        SUM(OD.Quantity * OD.UnitPrice * (1 - OD.Discount)) AS Sales
    FROM
        
        dbo.Products P
        LEFT OUTER JOIN dbo.Categories C
            ON P.CategoryID = C.CategoryID
        LEFT OUTER JOIN 
        (
            dbo.Orders O
            INNER JOIN dbo.OrderDetails OD
                ON O.OrderID = OD.OrderID
        ) ON P.ProductID = OD.ProductID
    GROUP BY
        P.CategoryID, C.CategoryName, P.ProductID, P.ProductName,
        DATEPART(year, O.OrderDate),
        DATEPART(quarter, O.OrderDate)
GO

The following query:

SELECT CategoryName, ProductName, Year, Quarter, Sales
FROM dbo.ProductSale_Quarter
WHERE 
    ProductID IN (1, 2)
    AND Year = 1997
ORDER BY
    CategoryName, ProductName, Year, Quarter

Returns the following result set:

Now, imagine you need to show these results on a screen and accomplish the following requirements:

1. The user must be able to view all employees sales or the sales done by a specific employee.
2. The user must be able to filter by year.
3. The user must be able to get the sales grouped category or by product.
4. You must implement query pagination.
5. The user must be able to sort the result by any shown field.

Requirements 2, 4 and 5 can be easily accomplished using a QueryLite object based on ProductSale_Quarter entity view. But for requirement 1, you need an inline table valued function because EmployeeId column is not in ProductSale_Quarter view. To accomplish the requirement number 3, you would need two inline table valued functions: one that groups by product and another that groups by category. The problem is that your database might not support table valued functions and there should be a better way than using two table valued functions (in more extreme cases, you might need more table valued functions). This is where TemplatedQueryLite comes into play.

TemplatedQueryLite can do what inline table valued functions can and much more, but it is a bit more complex to use. TemplatedQueryLite is great when you need dynamic SQL generation or you cannot build the query using a QueryLite object based on a table or view.

To create a runtime template-based query to accomplish the above requirements, the first step is creating the query template. Add a new Runtime Text Template item (Preprocesed Text Template item in VS 2010) item to your project named SalesQueryTemplate.tt with the following content:

<#@ template language="C#" #>
<#@ assembly name="System.Core" #>
SELECT
    P.CategoryID, C.CategoryName,
<# if (Grouping == "Product") { #>
    P.ProductID, P.ProductName,
<# } else { #>
    NULL AS ProductID, NULL AS ProductName,
<# } #>
    DATEPART(year, O.OrderDate) AS [Year],
    DATEPART(quarter, O.OrderDate) AS [Quarter],
    SUM(OD.Quantity * OD.UnitPrice * (1 - OD.Discount)) AS Sales
FROM
    dbo.Products P
    LEFT OUTER JOIN dbo.Categories C
        ON P.CategoryID = C.CategoryID
    LEFT OUTER JOIN 
    (
        dbo.Orders O
        INNER JOIN dbo.OrderDetails OD
            ON O.OrderID = OD.OrderID
    ) ON P.ProductID = OD.ProductID
<# if (EmployeeId.HasValue) { #>
WHERE
    O.EmployeeID = $(EmployeeId)
<# } #>
GROUP BY
    P.CategoryID, C.CategoryName, 
<# if (Grouping == "Product") { #>
    P.ProductID, P.ProductName,
<# } #>
    DATEPART(year, O.OrderDate),
    DATEPART(quarter, O.OrderDate)    

Note that SalesQueryTemplate.tt is similar to ProductSale_Quarter view. But SalesQueryTemplate.tt uses Grouping and EmployeeId template properties to dynamically build the SQL statement to accomplish requirements 1 and 3. EmployeeId is also a query parameter. EntityLite uses a special notation for query templates parameters. Parameters are enclosed by parentheses and preceded by $ symbol, $(EmployeeId) on the sample. This special notation is replaced at runtime by the right parameter notation used by the current ADO.NET provider.

The query template must produce just one single and complete SELECT statement. For dynamic, multi-statement, not-only-select queries you can use TemplatedCommand objects.

The second step is to extend the SalesQueryTemplate partial class which is generated from SalesQueryTemplate.tt runtime template. The query template class must implement ISqlTemplate interface and define the template properties. Template properties that act as query parameters must be decorated with DbParameter attribute. For example, you can extend the SalesQueryTemplate class adding a file named SalesQueryTemplate.partial.cs with the following content (namespaces removed for simplicity):

public partial class SalesQueryTemplate : ISqlTemplate
{
    public string Grouping { get; set; }

    [DbParameter(DbType= DbType.Int32)]
    public int? EmployeeId { get; set; }
}

The third step is to extend the repository class with a method that creates and returns a TemplatedQueryLite object. For example:

public partial class ProductSaleRepository
{
    public IQueryLite<ProductSale> TemplatedQuery(string grouping, int? employeeId)
    {
        var template = new SalesQueryTemplate
        {
            EmployeeId = employeeId,
            Grouping = grouping
        };
        return new TemplatedQueryLite<ProductSale>(this.DataService, template);
    }
}

TemplatedQueryLite implements IQueryLite, therefore you can add filter and order as usual. You can also perform query pagination easily. The following example shows you the first 10 Andrew Fuller sales in 1997 grouped by product and ordered by category, product, year and quarter.

using (var ds = new NorthwindDataService("Northwind"))
{
    var salesQuery = ds.ProductSaleRepository
        .TemplatedQuery("Product", 2)
        .Where(ProductSaleFields.Year, 1997)
        .OrderBy(ProductSaleFields.CategoryName, ProductSaleFields.ProductName)
        .OrderBy(ProductSaleFields.Year, ProductSaleFields.Quarter);
    foreach(var s in salesQuery.ToEnumerable(0, 9))
    {
        Console.WriteLine("{0}, {1}, {2}, {3}, {4}", 
            s.CategoryName, s.ProductName, s.Year, s.Quarter, s.Sales);
    }
}

Stored Procedures

EntityLite supports stored procedures for SQL Server, Oracle, MySQL and Postgre-SQL. The following stored procedure is included in the sample code:

CREATE PROCEDURE [dbo].[RaiseProductPrices] @rate numeric(5,4)
AS
    UPDATE dbo.Products
    SET UnitPrice = UnitPrice * (1 + @rate);

To generate a method for a stored procedure, you include a StoredProcedureSetting object in DataLayer.tt file. The method is placed in the RelatedEntity repository class. In the sample DataLayer.tt, ProcedureName is "RaiseProductPrices" and RelatedEntity is "Product", therefore a method named RaiseProductPrices is generated in the ProductRepository class. The following is the signature of the method:

public void RaiseProductPrices(Decimal? rate)

The method return type is determined by the ResultSetKind property of the StoredProcedureSetting object. It can be one of the following values:

• ProcedureResultSetKind.None. The procedure returns no result set. The method return type is void.
• ProcedureResultSetKind.Scalar. The result set contains only one row with only one column. The method return type is the value of the ScalarReturnType property of the StoredProcedureSetting object.
• ProcedureResultSetKind.SingleRow. The result set contains only one row with multiple columns. The method return type is the related entity POCO class.
• ProcedureResultSetKind.MultipleRows. The result set contains multiple rows with multiple columns. The method returns a list of related entities.

Multiple result sets are not supported.

The generated method has an argument for each stored procedure parameter. IN/OUT parameters are declared as ref arguments.

Calling a stored procedure is as easy as calling a method. For example:

using (var ds = new NorthwindDataService("Northwind"))
{
    ds.ProductRepository.RaiseProductPrices(0.10m);
}    

Template-Based Commands

EntityLite provides a highly dynamic and flexible way to execute multi-statement SQL commands based on runtime T4 templates. This is implemented by the TemplatedCommand class. Template-based commands are similar to template-based queries. But template-based commands can contain not-only-select statements, they can contain any number of valid SQL statements. The downside is that TemplatedCommand doesn't implement IQueryLite, therefore you cannot add additional filter and ordering, and query pagination is not as easy.

Template-based commands are intended mainly for executing multi-statement SQL commands that perform set based modifications. You can use them, for example, to execute Transact-SQL batches and PL/SQL anonymous blocks. They are a dynamic client side alternative to stored procedures. If you are using a database server that doesn't support stored procedures, don't worry, you can do the same thing using template-based commands. If you have a stored procedure that uses server side dynamic SQL, template-based commands might be a better alternative.

The following is the content of RaiseProductPricesTemplate.tt sample file which is a Runtime Text Template in Visual Studio terminology, and a command template in EntityLite terminology.

<#@ template language="C#" #>
<#@ assembly name="System.Core" #>

UPDATE <#= SchemaPrefix #>Products
SET UnitPrice = UnitPrice * (1 + $(Rate))
<# if (CategoryId.HasValue) { #>
WHERE CategoryId = $(CategoryId)
<# } #>

The generated command template RaiseProductPricesTemplate partial class must be extended the same way query templates must. It must implement the ISqlTemplate interface and define template properties. Template properties that act as command parameters must be decorated with DbParameter attribute.

public partial class RaiseProductPricesTemplate : ISqlTemplate
{
    public string DefaultSchema { get; set; }
    public string SchemaPrefix
    {
        get { return string.IsNullOrEmpty(DefaultSchema) ? string.Empty : DefaultSchema + "."; }
    }

    [DbParameter(DbType= DbType.Int32)]
    public int? CategoryId { get; set; }

    [DbParameter(DbType = DbType.Decimal, Precision=5, Scale=4)]
    public decimal Rate { get; set; }
}

The repository class ProductRepository should be extended with a method that executes the template-based command as follows:

public partial class ProductRepository
{
    public int RaiseProductPrices(int? categoryId, decimal rate)
    {
        var template = new RaiseProductPricesTemplate
        {
            CategoryId = categoryId,
            DefaultSchema = this.DataService.EntityLiteProvider.DefaultSchema,
            Rate = rate
        };
        var cmd = new TemplatedCommand(this.DataService, template);
        return cmd.ExecuteNonQuery();
    }
}

TemplatedCommand objects have the following methods to execute them:

• ExecuteNonQuery()
• ExecuteScalar()
• FirstOrDefault<T>()
• ToEnumerable<T>()
• ToList<T>()
• ExecuteReader()

Template-based commands support output parameters. To include an output parameter, you need to add a property to the command template partial class with the same name as the parameter. The property must be decorated with the DbParamter attribute specifying Direction.Output or Direction.InputOutput. You can get the output parameter value by reading the property of the command template after command execution.

Writing to the Database

Each Repository class has strongly typed methods to insert, update and delete table rows on the database. These methods are the following:

• Insert(EntityType entity). Inserts a new row to the entity base table. If the table has an auto-generated primary key column, such as an auto-increment or identity column, or an ORACLE sequence driven column, EntityLite sets the corresponding entity property upon insertion. In ORACLE, the sequence must be named as follows: COLUMNNAME_SEQ. Guid columns are also considered auto-generated.
• Update(EntityType entity). Updates the corresponding row on the entity base table. This method has an additional overload where you can specify the columns you want to update.
• Save(EntityType entity). Saves the entity. It is a convenient method that inserts the row if it's new or updates it if it's not. It only works if the table has an auto-generated primary key column. The row is considered new if the property mapped to the auto-generated column is equals to zero.
• Delete(EntityType entity). Deletes the corresponding row on the entity base table. This method has an overload with the primary key as the argument instead of the entity.

The following piece of code shows you an example that inserts, updates and deletes a product:

using (var ds = new Entities.NorthwindDataService("Northwind"))
{
    ds.BeginTransaction();
    var p = new Entities.Product
    {
        CategoryId = 2,
        ProductName = "New Product",
        QuantityPerUnit = "2",
        ReorderLevel = 50,
        SupplierId = 2,
        UnitPrice = 10,
        UnitsInStock = 1,
        UnitsOnOrder = 0
                  
    };
    // inserts the new product
    ds.ProductRepository.Save(p);
    Console.WriteLine("Inserted product id:" + p.ProductId);
    p.ProductName = "Another Name";
    // updates the product
    ds.ProductRepository.Save(p);
    // Retrieves the product from the database and shows the product category name
    p = ds.ProductRepository.Get(Projection.Detailed, p.ProductId);
    Console.WriteLine("CategoryName:" + p.CategoryName);
    // deletes the product
    ds.ProductRepository.Delete(p.ProductId);
    ds.Commit();
}

These modification methods are virtual, so you can change the insert, update or delete default behaviour for a specific entity. If you want to change the behaviour for all entities globally, you can override these methods on the DataService class.

EntityLite supports transactions. For that purpose, the DataService class has the BeginTransaction(), Commit() and Rollback() methods. Nested transaction are also supported, you can call BeginTransaction() several times without calling Commit() or Rollback(). If you call BeginTransaction three times, you must call Commit three times to actually commit the transaction. If you call Rollback the transaction is rolled back no matter how many times you called BeginTransaction() before.

Performance

EntityLite is very fast, as fast as its micro ORM brothers. Frans Bouma (the LLBLGen Pro creator) wrote this blog post a while ago comparing the fetch performance of serveral ORM's. It doesn't include EntityLite because EntityLite is new and therefore few people use it currently. But I forked the github repository where the benchmark code is and included EntityLite in the benchmark. The results are here.

Other Features

I think this article is long enough and would like to thank you for reading up to this point. However, I don't want to finish it without enumerating some other features EntityLite has:

• Optimistic concurrency on update. Just add an integer column named EntityRowVersion to your table to enable it.
• Automatic audit fields. CreatedDate, ModifiedDate, ModifiedBy and CreatedBy columns are managed automatically by EntityLite. Audit field names can be changed through DataService.SpecialFieldNames property. You must set DataService.CurrentUserId to allow EntityLite to set ModifiedBy and CreatedBy columns.
• Localization. EntityLite supports two ways to implement localization. The most simple is to have columns named MyColumn_Lang1, MyColumn_Lang2, etc. When you access MyEntity.MyColumn, EntityLite picks the right one based on thread current culture. This also works on filters.
• Automatic query retry. EntityLite automatically retries failed queries.
• Error logging to NLog
• Query Profiling. Just implement IProfilerLite interface and set ProfilerLite.Current property. I'm going to publish on Nuget an implementation that logs query execution into a SQLite database.
• Naming conventions. EntityLite transforms column names to Pascal naming convention property names. For example, a table column named PRODUCT_PRICE will map to a property named ProductPrice.
• Support for ORACLE Ref Cursors for stored procedures.
• Support for SQL Server spatial types and hierarchyId.