Hello,

I have written a very simple IExpression to associate with an Entity Fields (Start Date) to a another Entity Fields (Duration in Months). I am using the ExOp.Add Operator to add the Fields.

However, the result i am getting back is the start date plus Duration In Months as the number of days and not months. This is the correct behaviour because that is the result if you did a simple SQL Query to add the two columns

So if the start date is 1/1/08 and Duration is 2 then i want the expression to treat the start date as 1/03/08 and not 3/01/08 as it is currently doing.

My question is how do you form an expression to add the Duration In Months Field as Months to the start date and not as days (as defaulted). Here is part of my code i have used to form the expression.

Expression ex = new Expression(new DbFunctionCall("DATEADD", new object[] { ContractVersionFields.StartDate }),ExOp.Add, ContractVersionFields.DurationInMonths);

ContractVersionFields.StartDate.ExpressionToApply = ex;

filter.Add(ContractVersionFields.StartDate <DateTime.Today);

Basically, i am getting results where the Start Date plus the Duration in Months (in days) is less than todays date. Any help would be appreciated. I am having trouble finding support for this operator with DateTime.

Thanks, Pav

try this instead:

ContractVersionFields.StartDate.ExpressionToApply = new DbFunctionCall("DATEADD(month,{0},{1})", new object[] { ContractVersionFields.DurationInMonths, ContractVersionFields.StartDate});

Hi goose,

Thanks for your reply,

I have tried the snippet you have given but unfortuntely this is not working, i am getting back all the contracts now from the database (as if the filter is not being applied anymore)

so my code now looks like

                    
ContractVersionFields.StartDate.ExpressionToApply = new DbFunctionCall("DATEADD(month,{0},{1})", new object[] { ContractVersionFields.DurationInMonths, ContractVersionFields.StartDate }); 

filter.Add(ContractVersionFields.StartDate < DateTime.Today);

where exactly does the addition of the two fields take place if we are not using ExOp anymore?

Regards, Pav

I have tried the snippet you have given but unfortuntely this is not working, i am getting back all the contracts now from the database (as if the filter is not being applied anymore)

Would you please examine and post the generated SQL Query? Consult the LLBLGen manual "Using the generated code -> Troubleshooting and debugging"

where exactly does the addition of the two fields take place if we are not using ExOp anymore?

In the database, that's what DATEADD() does. Please refer to SQL Server books online.

Hi, How do i get to the generated SQL produced by LLBlgen?

Check the LLBLGen manual "Using the generated code -> Troubleshooting and debugging"

Here is the code from Tracing, note the WHERE clause:

( [WESS].[mpm].[contract_version].[start_date] < @StartDate2)))

I would have expected this to be

DATEADD(month,[WESS].[mpm].[contract_version].[duration_in_months] , [WESS].[mpm].[contract_version].[start_date]) < @StartDate2

EXEC sp_executesql N'SELECT DISTINCT [WESS].[mpm].[contract_version].[deployment_id] 
AS [DeploymentId], [WESS].[mpm].[contract_version].[contract_version_id] 
AS [ContractVersionId], [WESS].[mpm].[contract_version].[contract_version_name] 
AS [ContractVersionName], [WESS].[mpm].[contract_version].[contract_version_description] 
AS [ContractVersionDescription], [WESS].[mpm].[contract_version].[created_on_date] 
AS [CreatedOnDate], [WESS].[mpm].[contract_version].[submitted_on_date] 
AS [SubmittedOnDate], [WESS].[mpm].[contract_version].[approved_on_date] 
AS [ApprovedOnDate], [WESS].[mpm].[contract_version].[contract_type_id] 
AS [ContractTypeId],
 [WESS].[mpm].[contract_version].[start_date] 
