Equivalent of SQL Between Statement Using Linq or a Lambda expression

Question

Don't think this is a repost, difficult to search for the word between because it is used in everything (like searching for AND).

I want to filter a list based on a date range.

I have a list with some dates and I want to filter them by a date range. Is there a Linq or Lambda equivalent of the between statement in SQL.

For example, the code below will not work in Linqpad (or Visual Studio):

void Main()
{
    List<ListExample> list = new List<ListExample>();

    list.Add(new ListExample("Name1","23 Aug 2010"));
    list.Add(new ListExample("Name2","23 Aug 2009"));

    var query = from l in list
        where l.DateValue between "01 Jan 2010" and "01 Jan 2011"
        select l;

}

public class ListExample
{

    public ListExample(string name, string dateValue)
    {
        Name = name;
        DateValue = DateTime.Parse(dateValue);
    }

    public string Name{get;set;}
    public DateTime DateValue{get;set;}
}

looks like a duplicate of: stackoverflow.com/questions/1447635/linq-between-operator — Piotr Czapla
– Piotr Czapla, Commented Nov 3, 2010 at 13:47

Ani · Accepted Answer · 2012-07-04 10:51:04Z

28

Something like this?

var query = from l in list
            where l.DateValue >= new DateTime(2010, 1, 1) 
               && l.DateValue <= new DateTime(2011, 1, 1)
            select l;

You can write your own extension method:

public static bool IsBetween(this DateTime dt, DateTime start, DateTime end)
{
   return dt >= start && dt <= end;    
}

In which case the query would look something like (method syntax for a change):

var start = new DateTime(2010, 1, 1);
var end = new DateTime(2011, 1, 1);
var query = list.Where(l => l.DateValue.IsBetween(start, end));

I see you've provided some samples with the dates as strings. I would definitely keep the parsing logic (DateTime.ParseExactor other) separate from the query, if at all possible.

edited Jul 4, 2012 at 10:51

answered Nov 3, 2010 at 13:09

Ani

114k27 gold badges269 silver badges313 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Simon Over a year ago

Thanks I was having problems with the >= and needing to create a DateTime. I find Linq a little confusing because they changed the syntax.

Ani Over a year ago

@Simon: Cheers. The >= doesn't have much to do with LINQ; it's an overloaded operator on DateTime. You can use DateTime.CompareTo or similar if you prefer.

Simon Over a year ago

Thanks Ani - the DateTime.Parse was just for my example in LinqPad as I did not want to post a clients code onto the internet.

Tzvi Gregory Kaidanov Over a year ago

the only thing i've done is to change the extention function to stattic for global usage

Renato Heeb Over a year ago

How would you rewrite your extension method to support Linq to Entities?

kͩeͣmͮpͥ ͩ · Accepted Answer · 2010-11-03 13:10:27Z

1

var query = from l in list
        where new DateTime(1,1,2010) <= l.DateValue and DateValue <= new DateTime(1,1,2011)
        select l;

of course, normally warning about timezones and different times on clients and servers apply

answered Nov 3, 2010 at 13:10

kͩeͣmͮpͥ ͩ

7,86229 silver badges41 bronze badges

2 Comments

Simon Over a year ago

Looks like the extension methods are not supported by Linq to Entities if I link to the entity framework. Does this sound correct or am I missing something.

Simon Over a year ago

NotSupportedException: Method 'Boolean IsBetween(System.Nullable`1[System.DateTime], System.DateTime, System.DateTime, Boolean)' has no supported translation to SQL.

Stefanvds · Accepted Answer · 2010-11-03 13:11:37Z

1

Datetime DT1 = DateTime.Parse("01 Jan 2010");
Datetime DT2 = DateTime.Parse("01 Jan 2011");
var query = from l in list
            where l.DateValue >= DT1 && l.DateValue <= DT2
            select l;

in linq you use the && and || like you would in a normal boolean statement of C#.

answered Nov 3, 2010 at 13:11

Stefanvds

5,9065 gold badges51 silver badges73 bronze badges

Collectives™ on Stack Overflow

Equivalent of SQL Between Statement Using Linq or a Lambda expression

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related