Complex LINQ to SQL Query with dates

Question

I'm building a query with LINQ to SQL in my C# project, but I have some problems with it...

What I want to do, is select the 4 lasts days that are like today (For example, a Friday), so if we're on Friday 28, I want to query for: Friday 21, 14, 7... The last four Fridays but NOT today.

This is easy, I've done that but here's the complex part, I want to not query the exceptions I set, for example End of month, which are from 28th to 1st day of each month, so let's say i want to query this (october, fridays):

Today is Friday 26, I want to query:

19, 12, 5 and September 28th (the fourth friday from now), but as I said, 28th is end of month, so i need to return September 21th which is the last friday and it is not end of month... I have the same issues with holidays, but I think if I can handle end of months, I can do with them...

i hope I've explained good for you to understand what I want... Here's my query, which is working but can't handle exceptions. (the field b.day is the Id for each days, 8 means end of month, and 7 holiday)

var values =
    from b in dc.MyTable
    where // This means end of month
    b.day != 8

    // This triggers to query last 4 days
    && b.date == Convert.ToDateTime(last.ToString("dd/MM/yyy")).AddDays(-28)
    || b.date == Convert.ToDateTime(last.ToString("dd/MM/yyy")).AddDays(-21)
    || b.date == Convert.ToDateTime(last.ToString("dd/MM/yyy")).AddDays(-14)
    || b.date == Convert.ToDateTime(last.ToString("dd/MM/yyy")).AddDays(-7)
    orderby b.id descending
    group b.valor by b.hora_id into hg
    orderby hg.Key descending

    select new
    {
        Key = hg.Key,
        Max avg = System.Convert.ToInt32(hg.Average() + ((hg.Average() * intOkMas) / 100)),
        Min avg = System.Convert.ToInt32(hg.Average() - ((hg.Average() * intOkMenos) / 100))
    };

Sorry @John for the ortographic errors, I'm not english and my keyboard is making fun of me duplicating things... Thank you for editing — Robert W. Hunter
– Robert W. Hunter, Commented Nov 7, 2012 at 18:50

Allon Guralnek · Accepted Answer · 2012-11-07 21:34:53Z

3

You should prepare the list of days you'd like retrieve before trying to query:

// Get the last four days excluding today on the same weekday
var days = Enumerable.Range(1, 4).Select(i => DateTime.Today.AddDays(i * -7));

Then remove any days you don't want:

// Remove those pesky end-of-month days
days = days.Where(d => d.Day < 28 && d.Day > 1);

When you're done preparing the list of days you want to retrieve, only then should you perform your query:

from b in dc.MyTable
where days.Contains(b.date)  // Translated to SQL: date IN (...)
...

EDIT: As you mentioned in your comment, you want a total of four days even after any filtering you perform. So simply generate more days and take the first four:

var days = Enumerable.Range(1, int.MaxValue - 1)
                     .Select(i => DateTime.Today.AddDays(i * -7))
                     .Where(d => d.Day < 28 && d.Day > 1)
                     .Take(4);

Due to the way LINQ (and in general, enumerators) work, only four days plus any skipped days will be calculated.

edited Nov 7, 2012 at 21:34

answered Nov 7, 2012 at 19:01

Allon Guralnek

16.2k6 gold badges65 silver badges96 bronze badges

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

2 Comments

Robert W. Hunter Over a year ago

Thanks, i've tried it, but it doesn't work as I want, for example today, if I do that query, I just get values for october 24, 17 and 10, because today -7 is 31, and that's end of month, it must return me 24, 17, 10 and 3 because Octiber 3rd is the 4th day from today without end of month...

Allon Guralnek Over a year ago

@user1381537: Whoops. I didn't notice SPFiredrake beat me to it.

SPFiredrake · Accepted Answer · 2012-11-07 20:04:20Z

2

Building on Allon Guralnek's answer, I'd modify it slightly:

First, build an infinite date generator:

public IEnumerable<DateTime> GetDaysLikeMe(DateTime currentDate)
{
    DateTime temp = currentDate;
    while(true)
    {
        temp = temp.AddDays(-7);
        yield return temp;
    }
}

Then you can use deferred execution to your advantage by limiting to only dates that meet your additional criteria:

GetDaysLikeMe(DateTime.Now).Where(dt => /* dt meets my criteria */).Take(4)

Then you can use this list that was generated to query in your LINQ to SQL like Allon Guralnek suggested above:

from b in dc.MyTable
where days.Contains(b.date)  // Translated to SQL: date IN (...)
...

This has the benefit of you being able to specify additional predicates for what are acceptable dates and still get at least 4 dates back. Just be sure to put some bounds checking on the infinite date generator in case one of your predicates always returns false for whatever reason (which means the generator will never exit).

IE: while(temp > currentDate.AddYears(-1))

answered Nov 7, 2012 at 20:04

SPFiredrake

3,89220 silver badges26 bronze badges

2 Comments

Robert W. Hunter Over a year ago

That was what I was looking for! Thank you so much, and also thank you Allon and Erik

Allon Guralnek Over a year ago

A simple way to prevent a generation of an infinite sequence is: GetDaysLikeMe(DateTime.Today).Take(1000).Where(predicate).Take(4). This will limit the number of searched days to 1000.

Erik Philips · Accepted Answer · 2012-11-07 19:06:46Z

2

I would highly suggest writing your exception code (last friday of the month) after retrieving your rows, as this logic seems to be too complicated for a LINQ statement. Instead of retrieving the last 4 days, retrieve the last 5. Remove any that are the last Friday of each respective months. If you still have 5 rows, remove the last one.

Update

var values1 =
  from b in dc.MyTable
  where // This means end of month
  b.day != 8

  // This triggers to query last 4 days
  && b.date == Convert.ToDateTime(last.ToString("dd/MM/yyy")).AddDays(-28)
  || b.date == Convert.ToDateTime(last.ToString("dd/MM/yyy")).AddDays(-21)
  || b.date == Convert.ToDateTime(last.ToString("dd/MM/yyy")).AddDays(-14)
  || b.date == Convert.ToDateTime(last.ToString("dd/MM/yyy")).AddDays(-7)
  orderby b.id descending
  select b;

//Do stuff with values

var values2 = from b in values2
  group b.valor by b.hora_id into hg
  orderby hg.Key descending

  select new
  {
    Key = hg.Key,
    Max avg = System.Convert.ToInt32(hg.Average() + ((hg.Average() * intOkMas) / 100)),
    Min avg = System.Convert.ToInt32(hg.Average() - ((hg.Average() * intOkMenos) / 100))
  };

edited Nov 7, 2012 at 19:06

answered Nov 7, 2012 at 18:43

Erik Philips

54.9k11 gold badges133 silver badges158 bronze badges

3 Comments

Robert W. Hunter Over a year ago

But, how can I handle that? I mean, the query returns me a grouped values per key (Hour), not by date... I always get 96 results (from 0 to 95) how can I tell my script what you say?

Robert W. Hunter Over a year ago

I think you mean "Var values2 = from b in values1" right? I'll give it a try... thank you

Erik Philips Over a year ago

Oops yes that is what I meant

Collectives™ on Stack Overflow

Complex LINQ to SQL Query with dates

3 Answers 3

2 Comments

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related