Translate Generic Query of Business Layer to Data Access Layer

Question

I am in the middle of rewriting legacy software to .Net and have created a Data Access Layer using Linq 2 SQL that is working pretty well. The database I am working with has over 230 tables and is not something I can really change. The problem I am running into is with the Business Layer. I would like for the developers to be able to query the business objects and have those queries map to the data layer. So something like Customers.Query(c=>c.ID=="MyID") and have that be able to be passed to my DAL, context.CUSTOMERS.Query(c=>c.UID == "MyID") I have generic Query methods in my DAL that will allow me to pass in the DAL query.

This is where I am stuck. I can create a method that uses an Expression but how do I get and then map those fields to the corresponding DAL fields and get the value that is trying to be matched. What I don't want is to have to expose the DAL objects to the end developers who are doing presentation layer stuff. I am open to ideas and suggestions.

Answer 1

Do the developers need to query the business objects using an expression? The reason I ask is because mapping expressions may be complicated. At some point, some mapping has to be done. The DAL is usually the mapping layer that turns DB objects into domain objects, and vice-versa.

Two other approaches to consider:

1. Use the repository pattern, where the caller passes in a query object. The DAL would be responsible for turning that query object into an expression. An example of the repository pattern is shown in a question that I asked.

2. Expose more specific methods, like:

   public Customer GetCustomersById(int id) { ... }

I believe either of those two approaches would make things easier to query.

Answer 2

So I think I have been able to find a solution to this. Using ExpressionVisitor and help from this post

I modified the VisitMember method:

    protected override Expression VisitMember(MemberExpression node)
    {
        string sDbField = ((SFWBusinessAttributes)node.Expression.Type.GetProperty(node.Member.Name).GetCustomAttribu`tes(typeof(SFWBusinessAttributes), true)[0]).DBColumn;
        var expr = Visit(node.Expression);
        if (expr.Type != node.Type)
        {
            MemberInfo newMember = expr.Type.GetMember(sDbField).Single();
            return Expression.MakeMemberAccess(expr, newMember);
        }
        return base.VisitMember(node);
    }

To pull the Attribute off of the Property of the Business Object.

To call everything I can do from the app:

    BusObj.Query(a => a.IsDeleted != true && a.Company.Contains("Demo"))

Which calls a method in the business object

    public List<Account> Query(Expression<Func<Account, bool>> expression)
    {
        using (Data.CustomerData data = new Data.CustomerData(_connstring))
        {
             return MapList(data.Query<Data.Database.ACCOUNT>(expression.Convert<Account, Data.Database.ACCOUNT>()).ToList());
        }

Performance seems pretty good, I know there is going to be a hit with the mapping but it is something I can live with for now.