LINQ to SQL: Repository Pattern

This article will discuss one of the options for implementing a pattern repository based on LINQ to SQL.

Today, LINQ to SQL is one of Microsoft technologies designed to solve the problem of object-relational mapping ( object-relational mapping ). The alternative Entity Framework technology is a more powerful tool, however LINQ to SQL has its advantages - relative simplicity and low level.

This article is an attempt to demonstrate the strengths of LINQ to SQL. The repository pattern fits perfectly with the LINQ to SQL paradigm.

Repository

First, remember what a repository is.

public interface IRepository<T> where T: Entity
{
IQueryable<T> GetAll();
bool Save(T entity);
bool Delete( int id);
bool Delete(T entity);
}

* This source code was highlighted with Source Code Highlighter .

The repository is the facade to access the database. All application code outside the repository works with the database through it and only through it. Thus, the repository encapsulates the logic of working with the database; this is the object-relational mapping layer in our application. More precisely, the repository, or repository, is an interface for accessing data of one type — one model class, one database table in the simplest case. Access to data is organized through a collection of all repositories. Note that the repository interface is defined in terms of the application model: Entity is the base class for all classes of the application model ( POCO objects).

public abstract class Entity
{
protected Entity()
{
Id = -1;
}

public int Id { get ; set ; }

public bool IsNew()
{
return Id == -1;
}
}

* This source code was highlighted with Source Code Highlighter .

Generally speaking, the Id attribute is required only at the database level. At the application model level, the uniqueness of objects can be resolved without using an explicit identifier. Thus, the proposed solution is not an entirely honest solution to the problem of object-relational mapping from a theoretical point of view. However, in practice, using the primary key attribute in an application model often results in even more flexible schemes. The proposed solution is a compromise between the level of abstraction of the database layer and the flexibility of the architecture.
')
The IRepository interface methods provide a complete set of CRUD operations.

GetAll - returns the entire collection of objects of this type stored in the database. Filtering, sorting and other operations on the selection of objects are performed at a higher level, thanks to the use of the IQueryable <T> interface. See more in the Filters and Conveyor section.
Save - saves the model object in the database. If it is new, the INSERT operation is performed, otherwise UPDATE.
Delete - deletes an object from the database. There are two options for calling the function: with the id parameter of the record to be deleted and with the parameter of the object class of the application model.

Implementation

Suppose we have a database consisting of a single table Customers.

CREATE TABLE dbo.Customers
(
[Id] int IDENTITY (1,1) NOT NULL PRIMARY KEY ,
[Name] nvarchar(200) NOT NULL ,
[Address] nvarchar(1000) NULL ,
[Balance] money NOT NULL
)

* This source code was highlighted with Source Code Highlighter .

To begin with, we will add a dbml file to the project, in which classes of database model objects and their display properties will be defined. To do this, use the context menu of the Solution Explorer ( New Item ... -> Data-> LINQ to SQL Classes ) in Visual Studio. After the designer window appears, open Server Explorer and drag the Customers table into the designer window. This is what should happen:



As a result, Visual Studio will generate the Customer class of the database model. The model of the application itself is generally different from the database model, but in this example, they practically coincide. The following is a description of the Customer class of the application model:

public class Customer : Entity
{
public string Name { get ; set ; }
public string Address { get ; set ; }
public decimal Balance { get ; set ; }
}

* This source code was highlighted with Source Code Highlighter .

It's time to start implementing CustomersRepository , a repository of Customer objects. In order to avoid duplication of code when creating repositories for other classes of the model, most of the functionality is moved to the base class.

public abstract class RepositoryBase<T, DbT> : IRepository<T>
where T : Entity where DbT : class , IDbEntity, new ()
{
protected readonly DbContext context = new DbContext();

public IQueryable<T> GetAll()
{
return GetTable().Select(GetConverter());
}

public bool Save(T entity)
{
DbT dbEntity;

if (entity.IsNew())
{
dbEntity = new DbT();
}
else
{
dbEntity = GetTable().Where(x => x.Id == entity.Id).SingleOrDefault();
if (dbEntity == null )
{
return false ;
}
}

UpdateEntry(dbEntity, entity);

if (entity.IsNew())
{
GetTable().InsertOnSubmit(dbEntity);
}

context.SubmitChanges();

entity.Id = dbEntity.Id;
return true ;
}

public bool Delete( int id)
{
var dbEntity = GetTable().Where(x => x.Id == id).SingleOrDefault();

if (dbEntity == null )
{
return false ;
}

GetTable().DeleteOnSubmit(dbEntity);

context.SubmitChanges();
return true ;
}

public bool Delete(T entity)
{
return Delete(entity.Id);
}

protected abstract Table<DbT> GetTable();
protected abstract Expression<Func<DbT, T>> GetConverter();
protected abstract void UpdateEntry(DbT dbEntity, T entity);
}

* This source code was highlighted with Source Code Highlighter .

All classes of the LINQ to SQL model have a common IDbEntity interface:

public interface IDbEntity
{
int Id { get ; }
}

* This source code was highlighted with Source Code Highlighter .

