How to determine if an IQueryable expression needs additional processing
With additional processing, I mean besides the standard LINQ to SQL to Transact SQL translation (and possible work around)
Ive two questions that I was hoping for some insight and / or some pertinent links or google terms to use to find more information on how I am not finding anything. This boils down to what I would like to know when / how an IQueryable expression to be executed determines that part of the result of the expression should be run on the client side, which means that the LINQ expression cannot be translated directly to Transact SQL to return the entire result.
This applies to my post / code.
Situation 1
string.Format()
does not convert to Transact SQL. I can agree with this, but suppose I was just looking for some advice on how to determine if Expression can fully translate Transact SQL.
LINQ output:
from h in HistoryData
select new { NewData = string.Format( "New Data: {0}", h.hisData ) };
SQL provider context:
SELECT [t0].[hisData] AS [arg0]
FROM [HistoryData] AS [t0]
My workaround: I am currently using the method DataContext.GetCommand()
and then go through the Transact SQL and look up [arg0]
. Obviously this is not ideal, so I was looking for a more robust mechanism (perhaps I discovered something with an ExpressionTree visitor?)
Situation 2
Using the tertiary operator in some situations seems to return some Transact SQL, which obviously has some post / client processing being applied to it to get the correct values. In the examples below, I'm running in the LINQPad context (so an extension method is available Dump()
). There seem to be two situations where post-processing is needed when the tertiary operator is in play:
- When a boolean variable is used together with a boolean field (var testBool expression ... below) and
- When two variables are used, regardless of type, and no database fields are requested (second testNumber expression below).
This situation surprised me that client-side processing was necessary. So, apart from learning how to correctly detect when an expression needs additional processing outside of regular Transact SQL, if anyone has an idea of why L2S cannot execute a simple CASE statement like other situations, and that more importantly, a possible workaround that can be used to avoid "client-side processing".
LINQ expressions:
var before9_1_2009 = DateTime.Today < new DateTime( 2009, 9, 1 );
var fiveThousand = 5000;
var tenThousand = 10000;
var testNumber =
Profiles.Where( p => p.pAuthID == "111111111" )
.Select( p => new { Number = before9_1_2009 ? fiveThousand : p.pKey } );
var cmd = GetCommand( testNumber );
cmd.CommandText.Dump( "Number Property: Works (i.e. evaluates on SQL Server)" );
var testString =
Profiles.Where( p => p.pAuthID == "111111111" )
.Select( p => new { String = before9_1_2009 ? "StringConstant" : p.pAuthID } );
cmd = GetCommand( testString );
cmd.CommandText.Dump( "String Property: Works (i.e. evaluates on SQL Server)" );
var testBool =
Profiles.Where( p => p.pAuthID == "111111111" )
.Select( p => new { Boolean = boolExp ? true : p.pProcessed } );
cmd = GetCommand( testBool );
cmd.CommandText.Dump( "Boolean Property: Has NULL AS [EMPTY] - Post Processing Needed" );
testNumber =
Profiles.Where( p => p.pAuthID == "111111111" )
.Select( p => new { Number = before9_1_2009 ? fiveThousand : tenThousand } );
cmd = GetCommand( testNumber );
cmd.CommandText.Dump( "Number Property (using two constants): Has NULL AS [EMPTY] - Post Processing Needed" );
SQL provider context (in order): ▪ Number property: running (i.e. evaluating on SQL Server)
SELECT
(CASE
WHEN @p1 = 1 THEN @p2
ELSE [t0].[pKey]
END) AS [Number]
FROM [Profile] AS [t0]
WHERE [t0].[pAuthID] = @p0
▪ String property: works (i.e. evaluates on SQL Server)
SELECT
(CASE
WHEN @p1 = 1 THEN CONVERT(NVarChar(255),@p2)
ELSE [t0].[pAuthID]
END) AS [String]
FROM [Profile] AS [t0]
WHERE [t0].[pAuthID] = @p0
▪ Boolean property: NULL AS [EMPTY]
SELECT NULL AS [EMPTY]
FROM [Profile] AS [t0]
WHERE [t0].[pAuthID] = @p0
▪ Number property (using two constants): NULL AS [EMPTY]
SELECT NULL AS [EMPTY]
FROM [Profile] AS [t0]
WHERE [t0].[pAuthID] = @p0
My workaround: Again, I use a method DataContext.GetCommand()
and then look at Transact SQL and look for NULL AS [EMPTY]
. I wonder if there are any other "magic lines" that will show up for expression code that can't work on the server ... as above, I'm looking for a correct and more reliable way to detect these situations.
source to share