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

Question

This probably extends the boundaries of Linq-to-Sql a bit, but considering how versatile it is so far, I thought I'd ask.

I have 3 queries that select identical information and differ only in the where clause, now I know that I can pass a delegate, but this only allows me to filter the results that have already been returned, but I want to create a query through to ensure efficiency.

Here is the request:

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 that I want to apply using the parameter will be as follows:

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

Is it possible? I do not think that is all, but I thought that I would look 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 pass an object that lets you filter IQueryable. When you do this, you can write code like this is your level of service:

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

and at your caller level 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 for implementing this EntityFilter<T> here (it's about 40 lines of code) and as a blog post about it here .

Please note that your query is a little more complicated than the example given here, so it may take a little more to determine the right filter.