Unfortunately, visual designer tools do not allow specifying the base class for LINQ to SQL objects. To do this, open the dbml file in the XML editor (Open with ...) and specify the EntityBase attribute on the Database element:

< Database EntityBase ="Data.Db.IDbEntity" ... >

* This source code was highlighted with Source Code Highlighter .

The following is a description of the CustomersRepository class.

public class CustomersRepository : RepositoryBase<Customer, Db.Entities.Customer>
{
protected override Table<Db.Entities.Customer> GetTable()
{
return context.Customers;
}

protected override Expression<Func<Db.Entities.Customer, Customer>> GetConverter()
{
return c => new Customer
{
Id = c.Id,
Name = c.Name,
Address = c.Address,
Balance = c.Balance
};
}

protected override void UpdateEntry(Db.Entities.Customer dbCustomer, Customer customer)
{
dbCustomer.Name = customer.Name;
dbCustomer.Address = customer.Address;
dbCustomer.Balance = customer.Balance;
}
}

* This source code was highlighted with Source Code Highlighter .

Filters and conveyor

The GetAll method of repositories returns an object that implements the IQueryable <T> interface. This allows you to apply filtering operations to the selection of objects (the Where method), sorting, and any other operations defined on IQueryable <T> .
For convenience, frequently used operations can be made into extension-methods. For example, filtering by customer name.

public static IQueryable<Customer> WithNameLike( this IQueryable<Customer> q, string name)
{
return q.Where(customer => customer.Name.StartsWith(name));
}

* This source code was highlighted with Source Code Highlighter .

Now we can use the repository as follows.

IRepository<Customer> rep = new CustomersRepository();
foreach ( var cust in rep.GetAll().WithNameLike(“Google”).OrderBy(x => x.Name)) { … }

* This source code was highlighted with Source Code Highlighter .

It doesn't matter what complexity the filters or other operations we use. No matter how many of them. As a result, exactly one database query will be executed. This principle is called deferred execution — a final SQL query is generated and executed only at the moment when you need to get a final sample. In this case, this happens immediately before the first iteration of the foreach loop.
An important advantage of the architecture is that filters, like the entire application with the exception of the repository layer, work on the application model and not on the database model.

Analysis

Next, we analyze the database queries generated by LINQ to SQL when performing a particular operation on the repository.

GetAll . In the case of an example:

rep.GetAll().WithNameLike(“Google”).OrderBy(x => x.Name)

* This source code was highlighted with Source Code Highlighter .

A single request is made:

exec sp_executesql N 'SELECT [t0].[Name], [t0].[Address], [t0].[Balance], [t0].[Id]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[Name] LIKE @p0
ORDER BY [t0].[Name]' ,N '@p0 nvarchar(7)' ,@p0=N 'Google%'

* This source code was highlighted with Source Code Highlighter .

The Save method for a new object performs a single INSERT request. For example:

exec sp_executesql N 'INSERT INTO [dbo].[Customers]([Name], [Address], [Balance])
VALUES (@p0, @p1, @p2)
SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]' ,N '@p0 nvarchar(6),@p1 nvarchar(3),@p2 money' ,@p0=N 'Google' ,@p1=N 'USA' ,@p2=$10000.0000

* This source code was highlighted with Source Code Highlighter .

In the case of a call to Save for an existing object or Delete , two queries are executed. The first is to retrieve a record from the database. For example:

exec sp_executesql N 'SELECT [t0].[Id], [t0].[Name], [t0].[Address], [t0].[Balance]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[Id] = @p0' ,N '@p0 int' ,@p0=29

* This source code was highlighted with Source Code Highlighter .

The second query is the direct execution of UPDATE or DELETE operations, respectively. Example for DELETE:

exec sp_executesql N 'DELETE FROM [dbo].[Customers] WHERE ([Id] = @p0) AND ([Name] = @p1) AND ([Address] = @p2) AND ([Balance] = @p3)' ,N '@p0 int,@p1 nvarchar(6),@p2 nvarchar(3),@p3 money' ,@p0=29,@p1=N 'Google' ,@p2=N 'USA' ,@p3=$10000.0000

* This source code was highlighted with Source Code Highlighter .

In the case of UPDATE and DELETE, the first query is redundant, but without it you cannot save or delete an object using standard LINQ to SQL tools.
One of the options for getting rid of an unnecessary request is to use stored procedures.

Conclusion

The main goal of the article is to give a general idea of ​​the repository pattern and its implementation in LINQ to SQL. The considered example of applying the approach is too simple. In real-world applications, many problems arise when implementing this architecture. Here are some of them.

• Conversion between a database model object and an application model object can be significantly more complex. In such cases, it is impossible to implement filters on the application model so that the final query can be translated to SQL.
• Often, as a result of a sample, it is necessary to obtain the result of joining (JOIN) of several tables, and not the data of just one table.
• Not all SQL operations and functions have their equivalent in LINQ.

Most problems can be solved, but these issues are beyond the scope of this article.

The source code for the article (ASP.NET MVC project).

Related Links

Pattern Repository (Martin Fowler)

Scott Guthrie LINQ to SQL Articles

Storefront MVC (screencasts):
Repository Pattern
Pipes and Filters