LINQ to SQL Debug Visualizer

Probably the biggest programming model improvement being made in .NET 3.5 is the work being done to make querying data a first class programming concept.  We call this overall querying programming model "LINQ", which stands for .NET Language Integrated Query.  Developers can use LINQ with any data source, and built-in libraries are included with .NET 3.5 that enable LINQ support against Objects, XML, and Databases.

Earlier this summer I started writing a multi-part blog series that discusses the built-in LINQ to SQL provider in .NET 3.5.  LINQ to SQL is an ORM (object relational mapping) implementation that allows you to model a relational database using .NET classes.  You can then query the database using LINQ, as well as update/insert/delete data from it.  LINQ to SQL fully supports transactions, views, and stored procedures.  It also provides an easy way to integrate data validation and business logic rules into your data model.

You can learn more about LINQ to SQL by reading my posts below (more will be coming soon):

Using the LINQ to SQL Debug Visualizer

One of the nice development features that LINQ to SQL supports is the ability to use a "debug visualizer" to hover over a LINQ expression while in the VS 2008 debugger and inspect the raw SQL that the ORM will ultimately execute at runtime when evaluating the LINQ query expression.

For example, assume we write the below LINQ query expression code against a set of data model classes:

We could then use the VS 2008 debugger to hover over the "products" variable after the query expression has been assigned:

And if we click the small magnifying glass in the expression above, we can launch the LINQ to SQL debug visualizer to inspect the raw SQL that the ORM will execute based on that LINQ query:

If you click the "Execute" button, you can even test out the SQL query and see the raw returned results that will be returned from the database:

This obviously makes it super easy to see precisely what SQL query logic LINQ to SQL ORM is doing for you. 

You can learn even more about how all this works by reading the Part 3: Querying our Database segment in my LINQ to SQL series above.

How to Install the LINQ to SQL Debug Visualizer

The LINQ to SQL Debug Visualizer isn't built-in to VS 2008 - instead it is an add-in that you need to download to use.  You can download a copy of it here.

The download contains both a binary .dll assembly version of the visualizer (within the \bin\debug directory below), as well as all of the source code for the visualizer:

To install the LINQ to SQL debug visualizer, follow the below steps:

1) Shutdown all running versions of Visual Studio 2008

2) Copy the SqlServerQueryVisualizer.dll assembly from the \bin\debug\ directory in the .zip download above into your local \Program Files\Microsoft Visual Studio 9.0\Common7\Packages\Debugger\Visualizers\ directory:

3) Start up Visual Studio 2008 again.  Now when you use the debugger with LINQ to SQL you should be able to hover over LINQ query expressions and inspect their raw SQL (no extra registration is required).

Hope this helps,

Scott

