Creating LINQ to SQL Object Models using SqlMetal

By: Edwin Sarmiento | Comments (4) | Related: 1 | 2 | 3 | 4 | 5 | > Language Integrated Query LINQ

Problem

In a previous tip on Querying SQL Server databases using LINQ to SQL, you have seen how we can model our SQL Server databases using LINQ with Visual Studio 2008. I have a database with a lot of tables and stored procedures, so dragging-and-dropping them on the Visual Studio design surface would take some time. Is there a faster way to create LINQ to SQL object models?

Solution

While Visual Studio provides a way for developers to be more productive, dealing with a lot of SQL Server database objects with LINQ is totally inefficient. The dragging-and-dropping process to create the object model will take a lot of time, depending on the amount of objects.

As I was going thru the April 2008 edition of the SQL Server Magazine, I've learned about this utility called SqlMetal. SqlMetal.exe is a command-line tool that generates code and mapping for the LINQ to SQL component of the .NET Framework. When I started working with LINQ, I was only using Visual Studio 2008 to generate the object-relational diagram by (you guessed it) dragging-and dropping the database objects on my designer surface. You can find this tool inside the \Program Files\Microsoft SDKs\Windows\v6.0a\bin folder. It generates the necessary data entity and DataContext object for LINQ as either a .vb or .cs source file. You can run this tool by navigating to the folder location using command prompt and execute from there.

A sample code to use SqlMetal.exe is shown below

sqlmetal /server:localhost /database:Northwind /language:cs /code:Northwind.cs

The command above generates a .cs file (used for C# as the default language for the generated code is in VB.NET) from the Northwind database running on my SQL Server 2000 instance that represents the database object model. This can then be imported as code in your Visual Studio project. Just remember the file name and where the file is stored when importing it in your Visual Studio project or you can specify the path where you want to store the files as they are generated by the tool. In this case, it's in the same location as SqlMetal. In case you want both code and the object diagram, simply replace the /code switch with the /dbml switch. The /dbml switch will generate the dbml file which will then generate the corresponding source code when imported in your Visual Studio project. You cannot specify both /dbml and /code switches at the same time.

sqlmetal /server:localhost /database:Northwind /language:cs /dbml:Northwind.dbml

When you open either the dbml file or the code in Visual Studio 2008, you will see things which may seem familiar if you have used the designer to create LINQ objects. The dbml file and the code includes all the database entities and their corresponding attributes - the tables and their fields with constraints and data types - as well as their relationships.

If you need to include stored procedures and functions in your code, you can simply add the /sprocs and /functions switches. The available switches for SqlMetal are available on the MSDN website.

LINQ to SQL Server Compact is unsupported?

You probably have heard about SQL Server Compact and how you can use it as an embedded database engine for Windows Desktop and mobile device applications. Unfortunately, Visual Studio does not support the data provider for SQL Server Compact. When you create a SQL Server Compact database in you Visual Studio project and add a LINQ to SQL class that references the database, you will get an error similar to the one below.

While you can't use the designer surface to generate the object model for SQL Server Compact databases, SqlMetal gives you that option. You can generate the dbml file for your SQL Server Compact database for import in you project as described in this MSDN article.

A sample code is shown below

SqlMetal /dbml:northwind.dbml northwind.sdf

Remember to specify the complete path of your SQL Server Compact database file when using SqlMetal as this is not the same when connecting to a full-blown SQL Server instance.

Next Steps

Generating code or dbml file using SqlMetal takes only a few seconds on my local SQL Server instance, as compared to dragging-and-dropping all of the objects on the designer surface in Visual Studio. This is a great time saver especially if you have to deal with a lot of objects in your SQL Server database. What's more, you can use it with a combination of T-SQL scripting with VBScript/PowerShell if you need to generate code for several databases across SQL Server instances.

Try using SqlMetal to generate LINQ to SQL object models for your databases
Read these other tips related

About the author

Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, February 26, 2013 - 9:24:26 AM - asava samuel

Edwin Sarmiento

It looks like Kellerman Software has a MySQL LINQ Provider:
https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx

Tuesday, January 18, 2011 - 7:22:42 PM - Piyush Varma

Hi,

I have generated Northwind.dbml and Northwind.vb using SqlMetal commands and added them to my vb project.

I have the declarations in te VS2010 project.

But I can't use it in a module as it reports an error.

The code is below:

    Sub ShowOrdersData()
 
        Dim db As Northwind
 
        Dim custQuery = From cust In db.Customers
Where cust.City = "London" _
Select cust
 
    End Sub

The error is on Dim custQuery = From cust in db.Customers

System.NullReferenceException was unhandled
Message=Object reference not set to an instance of an object.
Source=TestLinq2Sql
StackTrace:
       at TestLinq2Sql.ViewNortwindData.ShowOrdersData() in C:\Data\Visual Studio 2010\Projects\TestLinq2Sql\TestLinq2Sql\ViewNortwindData.vb:line 8
       at TestLinq2Sql.Module1.Main() in C:\Data\Visual Studio 2010\Projects\TestLinq2Sql\TestLinq2Sql\Module1.vb:line 4
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
InnerException:

I do get intelliesense for db.

What is missing or incorrectly defined?

Thursday, August 27, 2009 - 8:01:41 AM - bass_player	Back To Top (3969)
Linq to Entities would be great if you already have an application that interacts with the database thru an object model. That is actually the best practice when working with applications that interact with data - create object models that would represent the data access layer. The tip is just another option to access the database directly using LINQ

Thursday, August 27, 2009 - 2:09:49 AM - ALZDBA

I forwareded your nice article to my dev collegues and immediately got the reply they use the prefered LinQ to Entities http://msdn.microsoft.com/en-us/library/bb386964.aspx

Do you have an advise on this ?

Johan