Dynamic selects in LINQ

Question

Let's say I have class PersonSummary, which combines properties (columns) from 2 database tables Person and Address (with a foreign key of Person), so a PersonSummary has a First Name, State and Zip.

The creation of PersonSummary could be in a select clause with (person, address => new PersonSummary {person, address} )

My data access layer has no knowledge of PersonSummary, so I'd like to add the select clause at a higher level, and only construct the from, where, etc. at a lower level, to select rows from Person and Address based on different criteria, for e.g. have methods like GetFemales() or GetPeopleInAState().

Any recommendations on how to do this? Should my data-access functions return Expressions and the higher level method just tacks on a Select clause to it?

Signatures/Return types most appreciated.

Answer 1

You can just add extensions to IQueryable:

public static IQuearyable<Person> Famales(IQueryable<Person> this entry)
{
    return entry.Where(p => p.Gender == Gender.Female);
}

public static IQuearyable<Person> LivingInState(IQueryable<Person> this entry, State state)
{
    return entry.Where(p => p.State == state);
}

public static IQuearyable<Person> PeopleWithAddress(IQueryable<Person> this entry)
{
    return entry.Where(p => p.Address != null);
}

// Use like this
var marriedFemales = GetPersonsQuery().Females().Where(f => f.IsMarried)
var femaleVictorians = GetPersonsQuery().Females().LivingInState(State.Victoria)
var femaleVictorians = GetPersonsQuery().PeopleWithAddress()
    .Females().Where(x => x.IsMarried)
    .Select( x => new { x.LastName, x.Address} )

Answer 2

When you're using the underlying extension methods LINQ uses, you can pretty much do anything you want.

So als long as your data layer returns IQueryable information, you could say

var withSummary = db.GetFemales()
                    .Select( p => new PersonSummary(p, p.Address) );

If you're using LINQ to SQL or EF, all data is retrieved with a single query. If, however, your LINQ provider is less advanced, you could try

var withSummary = db.GetFemales().AsEnumerable()
                    .Select( p => new PersonSummary(p, p.Address) );

But that does require you to somehow preload (or lazy load) the address.