LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Over the last few weeks I've been writing a series of blog posts that cover LINQ to SQL.  LINQ to SQL is a built-in O/RM (object relational mapper) that ships in the .NET Framework 3.5 release, and which enables you to model relational databases using .NET classes.  You can use LINQ expressions to query the database with them, as well as update/insert/delete data.

Below are the first six parts in this series:

In part 6 I demonstrated how you can optionally use database stored procedures (SPROCs) and user defined functions (UDFs) to query and retrieve data using your LINQ to SQL data model. In today's blog post I'm going to discuss how you can also optionally use SPROCs to update/insert/delete data from the database.

To help illustrate this - let's start from scratch and build-up a data access layer for the Northwind sample database:

Step 1: Creating our Data Access Layer (without using SPROCs yet)

In my Part 2: Defining our Data Model Classes tutorial I discussed how to use the LINQ to SQL ORM designer that is built-in to VS 2008 to create a LINQ to SQL class model like below:

Adding Validation Rules to our Data Model Classes

After defining our data model classes and relationships we'll want to add some business logic validation to our data model.  We can do this by adding partial classes to our project that add validation rules to our data model classes (I cover how to-do this in-depth in my Part 4: Updating our Database LINQ to SQL tutorial).

For example, we could add validation rules to enforce that the Customer's phone number follows a valid phone pattern, and that we don't add Orders where the customer's RequiredDate for delivery is before the actual OrderDate of the Order.  Once defined in partial classes like below, these validation methods will automatically be enforced anytime we write code to update our data model objects in an application.

VB:

C#:

 

Adding a GetCustomer() Helper Method to our DataContext

Now that we have our data model classes created, and have applied validation rules to them, we can query and interact with the data.  We can do this by writing LINQ expressions against our data model classes to query the database and populate them (I cover how to-do this in my Part 3: Querying our Database LINQ to SQL tutorial).  Alternatively we could map SPROCs to our DataContext and use them to populate the data model classes (I cover how to-do this in my Part 6: Retrieving Data using Stored Procedures LINQ to SQL tutorial).

When building a LINQ to SQL data layer you'll usually want to encapsulate common LINQ queries (or SPROC invocations) into helper methods that you add to your DataContext class.  We can do this by adding a partial class to our project.  For example, we could add a helper method called "GetCustomer()" that enables us to lookup and retrieve a Customer object from the database based on their CustomerID value:

VB:

C#:

 

Step 2: Using our Data Access Layer (still without SPROCs)

We now have a data access layer that encapsulates our data model, integrates business validation rules, and enables us to query, update, insert, and delete the data.

Let's look at a simple scenario using it where we retrieve an existing customer object, update the customer's ContactName and Phone Number, and then create a new Order object and associate it with them.  We can write the below code to do all of this within a single transaction.  LINQ to SQL will ensure that our business logic validation rules are clean before saving anything in the database:

VB:

C#:

LINQ to SQL monitors the modifications we make to the objects we retrieve from the DataContext, and keeps track of all of the objects we add into it.  When we call DataContext.SubmitChanges() at the end, LINQ to SQL will check that our business logic rules are valid, and if so automatically generate the appropriate dynamic SQL to update our Customer record above, and insert a new record into the Orders table. 

Hang on a second - I thought this post was about using SPROCs??? 

If you are still reading this, you might be feeling confused about where SPROCs fit into this post.  Why did I show you above how to write code that works with our data model objects, and then causes dynamic SQL to run?  Why haven't I showed you how to call a SPROC for doing inserts/updates/deletes instead yet? 

The reason is that the programming model in LINQ to SQL for working with data model objects backed by SPROCs is the same as those updated via dynamic SQL.  The way you add data model validation logic is exactly the same (so all the validation rules on our data model classes above still apply when we use SPROCs).  The code snippet above where we use our data access layer to retrieve a customer, update it, and then add a new order associated with it is also exactly the same regardless of whether we are using dynamic SQL for updates, or whether we have configured our data model classes to use SPROCs instead.

This programming model symmetry is powerful both in that you don't have to learn two ways of doing things, and also because it means that you don't have to decide up front at the beginning of your project whether you are going to use SPROCs or not.  You can start off using the dynamic SQL support provided by the LINQ to SQL ORM for all queries, inserts, updates and deletes.  You can then add your business and validation rules to your model.  And then later you can optionally update your data mapping model to use SPROCs - or not if you decide you don't want to.   The code and tests you write against your data model classes can stay the same regardless of whether you use dynamic SQL or SPROCs.

We'll now spend the rest of this blog post demonstrating how we can update the data model we've built to use SPROCs for updates/inserts/deletes - while still using the same validation rules, and working with the same code snippets above.

How to Use SPROCs for Insert/Update/Delete Scenarios

We can modify the data access layer we've been building to use SPROCs to handle updates, instead of dynamic SQL, in one of two ways:

1) By using the LINQ to SQL designer to graphically configure SPROCs to execute in response to Insert/Update/Delete operations on our data model classes.

or:

