How do you re-use select statements with Entity Framework?

Question

Given the following query:

var query = from item in context.Users // Users if of type TblUser
            select new User()          // User is the domain class model
            {
                ID = item.Username,
                Username = item.Username
            };

How can I re-use the select part of the statement in other queries? I.e.

var query = from item in context.Jobs  // Jobs if of type TblJob
            select new Job()           // Job is the domain class model
            {
                ID = item.JobId,
                User = ReuseAboveSelectStatement(item.User);
            };

I tried just using a mapper method:

public User MapUser(TblUser item)
{
   return item == null ? null : new User()
   {
      ID = item.UserId,
      Username = item.Username
   };
}

With:

var query = from item in context.Users // Users if of type TblUser
            select MapUser(item);

But if I do this, then the framework throws an error such as:

LINQ to Entities does not recognize the method 'MapUser(TblUser)' method, and this method cannot be translated into a store expression.

Answer 1

Try making your MapUser method static.

Answer 2

You can't use regular function calls in a query definition like that. LINQ needs expression trees, it can't analyze compiled functions and magically translate that to SQL. Read this for a more elaborate explanation

The techniques used in the cited article are incorporated in linqkit (factoring out predicates) and might be of help, though I'm not sure you can use the same technique for managing projections, which is what you seem to want.

The more fundamental question you should ask yourself here IMHO is whether you really need this extra mapping layer? It seems like you're implementing something that EF is already perfectly capable of doing for you...

Answer 3

You have to pass around an Expression<Func<yourObject, bool>> if you want to re-use queries:

    DataClasses1DataContext dc = new DataClasses1DataContext();

    Expression<Func<Book, bool>> basicFilter = b => b.UserID == 7 && !b.IsRead;

    IQueryable<Book> BooksQuery1 = dc.Books.Where(basicFilter);

    IQueryable<Book> BooksQuery2 = dc.Books.Where(basicFilter);
    BooksQuery2 = BooksQuery2.Where(b => b.mapItemsSubjects.Any());

Answer 4

Oh - yeah, I guess it was right there in your question title. Well the same idea should work - so long as you're returning an actual type, and not an anonymous type. Just substitute User in place of string and it should work.

    Expression<Func<Book, string>> selectLambda = b => b.EAN;

    IEnumerable<string> strings = dc.Books.Select(selectLambda);
    string singleString = dc.Books.Select(selectLambda).First();