Home » .Net Framework

Tracing The Root Cause of a SQL Query From the EF

I want to be able to easily tie the TSQL I'm seeing executed via SQL Profiler to the calls to the ObjectContext that was the "root cause" for the query.

I was hoping to see some event exposed by the ObjectContext which was raised when the ObjectContext was about to execute against the data store, but I don't see any such option.



2 Answers Found


Answer 1

Take a look at this: http://huagati.blogspot.com/2010/06/entity-framework-support-in-huagati.html   ...it will allow you to profile queries, and ties managed code call stacks to queries with execution plans, I/O cost etc...

There is also Jarek Kowalski's EFTracingProvider that will allow you to intercept and log queries:

I think the 'Data Points' column by Julie Lerman in an upcoming issue of MSDN Magazine will cover EF profiling and some tools available for that. Not sure when that will be out though...  Julie?



  Cool tools for Linq-to-SQL and Entity Framework 4:
 huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
 huagati.com/L2SProfiler - Query profiler  for Linq-to-SQL and Entity Framework v4

Answer 2

Ah, cool app. Thanks for the info.

hi friends,

Is it possible that while a query is running in sql server 2005 but cann't be possible to trace it in profiler or in activity monitor.

Thanks in Advance

Hi all,

I am running a cursor to compress the tables in one of my dbs from the SSMS (The cursor will store the table names from sys.tables and compress the tables one by one) and want to know how far I am away from finish. Especially I want to know how to do it using SQL profiler, which events and column should I trace?



Using EF in .NET 3.5 SP1, Visual Studio in 2008.

Sql Server Sproc is returning different values in Query Analyzer and EF for some columns.

The sproc returns results from a view joined to a table.  The proper count of records is returned.  But for one record, the VariationID column in SQL Server returns 10449, while EF returns a VariationID of null for that same record.

Any suggestions on what could be causing this discrepancy and how to fix it?




I'm using WCF Data Services against an EF model. The one Entity in the model goes against a table with about 375 million records (the service has a limit set to 10,000 against that table), however when I even try to select the top 10 records the SQL EF generates is inefficient (see example below).

[Extent1].[Key] AS [Key], 
[Extent1].[Field1] AS [Field1], 
[Extent1].[Field2] AS [Field2], 
[Extent1].[Field3] AS [Field3], 
[Extent1].[Field4] AS [Field4], 
[Extent1].[Field5] AS [Field5]
   [TABLE_A].[Key] AS [Key], 
   [TABLE_A].[Field1] AS [Field1], 
   [TABLE_A].[Field2] AS [Field2], 
   [TABLE_A].[Field3] AS [Field3], 
   [TABLE_A].[Field4] AS [Field4], 
   [TABLE_A].[Field5] AS [Field5], 
  FROM [dbo].[TABLE_A] AS [TABLE_A]) AS [Extent1]
ORDERBY [Extent1].[Key] ASC

With a table size i'm dealing with this is horrible. Is there anyway to make EF remove that nested select? 


Yesterday I ran a 3 hour load test on a web application which uses a SQL Server 2008 database. In the run settings, I enabled SQL Trace.  At the end of the test run, I got the following error:

"The LoadTest completed but an error occurred while collecting the final results: Could not stop SQL tracing: Could not find the requested trace."

A trace file was in fact successfully created and had a file size of 102400KB.  On opening it in SQL Profiler I could see the trace.  What I think happened is that a maximum file size was reached and it stopped tracing.

It appears that a maximum file size may be set up together with a rollover option when creating a new trace.  So I assume that Visual Studio sets the maximum file size to 102400KB with no rollover enabled.  So I would like to know if there is any way of configuring maximum file size and rollover for SQL Trace integrated into Load Tests?  Otherwise, the SQL trace integration is not going to work for real world testing!




Does anyone know if the EF caching and tracing provider listen on this blog post works with EF v1?





Let's start by giving you the layout of my tables.

Orders -> OrderRows -> Product -> Category

I am listing categories on my page and I also need to present the totalsales per category. The category table has a field named highercategoryID which tells the system which category a category might belongs to.

But there is a couple of root categories and they have highercategoryID = 0 and I need to get all sales connected to that categoryID.

So I get a list of root categories. But then I need to query the orderrows table joined to categories to find all products that belongs to a root level category and sum the sales in that category.

If any of you can follow this please start tipping me of :)



How can I trace queries created by EF CTP 5? For all CRUD operations.

