How to easily access the results of a LINQ-to-SQL query

I have introduced several developers to LINQ-to-SQL and each has been very impressed, immediately followed by the same question. I'll share the question and the answer here.

I show a standard LINQ-to-SQL query and assign the results to a DataGridView as the Datasource. This is consistent with most introductory examples. Think of it as the LINQ-to-SQL version of Hello World.

The next question is inevitably some variation of "OK, now how do I use the data that LINQ returned?".

The answer is simply to convert it to a Generic List with the LINQ "ToList" extension method.

A fully functional code sample will help make it clear.

Dim dbConn as string = "Server=(local);Database=AdventureWorks;" & _
"Trusted_Connection=yes;"

Dim dbx As New System.Data.Linq.DataContext(dbConn)

Dim Promo1 = _
From emp In dbx.GetTable(Of HumanResources_Employee)() _
Where emp.Gender = "M" _
Order By emp.Person_Contact.LastName, emp.Person_Contact.FirstName _
Select emp


Me.DataGridView1.DataSource = Promo1


'Promo1 is of type DatabaseQuery1
Dim PromoList = Promo1.ToList


MessageBox.Show(String.Format("Third Person's Name is {0}, {1}", _
PromoList(2).Person_Contact.LastName, _
PromoList(2).Person_Contact.FirstName))

Hope that helps.

Joe Kunk
Okemos, MI

Simple Batch File to Generate a Full Database Schema using SQLMetal for LINQ to SQL

Applications that use LINQ-to-SQL need to instantiate a System.Data.Linq.DataContext for the database that they want to use. The utility SQLMetal is provided to generate the schema classes needed to instantiate the DataContext. I created the following batch file to easily generate a complete Visual Basic or C# schema after database updates.

I find that generating the schema utilizing SQLMetal is a great error check on the logical consistency of the database Programmabilty objects. The warning messages generated during the process often identify previously unknown errors in stored procedures, user defined functions, views, etc.

The example assumes SQL authentication using the 'sa' username and the (local) server, and a SQL2005 database.

The batch file has three parameters.
Parameter 1 is the Database name.
Parameter 2 is the Password for the 'sa' server login.
Parameter 3 is the language file extension for the output. Values are 'vb' or 'cs'.

The batch file is named DBSchema.bat and is placed in the default folder opened with the Visual Studio 2008 Command Prompt, C:\Program Files\Microsoft Visual Studio 9.0\VC\ to make it easy to access. You can create the batch file by copying the following code into Notepad and saving it in the folder listed above as DBSchema.bat. Use the command "Type DBSchema.bat" to review the needed parameters before using the batch file.

Example usage:
DBSchema AdventureWorks password vb

The resulting .vb file will reside in the C:\Program Files\Microsoft Visual Studio 9.0\VC\ folder and can be copied as needed for use in your .Net projects.

REM Requires Parameter 1: Database name
REM Requires Parameter 2: Password
REM Requires Parameter 3: Language file extension

SqlMetal /server:(local) /database:%1 /user:sa /password:%2 /namespace:%1Schema /functions /sprocs /views /code:%1Schema.%3 /Provider:SQL2005

The batch file requires that the SqlMetal command be on a single line. But for readability purposes, it is repeated here in multiple lines:

SqlMetal /server:(local) /database:%1
/user:sa /password:%2 /namespace:%1Schema
/functions /sprocs /views
/code:%1Schema.%3 /Provider:SQL2005

Hope that helps,

Joe Kunk
Okemos, MI

Speaking at CodeStock 2008

I will be giving the presentation "LINQ to SQL - CRUD!" at CodeStock 2008 in Knoxsville, TN on Saturday August 10th. This talk is an overview of LINQ to SQL designed for those that are just beginning with the technology and want to see easy-to-implement examples for Creating, Reading, Updating and Deleting data from a SQL Server database.

LINQ query to pull a single record from a collection still requires enumercation over results

I wrote a LINQ query in Visual Basic .Net 2008 in order to pull a single record from a Generic List collection. The intent was to use LINQ as an easier alternative to writing a Predicate function for the .Find method of the collection. The code is below. I made what I believe will be a common LINQ mistake. Below is the explanation of the errors I saw, the code that produced those errors, and then the proper way to do it.

A symptom of the problem is that the LINQ query returned a SelectIterator object and in order to see the string I had to enumerate (For Each) over the result in order to obtain the data. I knew that the WHERE condition was sufficient to return only a single result, but LINQ did not so it returned a SelectIterator to be enable me to enumerate through the result set.

If I tried to assign the LINQ result directly to a string, I get the error message "Conversion from type 'd__d(Of MyDataType,String)' to type 'String' is not valid.".

The incorrect code is:

Dim Result as string = string.empty
Dim Filename = From item In myCollection _
Where item.key.ToLower = MenuPath.ToLower _
Select item.MyFilename
For Each s As String In Filename : Result = s : Next
Console.WriteLine("Returned String : " + s)

The correct code is achieved by putting parenthesis around the LINQ query and using the .Single method to inform LINQ that we are expecting a single-value result. The corrected code is :

Dim Result as string = string.empty
Dim Filename = (From item In myCollection _
Where item.key.ToLower = MenuPath.ToLower _
Select item.MyFilename).Single
Console.WriteLine("Returned String : " + Filename)

A special thank you to Bill Wagner of SRT Solutions for his solution to the error in the first listing when I initially blogged it.

Hope that helps.

Joe Kunk
Okemos, MI