2) By adding a NorthwindDataContext partial class in our project, and then by implementing the appropriate Insert/Update/Delete partial methods provided on it (for example: InsertOrder, UpdateOrder, DeleteOrder) that will be called when we insert/update/delete data model objects.  These partial methods will be passed the data model instances we want to update, and we can then execute whatever SPROC or SQL code we want to save it within the database. 

When we use approach #1 (the LINQ to SQL designer) to graphically configure SPROCs to call, it is under the covers generating the same code (in a partial class it creates) that you'd write when using approach #2.  In general I'd recommend using the LINQ to SQL designer to configure the SPROCs for the 90% case - and then in more advanced scenarios go in and custom tweak the SPROC invocation code it generates if you need to. 

Step 3: Doing Order Inserts with a SPROC

We'll begin switching our data model to use SPROCs by starting with the Order object. 

We'll first go to the Visual Studio "Server Explorer" window, expand into the "Stored Procedures" node of our database, and then right-click and choose "Add New Stored Procedure":

We'll then create a new SPROC that we'll call "InsertOrder" that inserts a new order record into our Orders table:

Notice above how the SPROC defines the "OrderID" parameter as an output param.  This is because the OrderID column in the database is an identity column that is set to auto-increment each time a new record is added.  The caller of the SPROC will pass in NULL as a value when calling it - and the SPROC then passes back the newly created OrderID value as the output value (by calling the SCOPE_IDENTITY() function at the end of the SPROC).

After creating the SPROC we'll then open up the LINQ to SQL ORM designer for our data access layer.  Like I discussed in my last blog post in this series (Part 6: Retrieving Data Using Stored Procedures), we can drag/drop SPROCs from the server-explorer onto the method pane of our DataContext designer.  We'll want to-do this with our newly created InsertOrder SPROC:

Our last step will be to tell our data access layer to use the InsertOrder SPROC when inserting new Order objects into the database.  We can do that by selecting the "Order" class in the LINQ to SQL ORM designer, and then by going to the property grid and clicking the "..." button to override how Insert operations happen for it:

Clicking the "..." button will bring up a dialog that allows us to customize how insert operations happen:

Notice above how the default mode ("Use Runtime") is to have LINQ to SQL calculate and execute dynamic SQL to handle the insert operations.  We can change that by selecting the "Customize" radio button and then pick our InsertOrder SPROC from the list of available SPROCs:

The LINQ to SQL designer will populate a parameter list for the SPROC we picked, and enable us to map properties on our Order class to parameters of our InsertOrder SPROC.  By default it is smart and tries to "best match" them based on name.  You can go in and override them if you want.

Once we click "ok" on the dialog, we are done.  Now whenever a new Order is added to our DataContext and the SubmitChanges() method is invoked, our InsertOrder SPROC will be used instead of executing dynamic SQL. 

Important: Even though we are now using a SPROC for persistence, the custom Order "OnValidate()" partial method we created earlier (in step 1 of this blog post) to encapsulate Order validation rules still executes before any changes are saved or the SPROC is invoked.  This means we have a clean way to encapsulate business and validation rules in our data models, and can re-use them regardless of whether dynamic SQL or SPROCs are used.

Step 4: Doing Customer Updates with a SPROC

Now let's modify our Customer object to handle updates using a SPROC.

We'll start by creating a new "UpdateCustomer" SPROC like below:

 

Notice above how in addition to passing in the @CustomerID parameter, we are also passing in a @Original_CustomerID parameter.  The CustomerID column in the Customers table is not an auto-increment identity column, and it can be modified as part of an update of the Customer object.  Consequently we need to be able to provide the SPROC with both the original CustomerID and the new CustomerID in order to update the record.  We'll look at how we map this using the LINQ to SQL designer shortly.

You'll notice above how I'm also passing in a @Version parameter (which is a timestamp) to the SPROC.  This is a new column I've added to the Northwind Customers table to help handle optimistic concurrency.  I will cover optimistic concurrency in much more depth in a later blog post in this LINQ to SQL series - but the short summary is that LINQ to SQL fully supports optimistic concurrency, and enables you to use either a version timestamp or to supply both original/new values to your SPROCs to detect if changes have been made by another user since you last refreshed your data objects.  For this sample I'm using a timestamp since it makes the code much cleaner.

Once we've created our SPROC, we can drag/drop it onto the LINQ to SQL designer to add it as a method on our DataContext.  We can then select the Customer class in the ORM designer and click the "..." button to override the Customer object's Update behavior in the property grid:

We'll select the "Customize" radio button and pick our UpdateCustomer SPROC to use:

When mapping our Customer object's properties to the SPROC parameters, you'll notice that we'll want to be deliberate about whether we supply the "current" property value on the data object, or the original value that was in the database when the object was first retrieved.  For example, we'll want to make sure we map the "current" value of the Customer.CustomerID property for the @CustomerID SPROC parameter, and that we map the original value for the @original_customerID SPROC parameter.

Once we click "ok" on the dialog, we are done.  Now whenever a new Customer is updated and the SubmitChanges() method is invoked, our UpdateCustomer SPROC will be used instead of executing dynamic SQL. 