I can see read-queries by calling ToString() on my DbSet<TEntity>, but thats just the query generated, and not the "one" actually send to the data store (in every case, that is).

Can I hook an event some where that gets handled when a command/query is sent to the data store?

I'm using EF CTP 5 with an existing database, so no "code first" here.


I use many common modules to create a data view of an SQL query result of a specific table. This result populates a matrix of rows and columns, similar to a data grid, but a row includes a mixture of combobox and textbox controls. The matrix is navigated and paged with a navigation group of buttons and identical keystroke commands as the Microsoft Access product.

When the user selects a new set of parent values, I have to generate a new SQL result, with which I will populate the matrix.

An example would be the user selects a supplier, then a purchase order, and the items on this PO appear in the data grid (matrix), and individual matrix items can be inserted, deleted or updated. When the user selects a different PO, a new SQL result is generated and the matrix items are re-populated. a similar scenario would be a WorkOrder, an assembly, and the parts on a Bill of Material.

I need to replace the existing data in the dataview with data from the new SQL query result, but I do not know how to do this. (I could do this 30 years ago(in COBOL) and 1 year ago (in VB6) by "closing" and "opening" the table). The relevant snippets of dataview coding from the "dbItems" module are:











'+-- 'Items' table -----------------------------------------------------------------+


Public cmItems As CurrencyManager 'table record count/currency


Public dcItems As OdbcCommand 'odbc command


Public dsItems As DataSet 'table dataset

Public dvItems As Dataview 'table dataview

Public sqSQL As String 'Sql statement


PublicSub OpenViewItems()


'open the Items table and retrieve the SQL records


Dim daItems AsNew OdbcDataAdapter(sqSql, svroConnection) 'create a loaded DataAdapter


'close the connection

dsItems =

New DataSet() 'create a new DataSet


"Items") 'fill the DataSet using the DataAdaptor<->SQLdatabase link

dvItems =

New DataView(dsItems.Tables("Items")) 'create the DataView




PublicSub ResetViewItems()


'open the Items table and retrieve the new SQL records


Dim daItems AsNew OdbcDataAdapter(sqSql, svroConnection) 'create a loaded DataAdapter


'close the connection

dsItems.Clear 'clear the DataSet


"Items") 'fill the DataSet using the DataAdaptor<->SQLdatabase link

dvItems =

New DataView(dsItems.Tables("Items")) 'create the DataView



 With the new 'sqSQL' query result, I call the "ResetViewItems" subroutine, but it fails on the "daItems.Fill ..." statement with an SQLServer error.

 If I use the original 'sqSQL' query result when I call the  "ResetViewItems", no SQLServer failure. So the 'Clear' method only works if you use the exact same SQL query. (?? 'Refresh')

 If I call the "OpenViewItems" subroutine wih the new "sqSQL" result, I get the same SQLServer error.

 Since the user can browse through 1 to 100 PO's and their items, I obviously need one common subroutine to execute this logic.

How can I reload the "dsItems" data set with a different query result?


Richard Kirby


hi guys,

i want to upload a file to my ftp server.i use code that it is below :

if (FileUpload1.HasFile)
                string fileExt = System.IO.Path.GetExtension(FileUpload1.FileName);
                FileUpload1.SaveAs(request.MapPath + FileUpload1.FileName);

                FileUpload1.SaveAs("ftp://myftp.com" + FileUpload1.FileName);


but i take some error


"The SaveAs method is configured to require a rooted path, and the path 'ftp://myftp.com/Links.txt' is not rooted."


when i want to upload it to c:\\ , it's work.

what's problem?anybody help me?



I'm running into a problem that is puzzling to say the least.  I'm using the WebRequestMethods.FTP.listdirectory to get a list of files in a ftp directory.  The problem is that the web request is inserting the directory root when my url is  On this server the root directory is the same hiearchy as the archive directory.  If I leave off the "archive" it gives me the contents of the root folder which is not at the highest level.  Any ideas how to stop root from being inserted?



 am using HierrchicalDataTemplate for display all categeory but how to do for all categeory under one root node like product categeory







      Pro C# .net

     Pro Asp.net MVC

