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:
using (var ds = new NorthwindDataService("Northwind"))
{
Category c = ds.CategoryRepository.Get(Projection.BaseTable, 1);
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"))
{
IQueryLite<Category> query1 = ds.CategoryRepository.Query(Projection.BaseTable);
IQueryLite<Product> query2 = ds.ProductRepository.Query(Projection.Detailed);
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)
.And(ProductFields.SupplierId, OperatorLite.In, new int[] {2, 3})
.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);
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);
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);
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"))
{
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:
| CategoryName | ProductName | Year | Quarter | Sales |
|---|
| Beverages | Chai | 1997 | 1 | 705.60 |
| Beverages | Chai | 1997 | 2 | 878.40 |
| Beverages | Chai | 1997 | 3 | 1174.50 |
| Beverages | Chai | 1997 | 4 | 2128.50 |
| Beverages | Chang | 1997 | 1 | 2435.80 |
| Beverages | Chang | 1997 | 2 | 228.00 |
| Beverages | Chang | 1997 | 3 | 2061.50 |
| Beverages | Chang | 1997 | 4 | 2313.25 |
Now, imagine you need to show these results on a screen and accomplish the following requirements:
- The user must be able to view all employees sales or the sales done by a specific employee.
- The user must be able to filter by year.
- The user must be able to get the sales grouped category or by product.
- You must implement query pagination.
- 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
};
ds.ProductRepository.Save(p);
Console.WriteLine("Inserted product id:" + p.ProductId);
p.ProductName = "Another Name";
ds.ProductRepository.Save(p);
p = ds.ProductRepository.Get(Projection.Detailed, p.ProductId);
Console.WriteLine("CategoryName:" + p.CategoryName);
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.
