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?

Solution 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.

Solution 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));

    }
}

Solution 3

public static class Extensions
{
    public static IEnumerable<MyClass> isActive(this IEnumerable<MyClass> list)
    {
        return list.Where(a =>  
               ((a.publishEnd > DateTime.Now) || (a.publishEnd == null))
                 && ((a.publishStart <= DateTime.Now) || (a.publishStart == null))
                 && a.active == true);
    }
}

Comments

[Paul Fleming]

This won't transform to sql, and would therefore require full evaluation for filtering.

[default]

Is there a reason to use IQueryable<T> instead of IEnumerable<T>?

[casperOne]

This won't work in a call to SingleOrDefault which is what the OP asks for.

[Daniel Brückner]

That is true, but there is no really good solution that is translatable into SQL (without a custom provider) and so decided to present an LINQ to Objects solution.

[Paul Fleming]

@Default. I prefer to use IQueryable<T> as I've had polymorphism issues in the past with IEnumerable<T>. Given that there exist extensions for both IEnumerable<T> and IQueryable<T>, returning an IEnumerable<T> can result in the wrong extensions being called further in the chain resulting in enumeration rather than deferred queries.

[Servy]

@Default It's a question of whether you want to be performing the query on the database, or returning all of the items and filtering in C# code. Filtering in the DB is almost always preferable, when possible, but it's not always possible.

[Daniel Brückner]

There is no need to make this a generic method and then constrain the type parameter to a type unless all types having this three properties are modeled using inheritance - something I would really not suggest to do in most cases.

[Servy]

@flem No, the compiled assembly wouldn't be the same. C# generics aren't templates. It may be functionally equivalent if the type is sealed (or no derived types are ever used), but it's not going to have equivalent IL.

[Paul Fleming]

@DanielBrückner In my example YourEntityType may be a base entity or an interface in which case it's completely valid to be generic. If the OP uses the actual single entity type, then yes I agree.

[reinhard]

@flem : thank you - I am using entity framework first, and I dont know what you mean with " then you can implement partial classes, having them implement the interface. In this case you would replace YourEntityType with IYourContract." A small code sample please?

[PositiveGuy]

where is the extension method example, you just showed the long Lambda syntax

[Paul Fleming]

@CoffeeAddict The code inside the method has nothing to do wit extension methods (it is actually the OP's code). The extension method is the Active method itself. Notice the this keyword prefixing the first parameter. A static method in a static class can be made into an extension method by adding thia to the first parameter. This means you can call the static method as if it is an instance method of the first parameter. I've added a link to my answer for further reading.