I had a need to execute a SQL statement using LINQ to SQL. Its a long story, but I couldn't use the LINQ to SQL Designer because I was calling a SQL Server 2005 System View. So, I just decided to use the DataContext.ExecuteQuery method. It was a welcome surprise when the intellisense tooltip filled the screen for the first parameter "elementType" -

Actually, the tooltip really helped. It explains the rules and priority of how the return resultset is mapped to the type you specify. To paraphrase, even though my type doesn't have LINQ to SQL attributes on each property, the system will still attempt to match properties to result columns using a variety of methods.

1. If a field or property is matched to a specific column name, that column is expected in the result set
2. If the field or property is not matched, a column is expected with the same name in the result set (first Case Sensitive search, then case in-sensitive search)

It goes onto specify the rules about change tracking, primary keys, etc.

A lot to read, but definitely saved me a having to hunt around for the necessary information. Nice work to whoever spent the extra time going to this detail; it shows they really thought about what someone would need when they used this method for the first time.

It could have been "elementType: The element type." if i'd been writing it :-)

Troy.

Finding what dependencies a Stored Procedure has on underlying tables, views, functions, etc is often necessary when trying to assess the impact of a change. SQL Server has built-in functions that will indicate in most cases a dependency for any object in the database. The system view "sys.sql_dependencies" is viewed with skepticism by some people who have obviously been bitten in the past.

In order to see for myself the results, I wrote a simple helper class, and thought i'd share the boilerplate code to start you off here (I may clean it up and share it as a library, email me if you have difficulty getting it running). Its a rough prototype, but it is returning good results for my purposes.

Note: This code requires Visual Studio 2008. It uses LINQ to SQL in a very loose way due to the LINQ to SQL Designer not listing the System Views and Functions. Its a good example of just how flexible LINQ to SQL is though.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Configuration;

namespace DatabaseDependencyCrawler
{
    public class SysDependsResult
    {
        public int referenced_major_id { get; set; }
    }

    public class ObjectInfoResult
    {
        public int id { get; set; }
        public string name { get; set; }
        public string xtype  { get; set; }
        public DateTime crdate { get; set; }
    }

    public class DatabaseDependencyCrawler
    {
        public List<DatabaseDependencyEntity> GetDBObjectDependencies(string connectionString, string name)
        {
            // the system views built-into SQL Server 2005
            string dependsQuery = "select referenced_major_id from sys.sql_dependencies where object_id = object_id('{0}')";
            string objectInfoQuery = "select * from sys.sysobjects where id in ( {0} )";

            List<DatabaseDependencyEntity> result = new List<DatabaseDependencyEntity>();

            // find the list of dependencies based upon a database object's name
            DataContext context = new DataContext(connectionString);

            var dependencies = (IEnumerable<dependencies>)context.ExecuteQuery(
                typeof(SysDependsResult),
                string.Format(dependsQuery, name), 
                new object[] { });

            // build a list of object_is's to we can ask for their name in a second query
            StringBuilder ids = new StringBuilder();
            foreach (SysDependsResult d in dependencies)
            {
                if (ids.Length > 0)
                     ids.Append(",");

                ids.Append(d.referenced_major_id);
            }

            // if any records were found...lookup the names of those id's comma separated
            if (ids.Length > 0)
            {

                IEnumerable<ObjectInfoResult> objects = (IEnumerable<ObjectInfoResult>)context.ExecuteQuery(typeof(ObjectInfoResult),
                    string.Format(objectInfoQuery, ids.ToString()),
                    new object[] { });

                foreach (ObjectInfoResult o in objects)
                {
                    result.Add (
                        new DatabaseDependencyEntity {
                            DatabaseConnectionString = connectionString,
                            SourceObject = name,
                            Dependent = o
                        });
                }
            }
        
            return result;
        }
    }
}

Consider the scenario: V1.0 is the current application which needs to continue to run without change, and V2.0 is the new application which will alter the existing stored procedure that both V1.0 and V2.0 will be using when released. Both applications use LINQ to SQL to access the stored procedure code.

What Stored Procedure signature changes will cause V1.0 to fail?

Add Parameter – Optional  (OK)

Non-breaking change for V1.0. Since this parameter is not passed in from V1.0, the stored procedure will use the default specified in the stored procedure parameter declaration.

Add Parameter – Mandatory (Breaking)

Breaking change for V1.0 with an exception message “'stored procedure name' expects parameter '@param', which was not supplied.” The workaround is to make this parameter optional by adding a default value to the parameter declaration.

Delete Parameter – Optional (Mostly breaking)

Breaking change for V1.0, except in the case where a Stored Procedure definition for LINQ to SQL has been manually coded to exclude the parameter being deleted (in the V1.0 codebase). In general, all parameters are declared in the LINQ to SQL C#, or VB code to call a Stored Procedure. However, it is possible to declare your own method definition that chooses to exclude one or more optional parameters.

Delete Parameter – Mandatory (Breaking)

Breaking change for V1.0. No workaround. The parameter can be ignored in the new implementation of the stored procedure, but the declaration needs to remain in place until all versions who pass that parameter are deprecated.