my code

 <TreeView Name="CategoryTreeView" ContextMenu="{StaticResource CtrlTreeView}"   TreeViewItem.Selected="CategoryTreeView_Selected" >

                    <HierarchicalDataTemplate  DataType="{x:Type local:RootCategeory}" ItemsSource="{Binding  Path=Children}" >
                        <StackPanel Style="{StaticResource treeviewSyle}">
                            <TextBlock Text="{Binding Path=RootNodeCategeory.CategoryName}" >

                            <Style TargetType="TreeViewItem">
                                <Setter Property="IsExpanded" Value="True"/>
                                <Setter Property="Foreground" Value="Green"/>



I have flowchart as implementation  of activitybuilder and added two dynamicactivityproperty to it. The flowchart is having flowdecision and 3 other customized activities with same defined arguments. But when i invoke the workflow and pass dictionary, it gives the error:

The values provided for the root activity's arguments did not satisfy the root activity's requirements:
'Sample Workflow': The following keys from the input dictionary do not map to arguments and must be removed: FolderName, FolderSize.  Please note that argument names are case sensitive.
Parameter name: rootArgumentValues





fc = newFlowchart(); 

protectedoverridevoid OnInitialized(EventArgs e)

{ wd.Load(newActivityBuilder { Implementation =  fc});

            ModelTreeManager mtm = wd.Context.Services.GetService<ModelTreeManager>();

            ModelItem ab = mtm.Root;

            ModelItemCollection argsAndProperties = ab.Properties["Properties"].Collection;        

            InArgument<string> foldername = newInArgument<string>();

            InArgument<double> foldersize = newInArgument<double>();










{Name = "FolderName",

Type =





Value = foldername






Type =




Name =




Value = foldersize



My invoke method: 

Dictionary<string, object> Para = newDictionary<string, object>();



"FolderName", "Workflow");



"FolderSize", 100);




WorkflowInvoker.Invoke(fc, Para);

This error gets removed if I add [RequiredArgument][DefaultValue(null)] to FolderName and FolderSize(default value =0) but new error comes :

Value for a required activity argument 'FolderName' was not supplied.

Value for a required activity argument 'FolderSize' was not supplied.

 Guide me through my mistake.

Thanks in advance.





LINQ Queries
//linq distinctvar tns = (
 from t in tnlist
 orderby t
 select t)

//linq selectusing (var db = new GetPONsDataContext())
	var stop = new Stopwatch();
	db.Log = new Diagnostics.DebuggerWriter();
	db.CommandTimeout = 3600;

	var custdata = 
	 from c in db.CSTMR
	 where tns.Contains(
   c.PHN_NBR) && c.PON != nullselectnew { c.PON, c.PTNR_ID };

 var strmw = File.CreateText(path + "test.txt");
	foreach (var item in custdata)
SQL Query
SELECT [t0].[PHN_NBR],[t0].[PON], [t0].[PTNR_ID]
FROM [dbo].[CUST] AS [t0]
	 [t0].[PHN_NBR] IN 
....(notin the actually query, 
  didnt want to post 200 numbers)

In the LINQ query the tnlist is a string list, will vary in number (20-1000) of 10 digit phone numbers.  The SQL query finishes in about 5-10 seconds(having approx 190 number in the list) in SQL Server Mgmt Studio, but the LINQ query takes over 3 minutes. I'm using a debugger class that writes the SQL text to the debug window in VS and when the LINQ query is running it shows the query text twice, so after stepping thru the code, it produces the query on the LINQ select and then again when it gets to the foreach loop.

I am still a beginner with LINQ so any assistance is greatly appreciated!




I am using EF on my server, and I need a possibility to cancel queries that run more than 30 Sec,

 is it possible to do in Entity Framework,

 what is the recommended approach?


I have a simple LINQ query across my Entity Framework model like this:



var result1 = from p in ctx.Parts
               where p.DamageLine.RepairJob.ShopId == "1002"
               select p;

The problem with this is that it can take up to 50+ seconds to bring back 11 Parts records. I looked at the SQL output and it's selecting not only all the fields from Parts (which it should) but also including the DamageLine and RepairJob fields.

Any ideas?

David McCarter

Is there any way with EF CTP 5 to trace queries and potentially profile them?

In my database I have an Event table, a Doc table, and an EventDoc table that supports a many-to-many relationship between Event and Doc.  My problem is that I'd like to create an EF/LINQ query that joins across the three tables and control which columns show up in the resulting projection.

In a SQL terms, here's what I'm looking for:


	Event e
	innerjoin EventDoc ed on ed.EventID = e.EventID
	innerjoin Doc d on d.DocID = ed.DocID

