I have been using LLBL with WCF project. The server currently at another location from the clients. Basically WCF automatically translates DateTime variable that send through function parameter to be in the same timezone offset of server. I have database(SqlServer2008 express) on the server containing the table with Date column (Date type). When I try to fetch entity with plain predicate filter like this:

IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(SqldateFields.Date == date);

I'm sure the date exists(also the date before and after) in database but it result no record. Basically the timezone offset shouldn't matter since Date column is in Date type not DateTime. But when I do Unspecify datetime kind before sending through WCF, I get a record properly.

DateTime.SpecifyKind(DateTime.Today, DateTimeKind.Unspecified)

Note: LLBL v.2.6 final (July 27th, 2009) with Adapter generated code. .NET 3.5

Any idea?

Hong

Would you please inspect the generated SQL Query. (check the docs: Using the generated Code -> Troubleshooting and debugging).

Try to run it manually against the database, to see what's wrong with it, and please post it here.

Walaa wrote:

Would you please inspect the generated SQL Query. (check the docs: Using the generated Code -> Troubleshooting and debugging).

Try to run it manually against the database, to see what's wrong with it, and please post it here.

Because of the service that have LLBL running is on the server so I rather ran SqlProfiler against the database. Here is the what I get.

exec sp_executesql N'SELECT [MyDatabase].[dbo].[SQLDatesTable].[DateId], [MyDatabase].[dbo].[SQLDatesTable].[Date] FROM [MyDatabase].[dbo].[SQLDatesTable]  WHERE ( ( [MyDatabase].[dbo].[SQLDatesTable].[Date] = @Date1))',N'@Date1 datetime',@Date1='2009-09-15 12:00:00'

The problem is at '@Date1 datetime', if I change it to '@Date1 date'. Then the query returns a record.

I also capture the query string when I send date in DateTimeKind.Unspecified. The result is below.

exec sp_executesql N'SELECT [MyDatabase].[dbo].[SQLDatesTable].[DateId], [MyDatabase].[dbo].[SQLDatesTable].[Date] FROM [MyDatabase].[dbo].[SQLDatesTable]  WHERE ( ( [MyDatabase].[dbo].[SQLDatesTable].[Date] = @Date1))',N'@Date1 datetime',@Date1='2009-09-16 00:00:00'

The different is at their time that answer the question why it result the record properly. I think to workaround this issue the datetime that comparing to the Date field should be in date type not datetime.

Why don't you just compare the Date part, using the DATEPART database function. (LLBLGenPro -> DBFunctionCall)

Walaa wrote:

Why don't you just compare the Date part, using the DATEPART database function. (LLBLGenPro -> DBFunctionCall)

That would be a solution but with some extra concern for developers in the team. Actually this project support local database as a major and over WCF (across different timezone server) as minor. The developers shouldn't need to know and remember what date column is in datetime or just date only because of this issue. Imagine that the database schema changes often and number of developers that new for LLBL.

Also I thought this issue is a common for any user who uses LLBL over WCF across different timezone offset.

Regards,

Hong

Specifying the type of the date serialized by WCF doesn't affect the field typegenerated in the SQL query.

Why don't you use UTC DateTime.

Walaa wrote:

Specifying the type of the date serialized by WCF doesn't affect the field typegenerated in the SQL query.

Why don't you use UTC DateTime.

You are right that it doesn't affect the field typegenerated. So my question would be a common use case that not involved to WCF. If I have datetime variable from particular sources that probably contain time also. I have to check to make sure it's actually 00:00:00 time prior to make a regular filter via predicate of LLBL? Any reason why don't LLBL just pass parameter in date type instead of datetime. Since I see that the generated entity knows the actual type of datetime field (date/datetime). I still have a through that LLBL should handle this properly rather than let the users handle this kind of common usage by another approach that come with extra work anyway.

For your suggestion about using UTC datetime. In order to convert local time to UTC, it means another line of code to do that for every single function though.

Regards,

Hong

Let's keep the discussion focused on the issue, which I think is the fact that although you have a Date field, the parameter with the value is sent as DateTime, so a 'time' fraction is sent with it, causing a problem.