Important: Even though we are now using a SPROC for persistence, the Customer "OnPhoneChanging()" partial method we created earlier (in step 1 of this blog post) to encapsulate Phone Number validation rules still executes before any changes are saved or the SPROC is invoked.  We have a clean way to encapsulate business and validation rules in our data models, and can re-use them regardless of whether dynamic SQL or SPROCs are used.

Step 5: Using our Data Access Layer Again (this time with SPROCs)

Once we've updated our data layer to use SPROCs instead of dynamic SQL for persistence, we can re-run the exact same code we wrote in Step 2 earlier against our data model classes:

Now the updates for the Customer object, and the insert for the Order object, are executing via SPROCs instead of dynamic SQL.  The validation logic we defined still executes just like before, though, and the data access code we write to use the data model classes is exactly the same.

Some Advanced Notes When Using SPROCs

A few quick notes that you might find useful for more advanced SPROC scenarios with LINQ to SQL:

Usage of SPROC Output Params:

In the Insert scenario (Step 3) above I showed how we could return back the new OrderID value (which is an auto-increment identity column in the Orders table) using an output parameter of the SPROC.  You aren't limited to only returning back identity column values when using SPROCs with LINQ to SQL - in fact you can update and return back output values for any parameter of your SPROC.  You can use this approach both for Insert and Update scenarios.  LINQ to SQL will then take the return value and use it to update the property value of your data model object without you having to-do any second queries against the database to refresh/populate them. 

What Happens if the SPROC Throws an Error?

If a SPROC raises an error when doing an Insert/Update/Delete operation, LINQ to SQL will automatically cancel and rollback the transaction of all changes associated with the current SubmitChanges() call on the DataContext.  This ensures that your data is always kept in a clean, consistent state. 

Can you write code instead of using the ORM designer to call SPROCs?

As I mentioned earlier in this post, you can use either the LINQ to SQL ORM designer to map your insert/update/delete operations to SPROCs, or you can add partial methods on your DataContext class and programmatically invoke them yourself.  Here is an example of the explicit code you could write in a partial class for the NorthwindDataContext to override the UpdateCustomer behavior to call a SPROC:

The code above was actually what was generated by the LINQ to SQL ORM designer when we used the designer to map the SPROC and then associate the update behavior of the Customer object with it.  You can use it as a starting point and then add any additional logic you want to it to make it more advanced (for example: use the return value of the SPROC to raise custom exceptions for error conditions, optimistic concurrency, etc).

Summary

LINQ to SQL is a very flexible ORM.  It enables you to write clean object-oriented code to retrieve, update and insert data.

Best of all - it enables you to cleanly design data model classes independent of how they are persisted and loaded from a database.  You can use the built-in ORM engine to efficiently retrieve and update data in the database using dynamic SQL.  Or alternatively you can configure your data layer to use SPROCs.  The nice thing is that your code consuming the data layer, and all of the business logic validation rules you annotate it with, can be the same regardless of which persistence approach you use.

In future blog posts in this series I'll cover some remaining LINQ to SQL concepts including: Single Table Inheritance, Deferred/Eager Loading, Optimistic Concurrency, and handling Multi-Tier scenarios.  I'm on vacation next week, and so will hopefully have some free time to get a few of them written then. 

Hope this helps,

Scott

Published Thursday, August 23, 2007 1:27 AM by ScottGu

Comments

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 5:16 AM by Per

Yet another great article.

Your articles alone would be great first chapters of one of the many "missing" books on the topic LINQ to SQL.

Looking forward the "Multi-Tier" part.

I think you have to review your defenition of vacation... :p

"I'm on vacation next week, and so will hopefully have some free time to get a few of them written then"

peace.

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 6:13 AM by Wesley Bakker

Hi there Scott,

I see a problem in the UpdateCustomer sproc. Since CustomerID is the primary key there's a (non)clustered index on that column. By using "CustomerID=@CustomerID" in the update statement SQL Server Engine drops the cached indexpages with every update. The engine is not smart enough to recognize it if the CustomerID doesn't get changed at all.

This can cause a major performance issue if you update a lot of records at a time. I've experienced this one time where a batch took 28hrs to run with the update of the id and 20 minutes without the update of the id.

tmho the CustomerId should never ever change anyway so it's better to drop that part of the statement.

Thanks for all your tutorials!

Wesley

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 6:43 AM by Fredrik

Hi Scott,

Great Tutorials indeed. BTW, when are you going to unveil your plans about MVC.( Model, View Controller)by Microsoft.

We need to select one of the best possible ways for future...Either LINQ way or MVC way.

IronLISP is also on its way on codeplex, what are your plans about Dynamic VB( VBx )..?

We are waiting to hear more on this, after great LINQ tutorials.

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 7:03 AM by Andrew Myhre

Hi Scott,

Thanks to you and the LINQ team - this is fantastic.

When you wrote "LINQ to SQL will automatically cancel and rollback the transaction of all changes" this got me curious - how easy is it to control when LINQ uses transactions? Is LINQ smart about when it uses them i.e: only when there are updates? Now that I think about it I invoking SubmitChanges() is an automatic indication that a transaction is required - though I often don't use transactions for simple record updates.

Just wondering what information you can supply about this!

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 7:06 AM by Christian Nielsen

Hi Scott.

