I have the following query:

var result = from a in metaData.Account
             where a.LastStatementDate >= statementDate.Date &&
                   a.LastStatementDate < statementDate.Date.AddDays(1)
             select a;

where LastStatementDate and statementDate are both DateTimes. This query produces the following SQL:

SELECT DISTINCT
    [LPLA_1].[accountID] AS [AccountId],
    [LPLA_1].[accountName] AS [AccountName], 
    [LPLA_1].[lastStatementDate] AS [LastStatementDate]
FROM
    [TheDB].[dbo].[accounts] [LPLA_1]
WHERE
    ( ( ( ( ( [LPLA_1].[lastStatementDate] IS NULL) AND ( [LPLA_1].[lastStatementDate] < @LastStatementDate1)))))

The problem is the "IS NULL" that has spawned itself in the query. The amusing bit is that, if I change "statementDate.Date" to "statementDate.Date.AddDays(0)", then everything works correctly. Is there some difference between the output of a property and a method that is causing a problem?

Which build are you using btw? Please provide more information about the elements used in the query so we can try to reproduce it.

(edit) DateTime.Date is mapped on:

"DATEADD(ms, -DATEPART(ms, {0}), DATEADD(s, -DATEPART(s, {0}), DATEADD(n, -DATEPART(n, {0}), DATEADD(hh, -DATEPART(hh, {0}), {0}))))"

so it should execute that as a function. I'll see if I can repro this.

Reproduced:

DateTime toCompareWith = new DateTime(2004, 4, 28);
var q = from o in metaData.Order
        where o.OrderDate == toCompareWith.Date
        && o.OrderDate < toCompareWith.AddDays(1)
        select o;

Interestingly enough, this works:

var q = from o in metaData.Order
        where o.OrderDate.Value.Date == new DateTime(2004, 4, 28)
        select o;

Will check it out.

(edit). More interestingly, this ALSO works:

var q = from o in metaData.Order
        where o.OrderDate.Value == toCompareWith.Date
        && o.OrderDate.Value < toCompareWith.AddDays(1)
        select o;

So the nullable field should be used with 'Value' to work around this. The 'Date' property in your query isn't executed on the db, it's an in-memory constant, as the property is called in an in-memory object.

I'll still try to fix this though.

(edit) the cause is that the constant is wrapped twice in a Convert() expression by the C# compiler: first to cast the DateTime to a Nullable<DateTime> and then again to a DateTime... The Convert stripper routine just unwrapped 1, but should unwrap all.

(edit). Fixed it in next build.

Sorry I didn't have more information. It was getting close to the end of the day for me and I wasn't certain of what else to provide. I'm also not sure why it didn't occur to me that the entity's property might be nullable.

For reference (not that it matters anymore), this was on the current demo version of 2.6. The guy who has our customer account information is on vacation this week. =P

JustinPierce wrote:

Sorry I didn't have more information. It was getting close to the end of the day for me and I wasn't certain of what else to provide. I'm also not sure why it didn't occur to me that the entity's property might be nullable.

No problem. It was possible to repro it with a similar looking query, it's fixed now. The fix will be uploaded in a couple of hours.

For reference (not that it matters anymore), this was on the current demo version of 2.6. The guy who has our customer account information is on vacation this week. =P

heh And you didn't call him back from the holidays?

heh And you didn't call him back from the holidays?

It would have been a more dire issue had there not been a shiny demo available to scratch our Linq itch.

Well, let's hope the # of 'after release' issues is kept to a minimum to let it keep its shineyness