The linq:

var authDate = DateTime.Now;
DateTime? inputDate = DateTime.Now;

var q = from i in Query.All<RegCalculatedPrice>()
                    where i.ActualizationDate <= authDate
                          && i.PriceDate <= (inputDate.HasValue ? inputDate.Value : i.ActualizationDate)
                    group i by new { FinTool = i.FinTool.Id, Fund = i.Fund.Id }
                    into gr
                    join c in Query.All<RegCalculatedPrice>() on
                        new { gr.Key.FinTool, gr.Key.Fund, Date = gr.Max(r => r.ActualizationDate) } equals
                        new { FinTool = c.FinTool.Id, Fund = c.Fund.Id, Date = c.ActualizationDate }
                    where c.ActualizationDate <= authDate
                          && c.PriceDate <= (inputDate.HasValue ? inputDate.Value : c.ActualizationDate)
                    select new { FinTool = c.FinTool.Id, Fund = c.Fund.Id, c.Price };

The Sql:

SELECT  [a].[#a.Price] AS Price
       ,[a].[#a.FinTool_Id] AS FinTool_Id
       ,[a].[#a.Fund_Id] AS Fund_Id
FROM    ( SELECT    [b].[FinTool_Id]
                   ,[b].[Fund_Id]
                   ,MAX([b].[ActualizationDate]) AS [column]
          FROM      ( SELECT    [c].[Id]
                               ,208 AS [TypeId]
                               ,[c].[ActualizationDate]
                               ,[c].[MarketPlace_Id]
                               ,[c].[Price]
                               ,[c].[PriceDate]
                               ,[c].[Variant_Id]
                               ,[c].[Algorithm_Id]
                               ,[c].[FinTool_Id]
                               ,[c].[Fund_Id]
                               ,[c].[LooseDate]
                      FROM      [dbo].[RegCalculatedPrice] [c]
                      WHERE     ( ( [c].[ActualizationDate] <= @p0_0 )
                                  AND ( [c].[PriceDate] <= ( CASE WHEN ( CAST(1 AS BIT) <> 0 ) THEN @p0_1
                                                                  ELSE [c].[ActualizationDate]
                                                             END ) )
                                )
                    ) [b]
          GROUP BY  [b].[FinTool_Id]
                   ,[b].[Fund_Id]
        ) [d]
INNER JOIN ( SELECT [e].[Id] AS [#a.Id]
                   ,208 AS [#a.TypeId]
                   ,[e].[ActualizationDate] AS [#a.ActualizationDate]
                   ,[e].[MarketPlace_Id] AS [#a.MarketPlace_Id]
                   ,[e].[Price] AS [#a.Price]
                   ,[e].[PriceDate] AS [#a.PriceDate]
                   ,[e].[Variant_Id] AS [#a.Variant_Id]
                   ,[e].[Algorithm_Id] AS [#a.Algorithm_Id]
                   ,[e].[FinTool_Id] AS [#a.FinTool_Id]
                   ,[e].[Fund_Id] AS [#a.Fund_Id]
                   ,[e].[LooseDate] AS [#a.LooseDate]
             FROM   [dbo].[RegCalculatedPrice] [e]
           ) [a] ON ( ( ( [d].[FinTool_Id] = [a].[#a.FinTool_Id] )
                        AND ( [d].[Fund_Id] = [a].[#a.Fund_Id] )
                      )
                      AND ( [d].[column] = [a].[#a.ActualizationDate] )
                    )
WHERE   ( ( [a].[#a.ActualizationDate] <= @p0_2 )
          AND ( [a].[#a.PriceDate] <= ( CASE WHEN ( CAST(1 AS BIT) <> 0 ) THEN @p0_3
                                             ELSE [a].[#a.ActualizationDate]
                                        END ) )
        ) ;

As you can see

i.PriceDate <= (inputDate.HasValue ? inputDate.Value : i.ActualizationDate)

Is translated into

[c].[PriceDate] <= ( CASE WHEN ( CAST(1 AS BIT) <> 0 ) THEN @p0_1
                                                                  ELSE [c].[ActualizationDate]
                                                             END )

Which is obviously wrong and must be:

[c].[PriceDate] <= ( CASE WHEN ( @p0_1 IS NOT NULL ) THEN @p0_1
                                                                  ELSE [c].[ActualizationDate]
                                                             END )

Moreover, if i use null for inputDate i get Exception during the query!

[InvalidOperationException: Nullable object must have a value.]
   System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource) +52
   System.Nullable`1.get_Value() +1786133
   lambda_method(Closure , Object[] ) +86
   Xtensive.Core.<>c__DisplayClass1`2.<Bind>b__0() in c:\Temp\DataObjects.Net\Xtensive.Core\Xtensive.Core\Extensions\DelegateBindExtensions.cs:22
   Xtensive.Storage.Providers.Sql.CommandPartFactory.CreateQueryCommandPart(SqlQueryTask task, String parameterNamePrefix) in c:\Temp\DataObjects.Net\Xtensive.Storage\Xtensive.Storage.Providers.Sql\CommandProcessing\CommandPartFactory.cs:82
   Xtensive.Storage.Providers.Sql.BatchingCommandProcessor.ExecuteBatch(Int32 numberOfTasks, QueryRequest lastRequest) in c:\Temp\DataObjects.Net\Xtensive.Storage\Xtensive.Storage.Providers.Sql\CommandProcessing\BatchingCommandProcessor.cs:74
   Xtensive.Storage.Providers.Sql.BatchingCommandProcessor.ExecuteRequestsWithReader(QueryRequest request) in c:\Temp\DataObjects.Net\Xtensive.Storage\Xtensive.Storage.Providers.Sql\CommandProcessing\BatchingCommandProcessor.cs:56
   Xtensive.Storage.Providers.Sql.ExecuteTupleReader>d__1.MoveNext() in c:\Temp\DataObjects.Net\Xtensive.Storage\Xtensive.Storage.Providers.Sql\SessionHandler.IQueryExecutor.cs:23
   Xtensive.Storage.Providers.Sql.<OnEnumerate>d__0.MoveNext() in c:\Temp\DataObjects.Net\Xtensive.Storage\Xtensive.Storage.Providers.Sql\SqlProvider.cs:57
   Xtensive.Storage.Rse.Providers.<GetEnumerator>d__0.MoveNext() in c:\Temp\DataObjects.Net\Xtensive.Storage\Xtensive.Storage.Rse\Providers\ExecutableProvider.cs:220
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +327
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +58
   Xtensive.Storage.Rse.<GetEnumerator>d__4.MoveNext() in c:\Temp\DataObjects.Net\Xtensive.Storage\Xtensive.Storage.Rse\RecordSet.cs:72
   System.Linq.WhereSelectEnumerableIterator`2.MoveNext() +87
   Xtensive.Core.<Batch>d__20`1.MoveNext() in c:\Temp\DataObjects.Net\Xtensive.Core\Xtensive.Core\Extensions\EnumerableExtensions.cs:498
   Xtensive.Core.<ApplyBeforeAndAfter>d__28`1.MoveNext() in c:\Temp\DataObjects.Net\Xtensive.Core\Xtensive.Core\Extensions\EnumerableExtensions.cs:594
   Xtensive.Storage.<ToTransactional>d__0`1.MoveNext() in c:\Temp\DataObjects.Net\Xtensive.Storage\Xtensive.Storage\TransactionalExtensions.cs:327
   System.Linq.<SelectManyIterator>d__14`2.MoveNext() +399
   System.Linq.Enumerable.ToDictionary(IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector, IEqualityComparer`1 comparer) +164
   System.Linq.Enumerable.ToDictionary(IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector) +54
   Test.Controller.Services.Operations.DocPriceBalancePriceOperation.GetBalanceForDate(DateTime authDate, Nullable`1 inputDate) in D:\home\Docs\Visual Studio 2010\Projects\TestPrimer\Test.Controller.Services\Operations\DocPriceBalancePriceOperation.cs:310
   Test.Controller.Services.Operations.DocPriceBalancePriceOperation.CalculateAveragePrice(IQueryable`1 transactions, DateTime authDate, Nullable`1 inputDate) in D:\home\Docs\Visual Studio 2010\Projects\TestPrimer\Test.Controller.Services\Operations\DocPriceBalancePriceOperation.cs:140
   Test.Controller.Services.Operations.DocPriceBalancePriceOperation.Finish(OperationContext context) in D:\home\Docs\Visual Studio 2010\Projects\TestPrimer\Test.Controller.Services\Operations\DocPriceBalancePriceOperation.cs:116
   Werp.Controller.Services.<>c__DisplayClass6.<ExecuteOperation>b__2(IDocumentOperation o) in D:\Home\Docs\Visual Studio 2010\Projects\Platform\Werp.Controller.Services\DocumentDataObject.cs:216
   Werp.Controller.Services.DocumentDataObject`1.ExecuteOperationMethod(String operation, T[] documents, ParameterBase parameter, Action`1 func) in D:\Home\Docs\Visual Studio 2010\Projects\Platform\Werp.Controller.Services\DocumentDataObject.cs:600
   Werp.Controller.Services.DocumentDataObject`1.ExecuteOperation(DocumentOperation documentOperation, Guid[] documentIds, ParameterBase parameter) in D:\Home\Docs\Visual Studio 2010\Projects\Platform\Werp.Controller.Services\DocumentDataObject.cs:216
   Werp.Controller.Services.DocumentDataObject`1.ExecuteOperation(Guid operationId, Guid[] documentIds, ParameterBase parameter) in D:\Home\Docs\Visual Studio 2010\Projects\Platform\Werp.Controller.Services\DocumentDataObject.cs:184
   Werp.View.Ext.GridButtons.Operation.ExecuteOperation(Guid operation, String entityName, Guid[] ids, Guid operationLogId, String targetCmp) in D:\Home\Docs\Visual Studio 2010\Projects\Platform\Werp.View.Ext\GridButtons\Operation.cs:254
   lambda_method(Closure , Object , Object[] ) +333
   FastReflectionLib.MethodInvoker.Invoke(Object instance, Object[] parameters) +57
   FastReflectionLib.MethodInvoker.FastReflectionLib.IMethodInvoker.Invoke(Object instance, Object[] parameters) +46
   Werp.Utils.Helpers.ReflectionHelper.StaticCallMethod(Type type, String memberName, Object[] args) in D:\Home\Docs\Visual Studio 2010\Projects\Platform\Werp.Utils\Helpers\ReflectionHelper.cs:767
   Werp.View.Ext.ButtonHandler.ExecuteMethod(MethodCallInfo callInfo) in D:\Home\Docs\Visual Studio 2010\Projects\Platform\Werp.View.Ext\Handlers\ButtonHandler.ashx.cs:61
   Werp.View.Ext.ButtonHandler.ProcessRequest(HttpContext context) in D:\Home\Docs\Visual Studio 2010\Projects\Platform\Werp.View.Ext\Handlers\ButtonHandler.ashx.cs:126
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +100
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +75

asked Aug 12 '10 at 09:35

xumix's gravatar image

xumix
425737682

edited Aug 12 '10 at 10:17


One Answer:

This issue has been fixed and now expressions like that

i.PriceDate <= (inputDate.HasValue ? inputDate.Value : i.ActualizationDate)

will be translated into following SQL expressions

[c].[PriceDate] <= @p0_1

when inputDate.HasValue is true and

[c].[PriceDate] <= [c].[ActualizationDate]

when it's false using our Boolean Branching technology so the query plan will not contain table scans for expressions like above.

answered Aug 17 '10 at 00:05

Alexis%20Kochetov's gravatar image

Alexis Kochetov
1414

What about the latter exception?

(Aug 17 '10 at 03:49) xumix xumix's gravatar image
1

it's fixed as well

(Aug 17 '10 at 08:23) Alexis Kochetov Alexis%20Kochetov's gravatar image
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA