Passing a WHERE clause for a Linq-to-Sql query as a parameter

Question

Hi all

This is probably pushing the boundaries of Linq-to-Sql a bit but given how versatile it has been so far I thought I'd ask.

I have 3 queries that are selecting identical information and only differ in the where clause, now I know I can pass a delegate in but this only allows me to filter the results already returned, but I want to build up the query via parameter to ensure efficiency.

Here is the query:

from row in DataContext.PublishedEvents
join link in DataContext.PublishedEvent_EventDateTimes
    on row.guid equals link.container
join time in DataContext.EventDateTimes on link.item equals time.guid
where row.ApprovalStatus == "Approved"
    && row.EventType == "Event"
    && time.StartDate <= DateTime.Now.Date.AddDays(1)
    && (!time.EndDate.HasValue || time.EndDate.Value >= DateTime.Now.Date.AddDays(1))
orderby time.StartDate
select new EventDetails
{
    Title = row.EventName,
    Description = TrimDescription(row.Description)
};

The code I want to apply via a parameter would be:

time.StartDate <= DateTime.Now.Date.AddDays(1) &&
(!time.EndDate.HasValue || time.EndDate.Value >= DateTime.Now.Date.AddDays(1))

Is this possible? I don't think it is but thought I'd check out first.

Thanks

Answer 1

Yes it is.

var times = DataContext.EventDateTimes;
if (cond)
    times = times.Where(time => time.StartDate <= ...);

from row in ... join time in times ...

Answer 2

What you can do is passing an object that allows filtering an IQueryable. When you do this you can write code like this is your service layer:

public Person[] GetAllPersons(IEntityFilter<Person> filter)
{
    IQueryable<Person> query = this.db.Persons;

    query = filter.Filter(query);

    return query.ToArray();
}

and in your calling layer, you can define a filter like this:

IEntityFilter<Person> filter =
    from person in EntityFilter<Person>.AsQueryable()
    where person.Name.StartsWith("a")
    where person.Id < 100
    select person;

// or (same result, but without LINQyness)
IEntityFilter<Person> filter = EntityFilter<Person>
    .Where(p => p.Name.StartsWith("a"))
    .Where(p => p.Id < 100);

// Call the BL with the filter.
var persons = BusinessLayer.GetAllPersons(filter);

You can find the source code of an implementation of this EntityFilter<T> here (it's around 40 lines of code) and as blog post about it here.

Please note that your query is a bit more complex than the example I've shown here, so it could take a bit more work to define the correct filter.