Hi All,

I'm trying to show all clients with upcoming birthdays this week in a list, but I already expected the following LINQ query to be a bit too complex for the LLBLGen SQL driver:

public IEnumerable<ClientEntity> GetUpcomingBirthdaysForThisWeek()
{
    return from c in metaData.Client
           where (new DateTime(DateTime.Today.Year,c.Birthdate.Value.Month,c.Birthdate.Value.Day) - DateTime.Today).TotalDays <= 7
           select c;
}

Can anyone think of a way to express this in LINQ to LLBLGen so that it might work?

Thanks,

Wiebe

To make this easier, you can define 2 dateTime variables outside the linq query to represent the startOfWeek and endOfWeek values.

Then in the Linq query you can have 2 predicates to check whether the client birthday falls between the start and end dates.

Also you can use DateTime.DayOfYear to make the comparison easier.

(Edit) Using a Northwind example, the follwoing code should work:

            var startDate = DateTime.Now;
            var endDate = DateTime.Now.AddDays(7);

            var q = from emp in metaData.Employees
                    where emp.BirthDate.Value.DayOfYear >= startDate.DayOfYear
                    && emp.BirthDate.Value.DayOfYear <= endDate.DayOfYear
                    select emp;

Unfortunatly running this code today will bring back no results, as there are no emplyees in the northwind with a birthday in the next 7 days. But to make sure it's working, use HireDate instead of the BirthDate and you shall get 2 employees back

Walaa wrote:

To make this easier, you can define 2 dateTime variables outside the linq query to represent the startOfWeek and endOfWeek values.

Then in the Linq query you can have 2 predicates to check whether the client birthday falls between the start and end dates.

Also you can use DateTime.DayOfYear to make the comparison easier.

(Edit) Using a Northwind example, the follwoing code should work:

            var startDate = DateTime.Now;
            var endDate = DateTime.Now.AddDays(7);

            var q = from emp in metaData.Employees
                    where emp.BirthDate.Value.DayOfYear >= startDate.DayOfYear
                    && emp.BirthDate.Value.DayOfYear <= endDate.DayOfYear
                    select emp;

Unfortunatly running this code today will bring back no results, as there are no emplyees in the northwind with a birthday in the next 7 days. But to make sure it's working, use HireDate instead of the BirthDate and you shall get 2 employees back

Excellent! Thanks for thinking with me and the quick response