What is the best way to encapsulate Linq to SQL data access?

Question

I've been trying to encapsulate the object mapping in a projects data repository. Perhaps EF will provide the level of abstraction required but for a range of reasons I am using Linq to SQL at the moment. The following code aims to return the users in the database as a list of ModUser objects, where ModUser is POCO that the repository exposes:

public List<ModUser> GetUsers() {
    Users.Select(MapUser).ToList();
}

public Expression<Func<User, ModUser>> MapUser {
    get {
        return u => new ModUser() {
            UserId = u.User_Id,
            UserResources = u.Resources(MapResource)
        }
    }
}

public Expression<Func<Resource, ModResource>> MapResource { ...

The code will fail as I can't call the MapResource expression since I am trying to call it from inside another expression. I have managed to get around this by replacing 'MapResource' with u => new ModResource(), then using the ExpressionVisitor to find this placeholder node and replace it with the MapResource expression.

I also have similar issues when I try to assign a property of ModUser with an expression involving a single property, i.e. UserResource = MapResource. I have managed to get around this second issue by manually combining the expressions required using the methods on the Expression class.

I do realise that I could change the code above to

UserResources = u.Resources(r => MapResource.Compile().Invoke(r));

But then the final SQL query produced will need to obtain all the attributes of r, not just the ones needed by MapResouce, since we are now dealing with a function. Also, should MapResouce require access to further tables on r it won't be possible since it is being used as a function not an expression. I could set DeferredLoadingEnabled to true but that would spawn off a multitude of individual queries rather than modifying the main query to join with whatever tables are required.

Does anyone know if these operations are going to become easier in future versions of .NET or am I going about this the wrong way? I really like the Linq and Expression features, I just wish I could employ them using more readable code.

Updated

Thought I might add some examples of how I have made the expressions more composable. They aren't concise, but they get the job done.

public Expression<Func<User, ModUser>> MapUser {
    get {
        Expression<Func<User, ModUser>> mapUser = u => new ModUser() {
            UserId = u.User_Id,
            UserResources = u.Resources(r => new ModResource())
        };
        return mapUser.MapResources(this);
    }
}

public Expression<Func<Resource, ModResource>> MapResource { ... }


public static Expression<Func<T0, T1>> MapResources<T0, T1>(this Expression<Func<T0, T1>> exp, DataContext dc) {
    return exp.Visit<MethodCallExpression, Expression<Func<T0, T1>>>(m => {
        if(m.Arguments.Count > 1 && m.Arguments[1].Type == typeof(Func<DataContext.Resource, ModResource>)) { //Find a select statement that has the sub expression as an argument
            //The resource mapping expression will require the Resource object, which is obtained here
            ParameterExpression resourceParam =  ((LambdaExpression)m.Arguments[1]).Parameters[0];
            return Expression.Call(m.Method, m.Arguments[0], //The first argument is the record selection for the 'select' method
                Expression.Lambda<Func<DataContext.Resource, ModResource>>(//Provide the proper mapping expression as the projection for the 'select' method
                     Expression.Invoke(dc.MapResource, resourceParam),
                     resourceParam)
                );
        }
        return m;
    });
}

So what am I doing here? Note that in this version of MapUser I don't create the ModResource object correctly, I just create a dummy version. I then call an expression visitor method that looks for the dummy call and replaces it with the one I originally wanted in there. To me it seems like the expression syntax is lacking as I am able to essentially construct the expression tree I originally wanted, but I have to actually vist the tree to do it. Below is another workaround I've found that deals with the singular case:

public Expression<Func<User, ModUser>> MapUser {
    get {
        Expression<Func<User, ModResource, ModUser>> mapUser = (u, resource) => new ModUser() {
            UserId = u.User_Id,
            UserResource = resource;
        }

        return mapUser.CollapseArgument(MapResource, user => user.MainResource);
    }
}

public Expression<Func<Resource, ModResource>> MapResource { ... }

public static Expression<Func<T0, T3>> CollapseArgument<T0, T1, T2, T3>(this Expression<Func<T0, T1, T3>> exp, Expression<Func<T2, T1>> exp0, Expression<Func<T0, T2>> exp1) {
    var param0 = Expression.Parameter(typeof(T0), "p0");
    var argExp = Expression.Invoke(exp0, Expression.Invoke(exp1, param0));
    return Expression.Lambda<Func<T0, T3>>(
         Expression.Invoke(exp, param0, argExp),
         param0);
}

In this second example I know that I can obtain the Resource data from the User data but I can't "inline" an expression to show how to do this and map the Resource data to a resource POCO. But I can manually create an expression tree that is given an already mapped resource POCO and use it. I can then create another expression that shows how to obtain the resource raw data from the user and a final expression that shows how to map the raw resource data into a resource POCO. It is now conceivable that I can combine all of this information into a single expression tree in a way that "collapses" out the resource specific parameter since I can obtain it from the primary user parameter. This is what the code above does.

So I've found ways to make expressions highly composable... It just doesn't feel clean.

Answer 1

The way Linq To SQL supports POCO is a bit different.

To achieve persistence ignorance, you would use a mapping file that describes how a modUser is mapped (columns, associations etc), and not the LTS designer. When you create a new context, you pass it the XML mapping file as an XMLMappingSource.

This way, LTS will return your objects from the database.

I've read here and there that defining your collection association properties as Read/Write properties of type IList(of T) is enough for LinqToSQL to provide lazy loading on those collections, but I have not tried it, so I cannot vouch for it.

Entity Framework will be even worse for POCO support in its current version (basically none as far as most people understand the term POCO).

All the usual LTS limitations apply to this, so no "value Object" mapping. If you want something a bit farther removed from the database AND POCO support, then you need to look at NHibernate.