53 Comments

  • Of course it helps. Everything you do helps.

  • I'm loving this VS2008 series as I'm testing the new build this week. Thanks Scott
    Cheers
    Al

  • Hi Scott,
    maybe this is a stupid question, but I've not yet found a "supported databases by linq" page.
    which databases linq support? is MS Access supported for example?
    bye
    Lenny76

  • Hi Scott,
    Do you have a plan for beta 3?If you have,when is it release?Or what is the next edition of vs2008?

  • I am wondering performance issue while using LINQ. Is there any performance issue between using LINQ and without using LINQ?

  • Continue above,why I can't find "JavaScript" item in "tools-options-TextEditor"?

  • I think you might as well make the visualizer a part of the standard install for VS 2008.

    With all the talk about LINQ to SQL I'm not sure I understand this: is there no LINQ to Access, or LINQ to Oracle? Or is that not the way it works?

  • Does this only work in C#? I have it working in C# but in VB.Net the visualiser just shows the results of the query

  • It works great with C# but have anybody an idea why isn't this visulizer doesn't work with VB?

  • Hey Scott,

    How is this implemented?

    Any pointers on how to implement this on my own LINQ provider?

    Thanks again,
    Zombie

  • Is this going to be in the RTM?

  • Is there a way to debug the LINQ code being executing within the LinqDataSource? When I try to duplicate your seemingly simple demos with timestamp fields added, I get various errors. Is there a way see the source code being executed behind the scenes to understand what is causing the errors?

  • Hi Scott,
    There was a DataShape and Include methods in Beta 1 that were used when we don't want to allow deferred loading! what happened to this object/Method in Beta 2? is there an online place to check changes?

    Thanks

  • Another good blog on LINQ to SQl. But can we have a project download we are using the LINQ to SQl in the data acces layer.

    What will be the data type that I will pass back when I am using a LINQ query in a function.(I want to return the value retrieved from the LINQ query back)

    In the same condition above if I want to send only 2 of the 4 column what data type to be used to return the value of the LINQ query?

  • Hey Scott,

    How does the performance of LINQ stand against running a SQL QUery that returns a result set saved to an IList?

    I'm thinking in turns of, if 1,000 of these statements were ran every second, which one would be faster, and use less memory?

    Thanks,

    Ryan.

  • Any reason this is not part of the VS 2008 install? Will it be at some point in the future?

    Thanks.

  • Hi Scott;
    >>- with the next release of VS you should see considerable improvements there.<<

    When you say "Next release", are you referring to Beta 3 or RC or RTM or are you referring to future major upgrades like VS2009 0r VS2010?

    I thought you had mentioned (long time ago) that you're more likely going to do videos than blog. Or hopefully someone else will do some lengthy videos on datacontext, and all the relevant info like locking, transaction, life span of DC, "Events" and so on. Do you know if any thing is cooking for a series of formal Linq videos?
    Thanks!

  • I've now got my data context suppling data to a data grid view in a windows forms project. Can i use it to supply data to a WPF Grid in a WPF project?

  • Hi Zin,

    >>>>>> I am wondering performance issue while using LINQ. Is there any performance issue between using LINQ and without using LINQ?

    LINQ to SQL is very fast. If you are surfacing an object model for your data (meaning creating objects to represent relational data), I think you'll find LINQ to SQL is about as efficient as if you did it completely by hand.

    If you use a compiled LINQ to SQL query, you'll find with beta2 that it is about 93% the performance of iterating over a raw SQLDataReader object: http://blogs.msdn.com/mattwar/archive/2007/07/05/linq-to-sql-rico-drops-the-other-shoe.aspx

    Hope this helps,

    Scott

  • Hi Mike,

    >>>>>>> I think you might as well make the visualizer a part of the standard install for VS 2008.

    We wanted to have the debub visualizer built-in, but unfortunately due to time we won't be able to. Instead we'll continue to make it available as a separate free download with full source code.

    Hope this helps,

    Scott

  • Hi Boris,

    >>>>>> How is this implemented?

    The source code for the debug visualizer can be downloaded above - so you'll be able to see exactly how it is implemented. :-)

    >>>>>>> Any pointers on how to implement this on my own LINQ provider?

    Matt Warren from the LINQ to SQL team has recently completed an outstanding two part series on how to build a custom LINQ provider. You can read about it here:

    http://blogs.msdn.com/mattwar/archive/2007/07/30/linq-building-an-iqueryable-provider-part-i.aspx

    http://blogs.msdn.com/mattwar/archive/2007/07/31/linq-building-an-iqueryable-provider-part-ii.aspx

    Hope this helps,

    Scott

  • Hi Bilal,

    >>>>>> There was a DataShape and Include methods in Beta 1 that were used when we don't want to allow deferred loading! what happened to this object/Method in Beta 2? is there an online place to check changes?

    The DataContext has a LoadOptions property that allows you to specify loading behavior when using the DataContext. Specifically, it allows you to indicate whether to use deferred or eager loading for associations.

    I'll cover how to use this more in a future blog post.

    Thanks,

    Scott

  • Hi Ryan,

    >>>>>>> How does the performance of LINQ stand against running a SQL QUery that returns a result set saved to an IList? I'm thinking in turns of, if 1,000 of these statements were ran every second, which one would be faster, and use less memory?

    LINQ to SQL is very fast. If you are surfacing an object model for your data (meaning creating objects to represent relational data), I think you'll find LINQ to SQL is about as efficient as if you did it completely by hand.

    If you use a compiled LINQ to SQL query, you'll find with beta2 that it is about 93% the performance of iterating over a raw SQLDataReader object: http://blogs.msdn.com/mattwar/archive/2007/07/05/linq-to-sql-rico-drops-the-other-shoe.aspx

    Hope this helps,

    Scott

  • Hi Vikram,

    >>>>>> What will be the data type that I will pass back when I am using a LINQ query in a function.(I want to return the value retrieved from the LINQ query back). In the same condition above if I want to send only 2 of the 4 column what data type to be used to return the value of the LINQ query?

    You can indicate what data type you want to return from a LINQ query expression using the "select" clause of the LINQ query expression statement. For example, you could say:

    IEnumerable products = from p in northwind.Products
    where p.Category.CategoryName == "Beverages"
    select new MyFoo {
    ID = p.ProductID,
    Name = p.ProductName
    };

    This would give you back a sequence of objects of type "MyFoo". You could then pass this back from any method you are in.

    Hope this helps,

    Scott

  • Hi Scott,

    >>>>>>> I've now got my data context suppling data to a data grid view in a windows forms project. Can i use it to supply data to a WPF Grid in a WPF project?

    Yep - you can also databind the results of LINQ expressions to WPF controls. There isn't a built-in datagrid in WPF, but you can use the ListBox control with databinding expressions on this. I have it on my list of things to-do to cover this in a future blog post.

    Hope this helps,

    Scott

  • Hi Ben,

    >>>>>> When you say "Next release", are you referring to Beta 3 or RC or RTM or are you referring to future major upgrades like VS2009 0r VS2010?

    For major refactoring improvements I was thinking the next release of VS (which doesn't currently have a name). Unfortunately we won't be able to get those before RTM of VS 2008.

    >>>>>>> I thought you had mentioned (long time ago) that you're more likely going to do videos than blog. Or hopefully someone else will do some lengthy videos on datacontext, and all the relevant info like locking, transaction, life span of DC, "Events" and so on. Do you know if any thing is cooking for a series of formal Linq videos?

    It is on my list of things todo. :-) In the meantime, I'd also definitely recommend checking out the samples and whitepapers on the C# dev center on MSDN: http://msdn.microsoft.com/en-us/vcsharp/default.aspx as well as the VB dev center on MSDN: http://msdn.microsoft.com/en-us/vbasic/default.aspx

    They both have a lot of great content on LINQ and LINQ to SQL.

    Hope this helps,

    Scott

  • Hi Thomas,

    >>>>>> Is there a way to debug the LINQ code being executing within the LinqDataSource? When I try to duplicate your seemingly simple demos with timestamp fields added, I get various errors. Is there a way see the source code being executed behind the scenes to understand what is causing the errors?

    Unfortunately I haven't found a good way to use the debug visualizer with the LinqDataSource. What I sometimes do to watch update statements is to fire up the SQL profiler and keep it in trace mode in the background. I can then look at the changes there.

    Can you send me an email (scottgu@microsoft.com) about the schema for the table with the timestamp issue? I can then take a look and get it working for you.

    Thanks,

    Scott

  • Hi Ralf,

    >>>>>> It works great with C# but have anybody an idea why isn't this visulizer doesn't work with VB?

    Sorry about that - I just assumed it worked with VB. I have mail out to the VB team to understand why this isn't showing up. We should be able to get a fix.

    Thanks,

    Scott

  • Hi Christian,

    >>>>>>> I tried installing the visualizer on a 64 bit system, without much luck unfortunately.

    I've got some mail out to folks who understand better how debug visualizers should be installed on x64 systems. I'll let you know once I hear back from them.

    Thanks,

    Scott

  • |Hi Ryan,
    |&gt;&gt;&gt;&gt;&gt;&gt;&gt; How does the performance of LINQ stand against running a SQL QUery that returns a |result set saved to an IList? &nbsp;I'm thinking in turns of, if 1,000 of these statements were |ran every second, which one would be faster, and use less memory?
    |LINQ to SQL is very fast. &nbsp;If you are surfacing an object model for your data (meaning |creating objects to represent relational data), I think you'll find LINQ to SQL is about as |efficient as if you did it completely by hand.
    |If you use a compiled LINQ to SQL query, you'll find with beta2 that it is about 93% the |performance of iterating over a raw SQLDataReader object: |blogs.msdn.com/.../linq-to-sql-rico-drops-the-other-shoe.aspx
    Scott, that's pretty amazing. Instead of having 600+ stored procedures (which we have :P ) you could have your DataBase as an actual layer in code, and you could build your data access layer totally out of LINQ, eliminating Stored Procedures and Functions.
    Very interesting, as I doubt people will want to create a solution of 1/2 LINQ and 1/2 Stored Procedures - it would get messy. Time to play :)
    Interesting.

  • Thanks Scott!!

    Regards

  • Hi Mehfuz,

    >>>>>> In addition , i want to ask, the queries generated by LINQ, how can i optimize that more, let say,i want to select some products on basis of a fixed criteria, and i know that Index A has the best coverrge, how can i forcely specify the index, right in LINQ query?Will there be something like the following in future.

    You can actually override the raw SQL that LINQ to SQL uses if you want absolute control over the SQL executed. In general I'd recommend against it unless you absolutely need to, but you do have that option.

    Hope this helps,

    Scott

  • Hi Tony,

    >>>>>>> Can you do a textual parameter replacement in the query, so I can just copy the query to a query tool and run it without modification? In your example I would have to search for @p0 and replace it with 'Beverages' which can be cumbersome with, say, 15 parameters...

    The good news is that this is supported today! Just uncheck the "Original Query" checkbox and it will show you the raw SQL with the paramters encoded and placed in the SQL. This allows you to copy/paste like you are after and run it in any database query tool.

    Hope this helps,

    Scott

  • Hi Geoff,

    >>>>>> Hey Scott, Love the new VS2008 it’s awesome. I have been finding some minor bugs during use but when I try to report them at: connect.microsoft.com/visualstudio It puts me in a infinite loop. After doing the requested login. It tells me I don’t have access.

    Feel free to send all bug reports directly to me (scottgu@microsoft.com). I can then forward them to the appropriate team and put you in touch with them.

    Thanks,

    Scott

  • Scott,

    Can you give us an update on the compatability with vb.net.

    Thanks

    Ken

  • Hi Ken,

    >>>>>>> Can you give us an update on the compatability with vb.net.

    We've reprod the problem here internally, and someone is working on it now. I'll let you know once there is an update or a potential fix.

    Thanks,

    Scott

  • HI Scott,

    Thanks fo9r the explanation. That was very helpful

  • Hi Scott,

    I'm very impressed by LINQ, however one thing bothers me:
    Is it possible that LINQ to SQL would generate it's entity classes (e.g. Product for Products table) in separate files?
    Or even better - in separate project?

    That's how it is e.g. in NHibernate, and it makes sharing those classes across layers in multi-layered application a lot easier. It's also easier to use it with distributed application, web services etc.

    LINQ to SQL is totally totally better than typed datasets, and I'm very happy that MS chose this way. But until all generated classes will be stored simply in one file - it still won't be fully "enterprise" solution :(

    Your reply will be appreciated.

  • I am having difficulty connecting to a remote SQL Server. I have 2 copies of a database, one local and one remote. Using the wizard for a Dataset, I tried in both cases to create a new Stored PRocedure. IN the local case, all went fine, (as it has for years with 2005). However, when I try to do the same on the remote, it fails on the last step with the error msg, "THe specific schema for "woofwoof" either does not exist, or you do not have permission to use it." ("woofwoof" is the username for the database). It seems like a permission problem. I am running it in Vista Business OS. I have an capture of the error, if that helps.
    Otherwise, this looks like a great product!

  • Hi scott. i found a bug in the linq to sql. if the table name has any dot like 'Movie.Base.Studio' the linq to sql will generate the following code: [dbo].[[Movie].[Base].[Studio]]] instead of [dbo].[Movie.Base.Studio] and then return the following error: "Could not find server 'dbo' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers."

  • I could not find a method inside the linqdatasource that could return the results to a datatable or dataset. I found some sites saying about the GetDataTable() method in later linq releases. Do you know how is it called in the current release?

  • Scott,

    What are your thoughts on the whole issue of using SQL Server stored procedures or not? I assume that LINQ appeals to developers who send SQL statements to SQL Server rather than calling stored procedures.

    In your opinion, is it best to do as much in SQL stored procedures as possible, for better performance, scalability, etc, rather than using ASP to construct all the SQL necessary, and thus not needing stored procedures?

    Thanks,

    Hank

  • Scott,

    For those of us used to thinking in T-SQL, and who often take advantage of things like common table expressions and the new row and rank functions in 2005, would it be possible to provide a reverse visualizer/code generator that would generate C# LINQ from T-SQL? Any third parties working on this?

  • Hi PaoloTCS,

    >>>>>> I am having difficulty connecting to a remote SQL Server. I have 2 copies of a database, one local and one remote. Using the wizard for a Dataset, I tried in both cases to create a new Stored PRocedure. IN the local case, all went fine, (as it has for years with 2005). However, when I try to do the same on the remote, it fails on the last step with the error msg, "THe specific schema for "woofwoof" either does not exist, or you do not have permission to use it." ("woofwoof" is the username for the database). It seems like a permission problem. I am running it in Vista Business OS. I have an capture of the error, if that helps.

    If you can send me more emails about this problem, I can try and get someone to help investigate.

    Thanks,

    Scott

  • Hi Charles,

    >>>>>>> Hi scott. i found a bug in the linq to sql. if the table name has any dot like 'Movie.Base.Studio' the linq to sql will generate the following code: [dbo].[[Movie].[Base].[Studio]]] instead of [dbo].[Movie.Base.Studio] and then return the following error: "Could not find server 'dbo' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers."

    I think you sent me email on this issue - if not, would you mind doing so (scottgu@microsoft.com)?

    Thanks,

    Scott

  • Hi Hank,

    >>>>>> What are your thoughts on the whole issue of using SQL Server stored procedures or not? I assume that LINQ appeals to developers who send SQL statements to SQL Server rather than calling stored procedures. In your opinion, is it best to do as much in SQL stored procedures as possible, for better performance, scalability, etc, rather than using ASP to construct all the SQL necessary, and thus not needing stored procedures?

    I'm actually going to be doing a blog post on using SPROCs with LINQ to SQL shortly (probably over the weekend). Stay tuned!

    Scott

  • Hi David,

    >>>>>>> For those of us used to thinking in T-SQL, and who often take advantage of things like common table expressions and the new row and rank functions in 2005, would it be possible to provide a reverse visualizer/code generator that would generate C# LINQ from T-SQL? Any third parties working on this?

    That is a great idea. I'm not aware of someone working on just such a reverse code generator right now - but it definitley sems doable. As part of my upcoming SPROC post, I will also probably be covering how to use common table expressions and UDTs.

    Thanks,

    Scott

  • Hi Vijay,

    >>>>>> My question is about mapping automation and the LINQ to SQL approach. For the most part LINQ to SQL appears to only take the bottom up approach where you start from the database meta-data and work up to (or generate) the object model. What about the other direction? What if I have a robust domain model and I want to map to (or even generate) my data model. Are there any plans to expand the designer to allow for more complete mapping functionality? It would be nice to be able to drag my classes from "Solution Explorer" or "Class View" and have them show up in the dbml designer.

    LINQ to SQL does have the ability to start with a "class first" approach instead of starting with the schema. The modeling support within the LINQ to SQL designer is ok for this (not perfect, but still usable I think). On the toolbox there is a "Class" icon you can add to the designer and use to model your entities there.

    Alternatively, you can define your classes in code first, and then apply either attributes or use an external XML mapping file to define the mapping layer.

    Note that LINQ to SQL's mapping layer typically needs to map to the schema of the underlying database. LINQ to Entities provides you with more of a ability to define a conceptual layer that might be quite different from the underlying schema.

    Hope this helps,

    Scott

  • Hi Herbjörn,

    >>>>>> "Developers can use LINQ with any data source, and built-in libraries are included with .NET 3.5 that enable LINQ support against Objects, XML, and Databases. " "For me, the top 3 things to know about LINQ to Entities would be: 1 It will not ship with Visual Studio 2008 and .NET Framework V3.5. It comes "later". Am I missing out on something here or do You write different things about LINQ? If You write different things, whats the situation really like?

    LINQ to Entities does not ship directly in .NET 3.5 - but LINQ to SQL does. When I said there was built-in support for LINQ to work against a database I was referring to LINQ to SQL support.

    Hope this helps,

    Scott

  • Thursday, August 02, 2007 11:06 AM by ScottGu
    Hi Ken,

    >>>>>>> Can you give us an update on the compatability with vb.net.

    We've reprod the problem here internally, and someone is working on it now. I'll let you know once there is an update or a potential fix.

    Scott--can you provide an update on this. Thanks Ken

  • Can you provide an update regarding the fix for vb.net users.

    Thanks

    Ken

  • Hi Ken,

    >>>>>> Can you provide an update regarding the fix for vb.net users.

    Sorry for the delay on this. It turns out this was a bug in VS 2008 Beta2 with how debug visualizers are loaded. The good news is that this will be fixed for the final release though.

    Sorry for the delay!

    Scott

  • Hi,

    Does the visualizer not work with VB? Yeah I know I need to switch to C but this project is needed for a client in VB.

Comments have been disabled for this content.