Specification Pattern using SQL without an ORM, with the repository pattern

Question

I have been looking into the specification pattern that is briefly described in martin fowler's patterns of enterprise architecture under the repository pattern section, as well as several examples on the web. However, almost all of the examples/descriptions are created by utilizing an ORM and methods such as IsSatisfiedBy which are executed by the specification objects(and probably converted into SQL by the ORM).

I can see how you might adapt it to work with SQL, but due to a general lack of SQL examples, I was wondering if people are using this pattern with a SQL data access layer and the repository pattern, and their experience/approach with it if they are, or any alternatives that may be better suited to the task if there are any.

Answer 1

I believe that LINQ effectively implements the need to the specification pattern (I believe that this also ties into you request to work with SQL).

I suspect there are also APIs to spit out SQL without LINQ parse trees.

So, Entity Framework or LINQ to SQL would be worth looking into.

I hope this answers your question.

Answer 2

With Clean Architecture, you have these restrictions:

• Specifications are written in the application or domain layer
• Database entities and SQL are only allowed in the infrastructure layer

Therefore, you need to write your specifications

• without using SQL, and
• based on the domain entities.

If you are not using an ORM, the missing piece to making Specification Pattern work is the mapping from specifications and domain entities to SQL and database tables.

Entity Framework does this out of the box, based on either conventions, attributes or the Fluent API.

You can either

• look into other libraries that provide this feature set, or
• implement some kind of abstraction yourself, or
• break Clean Architecture and use SQL in the specifications.

Unfortunately, I haven't explored these options further yet.