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

Answer 1

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.