Refactoring Linq to Sql

Question

Is there a way to refactor linq to sql and take advantage of late evaluation? Is there a way to reuse object initilizer code?

I have business objects that are persisted in a database and hydrated via a separate linq to sql layer.

I would like to be able to reuse code from multiple queries that do the exact same thing. The portion of the query that I would like to reuse is the object creation part. I would also like to continue to take advantage of late evaluation (only get the columns that I want from the database.)

The examples given below are very simple and some of the names have been changed to protect the innocent.

Sample 1

void GetUserById(Guid userId)
{
    IQueryable<LinqLayer.User> x =
        from user in dataContext.Users
        where user.UserID == userId
        select user;

    IQueryable<BusinessLayer.User> y = hydrateUser(x);

    // Acutally execute the query against the database.
    BusinessLayer.User user = y.First();
}

void GetUserByName(string name)
{
    IQueryable<LinqUser> x =
        from user in dataContext.Users
        where user.FirstName == name
        select user;

    IQueryable<User> y = hydrateUser(x);

    // Acutally execute the query against the database.
    User user = y.First();
}

IQueryable<User> hydrateUser(IQueryable<LinqUser> x)
{
    IQueryable<User> y;
        y = from item in x
        select new User
        {
            ID = item.UserID,
            FirstName = item.FirstName,
            MiddleName = item.MiddleName,
            LastName = item.LastName,
        };
    return y;
}

A couple of things are going on in sample 1. In this example, I am able to take advantage of linq to sql late evaluation. If you watch the database queries, the only columns selected are the ones I am interested in (listed in hydrateUser as ID, FirstName etc). This is nice because I can reuse the code in hydrateUser.

Sample 2:

IQueryable<User> hydrateUser(IQueryable<LinqUser> x)
{
    IQueryable<User> y;
        y = from item in x
        select new User
        {
            ID = item.UserID,
            FirstName = item.FirstName,
            MiddleName = item.MiddleName,
            LastName = item.LastName,
            ... Big long list of properties
        };
    return y;
}

IQueryable<User> hydrateUserWithPermissions(IQueryable<LinqUser> x)
{
    IQueryable<User> y;
        y = from item in x
        select new User
        {
            ID = item.UserID,
            FirstName = item.FirstName,
            MiddleName = item.MiddleName,
            LastName = item.LastName,
            ... Big long list of properties
            PermissionList = item.Permissions
        };
    return y;
}

Sample 2 starts to fall down when I want to use hydrateUser and also hydrate an associated object. For example, hydrating a list of permissions. I have to spin off a whole seperate function now that does the extra hydration. this is less then desirable if I have many more properties I am initializing.

Sample 3

IQueryable<User> hydratePermissions(IQueryable<LinqUser> x)
{
    IQueryable<User> y;
        y = from item in x
        select new User
        {
            PermissionList = item.Permissions
        };
    return y;
}

IQueryable<User> hydrateUser(IQueryable<LinqUser> x)
{
    IQueryable<User> y;
        y = from item in x
        select new User
        {
            ID = item.UserID,
            FirstName = item.FirstName,
            MiddleName = item.MiddleName,
            LastName = item.LastName,
            ... Big long list of properties
        };
    return y;
}

What I would like to be able to do is build the query up with code like sample 3. Somehow try to take advantage of both functions. And make only one trip to the database. However, this does not work.

Sample 4

User newUpUserFromLinqUser(LinqUser item)
{
    y = new User
        {
            ID = item.UserID,
            FirstName = item.FirstName,
            MiddleName = item.MiddleName,
            LastName = item.LastName,
        };
    return y;
}

Sample 4 is close to what I want, but loses the expression evaluation that linq to sql uses to do late evaluation. All of the fields will come back in the query. Ever worse, if code like this is a child relationship, a call to the database gets made for every child. This is unacceptable in our situation.

Additional notes

Bandwidth is at a premium. We control exactly what data comes down the wire, so it is very important that the queries are as lean as possible.

I have looked at LinqKit and while it seems like it may work for what I need, I would prefer "out of the box".

At this time, the overall architecture is not up for debate.

Answer 1

Have you looked into explicitly loading the Permissions object with your user?

using (var db = new dbContext())
{
  DataLoadOptions dlo = new DataLoadOptions();
  dlo.LoadWith<LinqUser>(lu => lu.Permissions);
  db.LoadOptions = dlo;
  //Execute code, knowing that when you retrieve users, it will also retrieve their permissions.
}