I'm using LLBLGen 3.1, .NET 3.5 and SQL Server 2008 Express.
I finally found the solution.
var query3 = (from u in lmd.User.IncludeFields(f => f.UserCode, f => f.UserFirstName, f => f.UserLastName, f => f.UserBirtday)
let lThisYearsBirthday = u.UserBirtday.Value.AddYears(utcNow.Year - u.UserBirtday.Value.Year)
let lNextBirthday = ((lThisYearsBirthday >= utcNow) ? lThisYearsBirthday : lThisYearsBirthday.AddYears(1))
let lAge = (u.UserBirtday.Value.AddYears((utcNow.Year - u.UserBirtday.Value.Year)) > utcNow) ? (utcNow.Year - u.UserBirtday.Value.Year) - 1 : (utcNow.Year - u.UserBirtday.Value.Year)
where
(
lNextBirthday >= utcMin && lNextBirthday <= utcMax
)
select new
{
UserCode = u.UserCode,
CompleteName = u.UserFirstName + " " + u.UserLastName,
UserBirtday = u.UserBirtday,
ThisYearsBirthday = lThisYearsBirthday,
NextBirthday = lNextBirthday,
Age = lAge
}
).ToList();
I have many other solution but according the profiler, it is the fastest.
Anyway, I have another question related to this one. Is it possible to create a computed/calculated field inside the SELECT clause ? I mean, without using the LET clause ? If yes, then it is possible to make reference to this calculated field and resuse it to compute another one ? Ex :
select new
{
a = Field1 + Field2,
b = a + Field2,
}
It is well working with the LET clause, but when I use it I notice that this will create a sub query having a column for each LET clauses and then these columns are discarted by an other sourrounding query.
Best regards,
Martin