As always thanks for a fantastic blog. I have one short question though.

Is it possible to turn off concurrency handeling? I do think its a nice feature, but in some scenarios i would just like updates to be done no questions asked.

/Christian

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 7:12 AM by Matt

Scott, can comment at all about other LINQ providers that will be available with the release of Visual Studio 2008?

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 7:37 AM by Vikram

great, I loved it. I have been using LINQ at my house for practice and am loving it.

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 7:45 AM by Vikram

Hi Scott

One question. Many a times we want to keep different names for a database column and property in the entity of same table. I mean I might want to add PK_ or FK_ fro my primary and foreign key respectively. But I don't want to have a class with such property name. How can LINQ handle these situations.

Thanks in advance

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 7:53 AM by Howard

My apologies on missing the second of my issues: I see you have actually replied to this point in LINQ article 6 (it was so long between the post and the reply I thought I hadn't posted it correctly!!) - we do realise you're busy and do this when you can..

Perhaps you need to get someone in your team to improve the blog engine so users get email replies as well as the blog reply? It's quite difficult to track questions+responses on the comments..

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 8:37 AM by Jason

Great!

Go on ScottGu.

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 8:47 AM by Hakan

Very nice article, thanks.

Looking forward for the future blog posts - have a nice vacation!

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 9:07 AM by sunnyglen

Thanks! Hope to see the Multi-Tier scenarios soon. :)

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 9:30 AM by Josh

For security reasons, the SQL login I use to connect to a SQL database only has rights to exec stored procs, it never doesn't have direct access to tables. My big concern is a SQL injection, so forcing the use of stored procs for data access gets all of our developers honest.

To use LINQ to SQL, would I need to give the login access to the tables during development, even if I wanted to just use stored procs to execute?

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 10:38 AM by ScottGu

Hi Christian,

>>>>>>> Is it possible to turn off concurrency handeling? I do think its a nice feature, but in some scenarios i would just like updates to be done no questions asked.

Yes.  Optimistic concurrency is enabled by default, but you can turn this off completely if you want last writer wins semantics.  I'll cover this as part of my post on optimistic concurrency in more detail.

Hope this helps,

Scott

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 10:40 AM by ScottGu

Hi Josh,

>>>>>>> For security reasons, the SQL login I use to connect to a SQL database only has rights to exec stored procs, it never doesn't have direct access to tables. My big concern is a SQL injection, so forcing the use of stored procs for data access gets all of our developers honest.  To use LINQ to SQL, would I need to give the login access to the tables during development, even if I wanted to just use stored procs to execute?

You'll need schema access to use the LINQ to SQL ORM designer.  Two things to consider for your scenarios:

1) Giving your developers a copy of the schema that they run locally.  They can then just point their app's connection-string at the real database at deployment time.

or:

2) You can bypasss the LINQ to SQL ORM designer and write the data model definitions by hand.  This prevents any need for access to the schema.

Hope this helps,

Scott

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 11:01 AM by Colin Blair

While you are handling concurrency, can you also give us some good coverage on how to handle identity fields at the database level with multiple tables? For example, in datasets I have the identities setup to increment by -1 with cascading setup so that when the database generates the "real" IDs the updated values are propogated to the child tables automatically. Is LINQ going to use the same pattern?

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 11:29 AM by Ben Hayat

Hi Scott;

Do you have any plans to give us a blog on how to use Linq in a stateless environment like ASP which we need to use Attach(). From reading the docs, I still haven't got a clear picture how Linq technology should be used when DC has a short life, but in reality, we need to preserve it's state before it goes away, for the next sessions.

Thank you again for caring to teach us this great technology!

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 11:32 AM by Vince

I suppose this is a bit of an old chestnut, but, in general terms are there performance benefits in using Stored procedures as compared with using dynamic SQL?

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 11:37 AM by dat han

As my understanding, LINQ to SQL is a layer sits on top of ADO, it parse "lambda expressions" to sql expression and using ado provider to execute the sql query?

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 11:52 AM by Dave T

Can LINQ scale to Millions of Oracle DB Rows? If I wanted to convert my existing high performing oracle sp calls to LINQ compatible where do I start?

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 12:19 PM by Tim

This has become my fav blog! Great work Scott!

Just a few questions:

1. I noticed that the generated code tries to change table names from plural to singular (it removes the s). For example, if I had a table called Customers, the generated code would show Customer. However, when I added a table called CustomerStatus, it removed the s and changed it to CustomerStatu. This seems to be a bug. In any case, I always use the singular form for my table names. Is there a way to disable this feature?

2. I prefix my table names to help organize different table types (lookups, etc.). Is there anyway to remove this prefix automatically when the code is generated?

3. In your code above (VB) you had the following:

Return (From c In Me.Customers _

       Where c.CusomterID = customerID _

       Select c).Single

Is there any reason you didn't use the following, which more closely resembles the C# code?

Return Me.Customers.Single(Function(c) c.CustomerID = customerID)

Function(<variable>) is the VB equivalent of <variable>=>, if I'm not mistaken.

Thanks,

Tim

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 12:34 PM by Siraj Gadhia

That is what I was looking

