LINQ where not between a date range

Question

I have the following linq query:

public static int GetContributions(PERSON person, DateTime startDate, DateTime endDate)
    {
        using (var db = new TestEntities())
        {
            var creditsSum = (from u in db.PERSON_SOCIAL_INSURANCE_CONTRIBUTIONS
                              where u.StartDate >= startDate
                              where u.EndDate <= endDate
                              where (u.PersonId == person.Id)
                              select (int?)u.NumOfContributions).Sum() ?? 0;

            return creditsSum;
        }
    }

I would like to create a similar method that returns the number of contributions that do not fall between the start and end date provided. So basically for it to return all entries that do not fall between startDate and endDate values.

Any help?

Can you explain the term `fall`? No intersection with given interval or full inclusion? — Hamlet Hakobyan, May 01 '14 at 22:35

score 1 · Answer 1 · answered May 01 '14 at 22:30

1

AND all of your conditions together, put parentheses around them, and negate.

var creditsSum = (from u in db.PERSON_SOCIAL_INSURANCE_CONTRIBUTIONS
                          where !(u.StartDate >= startDate
                          && u.EndDate <= endDate)
                          where (u.PersonId == person.Id)
                          select (int?)u.NumOfContributions).Sum() ?? 0;

answered May 01 '14 at 22:30

Robert Harvey

178,213
47
333
501

The intersection can arise. – Hamlet Hakobyan May 01 '14 at 22:47

Ufuk Hacıoğulları · Answer 2 · 2014-05-01T22:43:25.070

0

Change your conditions then:

var creditsSum = (from u in db.PERSON_SOCIAL_INSURANCE_CONTRIBUTIONS
                  where (u.StartDate > endDate || u.EndDate < startDate) &&
                        u.PersonId == person.Id
                  select (int?)u.NumOfContributions).Sum() ?? 0;

You don't need that many where statements, just use AND operator.

edited May 01 '14 at 22:43

answered May 01 '14 at 22:30

Ufuk Hacıoğulları

37,978
12
114
156

If I make decision that the `u.StartDate < u.EndDate` and `startDate < endDate` (which pretty much to be true), your `where` clause is `false`. – Hamlet Hakobyan May 01 '14 at 22:41
@HamletHakobyan You are right, updated the condition. – Ufuk Hacıoğulları May 01 '14 at 22:43

score 0 · Answer 3 · answered May 01 '14 at 22:43

0

Your Where clause can looks like this:

b.PERSON_SOCIAL_INSURANCE_CONTRIBUTIONS
 .Where(
           (c => c.StartDate > endDate || c.EndDate < startDate)
           && u.PersonId == person.Id
       )

answered May 01 '14 at 22:43

Hamlet Hakobyan

32,965
6
52
68

LINQ where not between a date range

3 Answers3