When working with layers of abstraction, especially in modern design structures like Clean Architecture, it can easily become complicated getting a simple database query from the UI to the physcial database provider being queried due to all the abstraction and indirection. An obvious way of abstracting an application from any single underlying data provider is by implementing the Repository pattern.
DISCLAIMER: Before going any further, please note that the wider approach to using LINQ Expressions is implemented using .NET Reflection which is well known for being a performance hog, so it would probably not be suitable for high data bandwidth or performance critical scenarios.
Preamble
The Repository pattern proposes that higher level code should be able to be unaware of a specific data provider hosting its data and instead focus on the concept of the data being held in a provider agnostic repository that can be updated and queried (and even swapped out for another data provider) as required without having to re-write any of the higher level code interacting with the repository.
At this point you might be wondering what all this has to do with LINQ expressions and why I’m about to suggest using them with the repository pattern. Well, let me explain…
Modern .NET design and development practices have moved away from writing code that directly accesses a specific data provider by writing plain old SQL to more flexible data query architectures adopting dependency injection of services, and all the indirection and abstraction of service contract implementations that entails.
With the introduction of ORM tools such as nHibernate and Entity Framework (EF), and LINQ as a data query language in its own right, the divide between DBAs and developers has narrowed significantly too, with developers being able to use code to query and even manage databases and database schemas instead of writing plain old SQL and relying on an DBA to update database schemas.
With modern app architecture models promoting more and more abstraction of service contracts from their provider-specific implementations, and with distributed apps spread across multiple APIs and databases, we can end up with overly complicated and sprawling code to do even the simplest thing if we aren’t careful.
Potential Solutions – Do We Need A Provider Agnostic Data Layer?
Before blindly going down the route of implementing the Repository pattern, an initial question we should think about is if we need it at all.
There’s no doubt that adopting the Repository pattern for data access meets modern design best practice, and will offer greater flexibility in your code longer term. But, this is often at the expense of code transparency, increased code complexity, and higher development costs.
There are good reasons why acronyms like KISS/KISP (Keep It Simple, Stupid/Please!) hang around year after year on developers’ lips. Do we really want/need to be adding extra complexity into our code all over the place?!
If an app, within its expected lifetime, isn’t likely to use a different underlying data provider then aren’t we just creating unnecessary complexity for complexity’s sake?! Wouldn’t developing a solution targeting the specific data provider we know we’re always going to be using and then optimising queries and wider data access code for that more sensible?!
In all the years, and on all the projects I’ve worked, I have only ever needed to replace the database provider twice. Despite that, a requirement to be able to do just that is often baked into business requirements unnecessarily – leading to a need to implement a repository pattern style solution at the data layer boundary.
But, having said all that, this article aims to propose a way of implementing the Repository pattern using .NET, Entity Framework, and LINQ as generically as possible so let’s assume we do need a data agnostic abstraction layer in this case!
Potential Solutions – Per Repository Contracts and Implementations
The most straightforward solution to implementing the Repository pattern would be to adopt a service contract per repository (i.e. per table or object-store) model, where we define repository services as a lot of interfaces that each need to be implemented on a per database-provider-specific code library basis. For example:
public interface ICustomersRepository
{
Task<IEnumerable<Customer>> GetAsync(int skip = 0, int take = -1);
Task<IEnumerable<Customer>> GetAsync(
string? firstName = null, string? lastName = null,
DateTime? birthDate = null, int skip = 0, int take = -1);
Task<Customer> AddAsync(Customer entity);
Task<Customer> ModifyAsync(Customer entity);
Task RemoveAsync(Customer entity);
}
public interface ICustomerOrdersRepository
{
Task<IEnumerable<CustomerOrder>> GetAsync(int skip = 0, int take = -1);
Task<IEnumerable<CustomerOrder>> GetAsync(
int customerId, DateTime? from = null, DateTime? until = null,
int skip = 0, int take = -1);
Task<CustomerOrder> AddAsync(CustomerOrder entity);
Task<CustomerOrder> ModifyAsync(CustomerOrder entity);
Task RemoveAsync(CustomerOrder entity);
}Code language: C# (cs)These data-type-specific repository service contracts allow developers to focus on delivering just the code required to meet the business requirements (which, in this case for the customers repository, we can identify as needing to be able to search for customers using a combination of any of their first name, last name, or date of birth).
Avoiding generic service contracts removes the design complexity that genericism potentially introduces, but at the expense of a lot more service definitions and implementation code.
If implementation libraries are required for multiple data providers then the development overhead for each of them is costed directly as separate libraries would need to be fully developed in each case.
Providing only one or two data provider implementations are expected long term then this approach is likely to be as cheap or cheaper than a more complex generic solution.
A service-contract per repository design introduce potentially unnecessary development cost when additional behaviours are added to any existing service contract. If, for example, new business requirements require querying customer data based on geography too then the service contract and ALL implementations of it would need to be updated.
So, what if we don’t want to write service contracts for every table or object-store in our database schemas? What if we want a generic solution that we can apply globally to any specific repository with no or limited additional development overheads?
Potential Solutions – A Genericly-Typed Repository Contract
A common approach to implementing a generic repository pattern in .NET is by using Generic Types. For example:
public interface IRepository<T>
{
Task<IEnumerable<T>> GetAsync(int skip = 0, int take = -1);
Task<IEnumerable<T>> GetAsync(Query query, int skip = 0, int take = -1);
Task<T> AddAsync(T entity);
Task<T> ModifyAsync(T entity);
Task RemoveAsync(T entity);
}Code language: C# (cs)In the above IRepository<T> service contract definition, we have the following:
- T is a generic type that represents the type of data stored in a particular repository instance.
- GetAsync() reads all records from the repository, with optional parameters for controlling data paging.
- GetAsync(query) reads all records that match the query in the repository, again with optional paging.
- AddAsync(entity) creates a new record in the repository.
- ModifyAsync(entity) allows an existing record in the repository to be updated.
- RemoveAsync(entity) allows an existing record to be deleted from the repository.
This design has the immediate advantage that it potentially targets multiple different tables/stores generically based on data entity type. That’s a good thing, right?! Well yes, it’s what we are striving for!
You may have noticed I’ve purposely glossed over something in the descriptions above, and that is the Query class used as a parameter in the 2nd GetAsync() method. This is a significant omission by me, since it is cleverly hiding the fact that we need some way to describe all filtering, sorting, and grouping conditions we might want to apply to our more generically targeted repository queries.
The subject of how to effectively query a generic repository instance should not be understated either. The task of working out how to define query conditions and meaningfully translate them into database queries we can execute is a complex one.
Over the years I’ve approached the design for this in different ways, most regularly by defining a set of custom classes that can be used to describe filtering, sorting, etc. in one way or another, along with dedicated methods on those classes that can generate SQL statements for them.
After the widespread adoption of Entity Framework and LINQ (which took a lot longer than I expected, considering how long it has been around), it was more difficult to implement these kinds of designs as developers were discouraged from executing SQL directly. Instead, they started using LINQ to define the queries, with .NET’s built-in handlers generating any SQL behind the scenes at runtime.
While we still can, technically speaking, execute SQL statements directly in EF, it isn’t advised or encouraged and so I sought a better alternative to my earlier designs/solutions.
LINQ expressions provide a powerful way of specifying filtering, sorting, grouping, and other data processing statements in a straightforward and easy to read code-based form.
They are type specific though, and this is the crux of the problem when trying to work with generic types across abstraction boundaries (where the underlying .NET types might change).
Designing a Generic Repository For Entity Framework
We’ll assume we’re doing our design work using Clean Architecture design principles, by defining service contracts and specific implementations of them are injected into the app at design time (dependency injection (DI)) or runtime (factory pattern style service injection).
In the application architecture diagram above, domain driven-design and the adapters pattern are used to abstract service contracts from any underlying implementations.
Core entities are defined as POCO (Plain Old Code Object) classes, and these are referenced as the data types in service contracts. Use cases (aka business rules/services code) implement appropriate service contracts and work entirely with the core POCO classes.
Likewise, the presentation layer references core entities and service contracts and specific service implementations are wired up in a services registry (aka services provider) at design time, if using DI; or, all available service implementations are registered at design time, and app configuration settings control which ones are used, in conjunction with one or more service factories, at runtime. Modern .NET code project templates include a lot of boilerplate DI functionality out-of-the-box, so that has tended to become the preferred mechanism used, as it means less work for developers per-project.
So, having defined our application architecture, let’s get on with developing our solution…
I went slightly beyond the basic generic repository service contract in my final solution design, deciding instead to adopt the CQRS pattern (Command/Query Responsibility Segregation) for better separation of concerns.
Put simply, CQRS proposes that separating the reading of data from the wider management of it (creation, modification, deletion) allows greater flexibility, as it supports the independent scaling of data querying and data modification concerns.
I defined my service contracts as follows, in its own Class Library code project that I named ConvertingLinqExpressions.Core.DataServices:
namespace ConvertingLinqExpressions.Core.DataServices.Contracts;
public interface IRepositoryReader<T>
{
Task<int> CountAsync();
Task<int> CountAsync(Query<T> query);
Task<IEnumerable<T>> GetAsync(int skip = 0, int take = -1);
Task<IEnumerable<T>> GetAsync(Query<T> query);
}
public interface IRepositoryEditor<T>
{
Task<T> AddAsync(T entity);
Task<T> ModifyAsync(T entity);
Task RemoveAsync(T entity);
}Code language: C# (cs)While designing the revised solution, it also occurred to me there may be merit in counting the likely number of results a query will return so that the most performant style of querying can be executed, so I added methods for that too.
That Query class we noted earlier is still required so let’s add that code to the same code project now:
namespace ConvertingLinqExpressions.Core.DataServices.Queries;
/// <summary>
/// Represents an action that can be performed in a repository query.
/// </summary>
public enum QueryAction
{
/// <summary>
/// Sort the returned dataset (described using <see cref="OrderingRule{T}"/>).
/// </summary>
OrderBy,
/// <summary>
/// Filter the dataset being queried (described using <see cref="Query{T}"/>).
/// </summary>
Where
}
/// <summary>
/// Describes sorting rules to apply to results returned by a repository query.
/// </summary>
public class OrderingRule
{
public OrderingRule(bool ascending, Expression<Func<T, object>> rule)
{
Ascending = = ascending;
Rule = rule;
}
/// <summary>
/// Gets or sets a value indicating if sorting should proceed in ascending order.
/// <para>If not then descending order will be applied.</para>
/// </summary>
public Ascending { get; set; }
/// <summary>
/// The sorting rule to apply.
/// </summary>
public Expression<Func<T, object>> Rule { get; set; }
}
/// <summary>
/// Describes an operation that should be performed by a repository query.
/// </summary>
public class Operation<T>
where T : class
{
public Operation(QueryAction action, object instruction)
{
Action = action;
Instruction = instruction;
}
/// <summary>
/// Gets or sets the instructions or (filtering) expression to apply.
/// </summary>
public object Instruction { get; set; }
/// <summary>
/// Gets or sets the type of query action to perform.
/// </summary>
public QueryAction Action { get; set; }
}
/// <summary>
/// Describes the paging of records when executing data queries.
/// </summary>
public class Paging()
{
/// <summary>
/// Creates a new instance of the class, with paging disabled.
/// </summary>
public Paging() : this(0, -1)
{
}
/// <summary>
/// Creates a new instance of the class, configuring the paging options.
/// </summary>
/// <param name="skip">The number of records to skip before reading them,
/// if paging is enabled.</param>
/// <param name="take">The maximum number of records that can be returned
/// by the query (or 0 to disable paging and return all results).</param>
public Paging(int skip, int take)
{
if (skip > -1 && take > 0)
{
Skip = skip;
Take = take;
}
else
{
Skip = 0;
Take = -1;
}
}
/// <summary>
/// Gets a value indicating if paging is enabled.
/// </summary>
public bool IsPaged => Skip > -1 && Take > 0;
/// <summary>
/// Gets the number of records that will be skipped before results are returned,
/// if paging is enabled.
/// </summary>
public int Skip { get; }
/// <summary>
/// Gets the maximum number of results that can be returned
/// (or 0 or a negative number to disable paging and return all results).
/// </summary>
public int Take { get; }
}
/// <summary>
/// Describes a repository query.
/// <para>
/// NOTE: all queries should be constructed using <see cref="QueryBuilder{T}"/>.
/// </para>
/// </summary>
public sealed class Query<T> where T : class
{
/// <summary>
/// Creates a new instance of the class.
/// </summary>
public Query()
{
}
/// <summary>
/// Creates a new instance of the class, for use by <see cref="QueryBuilder{T}"/>.
/// </summary>
internal Query(IEnumerable<Operation<T>> operations, Paging paging)
{
Operations = operations;
Paging = paging;
}
/// <summary>
/// Gets a collection of the operations that comprise the query.
/// </summary>
public IEnumerable<Operation<T>> Operations { get; } = new List<Operation<T>>();
/// <summary>
/// Gets details about the data paging controls to apply to the query.
/// </summary>
public Paging Paging { get; } = new Paging();
}
/// <summary>
/// Provides a helper class that can be used to build <see cref="Query{T}"/> instances.
/// </summary>
public class QueryBuilder<T> where T : class
{
private Query<T>? _query = null;
private readonly List<Operation<T>> _operations = [];
private Paging _paging = new();
/// <summary>
/// Applies paging to the records being queried from the data repository to help manage load and data bandwidth.
/// </summary>
/// <param name="skip">The number of records to skip before reading results.</param>
/// <param name="take">The maximum number of records to include in the current 'page' of data.</param>
/// <exception cref="InvalidOperationException">Raised if the query has already been built (as the builder is complete).</exception>
public QueryBuilder<T> AddPaging(int skip, int take)
{
if (_query != null) throw new InvalidOperationException("Changes cannot be applied after the query has been built.");
_paging = new Paging(skip, take);
return this;
}
/// <summary>
/// Builds the current configuration into a <see cref="Query{T}"/> instance.
/// </summary>
public Query<T> Build()
{
if (_query != null) return _query;
_query = new Query<T>(_operations, _paging);
return _query;
}
/// <summary>
/// Adds an order-by operation that will be used to sort the query results.
/// </summary>
/// <param name="rule">The sorting rule to apply.</param>
/// <exception cref="InvalidOperationException">Raised if the query has already been built (as the builder is complete).</exception>
public QueryBuilder<T> OrderBy(OrderingRule<T> rule)
{
if (_query != null) throw new InvalidOperationException("Changes cannot be applied after the query has been built.");
_operations.Add(new Operation<T>(QueryAction.OrderBy, rule));
return this;
}
/// <summary>
/// Adds filtering conditions to the query.
/// </summary>
/// <param name="filtering">The filtering expression to apply.</param>
/// <exception cref="InvalidOperationException">Raised if the query has already been built (as the builder is complete).</exception>
public QueryBuilder<T> Where(Expression<Func<T, bool>> filtering)
{
if (_query != null) throw new InvalidOperationException("Changes cannot be applied after the query has been built.");
_operations.Add(new Operation<T>(QueryAction.Where, filtering));
return this;
}
}Code language: C# (cs)Now we’ve got repository service contracts and supporting classes that can describe data queries in generic terms.
Converting Between Data Types When Executing Queries
A major hurdle we still have to overcome is how we’re going to rationalise queries initially defined using core entities into equivalent LINQ expressions defined using data-provider-specific entities.
It may not be immediately clear what I’m getting at so let’s talk through a worked example:
- We have some core entities defined in the scope of business domains:
- An Example.Core.Sales.Lead class is used to represent a potential customer’s details.
- An Example.Core.Finance.Debtor class is used to represent a customer with unpaid invoices.
- Data is being stored in a Microsoft SQL Server database so we have data-layer entities too:
- An Example.Data.SqlServer.Customer class is used to represent a record in the Customers table.
A salesperson wants to query a potential customer’s details so the code requests an instance of IRepositoryReader<Core.Sales.Lead> from the app’s services registry. The code builds a Query<Core.Sales.Lead> query instance and then calls IRepositoryReader‘s Get(Query query) method passing the query object in.
When the SQL Server specific implementation of IRepositoryReader<T> tries to execute the query, the LINQ expressions will be targeting the Sales.Lead object type so the EF query will fail because EF is expecting the Data.SqlServer.Customer type in queries it is executing.
This is the crux of our final problem. We need a way of converting Query<Sales.Lead> into an equivalent Query<Data.Customer> representation that EF can execute, and we need to be able to do it largely generically so we don’t have to write workarounds in the data layer code to get it all to work.
Fortunately, LINQ includes functionality to support most of this, and by adding some custom behaviours ourselves to complement the out-of-the-box experience we can achieve what we’re aiming for.
Key to all this working is how LINQ processes expressions. By intercepting the default handling process we can inject the changes we want to apply to the data types and properties the expressions are targeting.
I made a few design assumptions to simplify things:
- Since each repository instance would be limited to a single type
T, LINQ expressions should only have a single parameter type to convert. - LINQ expressions will be processed individually, and executed in the order they were added to the query, so each can also be converted individually.
- Entity Framework will provide the underlying database access in all cases. The solution will not support any database providers that do not support EF.
First we’ll create a custom implementation derived from System.Linq.Expressions.ExpressionVisitor that we can use for swapping parameter types in our expressions. We’ll create a new Class Library code project for this, called ConvertingLinqExpressions.Core.Linq.ExpressionConverter:
namespace ConvertingLinqExpressions.Core.Linq.ExpressionConverter;
/// <summary>
/// Provides a custom implementation of <see cref="ExpressionVisitor"/> for use
/// converting parameter expressions from one type to another.
/// </summary>
internal class ParameterExpressionReplacer : ExpressionVisitor
{
private readonly ParameterExpression _source;
private readonly ParameterExpression _target;
/// <summary>
/// Creates a new instance of the class.
/// </summary>
/// <param name="source">The source parameter.</param>
/// <param name="target">The target parameter.</param>
public ParameterExpressionReplacer(
ParameterExpression source, ParameterExpression target)
{
_source = source;
_target = target;
}
/// <inheritdoc />
[return: NotNullIfNotNull("node")]
public override Expression? Visit(Expression? node)
{
return node == _source ? _target : base.Visit(node);
}
}Code language: C# (cs)Now we have a class that can be used to switch out a source parameter for a target one, we can look at the wider query conversion in LINQ. A minor issue at this point is what happens if property names or data-types differ between core entities and their equivalent data entity siblings?
The easiest way to handle this is using a ‘by exception’ explicit mapping facility. We can achieve this simply by defining an interface which individual repository instances can implement or omit as necessary.
namespace ConvertingLinqExpressions.Core.Linq.ExpressionConverter;
/// <summary>
/// Provides a service contract that supplies property mapping expressions from a
/// source type to a target type.
/// </summary>
/// <typeparam name="TSourceEntity">
/// The type of the source entity in the original LINQ expression.</typeparam>
/// <typeparam name="TTargetEntity">
/// The type of the target entity expected in the modified expression.</typeparam>
public interface IPropertyMapper<TSourceEntity,TTargetEntity>
where TSourceEntity : class
where TTargetEntity : class
{
/// <summary>
/// Returns a Lambda expression targeting the <typeparamref name="TTargetEntity"/>
/// type whose modified expression is being mapped from an expression specifying
/// the <typeparamref name="TSourceEntity"/> type, or null if an explicit mapping
/// hasn't been specified.
/// </summary>
/// <param name="property">
/// The source entity's property details to map to the equivalent target property.
/// </param>
LambdaExpression? Map(MemberInfo property);
}Code language: C# (cs)What the above interface allows us to achieve is to explictly control how the conversion of individual properties is handled per class type (well, per property). If the Map() method returns null then calling code should use LINQ’s own built-in functionality to convert data between types. If an explicit expression is returned then that should be used in place of the existing one referencing the source entity’s property.
Any implementation of IPropertyMapper should adopt the following strategy (only explicitly handling those properties that are mismatches):
namespace ConvertingLinqExpressions.Data.SqlServer.Mappers.Customers;
public class SalesLeadMapper : IPropertyMapper<SalesLead, CustomerDataEntity>
{
public LambdaExpression? Map(MemberInfo property)
{
switch (property.Name)
{
case nameof(SalesLead.CustomerId):
Expression<Func<CustomerDataEntity, long>> mappedId = x => x.Id;
return mappedId;
case nameof(SalesLead.FirstName):
Expression<Func<CustomerDataEntity, string>> mappedName = x => x.GivenName;
return mappedName;
default:
// In all other cases, let LINQ sort the conversion out.
return null;
}
}
}Code language: C# (cs)Now that little piece of the puzzle is solved we can add the code required to convert whole LINQ query expressions.
namespace ConvertingLinqExpressions.Core.Linq.ExpressionConverter;
/// <summary>
/// Provides a custom implementation of <see cref="ExpressionVisitor"/> that can be
/// used to convert LINQ query expressions from a source data entity type
/// (<typeparamref name="TSourceEntity"/>) to a target entity type
/// (<typeparamref name="TTargetEntity"/>).
/// </summary>
/// <typeparam name="TSourceEntity">
/// The type of the source data entity in the original query expression.</typeparam>
/// <typeparam name="TTargetEntity">
/// The type of the target data entity expected in the modified query expression.</typeparam>
internal class QueryExpressionReplacer<TSourceEntity, TTargetEntity> : ExpressionVisitor where TSourceEntity : class where TTargetEntity : class
{
private readonly Type _targetType = typeof(TTargetEntity);
private readonly Type? _rootTargetType =
Nullable.GetUnderlyingType(typeof(TTargetEntity));
private readonly ParameterExpression _sourceParameter;
private readonly ParameterExpression _targetParameter;
private readonly IPropertyMapper<TSourceEntity, TTargetEntity>? _mapper;
/// <summary>
/// Creates a new instance of the class,
/// specifying the source and target parameter expressions.
/// </summary>
/// <param name="sourceParameter">The source parameter expression to convert.</param>
/// <param name="targetParameter">The target parameter expression to modify.</param>
/// <param name="mapper">(Optional, default: null) A mapper that can convert
/// a Lambda expression from a source to a target property.</param>
public QueryExpressionReplacer(
ParameterExpression sourceParameter,
ParameterExpression targetParameter,
IPropertyMapper<TSourceEntity, TTargetEntity>? mapper)
{
_sourceParameter = sourceParameter;
_targetParameter = targetParameter;
_mapper = mapper;
}
/// <inheritdoc />
[return: NotNullIfNotNull("node")]
public override Expression? Visit(Expression? node)
{
// NOTE: Overridden simply to capture and add value to any type mismatch
// errors raised during expression conversions.
try
{
return base.Visit(node);
}
catch (InvalidOperationException ex)
{
if (ex.Message.Contains(
"The binary operator Equal is not defined for the types ") &&
ex.Message.Contains("'System.Nullable"))
{
throw new InvalidOperationException(
"An error occurred while attempting to convert a LINQ expression " +
"to target a different parameter type. " +
"A property type mismatch between nullable and non-nullable " +
"types is the most likely cause. " +
"It can usually be fixed by coercing any null values to a " +
"default value instead of null in the " +
"IPropertyMapper<TSourceEntity,TTargetEntity> implementation " +
"for the affected property.", ex);
}
throw;
}
}
/// <inheritdoc />
protected override Expression VisitMember(MemberExpression node)
{
if (node.Expression == null || node.Expression.Type != typeof(TSourceEntity))
{
// Unless we're targeting the source entity type, just process normally.
return base.VisitMember(node);
}
// Source data type detected so convert to the target data type instead...
ParameterExpression? parameter = Visit(node.Expression) as ParameterExpression;
if (parameter is null ||
(_mapper is null && !ValidateAsCompatibleWithTarget(parameter!.Type)))
{
// Fall back on default behaviours if the expression cannot be converted.
return base.VisitMember(node);
}
LambdaExpression? lambda = _mapper?.Map(node.Member);
if (lambda != null)
{
// An explicit type+property mapping conversion was achieved, so use that.
return new ParameterExpressionReplacer(
lambda.Parameters.Single(), parameter).Visit(lambda.Body);
}
// In all other cases fall back on an automatic property conversion
// using LINQ's built in behaviours.
return Expression.Property(parameter, node.Member.Name);
}
/// <inheritdoc />
protected override Expression VisitParameter(ParameterExpression node)
{
return node == _sourceParameter ? _targetParameter : node;
}
/// <summary>
/// Returns true if the supplied type is compatible with the target entity type,
/// or false otherwise.
/// <para>
/// NOTE: nullable and equivalent non-nullable types are considered compatible here.
/// </para>
/// </summary>
/// <param name="parameterType">The type used in the expression.</param>
private bool ValidateAsCompatibleWithTarget(Type parameterType)
{
Type? underlyingType = Nullable.GetUnderlyingType(parameterType);
if (parameterType== _targetType) return true;
if (underlyingType == null && _rootTargetType != null &&
parameterType == _rootTargetType)
{
return true;
}
if (underlyingType != null && _rootTargetType == null &&
underlyingType == _targetType)
{
return true;
}
if (underlyingType != null && _rootTargetType != null &&
underlyingType == _rootTargetType)
{
return true;
}
return false;
}
}Code language: C# (cs)We’ve now got the tools for converting LINQ parameter types and member references, and also for converting wider (query-based) expressions.
Next we need to expose this functionality in a way that data layer code can consume, so we’ll add a helper class to the expression-converter code project.
namespace ConvertingLinqExpressions.Core.Linq.ExpressionConverter.Helpers;
/// <summary>
/// Provides helper functions for use converting LINQ expressions between data types
/// to allow filtering, sorting, etc. to be defined as LINQ expressions using one set
/// of classes (e.g. core business data types), and for them to be converted into
/// equivalent expressions targeting a different set of classes (e.g. database
/// provider specific data types).
/// </summary>
public static class LinqExpressionsHelper
{
/// <summary>
/// Converts a LINQ expression targeting <typeparamref name="TSourceEntity"/>
/// to an equivalent expressions targeting <typeparamref name="TTargetEntity"/>.
/// </summary>
/// <typeparam name="TSourceEntity">
/// The type of the data entity referenced in the original expression.</typeparam>
/// <typeparam name="TSourceReturnType">The return type in the original expression.</typeparam>
/// <typeparam name="TTargetEntity">
/// The expected data entity type to be referenced in the modified expression.</typeparam>
/// <typeparam name="TTargetReturnType">
/// The expected return type in the modified expression.</typeparam>
/// <param name="source">The source expression to modify.</param>
/// <param name="mapper">(Optional, default: null) A property mapper that can be
/// used to explicitly convert between referenced properties in expressions, if
/// required.</param>
public static Expression<Func<TTargetEntity, TTargetReturnType>>
ConvertExpression<TSourceEntity, TSourceReturnType, TTargetEntity, TTargetReturnType>(
Expression<Func<TSourceEntity, TSourceReturnType>> source,
IPropertyMapper<TSourceEntity, TTargetEntity>? mapper = null
)
where TSourceEntity : class
where TTargetEntity : class
{
ParameterExpression targetParameter =
Expression.Parameter(typeof(TTargetEntity));
QueryExpressionReplacer<TSourceEntity, TTargetEntity> replacer =
new(source.Parameters[0], targetParameter, mapper);
Expression targetBody = replacer.Visit(source.Body);
Expression<Func<TTargetEntity, TTargetReturnType>> output =
Expression.Lambda<Func<TTargetEntity, TTargetReturnType>>(
targetBody, targetParameter);
return output;
}
/// <summary>
/// Converts a filtering predicate LINQ expression targeting
/// <typeparamref name="TSourceEntity"/> to an equivalent expression targeting
/// <typeparamref name="TTargetEntity"/>.
/// <para>
/// This is a specialised conversion method for use with where clause expressions
/// only.
/// </para>
/// </summary>
/// <typeparam name="TSourceEntity">
/// The type of the data entity referenced in the original expression.</typeparam>
/// <typeparam name="TTargetEntity">
/// The expected data entity type to be referenced in the modified expression.</typeparam>
/// <param name="predicate">The filtering expression to convert.</param>
/// <param name="mapper">(Optional, default: null) A property mapper that can be
/// used to explicitly convert between referenced properties in expressions,
/// if required.</param>
public static Expression<Func<TTargetEntity, bool>>
ConvertFilteringPredicate<TSourceEntity, TTargetEntity>(
Expression<Func<TSourceEntity, bool>> predicate,
IPropertyMapper<TSourceEntity, TTargetEntity>? mapper = null
)
where TSourceEntity : class
where TTargetEntity : class
{
return ConvertExpression<TSourceEntity, bool, TTargetEntity, bool>(
predicate, mapper);
}
/// <summary>
/// Returns an instance of <see cref="IQueryable{T}"/> preconfigured to query an
/// underlying repository based on the supplied set of query operations.
/// </summary>
/// <typeparam name="TSourceEntity">
/// The type of the data entity referenced in the original expression.</typeparam>
/// <typeparam name="TTargetEntity">
/// The expected data entity type to be referenced in the modified expression.</typeparam>
/// <param name="repository">
/// An initialised instance of <see cref="IQueryable{T}"/> connected to the
/// repository that will be updated with the query operations and then returned.
/// </param>
/// <param name="query">The query operations to apply.</param>
/// <param name="mapper">(Optional, default: null) A property mapper that can be
/// used to explicitly convert between referenced properties in expressions,
/// if required.</param>
/// <exception cref="InvalidOperationException">Raised if the operation configuration is invalid.</exception>
/// <exception cref="ArgumentException">Raised if the query action is not recognised.</exception>
public static IQueryable<TTargetEntity>
ConvertToQueryable<TSourceEntity, TTargetEntity>(
IQueryable<TTargetEntity> repository,
Query<TSourceEntity> query,
IPropertyMapper<TSourceEntity, TTargetEntity>? mapper = null
)
where TSourceEntity : class
where TTargetEntity : class
{
IQueryable<TTargetEntity> queryable = repository.AsQueryable();
IEnumerable<Operation<TSourceEntity>> operations = query.Operations;
foreach (Operation<TSourceEntity> operation in operations)
{
switch (operation.Action)
{
case QueryAction.OrderBy:
if (operation.Instruction is not OrderingRule<TSourceEntity> orderingRule)
{
throw new InvalidOperationException(
$"The specified ordering rule is invalid (operation-instruction: {operation.Instruction}).");
}
queryable = orderingRule.Ascending
? queryable.OrderBy(ConvertExpression<TSourceEntity, object, TTargetEntity, object>(orderingRule.Rule))
: queryable.OrderByDescending(ConvertExpression<TSourceEntity, object, TTargetEntity, object>(orderingRule.Rule));
break;
case QueryAction.Where:
if (operation.Instruction is not Expression<Func<TSourceEntity, bool>> whereClause)
{
throw new InvalidOperationException(
$"A where clause expression was expected (operation-instruction: {operation.Instruction}).");
}
queryable = queryable.Where(ConvertFilteringPredicate(whereClause, mapper));
break;
default:
throw new ArgumentException($"The operation action type '{operation.Action}' was not recognised.");
}
}
if (query.Paging.IsPaged)
{
queryable = queryable.Skip(query.Paging.Skip).Take(query.Paging.Take);
}
return queryable;
}
}Code language: C# (cs)That’s basically it from an expression conversion perspective!
So now business services code can just request an instance of IRepository<SalesLead> from the services registry (or, more likely have it DI injected automatically in the service’s constructor when the business service is requested), and then make a call something like this:
private IRepository<SalesLead> _leads; // (Populated in class constructor!)
// Constructor omitted for brevity.
public async Task<IEnumerable<SalesLead>> SearchForLeadsAsync(
string? firstName = null, string? lastName = null,
string? salesAccountManager = null)
{
if (string.IsNullOrEmpty(firstName) &&
string.IsNullOrEmpty(lastName) &&
string.IsNullOrEmpty(salesAccountManager))
{
return new List<SalesLead>();
}
QueryBuilder<SalesLead> builder = new();
if (!string.IsNullOrEmpty(firstName))
{
builder.Where(x => x.FirstName == firstName);
}
if (!string.IsNullOrEmpty(lastName))
{
builder.Where(x => x.LastName == lastName);
}
if (!string.IsNullOrEmpty(salesAccountManager))
{
builder.Where(x => x.SalesContact == salesAccountManager);
}
return await _leads.GetAsync(builder.Build());
}Code language: C# (cs)Implementing Per-Data-Provider Repository Services
Now all the LINQ expression conversion has been worked out, the last area of the design still left to do is implementing IRepository<T> in the data layer code. This will need to be a data-provider specific, or at least ORM tool-specific, implementation.
For EF, it should be possible to develop a single generic repository service implementation that should work across different database providers (MS SQL Server, PostgreSQL, etc.) providing EF is used to communicate with them throughout.
For example, consider a simple database schema that stores customer names and addresses in a pair of tables. The core entities used in business services code (i.e. outside of the data layer) are defined like this:
namespace ConvertingLinqExpressions.Core.Customers.Entities;
/// <summary>
/// Describes a postal address.
/// </summary>
public class Address
{
public string HouseName { get; set; } = "";
public string HouseNumber { get; set; } = "";
public string Street { get; set; } = "";
public string Locale { get; set; } = "";
public string TownOrCity { get; set; } = "";
public string CountyOrState { get; set; } = "";
public string Country { get; set; } = "";
public string PostalCode { get; set; } = "";
}
/// <summary>
/// Describes a unique customer on the system.
/// </summary>
public class Customer
{
public long CustomerId { get; set; }
public string Title { get; set; } = "";
public string FirstName { get; set; } = "";
public string LastName { get; set; } = "";
public DateTime DateOfBirth { get; set; } = DateTime.MinValue;
public Address ShippingAddress { get; set; } = new();
}Code language: C# (cs)MS SQL Server is being used for the back-end database, and EF has been chosen to communicate with it. Equivalent data entities are defined in the data layer code, fully annotated for use by EF.
namespace ConvertingLinqExpressions.Data.MsSqlServer.Entities;
[Table("CUSTOMER_ADDRESSES")]
public class CustomerAddressDataEntity
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public long Id { get; set; }
[StringLength(200)]
public string HouseName { get; set; } = "";
[StringLength(20)]
public string HouseNumber { get; set; } = "";
[StringLength(200)]
public string Street { get; set; } = "";
[StringLength(200)]
public string Locale { get; set; } = "";
[StringLength(200)]
public string TownOrCity { get; set; } = "";
[StringLength(200)]
public string CountyOrState { get; set; } = "";
[StringLength(200)]
public string Country { get; set; } = "";
[StringLength(25)]
public string PostalCode { get; set; } = "";
public Address ToAddress()
{
return new Address
{
HouseName = HouseName,
HouseNumber = HouseNumber,
Street = Street,
Locale = Locale,
TownOrCity = TownOrCity,
CountyOrState = CountyOrState,
PostalCode = PostalCode,
Country = Country
};
}
public static CustomerAddressDataEntity FromCustomer(Customer entity, long addressId = 0)
{
return new CustomerAddressDataEntity
{
Id = addressId,
HouseName = entity.ShippingAddress.HouseName,
HouseNumber = entity.ShippingAddress.HouseNumber,
Street = entity.ShippingAddress.Street,
Locale = entity.ShippingAddress.Locale,
TownOrCity = entity.ShippingAddress.TownOrCity,
CountyOrState = entity.ShippingAddress.CountyOrState,
PostalCode = entity.ShippingAddress.PostalCode,
Country = entity.ShippingAddress.Country
};
}
}
[Table("CUSTOMERS")]
public class CustomerDataEntity
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public long Id { get; set; }
[StringLength(10)]
public string Title { get; set; } = "";
[Required]
[StringLength(100)]
public string GivenName { get; set; } = "";
[Required]
[StringLength(100)]
public string LastName { get; set; } = "";
/// <summary>
/// Gets or sets the customer's date of birth, ISO formatted as text: "yyyy-mm-dd".
/// </summary>
[StringLength(20)]
public string BirthDate { get; set; } = "";
[DefaultValue(0)]
[ForeignKey(nameof(CustomerAddressDataEntity.Id))]
public long AddressId { get; set; } = -1;
public DateTime TryParseBirthDate()
{
if (DateTime.TryParseExact(BirthDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None, out DateTime dateOfBirth))
{
return dateOfBirth;
}
return DateTime.MinValue;
}
public Customer ToCustomer(CustomerAddressDataEntity? address = null)
{
return new Customer
{
CustomerId = Id,
Title = Title,
FirstName = GivenName,
LastName = LastName,
DateOfBirth = TryParseBirthDate(),
ShippingAddress = new Address
{
HouseName = address?.HouseName ?? "",
HouseNumber = address?.HouseNumber ?? "",
Street = address?.Street ?? "",
Locale = address?.Locale ?? "",
TownOrCity = address?.TownOrCity ?? "",
CountyOrState = address?.CountyOrState ?? "",
PostalCode = address?.PostalCode ?? "",
Country = address?.Country ?? ""
}
};
}
public static CustomerDataEntity FromCustomer(Customer entity, long? addressId = null)
{
return new CustomerDataEntity
{
Id = entity.CustomerId,
Title = entity.Title,
GivenName = entity.FirstName,
LastName = entity.LastName,
BirthDate = entity.DateOfBirth.ToString("yyyy-MM-dd"),
AddressId = addressId ?? 0
};
}
}Code language: C# (cs)You may have noticed a couple of type conversion helper methods attached to the data entities above. I tend to prefer explicit type mapping over using something like AutoMapper for code readability (co-located code) and for the performance gains.
The observant amongst you may have also noticed some differences between the core entity definitions and the data entity ones. Notably, the core Address class has no concept of a unique record ID, the forename property of the Customer class is variously called ‘GivenName’ and ‘FirstName’, and the database is storing the date of birth as an ISO format string.
Due to name mismatching, we therefore need a property mapper for any LINQ conversions.
namespace ConvertingLinqExpressions.Data.MsSqlServer.Entities.Mappers;
public class CustomerMapper : IPropertyMapper<Customer, CustomerDataEntity>
{
public LambdaExpression? Map(MemberInfo property)
{
switch (property.Name)
{
case nameof(Customer.CustomerId):
Expression<Func<CustomerDataEntity, long>> mappedId = x => x.Id;
return mappedId;
case nameof(Customer.FirstName):
Expression<Func<CustomerDataEntity, string>> mappedName = x => x.GivenName;
return mappedName;
default:
return null;
}
}
}Code language: C# (cs)To make things easier we’ll add a mappings helper class that can be used to simplify getting property mappers and converting between core and data entity types.
namespace ConvertingLinqExpressions.Data.Helpers;
/// <summary>
/// Defines a service contract that allows object data to be converted between
/// two explicit types.
/// </summary>
/// <typeparam name="TSource">The type of the source object.</typeparam>
/// <typeparam name="TTarget">The type of object that should be returned.</typeparam>
public interface ITypeConverter<TSource, TTarget>
where TSource : class
where TTarget : class
{
/// <summary>
/// Returns a <typeparamref name="TTarget"/> representation of the supplied
/// <typeparamref name="TSource"/> object data.
/// </summary>
/// <param name="entity">The source object to convert.</param>
TTarget Convert(TSource entity);
}
/// <summary>
/// Provides helper methods of use when converting between types.
/// </summary>
public static class MappingHelper
{
/// <summary>
/// Holds a collection of registered
/// <see cref="ITypeConverter{TSource, TTarget}"/> objects.
/// </summary>
private static List<object> Converters = [];
/// <summary>
/// Holds a collection of the registered
/// <see cref="IPropertyMapper{TSourceEntity, TTargetEntity}"/> objects.
/// </summary>
private static List<object> Mappers = [];
/// <summary>
/// Adds an instance of <see cref="ITypeConverter{TSource, TTarget}"/> to the helper class,
/// so it can be used to convert between the two specified types.
/// </summary>
/// <typeparam name="TSource">The expected type of the source data.</typeparam>
/// <typeparam name="TTarget">The expected type of the target data.</typeparam>
/// <param name="converter">The converter to register.</param>
public static void RegisterConverter<TSource, TTarget>(ITypeConverter<TSource, TTarget> converter)
where TSource : class
where TTarget : class
{
Converters.Add(converter);
}
/// <summary>
/// Adds an instance of <see cref="IPropertyMapper{TSourceEntity, TTargetEntity}"/>
/// to the helper class.
/// </summary>
/// <param name="mapper">The property mapper to register.</param>
public static void RegisterMapper<TSource, TTarget>(IPropertyMapper<TSource, TTarget> mapper)
where TSource : class
where TTarget : class
{
Mappers.Add(mapper);
}
/// <summary>
/// Returns a property mapper for the specified source and target entity types,
/// or null if no mapper exists.
/// </summary>
/// <typeparam name="TCoreEntity">The source entity type in the mapping activity.</typeparam>
/// <typeparam name="TDataEntity">The target entity type in the mapping activity.</typeparam>
public static IPropertyMapper<TCoreEntity, TDataEntity>? GetMapper<TCoreEntity, TDataEntity>()
where TCoreEntity : class
where TDataEntity : class
{
for (int i = 0; i < Mappers.Count; i++)
{
if (Mappers[i] is IPropertyMapper<TCoreEntity, TDataEntity>)
{
return (IPropertyMapper<TCoreEntity, TDataEntity>)Mappers[i];
}
}
return null;
}
/// <summary>
/// Returns a new instance of <typeparamref name="TTarget"/> populated with data from
/// <typeparamref name="TSource"/> if a conversion exists, or raises an exception otherwise.
/// </summary>
/// <typeparam name="TSource">The type of the source data.</typeparam>
/// <typeparam name="TTarget">The target type of the data to return.</typeparam>
/// <param name="source">The source object to convert.</param>
/// <exception cref="ArgumentException">Raised if a conversion between the specified types does not exist.</exception>
public static TTarget ConvertTo<TSource, TTarget>(TSource source)
where TSource : class
where TTarget : class
{
ITypeConverter<TSource, TTarget>? converter = GetConverter<TSource, TTarget>();
if (converter is not null)
{
return converter.Convert(source);
}
throw new ArgumentException(
$"No data mapping routine exists for converting from {typeof(TSource).Name} to {typeof(TTarget).Name}.");
}
/// <summary>
/// Returns the matching type converter, or null.
/// </summary>
/// <typeparam name="TSource">The expected type of the source data.</typeparam>
/// <typeparam name="TTarget">The expected type of the target data.</typeparam>
private static ITypeConverter<TSource, TTarget>? GetConverter<TSource, TTarget>()
where TSource : class
where TTarget : class
{
for (int i = 0; i < Converters.Count; i++)
{
if (Converters[i] is ITypeConverter<TSource, TTarget>)
{
return (ITypeConverter<TSource, TTarget>)Converters[i];
}
}
return null;
}
}
Code language: C# (cs)Implementations of ITypeConverter<TSource,TTarget> should be simple in most cases and can be combined with mapping methods that we added to the data entities earlier. For example:
public class CustomerTypeConverter
: ITypeConverter<CustomerDataEntity, Customer>
{
public Customer Convert(CustomerDataEntity entity)
{
return entity.ToCustomer();
}
}
public class CustomerDataEntityTypeConverter
: ITypeConverter<Customer, CustomerDataEntity>
{
public CustomerDataEntity Convert(Customer entity)
{
return CustomerDataEntity.FromCustomer(entity);
}
}
// Registration helper classes/methods could then be used to register
// any type converters and property mappers as bulk operations.
// For example...
public static class MappingRegistrationsHelper
{
public static void RegisterTypeConverters()
{
MappingHelper.RegisterConverter(new CustomerTypeConverter());
MappingHelper.RegisterConverter(new CustomerDataEntityTypeConverter());
// etc.
}
public static void RegisterPropertyMappers()
{
MappingHelper.RegisterMapper(new CustomerMapper());
// etc.
}
}Code language: C# (cs)Now let’s implement the reader side of our generic repository service contract.
namespace ConvertingLinqExpressions.Data.MsSqlServer.Services;
public class RepositoryReader<TCoreEntity, TDataEntity> : IRepositoryReader<TCoreEntity>
where TCoreEntity : class
where TDataEntity : class
{
private readonly DbContext _context;
public RepositoryReader(DbContext context)
{
_context = context;
}
public asyc Task<int> CountAsync()
{
return await _context.Set<TDataEntity>().CountAsync();
}
public asyc Task<int> CountAsync(Query<TCoreEntity> query)
{
IQueryable<TDataEntity> repository = _context.Set<TDataEntity>().AsQueryable();
IQueryable<TDataEntity> queryable =
LinqExpressionsHelper.ConvertToQueryable<TCoreEntity, TDataEntity>(
repository, query, MappingHelper.GetMapper<TCoreEntity,TDataEntity>());
return await queryable.CountAsync();
}
public asyc Task<IEnumerable<TCoreEntity>> GetAsync(int skip = 0, int take = -1)
{
List<TDataEntity> dbResults =
await _context.Set<TDataEntity>().Skip(skip).Take(take).ToListAsync();
return ProcessDbResults(dbResults);
}
public asyc Task<IEnumerable<TCoreEntity>> GetAsync(Query<TCoreEntity> query)
{
IQueryable<TDataEntity> repository = _context.Set<TDataEntity>().AsQueryable();
IQueryable<TDataEntity> queryable =
LinqExpressionsHelper.ConvertToQueryable<TCoreEntity, TDataEntity>(
repository, query, MappingHelper.GetMapper<TCoreEntity,TDataEntity>());
IEnumerable<TDataEntity> dbResults =
query.Paging.IsPaged
? await queryable.Skip(query.Paging.Skip).Take(query.Paging.Take).ToListAsync()
: await queryable.ToListAsync();
return ProcessDbResults(dbResults);
}
private static IEnumerable<TCoreEntity> ProcessDbResults(
IEnumerable<TDataEntity> dbResults)
{
List<TCoreEntity> results = [];
foreach (TDataEntity entity in dbResults)
{
results.Add(MappingHelper.ConvertTo<TDataEntity, TCoreEntity>(entity));
}
return results;
}
}Code language: C# (cs)The above implementation would work for straightforward database schemas where you only need to model the schema as one-table per repository service instance. Our Customer core entity includes the shipping address though, which is held in a separate table in our data model, so the above code won’t work out-of-the-box.
We could rabbit hole in the design here, and add more complexity to the Query class so that it can handle linked tables using something like EF’s Include or IncludeAsString approaches, but that would risk a provider specific approach influencing the design and make it less agnostic, so we’ll avoid doing that.
In this case, the easiest solution is to develop a separate class implementation just for our slightly left-field use case instead:
namespace ConvertingLinqExpressions.Data.MsSqlServer.Services;
public class CustomersWithAddressRepository : IRepositoryReader<Customer>
{
private readonly DbContext _context;
public (DbContext context)
{
_context = context;
}
public asyc Task<int> CountAsync()
{
return await _context.Set<CustomerDataEntity>().CountAsync();
}
public asyc Task<int> CountAsync(Query<Customer> query)
{
IQueryable<CustomerDataEntity> repository =
_context.Set<CustomerDataEntity>().AsQueryable();
IQueryable<CustomerDataEntity> queryable =
LinqExpressionsHelper.ConvertToQueryable<Customer, CustomerDataEntity>(
repository, query, new CustomerMapper());
return await queryable.CountAsync();
}
public asyc Task<IEnumerable<Customer>> GetAsync(int skip = 0, int take = -1)
{
List<CustomerDataEntity> dbResults =
skip > -1 && take > 0
? await _context.Set<CustomerDataEntity>().Skip(skip).Take(take).ToListAsync()
: await _context.Set<CustomerDataEntity>().ToListAsync();
return ProcessDbResults(_context, dbResults);
}
public asyc Task<IEnumerable<Customer>> GetAsync(Query<Customer> query)
{
IQueryable<CustomerDataEntity> repository =
_context.Set<CustomerDataEntity>().AsQueryable();
IQueryable<CustomerDataEntity> queryable =
LinqExpressionsHelper.ConvertToQueryable<Customer, CustomerDataEntity>(
repository, query, new CustomerMapper());
IEnumerable<CustomerDataEntity> dbResults =
query.Paging.IsPaged
? await queryable.Skip(query.Paging.Skip).Take(query.Paging.Take).ToListAsync()
: await queryable.ToListAsync();
return ProcessDbResults(_context, dbResults);
}
private static IEnumerable<Customer> ProcessDbResults(
DbContext context, IEnumerable<CustomerDataEntity> dbResults)
{
List<Customer> results = [];
CustomerAddressDataEntity? dbAddress;
foreach (CustomerDataEntity entity in dbResults)
{
dbAddress = null;
if (entity.AddressId > 0)
{
dbAddress =
context
.Set<CustomerAddressDataEntity>()
.FirstOrDefault(x => x.Id == entity.AddressId);
}
results.Add(entity.ToCustomer(dbAddress));
}
return results;
}
}Code language: C# (cs)Not much extra code, and only required for those edge cases where the RepositoryReader class won’t fit the bill.
That’s it! A fully abstracted implementation of the Repository pattern where per-repository implementation code is minimised to a few lines of code in most scenarios.