Debug Entity Framework Requests

Question

This is a slightly subjective question about a specific situation. The main purpose of this question for me is to remind myself that I need to copy the solution. However, if there is already a solution or an alternative approach, I would like to know.

I am working on a project and I am using Entity Framework 4 to access the database. Database design is something that I do not control. The database was developed many years ago, and, in my opinion, the database design is not suitable for the current purposes of the database. This leads to very complex queries.

This is the first time I use Entity Framework in a project, but I have a lot of experience in development compared to MS SQL Server.

What I did again and again is this:

• I am writing a complex L2E request. The query either slows down or returns incorrect results.
• I am looking at my L2E request and I absolutely do not know how to improve it.
• I run SQL Profiler and grab the SQL that EF generates from my query
• I want to run part of this sql to identify the part of the query that is causing the problems.
• The request comes through sp_executesql with a dozen parameters, because if the parameter is used 3 times in the request, L2E creates 3 parameters and passes all of them the same value. The same goes for each parameter.
• Now I need to extract SQL from sp_executesql, unescape of all escaped apostrophes and replace each parameter in the query with its value
• After that, I can finally run the request parts and indicate the problem.
• I go back to my L2E code, change it to fix the problem I found, and the loop repeats.

Honestly, I'm starting to think that you should not use ORM if you do not have your own database design.

As an aside, the unescaping sql process and parameter substitution is the one I want to automate. The goal is to get a bare, de-parameterized sql that I can run in SSMS.

This is a very simple example of what I see in the profile and what I want to get as a result. My real cases are many times more complicated.

Capture:

exec sp_executesql N'SELECT [Extent1].[ProductName] AS [ProductName] FROM [dbo].[Products] AS [Extent1] INNER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID] WHERE ([Extent1].[UnitPrice] > @p__linq__0) AND ([Extent2].[CategoryName] = @p__linq__1) AND (N''Chang'' <> [Extent1].[ProductName])',N'@p__linq__0 decimal(1,0),@p__linq__1 nvarchar(4000)',@p__linq__0=1,@p__linq__1=N'Beverages' 

Desired Result:

 SELECT [Extent1].[ProductName] AS [ProductName] FROM [dbo].[Products] AS [Extent1] INNER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID] WHERE ([Extent1].[UnitPrice] > 1) AND ([Extent2].[CategoryName] = N'Beverages') AND (N'Chang' <> [Extent1].[ProductName]) 

I'm just going to write code to convert what I liked first, for example, to the second, if there is nothing better, I will post the solution here. But maybe this is already done by someone? Or maybe there is a profiler or something else that can give me sql code that I can partially execute in SSMS?

Answer 1

So here is what I ended up with. A few notes:

• It will not work in 100% of cases, but for me it is good enough.
• In terms of usability, much can be improved. Currently, I put the shortcut on the compiled binary to the desktop, cut out the text for conversion to the clipboard, double-clicked the shortcut and pasted the result.

 using System; using System.Text.RegularExpressions; using System.Windows.Forms; namespace EFC { static class Program { [STAThread] static void Main() { try { string input = Clipboard.GetText(); const string header = "exec sp_executesql N'"; CheckValidInput(input.StartsWith(header), "Input does not start with {0}", header); // Find part of the statement that constitutes whatever sp_executesql has to execute int bodyStartIndex = header.Length; int bodyEndIndex = FindClosingApostroph(input, bodyStartIndex); CheckValidInput(bodyEndIndex > 0, "Unable to find closing \"'\" in the body"); string body = input.Substring(bodyStartIndex, bodyEndIndex - bodyStartIndex); // Unescape 's body = body.Replace("''", "'"); // Work out where the paramters are int blobEndIndex = FindClosingApostroph(input, bodyEndIndex + 4); CheckValidInput(bodyEndIndex > 0, "Unable to find closing \"'\" in the params"); string ps = input.Substring(blobEndIndex); // Reverse, so that P__linq_2 does not get substituted in p__linq_20 Regex regexEf = new Regex(@"(?<name>@p__linq__(?:\d+))=(?<value>(?:.+?)((?=,@p)|($)))", RegexOptions.RightToLeft); Regex regexLinqToSql = new Regex(@"(?<name>@p(?:\d+))=(?<value>(?:.+?)((?=,@p)|($)))", RegexOptions.RightToLeft); MatchCollection mcEf = regexEf.Matches(ps); MatchCollection mcLinqToSql = regexLinqToSql.Matches(ps); MatchCollection mc = mcEf.Count > 0 ? mcEf : mcLinqToSql; // substitutes parameters in the statement with their values foreach (Match m in mc) { string name = m.Groups["name"].Value; string value = m.Groups["value"].Value; body = body.Replace(name, value); } Clipboard.SetText(body); MessageBox.Show("Done!", "CEF"); } catch (ApplicationException ex) { MessageBox.Show(ex.Message, "Error"); } catch (Exception ex) { MessageBox.Show(ex.Message, "Error"); MessageBox.Show(ex.StackTrace, "Error"); } } static int FindClosingApostroph(string input, int bodyStartIndex) { for (int i = bodyStartIndex; i < input.Length; i++) { if (input[i] == '\'' && i + 1 < input.Length) { if (input[i + 1] != '\'') { return i; } i++; } } return -1; } static void CheckValidInput(bool isValid, string message, params object[] args) { if (!isValid) { throw new ApplicationException(string.Format(message, args)); } } } } 

Answer 2

Well, maybe it will be useful. MSVS 2010 has IntelliTrace. Every time EF makes a request, there is an ADO.Net event with the request

 Execute Reader "SELECT TOP (1) [Extent1].[id] AS [id], [Extent1].[Sid] AS [Sid], [Extent1].[Queue] AS [Queue], [Extent1].[Extension] AS [Extension] FROM [dbo].[Operators] AS [Extent1] WHERE [Extent1].[Sid] = @p__linq__0" Command Text = "SELECT TOP (1) \r\n[Extent1].[id] AS [id], \r\n[Extent1].[Sid] AS [Sid], \r\n[Extent1].[Queue] AS [Queue], \r\n[Extent1].[Extension] AS [Extension]\r\nFROM [dbo].[Operators] AS [Extent1]\r\nWHERE [Extent1].[Sid] = @p__linq__0", Connection String = "Data Source=paris;Initial Catalog=telephony;Integrated Security=True;MultipleActiveResultSets=True"