Is there a way to use Linq projections with extension methods

Question

I'm trying to use AutoMapper and a repository pattern along with a fluent interface, and running into difficulty with the Linq projection. For what it's worth, this code works fine when simply using in-memory objects. When using a database provider, however, it breaks when constructing the query graph. I've tried both SubSonic and Linq to SQL with the same result. Thanks for your ideas.

Here's an extension method used in all scenarios - It's the source of the problem since everything works fine without using extension methods

public static IQueryable<MyUser> ByName(this IQueryable<MyUser> users, string firstName)
{
     return from u in users
            where u.FirstName == firstName
            select u;
}

Here's the in-memory code that works fine

var userlist = new List<User> {new User{FirstName = "Test", LastName = "User"}};

Mapper.CreateMap<User, MyUser>();
var result = (from u in userlist
                   select Mapper.Map<User, MyUser>(u))
                   .AsQueryable()
                   .ByName("Test");

foreach (var x in result)
{
     Console.WriteLine(x.FirstName);
}

Here's the same thing using a SubSonic (or Linq to SQL or whatever) that fails. This is what I'd like to make work somehow with extension methods...

Mapper.CreateMap<User, MyUser>();

var result = from u in new DataClasses1DataContext().Users
                          select Mapper.Map<User, MyUser>(u);

var final = result.ByName("Test");
foreach(var x in final) // Fails here when the query graph built.
{
     Console.WriteLine(x.FirstName);
}

The goal here is to avoid having to manually map the generated "User" object to the "MyUser" domain object- in other words, I'm trying to find a way to use AutoMapper so I don't have this kind of mapping code everywhere a database read operation is needed:

var result = from u in new DataClasses1DataContext().Users
                          select new MyUser // Can this be avoided with AutoMapper AND extension methods?  
                          {
                             FirstName = v.FirstName,
                             LastName = v.LastName
                          };

Answer 1

Well I don't know SubSonic's LINQ-implementation. However the cause of the problem could be, that the LINQ-Provider fails to use the 'Mapper'-call. It expects something that is can be translated into SQL.

By the way, I would use the .ByName() on the 'User'-query instead of the current implementation. Because currently the result has to mapped in order to run the .ByName(). So you retrieve a lot of User-instance from the database, map them and filter them afterwards. If you would use .ByName on the 'User', it can be translated into SQL an never has to be retrieved.

So my guess it that something like this would work:

public static IQueryable<User> ByName(this IQueryable<User> users,
                                      string firstName)
{
    return from u in users
        where u.FirstName == firstName
        select u;
}

And now you add the mapping part to the end: Mapper.CreateMap();

var result = from u in new DataClasses1DataContext().Users.ByName("Test")
                select Mapper.Map<User, MyUser>(u);
// now the 'ByName'-constain can be ran on the database

foreach(var x in result)
{
     Console.WriteLine(x.FirstName);
}

If it still doesn't work, you might need to force the usage of LINQ-to-Object for the Mapper-part: Mapper.CreateMap();

var result = DataClasses1DataContext().Users.ByName("Test").ToList();
// now the result is a regualar list, so LINQ-to-object is used for the mapping part

var final = from u in result
                   select Mapper.Map<User, MyUser>(u);

foreach(var x in final)
{
     Console.WriteLine(x.FirstName);
}

By the way, you might wanna add a 'SubSonic'-tag, so that the SubSonic-experts answer your question.