2

I am going to write a stored procedure which will pull back X number of records based on a query. This SP may need to lock the records while it does the search. I am also using EF which I know can't use pessimistic locking.

Instead of having the filtering logic in the query itself I was thinking of using the specification pattern so the business logic will dictate the query. If I use this pattern and pass through the specification into my repository which in turn calls the SP how will the SP know what to do with the specification?

The specification can consist of criteria spanning multiple tables and sorting on different columns.

Would it make sense to use the specification pattern in this scenario or should I just create the SP with the query and filtering defined in there?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1180223
  • 111
  • 8
  • How much data are you talking about - how many rows to go through and what kind of result set? – Oded May 01 '12 at 08:58

2 Answers2

2

Specification pattern is for defining and executing query in your application - Linq is kind of specification. Stored procedure is for defining and executing query in database server directly. Those two are mostly contrary approaches.

There is possibility to create dynamic SQL inside stored procedure but that moves your logic to database which has nothing to do with DDD. Moreover it would be quite complex to allow such solution for arbitrary filter where you need to join additional tables.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
0

There is currently no way to translate a specification from a C# lambda expression into a structure that you can pass into a stored procedure in order to query the database. Specification pattern is usefull only if you intend to make full usage of ORM (not Store Procedures).

jnicolau
  • 106
  • 3