What I don't understand is that when you pass a DateTime parameter, it doesn't work, as the time fraction should be ignored by sqlserver. Could you post the DDL SQL of your table please so we can have a look?

The parameter type is constructed by letting it figure it out by passing in the value, due to the fact that the parameter is created using DbProviderFactory. The thing is that the official mapping for the 'Date' type to .NET type is DateTime, so passing in a DateTime should be OK, as the value itself is returned as a DateTime anyway, and there's always a time fraction in a DateTime, even if it's 00:00.00, it has a meaning.

(edit) I see we can make an exception for this type, as it likely isn't handled properly by SqlServer it seems. As we create the parameters using generic code (DbProviderFactory) we can't use the db specific type specification, so we've to convert the value to DbType. This goes ok, but apparently not for Date which is logical, as the value is a DateTime. I'll add a check for this, (as we also did for Time, which was added in a service pack of .NET) and will attach a new build for you to this thread.

Could you please try the attached DQE ? It's making an exception for SqlDbType.Date as it already did for SqlDbType.Time, which has the same issue: SqlParameter converts both to DateTime, instead of Date resp. Time.

Otis wrote:

Could you please try the attached DQE ? It's making an exception for SqlDbType.Date as it already did for SqlDbType.Time, which has the same issue: SqlParameter converts both to DateTime, instead of Date resp. Time.

I have copied DQE file to the server but it still builds the same query for me, not sure if this updated file should results the change to query string.

exec sp_executesql N'SELECT [MyDatabase].[dbo].[SQLDatesTable].[DateId], [MyDatabase].[dbo].[SQLDatesTable].[Date] FROM [MyDatabase].[dbo].[SQLDatesTable]  WHERE ( ( [MyDatabase].[dbo].[SQLDatesTable].[Date] = @Date1))',N'@Date1 datetime',@Date1='2009-09-16 12:00:00'

It would results the record if it is either

exec sp_executesql N'SELECT [MyDatabase].[dbo].[SQLDatesTable].[DateId], [MyDatabase].[dbo].[SQLDatesTable].[Date] FROM [MyDatabase].[dbo].[SQLDatesTable]  WHERE ( ( [MyDatabase].[dbo].[SQLDatesTable].[Date] = @Date1))',N'@Date1 [u]date[/u]',@Date1='2009-09-16 12:00:00'

or

exec sp_executesql N'SELECT [MyDatabase].[dbo].[SQLDatesTable].[DateId], [MyDatabase].[dbo].[SQLDatesTable].[Date] FROM [MyDatabase].[dbo].[SQLDatesTable]  WHERE ( ( [MyDatabase].[dbo].[SQLDatesTable].[Date] = @Date1))',N'@Date1 datetime',@Date1='2009-09-16'

And here is create script of the table.

CREATE TABLE [dbo].[SQLDatesTable](
    [DateId] [int] IDENTITY(1,1) NOT NULL,
    [Date] [date] NOT NULL,
 CONSTRAINT [PK_SQLDatesTable] PRIMARY KEY CLUSTERED 
(
    [DateId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

It should emit 'Date' as the type for the field, not Datetime...

Could you please verify in your LLBLGen Pro project that the field is seen as a 'Date' field and not as a DateTime field (the db type, not the .net type) in the entity editor? The persistence info of the entity field should contain SqlDbType.Date, not SqlDbType.DateTime for the field.

Reproduced. I don't know if there's a fix for this. As the issue is inside SqlParameter. We'll try to find a workaround for this bug in SqlParameter, as setting the parameters value sets it to DateTime, not Date, and setting the parameters .DbType property to DbType.Date will set it internally to DateTime. The only way to set it to Date is by casting it to SqlParameter, which completely destroys the use of DbProviderFactory.

Fixed it. See attached file.

Otis wrote:

Fixed it. See attached file.

It works like a charm!

exec sp_executesql N'SELECT [MyDatabase].[dbo].[SQLDatesTable].[DateId], [MyDatabase].[dbo].[SQLDatesTable].[Date] FROM [MyDatabase].[dbo].[SQLDatesTable]  WHERE ( ( [MyDatabase].[dbo].[SQLDatesTable].[Date] = @Date1))',N'@Date1 date',@Date1='2009-09-16'

Thank you for your help. Also hope this thread will figure out for others .