Category: Databases

Devart LINQ Insight

Posted onAuthorEmilLeave a comment

I was recently approached by a representative from Devart who asked if I wanted to have a look at some of their products, so I decided to try out the LINQ Insight add-in for Visual Studio.

LINQ Insight has two main functions:

  • Profiler for LINQ expressions
  • Design-time LINQ query analyzer and editor

If you work much with LINQ queries you probably know that Visual Studio is somewhat lacking with functionality around LINQ queries by default so the functions that LINQ Insight offers should be pretty welcome for any database developer out there on the .Net platform (which should be pretty many of us these days). Let’s discuss the two main features of LINQ Insight in some more detail.

Profiling LINQ queries

If you’re using Entity Framework (LINQ Insight apparently also supports NHibernate, RavenDB, and a few others but I have not tested any of those) and LINQ it can be a little difficult to know exactly what database activity occurs during the execution of the applications. After all, the main objective of OR mappers is to abstract away the details of the database and instead let the developer focus on the domain model. But when you’re debugging errors or analyzing performance it’s crucial to analyze the database activity as well, and that’s what LINQ Insight’s profiling function helps with.

There are other tools to this of course, such as IntelliTrace in Visual Studio Ultimate, but since it’s only included in Ultimate, not many developers have access to it. LINQ Insight profiler is very easy to use and gives access to a lot of information.

To enable profiling, follow these steps:

  1. Make sure that IIS Express process is not started. Stop it if it is. (This assumes we’re using IIS Express of course. I’m not quite sure how to work with the full IIS server in conjunction with LINQ Insight.)
  2. Open the profiling window by selecting View/Other Windows/LINQ Profiler, or pressing Ctrl+W, F
  3. Press the “Start profiler session” button in the upper left corner of the window (it looks like a small “Play” icon)
  4. Start debugging your application, for example by pressing F5.
  5. Debugging information such as this should now start to fill the profiler window:
    The profiler displays all LINQ activity in the application.

    The profiler displays all LINQ activity in the application.

    As you can see, in this case we have several different contexts that have executed LINQ queries. For example, ApplicationContext is used by ASP.Net Identity and HistoryContext is used by Code First Database Migrations. Context is our application context.

  6. We can now drill down into the queries and see what triggered them and what SQL statements were executed.
    Drilling down into profiler data.

    Drilling down into profiler data.

    We can see the LINQ query that was executed, the SQL statements, duration, call stack, etc. Very useful stuff indeed.

Query debugger and editor

The other feature LINQ Insight brings into Visual Studio is to help writing LINQ queries and debug them. To debug a query, follow these steps:

  1. To open a query in the query editor, just right-click on it in the standard C# code editor window and select the “Run LINQ Query” option:

    To debug or edit a LINQ query, use the right-click menu.

    To debug or edit a LINQ query, use the right-click menu.

  2. If the query contains one or more parameters, a popup will be shown where values for the parameters can be given.
  3. Next, the query will be executed, and the results will be displayed:

    Query results are displayed in the Results tab.

    Query results are displayed in the Results tab.

  4. This is of course useful in itself, and even better is that the generated Sql statements are displayed in the SQL tab and the original LINQ query is in the LINQ tab, where it can be edited and re-executed, after which the Sql and Results tab are updated. Really, really useful!

If an error is displayed in the Results tab, then the most probably reason is that the database could not be found in the project’s config file, or that it could not be interpreted correctly. The latter is the case if using the LocalDB provider with the "|DataDirecory|" placeholder, which only can be evaluated at runtime in a ASP.Net project. To make LINQ Insight find a database MDF file in App_Data in a web project, you can follow these steps:

  1. Make sure that your DbContext sub-class (for Entity Framework, that is) has an overloaded constructor that takes a single string parameter, namely the connection string to use: 
    public Context(string connString) : base(connString) {}

    This is required if LINQ Insight cannot deduce the connection string for the project’s config file. This is usually a problem in my projects since I like to separate domain logic into a separate project (normally a class library) from my “host application”.

  2. Double-click the MDF file in the App_Data folder to make sure it’s present in the Server Explorer panel in Visual Studio.
  3. Select the database in the Server Explorer and right-click it and select Properties. Copy its Connection String property.
  4. In the LINQ Interactive window, click the Edit Connection String button, which is only enabled if the DbContext class has a constructor overload with a connection string parameter, which we ensured in step 1.
  5. Paste the connection string to the Data/ConnectionString field in the panel:
    Use the connection string dialog to override the "guessed" connection string.

    Use the connection string dialog to override the “guessed” connection string.

    Click OK to close the dialog.

  6. Re-run the query with the Run LINQ Query button in the LINQ Interactive window, and it should now work correctly. If it doesn’t, try to Run LINQ Query command in the C# code editor again, since it re-initializes the query.