Increase Parameter Size/Scale (OK)

Non-breaking change for V1.0. If V1.0 encountered errors due to the parameter size being too small, these will be corrected by increasing the parameter size.

Decrease Parameter Size/Scale (Mostly OK)

The parameter data is truncated if it exceeds the size of a declared parameter. This will not cause an error during Stored Procedure execution, but it might cause a functional error in the application. A change of this type should be carefully analyzed.

Add Default to Existing Parameter (OK)

Non-breaking change for V1.0.

Remove Default from Existing Parameter (Mostly OK)

Non-Breaking change for V1.0, except in the case where a manual Stored Procedure declaration has been made omitting an optional parameter. All LINQ to SQL definitions for the Stored Procedure and callers should be examined to ensure they pass the parameter dropping the default.

Reorder Parameters (OK)

Non-breaking change for V1.0. Parameters are accessed via their names. However, other applications might set parameters by index position, and these will fail.

Rename Parameter (Breaking)

Breaking change for V1.0. A renamed parameter is seen as a deletion of the current parameter and the addition of a new parameter.

Troy.

I was fortunate enough to attend Code Camp in Seattle last weekend. It was great to see the event attended by around 300 keen developers.

I took notes throughout some sessions, and posted full details the HookedOnLINQ.com website.

• Entity Framework Notes from CodeCamp Seattle
• Parallel LINQ CodeCamp Seattle Notes
• Rethinking Unit Testing Codecamp Seattle Notes
• SilverLight Notes from CodeCamp Seattle

By far, the Parallel LINQ session was a standout. I was surprised just how cleanly the ParallelFx team have committed to making multiple processor development mainstream. This is a library and feature set that is important to watch.

Troy.

I've been fielding lots of LINQ related questions recently through comments and emails from the HookedOnLINQ site. I decided to document my responses over the last few months and post the answers. I'd be really interested in feedback and the questions you find cropping up around the LINQ.

LINQ to Objects FAQ

LINQ to SQL FAQ

LINQ to XML FAQ

Troy.

I got this email today posted from the HookedOnLINQ Wiki I host:

Troy -- 

	I am a new developer to the LINQ framework and had a few questions that I hope you can field.  
	

	Looking at a broad overview of LINQ, I see it as a way to simplify development, but it also raises a few other questions.   As most devs, I've been using the N-Tier architecture for having my applications access the data stores, requesting stored procs to do the processing, and returning the information that I'm interested in.  
	

	Is LINQ providing a way to eliminate the uses of stored procs, or is it just providing a more intelligent way to access the data?  
	

	For instance, in the past the way I have handled data access would be to have a separate class that shadows my stored procedures in my database.  When ever I would make a stored procedure, I would then have to go into my database interface class and create a subroutine with matching signatures to the stored procedure.  It would provide error checking, execute the data access object, then return a dataset for further processing.  From there it would loop through the dataset and display information as required.  
	

	When I think LINQ, the first thing that pops into my head is, will this eliminate that second layer?  Does LINQ provide a way to directly access the database without having to go through the process of constantly of creating a procedure, changing my code, and redeploying. 
	

	I've also had it hammered into me in the past that Inline SQL from within my code is a bad idea due to maintenance concerns.  
	

	As you can see, I am a bit confused.  I went over the 5 minute prep on your website, and understand manipulating objects with LINQ, but I'm not sure I get the whole picture. 
	

	Appreciate it, 
	

	G... 
	

All very good questions. I tried to answer some of them, but I must confess - I'm really interested to see what LINQ's impact is over the next few years.

	Hi G..., 
	

	I think the full LINQ story and what code it will replace is unfolding. Although I'll take a shot at answering some of your excellent questions: 
	

	1. LINQ to SQL can be used in a way that eliminates the need (in the vast majority of cases) for writing and maintaining Stored Procedures. You can write LINQ queries, update the returned object structure and apply changes back to the database without stored procs. 
	

	2. For LINQ to SQL to operate it needs to map its data entity classes to your database. You essentially have Three options, 
	

	    a) markup your own classes using LINQ .NET attributes ie. [DataColumn] 
	

	    b) use the DBML designer and drag tables from the Server Explorer in Visual Studio 
	

	    c) use an XML mapping file to link database objects to classes 
	

	    You do need to keep these in synch when you database structure changes. This will be interesting to see how teams solve this problem. 
	

	3. Inline SQL is bad from a "SQL injection" perspective (where malformed input into a text box could cause SQL you didn't really plan for being executed on your server). The mitigation for this is to pass data to the database via SQL parameters. LINQ does this (as do stored procedures, which is why they were the traditional workaround" 
	

	4. I think with LINQ, you are maintaining C# code which is type safe and checked during compilation. Inline SQL is just a string and you will only see it fail when it is executed (hopefully on a test box before it reaches production!). The jury is still out as to what maintaining over time a large amount of C# LINQ to SQL queries will be like - But until then, enjoy the intellisense, type checking and working in one coding language instead of two! 
	

	Hope this helps a little. Feel free to ask for clarifications, 
	

	Troy Magennis 
	

Did I answer G....'s questions?

Troy.