Thanks

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 3:50 PM by egergo

Hello,

I've found an issue according to m:m models that I can't resolve.

Let's assume that we have three tables: [Tickets] -> [TicketsOfUsers] <- [Users], the keys are TicketId and UserId, the TicketOfUsers' key contains both the other keys. My purpose is to query the Tickets of a given user.

I've found three reasonable ways to do that.

1.

var tickets =

   from ticket in dc.Tickets

   join cross in dc.TicketsOfUsers on ticket.TicketId equals cross.TicketId

   where cross.User.Name == "Something"

   select ticket;

Generated SQL query:

SELECT [t0].[TicketId], [t0].[Subject], [t0].[MergedTicketId], [t0].[Status]

FROM [dbo].[Tickets] AS [t0]

INNER JOIN [dbo].[TicketsOfUsers] AS [t1] ON [t0].[TicketId] = [t1].[TicketId]

INNER JOIN [dbo].[Users] AS [t2] ON [t1].[UserId] = [t2].[UserId]

WHERE [t2].[Name] = @p0

The hardest way to go is to manually join the main table to the join table and use the join tables reference. The result what I want to get, but why should I join the tables manually, if I have composed the model in the LINQ to SQL file?

2.

var tickets =

   from cross in dc.TicketsOfUsers

   where cross.User.Name == "Something"

   select cross.Ticket;

Generated SQL query:

SELECT [t2].[TicketId], [t2].[Subject], [t2].[MergedTicketId], [t2].[Status]

FROM [dbo].[TicketsOfUsers] AS [t0]

INNER JOIN [dbo].[Users] AS [t1] ON [t1].[UserId] = [t0].[UserId]

INNER JOIN [dbo].[Tickets] AS [t2] ON [t2].[TicketId] = [t0].[TicketId]

WHERE [t1].[NeptunKod] = @p0

It is comfortable, gives the good result, but cannot be used, if I have more m:m constructs (eg. amongst five tables) in a query.

3.

var tickets = from ticket in dc.Tickets

   from cross in ticket.TicketsOfUsers

   where cross.User.Name == "Something"

   select ticket;

Generated SQL query:

SELECT [t0].[TicketId], [t0].[Subject], [t0].[MergedTicketId], [t0].[Status]

FROM [dbo].[Tickets] AS [t0]

CROSS JOIN [dbo].[TicketsOfUsers] AS [t1]

INNER JOIN [dbo].[Users] AS [t2] ON [t2].[UserId] = [t1].[UserId]

WHERE ([t2].[NeptunKod] = @p0) AND ([t1].[TicketId] = [t0].[TicketId])

And here comes my problem. Why does it generate the CROSS JOIN? Wouldn't it be more logical to generate INNER JOIN ... ON instead of joining the tables using a WHERE condition? As far I know, they generate the same result set, but the CROSS JOIN construct is way too slow.

I tried to resolve the problem many ways. The most interesting was the next:

var tickets = from ticket in dc.Tickets

   from cross in ticket.TicketsOfUsers.DefaultIfEmpty()

   where cross.User.Name == "Something"

   select ticket;

And the generated SQL statement was:

SELECT [t0].[TicketId], [t0].[Subject], [t0].[MergedTicketId], [t0].[Status]

FROM [dbo].[Tickets] AS [t0]

LEFT OUTER JOIN [dbo].[TicketsOfUsers] AS [t1] ON [t1].[TicketId] = [t0].[TicketId]

INNER JOIN [dbo].[Users] AS [t2] ON [t2].[UserId] = [t1].[UserId]

WHERE [t2].[NeptunKod] = @p0

The JOIN ... ON construct comes back. At least it works the way I expected, but it's still not what I want.

Is there a way to resolve this problem? Can I make LINQ to generate fast queries using the relations in the LINQ to SQL file?

Best regards,

Gergő

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 4:31 PM by ScottGu

Hi Vikram,

>>>>> One question. Many a times we want to keep different names for a database column and property in the entity of same table. I mean I might want to add PK_ or FK_ fro my primary and foreign key respectively. But I don't want to have a class with such property name. How can LINQ handle these situations.

When you define your data model classes using the LINQ to SQL ORM designer you can rename the class name properties to be anything you want.  They don't need to have the same column names as the ones in the database.  If I understand your question correctly I think this gives you what you are after.

Hope this helps,

Scott

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 4:53 PM by ScottGu

Hi Colin,

>>>>>> While you are handling concurrency, can you also give us some good coverage on how to handle identity fields at the database level with multiple tables? For example, in datasets I have the identities setup to increment by -1 with cascading setup so that when the database generates the "real" IDs the updated values are propogated to the child tables automatically. Is LINQ going to use the same pattern?

I think you'll be able to handle this well.  The good news is that you can just associate relationships as object references.  For example:

  Order order = new Order();

  OrderDetail orderDetail = new OrderDetail();

  orderDetail.ProductID = 1;

  order.OrderDetails.Add(orderDetail );

Notice above how you don't need to set the PK/FK directly.  Instead, the PK/FK values automatically get set when you call SubmitChanges() - which makes it much easier to handle this type of multi-object insert.

Hope this helps,