The ability to freely set the connection string should make it possible to work against any database, be it a local MDF file, a full SQL Server database or a Windows Azure database. This could be used as a simple way to try out new or modified LINQ queries against a staging or production database, right from the development enviroment. Could be very useful in some situations for debugging nasty errors and such.

Summary

All in all, I think the LINQ Insight is a very useful tool and I recommend you try it out if you find yourself writing LINQ queries from time to time.

I should also mention that if you have tried LINQ Insight before and found it be slightly unstable then I should mention that Devart have recently fixed a few errors that really makes the tool much more robust and useful. If unsure, just download the trial version and test it out.

Happy Linqing!

Emil

Connection strings used for logging should not enlist in transactions

Posted onAuthorEmilLeave a comment

I was just reminded of an old truth when it comes to logging errors in a database: Do not enlist the error logging operation in the current transaction, if there is one! The default is to do just that, so be sure to add Enlist=false in your connection string used for logging:

Example:

  <connectionStrings>
    <add name="myDb" connectionString="..."  providerName="System.Data.SqlClient"/>
    <add name="logging" connectionString="...;Enlist=false"  providerName="System.Data.SqlClient"/>
  </connectionStrings>

If you don’t, then the error logging will be rolled back with the transaction in case of an error. Not so good…

Also note that it’s a good idea to use separate connection strings for the normal database operations (which should be done inside transactions) and the logging operations (that shouldn’t).

Now that I have written this down I will hopefully remember this the next time I do error logging in a database…

/Emil

Extracting stored procedure source text from SQL Server

Posted onAuthorEmil2 Comments

It’s been a really long time since the last post so here’s a small SQL Server tip. I recently had to extract the source code for more than two hundred stored procedures from a SQL Server database. Obviously I didn’t want to do that by hand so I wrote a simple script using sp_helptext:

exec sp_helptext proc1
exec sp_helptext proc2
exec sp_helptext proc3
...

This did indeed extract all the source code for the procs but for some obscure reason I had a newline character after every 255:th character in the result. Close but no cigar…

I did some googling without finding anyone with the identical problem (I had already increased the “Maximum number of characters displayed in each column” setting of SQL Server Management Studio). However, it turns out there’s an alternative to sp_helptext, namely the sys.sql_modules table which can be queried like this:

select definition from sys.sql_modules where object_id = object_id('proc1')
select definition from sys.sql_modules where object_id = object_id('proc2')
select definition from sys.sql_modules where object_id = object_id('proc3')
...

This time there were no irritating newlines in the output. Problem solved!

/Emil

Unique constraints in Sql Server do not allow multiple NULL values

Posted onAuthorEmilLeave a comment

For some obscure reason Sql Server does not allow multiple NULL values on columns with UNIQUE constraints. This is in conflict with ANSI standards and also very contra-intuitive.

If you think this is poorly designed, you’re not alone: http://connect.microsoft.com/SQLServer/feedback/details/299229/change-unique-constraint-to-allow-multiple-null-values

However, there is a way to create a constraint that allows multiple NULL values rather easily (this uses “filtered constraints”, introduced in Sql Server 2008):

CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;

Credits: http://stackoverflow.com/questions/767657/how-do-i-create-unique-constraint-that-also-allows-nulls-in-sql-server

/Emil

Fixing CLR trigger error after installing .Net Framework updates

Posted onAuthorEmil1 Comment

Today I discovered an error in one of our CLR triggers in a SQL Server database:

System.Data.SqlClient.SqlException: A .NET Framework error occurred during execution of user-defined routine or aggregate "PersonalChangedTrigger": 
System.IO.FileLoadException: Could not load file or assembly 'System.Messaging, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.

This was kind of unexpected since we didn’t modify the trigger. It turns out that it was probably a Windows Update patch that modified the System.Messaging assembly so we had to “re-import” it into the database. This is how to do that, in our case:

ALTER ASSEMBLY Messaging
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
WITH PERMISSION_SET = UNSAFE

/Emil

Storing and retrieving strings in the SQL Server session context

Posted onAuthorEmil1 Comment

Sometimes it can be useful to store temporary data (e.g. a string with the current username for auditing or similar) in the context of the current Sql Server session and it’s actually rather easy to do. All that’s needed is to convert the data to a varbinary of max length 128 and then call SET CONTEXT_INFO like this:

-- store data
DECLARE @encodedUsername AS VARBINARY(128)
SET @encodedUsername = convert(varbinary(128),'emila')
SET CONTEXT_INFO @encodedUsername

Unfortunately it’s not quite as easy to retrieve the original string… You can retrieve the varbinary data using CONTEXT_INFO(), but that will only give the varbinary data back:

SELECT CONTEXT_INFO()

0x656D696C610000000000000...

Converting that data back to a varchar seems to work at first but the string contains trailing 0-characters (cf. CHAR(0) ):

-- Convert to a string, looks ok...
SELECT CONVERT(VARCHAR(MAX), CONTEXT_INFO())
emila

