Full Anemia - Where can I move this data out of my Model?

Question

I was given a few dozen legacy SQL statements that are each hundred(s) of lines long. Each SQL is mapped to code with its own unique POCO in a shared Models project.

For example, the SQL Select Name, Birthday From People has an equivilent POCO in the Models project:

public class BirthdayPerson : SqlResultBase {
    public string Name { get; set; }
    public datetime Birthday { get; set; }

    //SqlResultBase abstraction:
    public string HardcodedSql { get {
        return "Select Name, Birthday From People";
    }}
}

In my DAL, I have a single generic SQL runner whose <T> represents the POCO for the SQL. So my business logic can call GetSqlResult<BirthdayPerson>():

public IEnumerable<T> GetSqlResult<T>() where T : SqlResultBase, new() {
    return context.Database.SqlQuery<T>((new T()).HardcodedSql);
}

The problem is that my Models library is used across the application, and I don't want SQL exposed across the application in that HardcodedSql property.

This is the architecture I'm using:

Answer 1

At first you have to separate your model (i.e. POCOs) from the SQL which actually belongs to the DAL. Inversion of Control is right way to do this. Instead of generic sql runner it is better to register mappings in the IoC container from abstract repositores (e.g. IRepository<MyPOCO>) to implementations that contain the SQLs.

EDIT: To be more concrete, a possible solution:

• Place all SQLs to a separate file(s) inside DAL, for example to a set of embedded resource files with name convention, e.g. Legacy-{0}.sql where {0} is name of the POCO.
• Create a generic implementation of legacy repository that uses POCO name as a key and picks corresponding Legacy-{0}.sql file from the resource set. Note that there may be other implementations as well that use other data access techniques, like ORM.
• In the composition root register explicitly all mappings from the legacy POCOs to the legacy implementation: IRepository<MyPOCO1> => LegacyRepo<MyPOCO1>; IRepository<MyPOCO2> => LegacyRepo<MyPOCO2>; etc. Moreover you may register other mappings from non-legacy entities to other implementations of repository.

Answer 2

The simplest solution would be to make HardcodedSql internal instead of public so it's only visible within a DAL Project. If the DAL is a separate project from the model you could use InternalsVisibleTo to expose it to that project. This assumes you can configure your project structure accordingly.

Answer 3

I suggest perhaps two possible ways of dealing with the question.

As for the first method, I would rather change how the sql is accessed and wrap the call locally in a method. So the class may have a function called public IEnumerable GetFromSql() you could pass in a context, or create a new one, I am not sure how you have setup EF in your project. this way you never publically expose the raw sql, as you would rather make it a private variable or local constant perhaps and simply access it from within the function.

As a second option, and I have actually done this and it turned out pretty great, was I moved all the sql to views and used EF to access them. That way there was no sql pollution in my code. Seeing that the models already exists, the result from calling the views would match the types that you already have.