Scott

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 4:54 PM by ScottGu

Hi Ben,

>>>>>> Do you have any plans to give us a blog on how to use Linq in a stateless environment like ASP which we need to use Attach(). From reading the docs, I still haven't got a clear picture how Linq technology should be used when DC has a short life, but in reality, we need to preserve it's state before it goes away, for the next sessions.

My Multi-Tier blog post (which will come after the next 3) will explictly cover this scenario and how to use the Attach() method to do it.

Hope this helps,

Scott

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 5:04 PM by ScottGu

Hi Vincent,

>>>>> I suppose this is a bit of an old chestnut, but, in general terms are there performance benefits in using Stored procedures as compared with using dynamic SQL?

That is not an easy nor controversial-free question to answer. :-)

In general SPROCs won't give you a performance difference if you are executing equivalent SQL logic (standard selects, updates, inserts, deletes, etc).  SPROCs can sometimes allow you to execute multiple statements together that work on data closer to the database (since they are running in SQL), which is where you can potentially see some gains depending on what you are doing.  SPROCs also in theory allow DBAs to tune more - since they can review them and create index plans without having to look at C# or VB code they don't know.

On the downside, SPROCs are usually much more time consuming to create when you are building a rich object model for your data layer, and end up being much harder to test and refactor.  That is why a lot of people prefer ORM approaches today - they can provide a lot of productivity benefits.

If you search on SPROC vs. ORM on the web you'll find many more detailed threads debating the question vigorously. ;-)

Hope this helps,

Scott

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 5:05 PM by ScottGu

Hi Dat,

>>>>> As my understanding, LINQ to SQL is a layer sits on top of ADO, it parse "lambda expressions" to sql expression and using ado provider to execute the sql query?

LINQ to SQL is an ORM that uses ADO.NET internally.  It can automatically translate LINQ expressions to dynamic SQL to execute.

Hope this helps,

Scott

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 5:06 PM by ScottGu

Hi Dave,

>>>>>> Can LINQ scale to Millions of Oracle DB Rows? If I wanted to convert my existing high performing oracle sp calls to LINQ compatible where do I start?

LINQ to SQL doesn't currently have a provider for Oracle yet, but I believe one is being built for LINQ to Entities (another ORM provider from Microsoft).  Hopefully in the next few months you'd be able to try this out.

Hope this helps,

Scott

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 5:17 PM by ScottGu

Hi Tim,

Glad you like the blog! :-)  Below are some answers to your questions:

>>>>>> I noticed that the generated code tries to change table names from plural to singular (it removes the s). For example, if I had a table called Customers, the generated code would show Customer. However, when I added a table called CustomerStatus, it removed the s and changed it to CustomerStatu. This seems to be a bug. In any case, I always use the singular form for my table names. Is there a way to disable this feature?

You can solve this one of two ways: 1) Manually modify the name of the class that you generated within the designer.  If you have only one or two tables that fall into this bucket that might be the best approach to use.  2) Alternatively you can use the Tools->Options menu and select the Database Tools->O/R Designer node and turn off the automatic pluralization support.

2. I prefix my table names to help organize different table types (lookups, etc.). Is there anyway to remove this prefix automatically when the code is generated?

>>>>>> Unfortunately I don't think there is anyway to automatically remove the prefix in the VS 2008 tools.  I know a few companies are already working on LINQ to SQL automated extensions to VS 2008 that might handle this though.

>>>>>>> In your code above (VB) you had the following:

>>>>>>>Return (From c In Me.Customers _

         Where c.CusomterID = customerID _

         Select c).Single

>>>>> Is there any reason you didn't use the following, which more closely resembles the C# code?

>>>> Return Me.Customers.Single(Function(c) c.CustomerID = customerID)

>>>> Function(<variable>) is the VB equivalent of <variable>=>, if I'm not mistaken.

I could have used the VB Labmda equivalent instead (which you have above), but I've found a lot of VB developers are still new to that syntax, and so doing it via the query syntax feels a little more understandable.  They ultimately execute the same logic at runtime - so either works.

Hope this helps,

Scott

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 5:27 PM by ScottGu

Hi Roger,

I'll go into more detail on lazy vs. eager loading in the post after next in my series.  Here are a few comments, though, that I posted in response to one of David's posts:

In cases where the relationship between two entities is (n:1), LINQ to SQL does a JOIN as opposed to separate queries since these tend to be safe from a data explosion perspective.  

In the case of a 1:n associations, LINQ to SQL only supports joining-in one 1:n association per query.

So for example, if you wrote code like below:

          DataLoadOptions options = new DataLoadOptions();

          options.LoadWith<Product>(c => c.Category);

          options.LoadWith<Product>(c => c.OrderDetails);

          options.LoadWith<OrderDetail>(o => o.Order);

          db.LoadOptions = options;

          IEnumerable<Product> products = db.Products.ToList<Product>();

You'd find that only 1 query is executed against the SQL database for everything (even though you are bringing back all of the Products and their associated Category and OrderDetail information).  This is because the product->category relationship is (n:1) and the OrderDetail->Order relationship is (n:1) and so both are automatically joined in.