-- ... but the length is 128 characters
SELECT LEN(CONVERT(VARCHAR(MAX), CONTEXT_INFO()))
128

Removing those illegal character is surprisingly difficult (e.g. REPLACE() cannot be used) but one possible solution is to do a conversion into an Xml string:

-- convert to xml for 
SELECT CONVERT(VARCHAR(MAX), (SELECT CONVERT(VARCHAR(MAX), CONTEXT_INFO()) AS data FOR XML PATH('')))
<data>emila&#x00;&#x00;&#x00;&#x00;&#x00;&#x00;&#x00;...

Substrings of that Xml string can then be replaced like in any other string and that logic is best put in a user-defined function:

-- Retrieve data from the context and cleanup illegal characters
CREATE FUNCTION GetLoggedInUserName() RETURNS varchar(20)
AS
  BEGIN
    RETURN
      REPLACE(
        REPLACE(
          REPLACE(
            CONVERT(VARCHAR(MAX), (SELECT CONVERT(VARCHAR(MAX), CONTEXT_INFO()) AS data FOR XML PATH('')))
            , '</data><data>', '')
          , '</data>', '')
        , '&#x00;', '')
  END
GO


SELECT dbo.GetLoggedInUserName()
emila

The result is now a string of the correct length and without illegal characters.

This code works as expected, but perhaps there is a more efficient alternative out there? Please leave a comment if you have a suggestion for improvement 🙂

/Emil

Deciding whether an NHibernate object reference is a proxy

Posted onAuthorEmil2 Comments

Have you ever had a need to decide whether an object reference to an NHibernate-mapped object is the real instantiated object or an NHibernate-generated proxy used for lazy loading? Here’s how to do it:

if (entityObject is NHibernate.Proxy.INHibernateProxy)
{
  ...
}

You don’t often need to make a distinction between proxies and the real objects but it may be useful when traversing object structures without triggering lazy-loading.

A related method is NHibernateUtil.IsInitialized which may come in handy in similar use cases as the above. It tells whether a proxy or persistent collection is initialized (i.e. loaded from the database) or not.

if (NHibernateUtil.IsInitialized(entityObject))
{
  ...
}

/Emil

Templates for SQL Server Management Studio

Posted onAuthorEmilLeave a comment

Templates for SQL Server Management Studio 2008 are stored in this folder on WIndows XP:

C:\Documents and Settings\[user]\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql

On my new WIndows 7 machine, the location is:

C:\Users\[user]\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql

Create sub-folders and SQL files there and they show up in the Template Explorer. Really useful…

/Emil

Best practice for NHibernate: Don’t store references to ISession-instances

Posted onAuthorEmil2 Comments

Do you see any (or all) of these exceptions (NHibernate.ADOException) in you NHibernate-based web application?

could not execute query
failed to lazily initialize a collection, no session or session was closed
Transaction not successfully started
The server failed to resume the transaction. Desc:86000002dc.

If you do, it might be a good idea to make sure you don’t store any ISession-references anywhere. If you do there’s a great risk that something goes wrong if two threads (i.e. two requests) are using the session at the same time as the NHibernate session implementation is not thread safe.

Instead, do this when an ISession is needed:

ISession session = SessionFactory.Factory.GetCurrentSession();

That code will get the session from the current context (which of course must be updated by your code, look here for more info).

/Emil

Distinct results sets with ICriteria in NHibernate

Posted onAuthorEmilLeave a comment

If you’re using ICriteria to write queries in NHibernate and you’re having citeria on items in a one-to-many relation (e.g. retrieve all departments with at least one employee named “John”) then you’re likely to have experienced that too many objects are returned (at least more than expected). In the example, all departments with multiple “Johns” will be returned once per matching employee. This is probably not what’s expected, so here’s how to fix it:

// The 'criteria' variable contains criteria on Department

// Create alias to use for employee conditions
ICriteria aliasCriteria = criteria.CreateAlias("Employees", "pr");

// Add criteria on the alias...

// We only want distinct root objects returned
criteria.SetResultTransformer(Transformers.DistinctRootEntity);

The trick is to use Transformers.DistinctRootEntity (at least in NHibernate 2.1). In earlier versions you may want to try something like this:

criteria.SetResultTransformer(new DistinctRootEntityResultTransformer());

Related to this is also how to write criteria that counts the results without actually retrieving them, which can be very useful for paging the result set (i.e. only read a subset of the results at a time). Normally you would write something like this:

criteria.SetProjection(Projections.Count("Id"));
int numberOfItems = (int)criteria.UniqueResult();

However, this suffers from the same problem as described above. To get the correct result, do this:

criteria.SetProjection(Projections.CountDistinct("Id"));
int numberOfItems = (int)criteria.UniqueResult();

Problem solved!

Credits to my colleague Jon for helping out with these rather obscure problem solutions!

/Emil