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
