LINQ Let Clause and TimeSpan duration

Posts   
 
    
erakis
User
Posts: 24
Joined: 17-Jan-2012
# Posted on: 18-Jan-2012 19:23:27   

Hi,

I would like to get birthday for two month in advance. I want to return user code, age, next birthday date and is complete name.

Here is what I did yet :

Users SQL Table


Users
{
    uCode       ID
    uBirthday   DATETIME
    FirstName   VARCHAR(200)
    LastName    VARCHAR(200)
    ...
}

And here is the LINQ query


DateTime dNow = DateTime.Now;
DateTime dMin = dNow;
DateTime dMax = dNow.AddMonths(2);
DataAccessAdapter adapter = new DataAccessAdapter();
LinqMetaData lmd = new LinqMetaData(adapter);
var query = from u in lmd.Users
            let thisYearsBirthday = u.uBirthday.Value.AddYears(dNow.Year - u.uBirthday.Value.Year)
            let nextBirthday = ((thisYearsBirthday >= dNow) ? thisYearsBirthday : thisYearsBirthday.AddYears(1))
            let age = (thisYearsBirthday - dNow).TotalDays * 365.25
            where
            (
                nextBirthday >= dMin && nextBirthday <= dMax
            )
            orderby nextBirthday ascending
            select new 
            { 
                Code = u.InterCode, 
                Age = age,
                NextBirthday = nextBirthday,
                CompleteName = (u.FirstName + " " + u.LastName), 
                
            };

That compiling but I got many error at execution. First I think that I cannot create a anonymous object because of my "IN clause" that is pointing on a USER object that do not contains definition for field Code, Age, NextBirthday, CompleteName. It is true ?

Also, the age is not well converting. The query store it into a DATETIME field instead of an INT containing year. Why ?

Best regards, Martin

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 19-Jan-2012 06:34:47   

What LLBLGen version and runtime library version are you using? (http://llblgen.com/tinyforum/Messages.aspx?ThreadID=7725)

What SQL generated that code? And if there is any runtime exception please provide the full exception message and stackTrace.

Also take a look at the LINQ2LLBL supported DateTime function mappings.

David Elizondo | LLBLGen Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39908
Joined: 17-Aug-2003
# Posted on: 19-Jan-2012 11:11:05   

What database (brand/version) are you using?

Frans Bouma | Lead developer LLBLGen Pro
erakis
User
Posts: 24
Joined: 17-Jan-2012
# Posted on: 19-Jan-2012 12:58:57   

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

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 19-Jan-2012 17:49:05   

Yes, you can do calculations in the select projection, and you could also reference your 'let' variables there.

David Elizondo | LLBLGen Support Team
erakis
User
Posts: 24
Joined: 17-Jan-2012
# Posted on: 19-Jan-2012 18:08:31   

daelmo wrote:

Yes, you can do calculations in the select projection, and you could also reference your 'let' variables there.

I tried using computed field in the select, but a is not recognize


select new
{
     a = Field1 + Field2,
     b = a + Field2,
}

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 20-Jan-2012 06:15:31   

If you want to have an expression you want to reuse in the select projection you could use 'let'. What is what you are trying to achieve?

David Elizondo | LLBLGen Support Team
erakis
User
Posts: 24
Joined: 17-Jan-2012
# Posted on: 20-Jan-2012 17:38:31   

daelmo wrote:

If you want to have an expression you want to reuse in the select projection you could use 'let'. What is what you are trying to achieve?

Yes, I already use the LET Clause, but sometime is create uncessary subquery. But if this is the way to go, then that's what I use wink

But I thought we could also do it in the SELECT clause. As my example:


select new
{
     a = Field1 + Field2,
     b = a + Field2,
}

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 20-Jan-2012 22:56:24   

erakis wrote:

daelmo wrote:

If you want to have an expression you want to reuse in the select projection you could use 'let'. What is what you are trying to achieve?

Yes, I already use the LET Clause, but sometime is create uncessary subquery. But if this is the way to go, then that's what I use wink

You also could just write the expression again in the select projection.

erakis wrote:

But I thought we could also do it in the SELECT clause. As my example:


select new
{
     a = Field1 + Field2,
     b = a + Field2,
}

I think the problem is that 'a' doesn't exist yet, so you can't use it.

David Elizondo | LLBLGen Support Team