AS [StartDate], 
[WESS].[mpm].[contract_version].[duration_in_months] 
AS [DurationInMonths], [WESS].[mpm].[contract_version].[contract_status_id] 
AS [ContractStatusId], [WESS].[mpm].[contract_version].[estimated_global_volume_inbev] 
AS [EstimatedGlobalVolumeInbev], [WESS].[mpm].[contract_version].[logistical_drops] 
AS [LogisticalDrops], [WESS].[mpm].[contract_version].[logistical_complexity] 
AS [LogisticalComplexity], [WESS].[mpm].[contract_version].[existing_investments_redisual_value] 
AS [ExistingInvestmentsRedisualValue], [WESS].[mpm].[contract_version].[wholesaler_investment_amount] 
AS [WholesalerInvestmentAmount], [WESS].[mpm].[contract_version].[wholesaler_investment_description] 
AS [WholesalerInvestmentDescription], [WESS].[mpm].[contract_version].[tenant_investment_amount] 
AS [TenantInvestmentAmount], [WESS].[mpm].[contract_version].[tenant_investment_description] 
AS [TenantInvestmentDescription], [WESS].[mpm].[contract_version].[tenant_investment_source_id] 
AS [TenantInvestmentSourceId], [WESS].[mpm].[contract_version].[submitted_by_user_id] 
AS [SubmittedByUserId], [WESS].[mpm].[contract_version].[submitted_by_user_name] 
AS [SubmittedByUserName], [WESS].[mpm].[contract_version].[approved_by_user_id] 
AS [ApprovedByUserId], [WESS].[mpm].[contract_version].[approved_by_user_name] 
AS [ApprovedByUserName], [WESS].[mpm].[contract_version].[signed_by_user_id] 
AS [SignedByUserId], [WESS].[mpm].[contract_version].[signed_by_user_name] 
AS [SignedByUserName], [WESS].[mpm].[contract_version].[created_by_user_id] 
AS [CreatedByUserId], [WESS].[mpm].[contract_version].[created_by_user_name] 
AS [CreatedByUserName], [WESS].[mpm].[contract_version].[last_modified_by] 
AS [LastModifiedBy], [WESS].[mpm].[contract_version].[last_modified_date] 
AS [LastModifiedDate], [WESS].[mpm].[contract_version].[is_deleted] 
AS [IsDeleted], [WESS].[mpm].[contract_version].[timestamp] 
AS [Timestamp] FROM ((( [WESS].[mpm].[contract_version]  INNER JOIN [WESS].[mpm].[contract_contract_version]  
ON  [WESS].[mpm].[contract_version].[deployment_id]=[WESS].[mpm].[contract_contract_version].[deployment_id] 
AND [WESS].[mpm].[contract_version].[contract_version_id]=[WESS].[mpm].[contract_contract_version].[contract_version_id]) 
INNER JOIN [WESS].[mpm].[contract]  
ON  [WESS].[mpm].[contract].[deployment_id]=[WESS].[mpm].[contract_contract_version].[deployment_id] 
AND [WESS].[mpm].[contract].[contract_id]=[WESS].[mpm].[contract_contract_version].[contract_id]) 
INNER JOIN [WESS].[poc].[business_partner]  ON  [WESS].[poc].[business_partner].[deployment_id]=[WESS].[mpm].[contract].[deployment_id] 
AND [WESS].[poc].[business_partner].[business_partner_id]=[WESS].[mpm].[contract].[poc_business_partner_id]) 
WHERE ( ( [WESS].[mpm].[contract_version].[deployment_id] = @DeploymentId1 
AND ( [WESS].[mpm].[contract_version].[start_date] < @StartDate2)))', N'@DeploymentId1 tinyint,
@StartDate2 smalldatetime', @DeploymentId1 = 6, @StartDate2 = '2008-02-19 00:00:00:000'

Try the following please:

EntityField myField = ContractVersionFields.StartDate;

myField.ExpressionToApply = new DbFunctionCall("DATEADD(month,{0},{1})", new object[] { ContractVersionFields.DurationInMonths, ContractVersionFields.StartDate }); 

filter.Add(myField < DateTime.Today);

or

IExpression myExpression = new DbFunctionCall("DATEADD(month,{0},{1})", new object[] { ContractVersionFields.DurationInMonths, ContractVersionFields.StartDate }); 

filter.Add(ContractVersionFields.StartDate.SetExpression(myExpression) < DateTime.Today);

Thanks, that worked!

I am using Self Servicing, so i just changed

EntityField myField = ContractVersionFields.StartDate;

to

EntityField2 myField = ContractVersionFields.StartDate;

Could you explain why we used and EntityField and not Expression?

Thanks, Pav

The expression wasn't available in the predicate. We need an instance of an entityField to hold the expression for us to be used in the predicate.