Linq extension method

Question

I frequently need to limit SELECTs by fields like publishStart, publishEnd, active

I have these fields in several different tables. So only rows should be selected, that are

a: active == true;
b: publishStart < now;
c: publishEnd > now;

So, for example:

db.myTable.SingleOrDefault(a => (a.ID == _theID 
          //now the active and start-end part:            
                      && ((a.publishEnd > DateTime.Now) || (a.publishEnd == null))
                      && ((a.publishStart <= DateTime.Now) || (a.publishStart == null))
                      && a.active == true));

This is a bit lengthy, so I wonder if it is possible to create a (extension?)-method like:

db.myTable.SingleOrDefault(a => (a.ID == _theID).isActive()

where the isActive() provides the 3 lines of the above snippet.

How could I do this? Is there a better way to clean up code?

Answer 1

To define an extension you need a static class. You can put this in whatever namespace you like, just remember to include it in your usings.

public static class Extensions
{
    public static IQueryable<T> Active<T>(this IQueryable<T> source)
        where T : YourEntityType
    {
        return source.Where(a => ((a.publishEnd > DateTime.Now) || (a.publishEnd == null))
                          && ((a.publishStart <= DateTime.Now) || (a.publishStart == null))
                          && a.active == true);
    }
}

Notice YourEntityType in there. This is used to ensure the method is aware of the existence of publishStart, publishEnd, and active. This should be either the class that implements these fields or a contract (interface) that defines them.

You would then call this like so:

var item = db.myTable.Active().SingleOrDefault(...);

More on extension methods here: http://msdn.microsoft.com/en-us/library/bb383977.aspx

---

As there are lots of comments popping up all over the place, I'm going to add here a brief explanation of the interface solution...

It's unclear in the question whether or not there is a common implementation for the three filtered fields or an interface to define them. If not, for the above to work, you will not either:

1. A base class implementing these fields. In this scenario you would replace YourEntityType with YourBaseEntityType.
2. An interface to define the fields. In this scenario you would need to have your classes implement the fields. If the classes are auto generated (e.g. entity framework model/db first) then you can implement partial classes, having them implement the interface. In this case you would replace YourEntityType with IYourContract.

Answer 2

Just define an interface like this

public interface IHaveAActivityPeriod 
{
    Boolean active { get; }

    DateTime? publishStart { get; }

    DateTime? publishEnd { get; }
} 

and add it to all relevant classes.

public class Foo : IHaveAActivityPeriod { [...] }

public class Bar : IHaveAActivityPeriod { [...] }

Now you can use this extension method

public static class Extensions
{
    public static Boolean IsActive(this IHaveAActivityPeriod item)
    {
        var now = DateTime.Now;

        return item.active &&
               (item.publishStart <= now)
               (!item.publishEnd.HasValue || (item.publishEnd > now));
    }
}

on every instance implementing IHaveAActivityPeriod.

var foo = new Foo();

var isFooActive = foo.IsActive();

var bar = new Bar();

var isBarActive = bar.IsActive();

I completely missed the possibility to construct an extension method that performs the filtering of a sequence instead of looking at a single entity at once. Just take the extension method from flem's answer an throw in the interface as type constraint.

public static class Extensions
{
    public IQueryable<T> IsActive<T>(this IQueryable<T> sequence)
        where T : IHaveAActivityPeriod
    {
        return source.Where(item =>
                   item.active &&
                   (item.publishStart <= now) &&
                   (!item.publishEnd.HasValue || (item.publishEnd > now));

    }
}