The reason the relationship in your blog post above is generating multiple queries is that you have two (1:n) relationship (Customers->Orders) and (Orders->OrderDetails).  If you just had one (1:n) relationship (Customer->Orders) or (Orders->OrderDetails) LINQ to SQL would optimize and grab it in one query (using a JOIN).  

Hope this helps,

Scott

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 5:29 PM by ScottGu

Hi egergo,

Can you send me email (scottgu@microsoft.com) about this M:M question?  I can then loop you in with a few folks from the LINQ to SQL team to get the best opinion.

Thanks,

Scott

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 5:32 PM by Lasse

Hey Scott, I hope you'll consider making a post about how to create a good BLL with Linq - or if you have a good place to read?

Im new at .NET, but I have been using PHP for many years. Linq seems like a very interesting technology, but it would be nice to know that im doing it correct all the way ;)

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 8:00 PM by Clayton Powell

Is there any way to execute the business rules (as you've defined them above) without actually doing the update? There are some occasions where I'd like to validate the data first without doing an actual update yet.

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 9:10 PM by Steve

Egergo, I am just starting out with LINQ with a view to move a vb6 project over to .NET This sounds like a scenerio that I will encounter in this move as there are about 5 or 6 places that the app does a M:M join.  Can you please post whatever you find out here for us all to view.  

Thanks in Advance,

Steve

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Thursday, August 23, 2007 11:11 PM by Dion

Hi Scott,

Could you please show us how to create a multi tier application with linq so we'll have UI, Business and Data Layers

with some validations

Regards

Dion

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Friday, August 24, 2007 3:32 AM by egergo

Hi,

I've made a few measurements according to the join issue, and I found out that the CROSS JOIN queries are slower only when they are generated with LINQ. When I compared the CROSS JOIN and INNER JOIN queries using a SQLConnection they run with nearly the same performance.

My test DB, Code and Result can be downloaded from here.

vik-hk.bme.hu/.../ManyToMany.zip

But I still don't understand why LINQ generates the CROSS JOIN queries.

Gergő

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Friday, August 24, 2007 10:38 AM by Dave Sanders

Hi Scott, thanks for these articles, its a real help while I'm learning this new tech.

Question that might have already been asked:  Can I somehow construct and map an enum to a lookup table in my database?  So, instead of creating a class from the table, have it inspect the table contents at design time and create an enum.

I can think of architecture arguments both ways for doing this, and it won't be the end of the world if I can't, but I'd like to know if its possible / feasible.

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Friday, August 24, 2007 11:37 AM by Jim Wooley

You mention that optomistic concurrency is automatically handled. However, when using Stored Procs, this is not the case (unless you are leveraging a Timestamp column). Since the OnTUpdate and OnTDelete no longer reference the originalValue, we can't pass this ourselves to the proc to manage our own concurrency. That option existed in the beta1 implementation (admittedly, it was mapped backwards at that time).

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Friday, August 24, 2007 12:45 PM by ScottGu

Hi Clayton,

>>>>>> Is there any way to execute the business rules (as you've defined them above) without actually doing the update? There are some occasions where I'd like to validate the data first without doing an actual update yet.

When you add the propertychanging partial methods, these will execute everytime you set a property on them (so no need to submit).  I need to investigate some more to see whether there is a way to externally trigger that (you could ceretainly add your own helper method to call it though).

Hope this helps,

Scott

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Friday, August 24, 2007 12:49 PM by ScottGu

Hi Jim,

>>>>>> You mention that optomistic concurrency is automatically handled. However, when using Stored Procs, this is not the case (unless you are leveraging a Timestamp column). Since the OnTUpdate and OnTDelete no longer reference the originalValue, we can't pass this ourselves to the proc to manage our own concurrency. That option existed in the beta1 implementation (admittedly, it was mapped backwards at that time).

You can lookup the previous entity value using the "GetOriginalEntityState" helper method on the tables:

       private void UpdateCustomer(Customer current) {

           Customer original = this.Customers.GetOriginalEntityState(current);

           …

       }

You can then use this for optimistic concurrency.  I'll cover this in my upcoming optimistic concurrency post.

Hope this helps,

Scott

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Friday, August 24, 2007 12:56 PM by ScottGu

Hi Doug,

>>>>> My question is what happens in Linq to SQL when the proc returns multiple result sets.  For example lets say I have a proc that returns active orders for a specific sales account.

In my last blog post here: weblogs.asp.net/.../linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx I talked a little about the IMultipleResult interface that gets passed back from a SPROC.  You can use this to handle scenarios where you return multiple results.

Hope this helps,

Scott

I'll cover this in my upcoming blog post on LoadOptions and

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Friday, August 24, 2007 12:58 PM by ScottGu

Hi Dion,

>>>>> Could you please show us how to create a multi tier application with linq so we'll have UI, Business and Data Layers

My last blog post in this series will talk about passing entities between physical tiers, adn how to re-attach entities that are disconnected to handle this.b

Hope this helps,

Scott

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Friday, August 24, 2007 1:07 PM by ScottGu

Hi Dave,

>>>>> Can I somehow construct and map an enum to a lookup table in my database?  So, instead of creating a class from the table, have it inspect the table contents at design time and create an enum.

Right now the LINQ to SQL designer doesn't automatically support this - but you could in theory run your own tool to auto-generate an enum for this.  I'd recommend being careful, though, since database cotents can change and you might find youself burning in stale data to enums if you aren't careful.

Hope this helps,

Scott

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Friday, August 24, 2007 2:10 PM by Lance Fisher

Man, I need to start reading these LINQ articles or I'll never catch up.  Thanks for all the great information.  Your blog is my first stop for good ASP.NET tutorials.

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Friday, August 24, 2007 4:39 PM by Dave Sanders

Thanks for the reply scott - I agree generated enums are probably not a smart architecture idea. :) Another quick question: Can one easily hack into the code generation for Linq to Sql and substitute or add in their own code?  Or should one just go out to third party tools like MyGeneration for this sort of thing?

Thanks again

Dave

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Saturday, August 25, 2007 1:32 PM by JasonBSteele

Scott,

Another great post! I have a 'slightly' unrelated question though - how similar will the Entity Framework be? How easy will it be to migrate code written against LINQ to SQL to LINQ to Entities? Will a tool be needed/provided?

Thanks,

Jason

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Sunday, August 26, 2007 7:52 AM by Vikram

Hi Scott,

Thanks for the answer. Yes you gave me the answer I was looking for.

Thanks

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Sunday, August 26, 2007 11:30 AM by Alexey Leukhin

Good day,

Just one simple question - do you know any good MSSQL 2008 blog with tutorials of the same quality as yours?

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Sunday, August 26, 2007 11:55 PM by Matt

Hi,

Great blogs - thanks.

I haven't seen how to do inheritance the way I would like to see it. I have a master table "Component" that stores items such as Server, Network Card, Storage Unit etc. The Component table stores most of the component information such as the name, ip address etc.  The Server, NIC and StorageUnit tables extend that table and have a foreign key reference to the ComponentID.

Using Linq, how would I go about having a Component C# class as a base class and Server, Nic and StorageUnit classes as derived classes?  The information is not all stored in the Component table so cannot use InheritanceMappingAttribute (unless I'm mistaken).

Thanks in advance.

Matt.

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Monday, August 27, 2007 2:10 AM by Ngoc

Are there anyway to query an entity without using lambda expression in C#?

Example:

- Instead: db.Products.Single(p => p.ProductID = "1");

- Use: from p in db.Products

      where p.ProductID equals 1

      select single p;

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Monday, August 27, 2007 9:18 AM by Greg McCarty

I try to build the examples and follow along.

So I built the data model.  Then I read the part about the version column on the Customer table.  So I added it.

Went along with the rest of the example, and discovered my update didn't.

Realized it was because my Customer table in the model was added before I added the version column.

Deleted the table in the model, added it again, and all worked.

Is there a better was to 'refresh' the model to reflect the schema?

Thanks.

GregM

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Monday, August 27, 2007 5:08 PM by ScottGu

Hi GregM,

>>>>>> Is there a better was to 'refresh' the model to reflect the schema?

Unfortunately there isn't a refresh mode in the LINQ to SQL designer - so deleting the table entity and re-creating it is probably the best approach to take.

Sorry!

Scott

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Monday, August 27, 2007 5:09 PM by ScottGu

Hi NGoc,

>>>>> Are there anyway to query an entity without using lambda expression in C#?

Yes, instead of: db.Products.Single(p => p.ProductID = "1");

You could write:

     (from p in db.Products

      where p.ProductID == 1

      select p).Single();

Hope this helps,

Scott

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Monday, August 27, 2007 5:10 PM by ScottGu

Hi Alexey,

>>>>> Just one simple question - do you know any good MSSQL 2008 blog with tutorials of the same quality as yours?

Unfortunately I don't know of any SQL 2008 specific blogs just yet.  But hopefully several will start to appear as we get closer to SQL 2008 ship date.

Hope this helps,

Scott

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Monday, August 27, 2007 5:12 PM by ScottGu

Hi Matt,

>>>> I haven't seen how to do inheritance the way I would like to see it. I have a master table "Component" that stores items such as Server, Network Card, Storage Unit etc. The Component table stores most of the component information such as the name, ip address etc.  The Server, NIC and StorageUnit tables extend that table and have a foreign key reference to the ComponentID.   Using Linq, how would I go about having a Component C# class as a base class and Server, Nic and StorageUnit classes as derived classes?  The information is not all stored in the Component table so cannot use InheritanceMappingAttribute (unless I'm mistaken).

LINQ to SQL supports Single Table Inheritance (which I'll be covering in my next LINQ to SQL blog post), but doesn't natively support multiple table inheritance.

What I think you could do, though, is to model a logical entity using SPROCs for querying, inserts, updates, deletes.  You could then automatically shred the data into the appropriate tables within the SPROC layer.  I haven't tried this myself yet - but in theory it should work.

Hope this helps,

Scott

# re: LINQ to SQL (Part 7 - Updating our Database using Stored Procedures)

Monday, August 27, 2007 9:22 PM by Matt

Thanks Scott.  I guess we'll go with SPROCs then :)