All I've been able to generate in LINQ so far is something equivalent to this:


	Event e
	innerjoin EventDoc ed on ed.EventID = e.EventID
	innerjoin Doc d on d.DocID = ed.DocID


So I've generated an EF model from my database and all of my tables have proper FK's.  Here's my C# code to generate the query:


var query = context.Event
    .Select(x => new
        EventDate = x.EventDate,
        FileName = x.EventDoc.Doc.Filename


The compiler complains with the following two error messages:


Error20 Cannot convert lambda expression to type 'string' because it is not a delegate type


Error21 'System.Data.Objects.DataClasses.EntityCollection<DataModel.EventDoc>' does not contain a definition for 'Doc' and no extension method 'Doc' accepting a first argument of type 'System.Data.Objects.DataClasses.EntityCollection<DataModel.EventDoc>' could be found (are you missing a using directive or an assembly reference?)

Now I know my model my EventDoc entity does indeed have a definition for Doc so I'm not sure how I'm supposed to do this.

Any ideas?
Thx, Mark



I've been looking at solutions to implementation logging/tracing within various Biztalk artifacts (orchestrations, custom pipeline components, etc) and came across a recent article on using Event Tracing for Windows along with a suggested framework to make use of it:


After a read through the article, seems like exactly what I'd like to accomplish so I did some testing with an orchestration and have run into a couple issues that I'm hoping someone here might have some experience with if they've also played with the framework from the Microsoft CAT team:

1.) The framework source includes some batch files to get things going (StartTrace, StopTrace).  When StopTrace is run, it automatically converts the binary file into a text file (using tracefmt.exe).  I went ahead and added 2 warnings and an exception to my test orchestration in an expression shape:

Microsoft.BizTalk.CAT.BestPractices.Framework.Instrumentation.TraceManager.WorkflowComponent.TraceError("Some error message here");
Microsoft.BizTalk.CAT.BestPractices.Framework.Instrumentation.TraceManager.CustomComponent.TraceError("Some custom component error message here");
Microsoft.BizTalk.CAT.BestPractices.Framework.Instrumentation.TraceManager.WorkflowComponent.TraceWarning("Some warning message here");

The result text files contains the following entries:

[0]12B4.1F04::06/17/2010-10:41:06.750 [Event]:Some error message here
[0]12B4.1F04::06/17/2010-10:41:06.751 [Event]:Some custom component error message here
[0]12B4.1F04::06/17/2010-10:41:06.751 [Event]:Some warning message here

Noticeably missing is the level of message (Error, Warning, Info, etc).  If I run DebugView simultaneously, I do see the levels there:

[4788] ERROR: Some error message here
[4788] ERROR: Some custom component error message here
[4788] WARN: Some warning message here

Anyone know if it's possible to get this information into the text dump files?  There's a "Default.tmf" file that get's used by tracefmt.exe, but it's not clear to me how it could/should be updated

2.) Not sure how to make the message severity available, I thought I should create 2 different logs -- one for just errors and warnings running all the time, the other for all messages running on an as-needed basis manually.  So I made two calls to the StartTrace batch file:

CALL StartTrace -log BtsAppErrors -level medium	
CALL StartTrace -log BtsAppInfo -level high

("high" means includes a greater number of message categories, not higher severity)

However, it seems that only the last run log ever gets events.  Above, the Info log will have all the events, the Errors none, including the Errors.  Is it possible to have the event messages going to two different logs?

3.) Any recommendations on tools to visualize/view the binary log files?  It seems that Windows Event Viewer can open them, but there is essentially no data associated with the entries, so I'm assuming it's not quite in the correct format for Event Viewer.

I realize this might be more ETW related than Biztalk, but I've seen a few recent mentions of the article above on this forum which makes me think a few folks might be using it.

Thanks all,




I am using ETW in my company, and I need some way to monitor when an executing process is loading a DLL and what exported functions from the dll it calls and when.

I have made a lot of research in the documentation in the last week and I could provide Systemcall trace, but there is a problem, ProcessId and ThreadId from the EventHeader are invalid ( -1 ).

I saw the xperf can make stack traces for each process, I was interested if I could user ETW to see when a process loads a dll and calls a function from it and I possible trace it to the native call from SSDT or SSDT shaddow like xperf stack trace.

I have used until now NT KERNEL LOGGER session to trace system calls, registry changes, etc....

Is there something more I can do ?

How is xperf showing the call stacks of each process when monitoring ?

Can I see some samples ?

Thank you.


<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure