Optionnal parameter in SEARCH CFQL method not of type varchar

Question

Is it possible to have a search function that would allow passing optional parameters other than varchar (i.e. DateTime, decimal .. ) ?

I need to search rows that are in a date range. User should be able to search FROM a date only, or up TO a date, or even in a FROM-TO range.

My approach was to create a SEARCH method with Date parameters. Those are correctly converted to DateTime object in CFE, but I won't be able to pass null value.

I managed to create a SEARCH method with unchecked parts, but those parts won't get optionnal in the body of the function.

How can I get fromDate and toDate parameters to be set as optional ?

CFQL body is

SEARCH(string amount, string fromDate, string toDate, string otherEntityId, date date) WHERE [$Entity::Date$ >= CAST(@fromDate AS DATE)] AND [$Entity::Date$ <= CAST(@toDate AS DATE)] AND OtherEntity.OtherEntityId = @otherEntityId AND Amount = @amount and date = @date

Body procedure generated is as follow. Notice the unchecked parts that aren't optionals

CREATE PROCEDURE [Schema].[Entity_NameOfProcedure]
(
 @amount [nvarchar] (256) = NULL,
 @fromDate [nvarchar] (256) = NULL,
 @toDate [nvarchar] (256) = NULL,
 @OtherEntityId [nvarchar] (256) = NULL,
 @date [date] = NULL,
 @_orderBy0 [nvarchar] (64) = NULL,
 @_orderByDirection0 [bit] = 0
)
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(max), @paramlist nvarchar(max)

SELECT @sql=
'SELECT DISTINCT * /*on purpose for stackoverflow */
    FROM [Schema].[Entity]
        LEFT OUTER JOIN [Schema].[OtherEntity] ON ([Schema].[Entity].[Entity_OtherEntity_OtherEntityId] = [Schema].[OtherEntity].[OtherEntity_OtherEntityId]) 
    WHERE (([Entity].[Entity_Date] >= CAST(@fromDate AS DATE) AND ([Entity].[Entity_Date] <= CAST(@toDate AS DATE) AND ((1 = 1) AND ((1 = 1) AND (([Schema].[OtherEntity].[OtherEntity_NumberInt] LIKE ''6%'') AND (1 = 1)))))) AND (1 = 1))'
SELECT @paramlist = '@amount nvarchar (256), 
    @fromDate nvarchar (256), 
    @toDate nvarchar (256), 
    @otherEntityId nvarchar (256), 
    @date date, 
    @_orderBy0 nvarchar (64), 
    @_orderByDirection0 bit'
IF @amount IS NOT NULL
    SELECT @sql = @sql + ' AND (([Schema].[Entity].[Entity_amount] = @amount))'
IF @otherEntityId IS NOT NULL
    SELECT @sql = @sql + ' AND (([Schema].[OtherEntity].[OtherEntity_OtherEntityId] = @OtherEntityId))'
IF @date IS NOT NULL
    SELECT @sql = @sql + ' AND (([Schema].[Entity].[Entity_Date] = @date))'
EXEC sp_executesql @sql, @paramlist,
    @amount, 
    @fromDate, 
    @toDate, 
    @OtherEntityId, 
    @date, 
    @_orderBy0, 
    @_orderByDirection0

RETURN
GO

Thanks for your answer

Answer 1

You can create a SEARCH method with optional date parameters:

SEARCH(date fromDate, date toDate)
WHERE CreationDate >= @fromDate AND CreationDate <= @toDate

This generate the following SQL:

SELECT @sql=
'SELECT DISTINCT [Employee_Id], [Employee_FirstName], [Employee_CreationDate] 
    FROM [Employee]
    WHERE (1 = 1)'
SELECT @paramlist = '@fromDate datetime, @toDate datetime'
IF @fromDate IS NOT NULL
    SELECT @sql = @sql + ' AND (([Employee_CreationDate] >= @fromDate))'
IF @toDate IS NOT NULL
    SELECT @sql = @sql + ' AND (([Employee_CreationDate] <= @toDate))'

The fromDate and toDate parameters are not nullable in the generated BOM. However if you use the default value which by default is equal to CodeFluentPersistence.DefaultDateTimeValue (More about default values), NULL will be sent to the store procedure.

If you prefer DateTime? just change the type of the parameter from date to date?

SEARCH(date? fromDate, date? toDate)
WHERE CreationDate >= @fromDate AND CreationDate <= @toDate

Which generate the following C# method:

public static EmployeeCollection Search(DateTime? fromDate, DateTime? toDate)