Good morning Juan Dent!
Firstly, I want to make sure I understand the issue correctly. We are trying
to get the native SQL statement when using the immediate operator like Max
in the Entity Date Model. If there is anything misunderstood by me, please
feel free to let me know.
In fact, we can still get the native SQL statement using the ToTraceString
function of the QueryObject, but we need to put the Max operator in the
command text instead of using it in code. For example, the following codes
give me the native SQL statement:
using (NorthwindEntities northWindContext = new NorthwindEntities())
{
try
{
ObjectQuery<Products> p = new ObjectQuery<Products>
("Select MAX(p.ProductID) from NorthwindEntities.Products as p",
northWindContext);
Console.WriteLine(p.ToTraceString());
}
catch (EntitySqlException ex)
{
Console.WriteLine(ex.ToString());
}
}
If the Max is used in the C# code as extension function:
using (NorthwindEntities northWindContext = new NorthwindEntities())
{
Int32 max = northWindContext.Products.Max(product => product.ProductID);
Console.WriteLine(max.ToString());
}
I looked through the object model but did not find any way to get the SQL
statement at this time. Personally speaking, comparing the two code
snippets, I think the latter one is designed to return the result
immediately after the code executed. So, it does not hold anything
internally about the native SQL statement, while the ObjectQuery does. Of
course, in the meantime, I am consulting the product team about this, and I
will let you know if I get any confirmation from them.
And would you mind letting me know for what objective we want to get the
native SQL? In my opinion, the mostly like purpose we need it is for
debugging, right? If that is the case, another approach is we can use some
tool to trace the SQL request at the server side. For the SQL Server, I use
the SQL Profiler to trace it and find the second code snippet’s
corresponding native SQL is:
SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]
LEFT OUTER JOIN (SELECT
MAX([Extent1].[ProductID]) AS [A1]
FROM [dbo].[Products] AS [Extent1] ) AS [GroupBy1] ON 1 = 1
Please let me know whether my suggestion works for you or not. If you have
any future questions or concerns, I will do my best to provide future help.
Have a nice weekend!
Best regards,
Ji Zhou (
[email protected], remove ‘online.’)
Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
