LINQ to SQL (Part 6 - Retrieving Data 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 five parts of my LINQ to SQL series:

In these previous LINQ to SQL blog posts I demonstrated how you could use LINQ query expressions to programmatically retrieve data from a database.

In today's blog post I'll cover how you can also use database stored procedures (SPROCs) and user defined functions (UDFs) with your LINQ to SQL data model.  Today's blog post will specifically cover how to call SPROCs to query and retrieve data from the database.  In my next blog post in this series I'll then show how you can optionally also use SPROCs to update/insert/delete data from the database.

To SPROC or not to SPROC?  That is the question....

The question of whether to use Dynamic SQL generated by an ORM or instead use Stored Procedures when building a data layer is a topic that generates endless (very passionate) debate amongst developers, architects and DBAs.  A lot of people much smarter than me have written on this topic, so I won't rehash the arguments for and against each side here.

The LINQ to SQL ORM that ships in .NET 3.5 is pretty flexible, and can be used to create data model classes whose object model can be independent of the underlying database schema, and which can encapsulate business logic and validation rules that work regardless of whether the data model is populated/persisted via dynamic SQL or via SPROCs.

In my LINQ to SQL Part 3: Querying our Database post I discussed how you can write LINQ query expressions against a LINQ to SQL data model using code like below:

When you write LINQ query expressions like this the LINQ to SQL ORM will execute the necessary dynamic SQL for you to retrieve Product objects that matches your query.

As you'll learn in this post, you can also optionally map SPROCs in the database to your LINQ to SQL DataContext class, which allows you to alternatively retrieve the same Product objects by calling a stored procedure instead:

 

This ability to use both dynamic SQL and SPROCs with a clean data model layer is pretty powerful, and provides a great deal of flexibility when working on projects.

The Steps to Map and Call a SPROC using LINQ to SQL

In my Part 2: Defining our Data Model Classes tutorial I discussed how to use the LINQ to SQL ORM designer to create a LINQ to SQL class model like below:

Notice above how there are two panes on the LINQ to SQL ORM designer surface.  The left pane enables us to define data model classes that map to our database.  The right method pane allows us to optionally map SPROCs (and UDFs) to our LINQ to SQL DataContext object, which we can then use in-place of dynamic SQL to populate the data model objects.

How to Map a SPROC to a LINQ to SQL DataContext

To map SPROCs to our DataContext class, let's first go to the VS 2008 Server Explorer window and look at the SPROCs within our database:

We can double click any of the SPROCs above to open and edit them.  For example, below is the "CustOrderHist" SPROC in Northwind:

To map the above SPROC to our LINQ to SQL DataContext, we can drag/drop it from the Server Explorer onto our LINQ to SQL ORM designer.  This will automatically create a new method on our LINQ to SQL DataContext class like below:

By default the method name created on the DataContext class will be the same as the SPROC name, and the return type of the method will be an automatically created type that follows the "[SprocName]Result" naming pattern. For example: the SPROC above would return a sequence of "CustOrderHistResult" objects.  We could optionally change the name of the method by selecting it in the designer and then use the property grid to rename it.

How to Call our Newly Mapped SPROC

Once we've done the steps above to map a SPROC onto our DataContext class, it is easy to use it to programmatically retrieve data.  All we need to-do is call the new method we mapped on our DataContext class to get back a sequence of strongly typed results from the SPROC:

Calling the SPROC in VB:

Calling the Sproc in C#:

In addition to programming looping over the result like in the code samples above, I could also obviously bind the results to any UI control to display them.  For example, the below code databinds the result of our SPROC to a <asp:gridview> control:

Which then displays the product history of our customer on a page like so:

Mapping the Return Type of SPROC Methods to Data Model Classes

In the "CustOrderHist" SPROC example above the stored procedure returned a sequence of product history results containing two columns of data: the ProductName of the product, and the Total Number of orders the customer has made for that product.  The LINQ to SQL designer automatically defined a new "CustOrderHistResult" class to represent this result.

We could alternatively choose to have the return result of a SPROC map to an existing data model class we have already defined in the LINQ to SQL designer (for example: an existing Product or Order entity class). 

For example, assume we have a "GetProductsByCategory" SPROC in our database that returns product information like so:

Like before we can create a "GetProductsByCategory" method on our DataContext that calls this SPROC by dragging it onto our LINQ to SQL designer.  Rather than just dropping the SPROC anywhere on the designer, though, we'll instead drop the SPROC on top of the existing "Product" class in our data model designer:

This gesture of dropping the SPROC onto the Product class tells the LINQ to SQL designer to have the "GetProductsByCategory" method return a sequence of "Product" objects as a return result:

 

One of the cool things about having our SPROC return "Product" objects like above is that LINQ to SQL will automatically track the changes made to the returned Product objects just like it would Product objects returned via LINQ queries.  When we call the "SubmitChanges()" method on our DataContext, the changes we have made to these objects will automatically be saved back to the database.

For example, we could write the code below to retrieve (using a SPROC) and change the price of all products within a specific Category to be 90% of their current value:

When we call SubmitChanges() at the end it will transactionally update all of the product prices.  To understand more about how change tracking and the SubmitChanges() method work, as well as about how Validation Business Logic can be added to data model entities, please read my LINQ to SQL Part 4: Updating our Database tutorial. 

In my next blog post in this LINQ to SQL series I'll also cover how you can replace the dynamic insert/update/delete SQL generated by the ORM with custom SPROCs that handle the database updates instead.  The nice thing is that the code above wouldn't change at all if I configured my DataContext to use SPROCs for updates - it would purely be a mapping layer change and the code written against my data model would be oblivious to it. 

Handling SPROC Output Parameters

LINQ to SQL maps "out" parameters in SPROCs as reference parameters (ref keyword), and for value types declares the parameter as nullable.

For example, consider the below "GetCustomerDetails" SPROC which takes a CustomerID as an input parameter, and which returns the company name as an output parameter in addition to its order history as a query result:

If we drag the above SPROC onto our "Order" class in the LINQ to SQL designer, we could then write the below code to call it:

VB:

C#:

Notice in the code above how the SPROC helper method returns back a sequence of Order objects - but also then returns the CompanyName as an output parameter to the helper method.

Handling Multiple Result Shapes from SPROCs

When a stored procedure can return multiple result shapes, the return type of the SPROC method on the DataContext cannot be strongly typed to a single class shape.  For example, consider the SPROC below which returns either a product result or an order result depending on the input parameter:

LINQ to SQL supports the ability to create SPROC helper methods that can return either a Product or Order shape by adding a partial "NorthwindDataContext" class to the project that defines a method (which in this case we'll call "VariablesShapeSample") that invokes the SPROC and returns an IMultipleResult object like so:

VB:

C#:

Once this method is added into our project we can then call it and convert the result to be either a Product or Order sequence when we are using it:

VB:

C#:

Supporting User Defined Functions (UDFs)

In addition to SPROCS, LINQ to SQL also supports both scalar-valued and table-valued user defined functions (UDFs), as well as the in-line counterpart to both.  Once added to your DataContext as a method, you can use these UDF functions within your LINQ queries.

For example, consider a simple scalar user defined function called "MyUpperFunction":

We can drag/drop it from the Visual Studio Server Explorer onto our LINQ to SQL Designer to add it as a method on our DataContext:

We can then use this UDF function inline within our LINQ expressions when writing queries against our LINQ to SQL data model (notice it is being used within the "where" clause below):

VB:

C#:

If you use the LINQ to SQL Debug Visualizer that I blogged about here, you can see how LINQ to SQL transforms the above expression queries into raw SQL that execute the UDF inside the database at runtime:

Summary

LINQ to SQL supports the ability to call Stored Procedures and UDFs within the database and nicely integrate them into our data model.  In this blog post I demonstrated how you can use SPROCs to easily retrieve data and populate our data model classes.  In my next blog post in this series I'll cover how you can also use SPROCs to override the update/insert/delete logic when you SubmitChanges() on your DataContext to persist back to the database.

Hope this helps,

Scott

Published Thursday, August 16, 2007 2:16 AM by ScottGu
Filed under: , , , ,

Comments

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 5:59 AM by Josh

How will the Enterprise Library be used with LINQ to SQL? Or does this new technology remove that layer of abstraction?

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 6:05 AM by webabcd

great!

great!

great!

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 6:38 AM by SirMike

This looks pretty nice but I have some problems with SPROCs.

Let's say that we have some optional parameters with default values different than NULL. Linq generated methods pass NULL's that override default values, so the procedure doesn't work correctly.

Second problem is that mappings are bad when columns in a SPROC's select are written as [Field] (with square brackets)

The third problem I encountered is that when a procedure has a return value different than zero. In this case Linq takes this return value as recordset.

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 7:31 AM by paints

Hi,Scott

My WPF Application program(builded by vs2008 beta1) can run at other vista machine with .net framework3.0 before,

when I installed vs2008 beta2,the .Net framework 3.0 has updated to Service pack 1,

then I rebuilded my program by vs2008 beta2 target to .net framework3.0 too,Now the program cann't run at the vista machine with .net framework3.0 ,  What can i do to update .Net Framework3.0 to sp1 in other machine? or must i install the .net framework3.5?

thanks

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 8:14 AM by Jan Bannister

Can LINQ for SQL return/handle multiple tables? So if a SPROC returns 2 tables how would iterate across them both?

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 8:30 AM by Dave Davis

Scott,

Is there a way to generate the database schema from objects using code? Most demos that I have seen create the context class from databases that already exist.

NHibernate allows you to create the objects and xml mapping files before creating the database.  Once you are happy with your object structure you can call NHibernate.Tool.hbm2ddl to create the database.  This creates all the tables with the appropriate relationships.

If there is no such functionality are there plans to implement it.

Thanks

Dave

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 8:54 AM by Hakan

Brilliant, looks like many 3rd party ORM mappers will be obsolete with Linq.

Im wondering if Linq can support dynamic sql where the 'where' clause is build using multiple optional filters like so inside a stored procedure,

if filter1 exists @sql_where = @sql_where + '.....'

if filter2 exists @sql_where = @sql_where + '.....'

and so on

Basically I am hoping there is a way to build a conditional/nested linq statement

Maybe you can shed some light on this,

Article is very well written and easy to understand as usual, great job

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 9:53 AM by Roger

Hi Scott,

This was the tutorial I've been waiting for! I'm really glad to see support for out variables and multiple result shapes.

Unfortunately for me I lie on the "procs only" side of the debate, as I've never worked with a DBA who would allow ad-hoc SQL queries.

So, I'm wondering if there is a way to use the object-mapping and stored proc features of LINQ, but to turn off the ability to run ad-hoc SQL?

I've been investigating this since the last beta but haven't found a way. I also haven't seen any way to specify a default select command, in the same way you can specify default update/insert/delete statements.

I also remember hearing some talk of a difference between "Linq to SQL" and "Link to entities", with someone saying that linq to entities would allow for more advanced scenarios, like possibly creating a stored-proc-only query model. Do you know if these technologies are now one in the same?

Thanks,

Roger

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

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

Hi Josh,

>>>>>> How will the Enterprise Library be used with LINQ to SQL? Or does this new technology remove that layer of abstraction?

I'm pretty sure there will be an update of the Enterprise Library to take advantage of all the new .NET 3.5 features (including new ones like LINQ).  I don't know the exact ETA for it - but I believe the PAG team is working on this now.

Thanks,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 10:41 AM by Ryan Ternier

Scott, all these things look amazing, I have a question though.

Performance wise you really don't gain anything by using LINQ (website -> DB), as compared with writing efficient SQL and a DataAccess  code.

What do you say are the biggest benefits of LINQ? Basically, why do you think people should/would choose to use LINQ, and what business/performance/product gains are received when moving to LINQ?

Thanks.

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 10:41 AM by ScottGu

Hi SirMike,

>>>>>>> Let's say that we have some optional parameters with default values different than NULL. Linq generated methods pass NULL's that override default values, so the procedure doesn't work correctly.  Second problem is that mappings are bad when columns in a SPROC's select are written as [Field] (with square brackets).  The third problem I encountered is that when a procedure has a return value different than zero. In this case Linq takes this return value as recordset.

Can you send me email with a description of these issues?  I can then loop you in with the LINQ to SQL team to have them investigate.

Thanks,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 10:42 AM by ScottGu

Hi paints,

>>>>>>> then I rebuilded my program by vs2008 beta2 target to .net framework3.0 too,Now the program cann't run at the vista machine with .net framework3.0 ,  What can i do to update .Net Framework3.0 to sp1 in other machine? or must i install the .net framework3.5?

Can you provide more details about your WPF application to me in email (scottgu@microsoft.com).  I can then have someone investigate why you are seeing a multi-targeting issue.

Thanks,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 10:44 AM by ScottGu

Hi Jan Bannister,

>>>>>>> Can LINQ for SQL return/handle multiple tables? So if a SPROC returns 2 tables how would iterate across them both?

Yes - LINQ to SQL can support this as well.  Basically you'd use the same approach that I demonstrated with the IMultipleResult sample above.  If your SPROC returned two results sequentially then you'd just call GetResult<T> twice in order to retrieve the sequences.

Hope this helps,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 10:47 AM by ScottGu

Hi Dave,

>>>>>>> Is there a way to generate the database schema from objects using code? Most demos that I have seen create the context class from databases that already exist.  NHibernate allows you to create the objects and xml mapping files before creating the database.  Once you are happy with your object structure you can call NHibernate.Tool.hbm2ddl to create the database.  This creates all the tables with the appropriate relationships.

Yes - the option to create schema based on the object model (as opposed to creating the object model based on the schema) is also supported with LINQ to SQL.  The DataContext class has a "CreateDatabase()" method that you can programmatically call to create the database schema for an application based on your LINQ to SQL object model.  Just set the connection-string of the DataContext to point to your database, call CreateDatabase() and you should be set.

Hope this helps,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 10:52 AM by ScottGu

Hi Hakan,

>>>>>>> Im wondering if Linq can support dynamic sql where the 'where' clause is build using multiple optional filters like so inside a stored procedure,

if filter1 exists @sql_where = @sql_where + '.....'

if filter2 exists @sql_where = @sql_where + '.....'

and so on.  Basically I am hoping there is a way to build a conditional/nested linq statement.  Maybe you can shed some light on this, Article is very well written and easy to understand as usual, great job

I don't believe LINQ to SQL can directly call SPROCs that dynamically build SQL on the fly - except maybe by calling the low-level ExecuteCommand() method that the DataContext exposes that allows you to override the SQL executed.

You can, though, definitely handle dynamic query scenarios using dynamic SQL using LINQ query expressions you write (which might be what you are asking for above).  I have it on my list of things to cover in a blog post in the future.

Thanks,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 11:10 AM by ScottGu

Hi Ryan,

>>>>>> What do you say are the biggest benefits of LINQ? Basically, why do you think people should/would choose to use LINQ, and what business/performance/product gains are received when moving to LINQ?

I think the biggest benefit of LINQ to SQL is that it provides a good way to cleanly structure your data model in an object oriented way.  The ability to define data model classes that can be independent of the underlying database schema gives you much more agility as you build your application (since you can change the underlying column names without having to update your code).  And the ability to cleanly integrate model-level validation and business logic rules helps avoid you writing duplicate logic in multiple places.

Hope this helps,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 11:25 AM by Howard

Just like to say I had been waiting for LINQ to SQL quite eagerly since DLINQ previews last year, but now it's here I'm not sure about it.

It makes for great demos where all the SQL is hidden away and all that. I looked into how I could add application level security into the datacontext - so that the datacontext knows how to restrict queries based on the user. For example i want to restrict the current user on northwind to customerID='ALFKI' (and related tables, e.g. orders), and if that user tried to modify a url e.g. CustomerID=AROUT he'd get no data.

The upshot of discussion on MSDN forums is that there is no extensibility to the datacontext class that might be usable in this way. So the conclusion I am reaching with Beta2 is that LINQ to SQL is a nice feature for your excellent demos Scott, but unusable for a serious application.

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 11:49 AM by Quoo

Hi Scott,

These posts are awesome, that you so much for the time and effort you put into this stuff; it makes picking things up at a trot so much easier.

I was wondering if you had thought of providing selections of your blogs as PDF docs for download, so that we could have, for example, this LINQ series of posts all together in one place in chronological order.

Just a thought, I will most probably be using something like Loop (www.drawloop.com/Loop.aspx) to do it anyway :P

ROCK ON

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 11:55 AM by Ben Hayat

Hi Scott;

I understand this thread is about SProcs, but I have questions unrelated to SProcs (if you don't mind).

I'm diving into Linq docs and learning stuff as we go along and have a few questions:

a) As a DataContext (DC)become a container for my tables, will there be any event handling in the designer to handle events (i.e. OnNewRecord, OnUpdateRecord, BeforeDeleteRecord) that relate to each tables in the DC?

b) When it comes to performance and connection time, does it matter the how many tables exist in a DC?

c) Is it better to have less DCs with more tables in them or is it better to have more DCs with less tables in them?

Thanks!

..Ben

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 12:23 PM by Koistya `Navin

Scott,

Tell me please, is there a way to use SqlDependency with SPROCs & LINQ scenarios?

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 12:43 PM by Vikram

I am really enjoying the series on the LINQ. :)

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 2:35 PM by Martin Robins

Slightly off topic (although still LINQ to SQL related); is there a way to use LINQ to retrieve hierarchical data?

For example, if I have a table that references back to itself through multiple levels (such as would normally be displayed in a treeview), how could I retrieve this using a LINQ query that would get to all levels?

I am guessing that the answer would lie in datashaping; I can do it using a recursive query in SQL2005, but alas I have no idea where to start on this one within LINQ!

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 2:54 PM by Chris Moseley

Hi Scott,

Great post again, I'm really inpressed by the SPROC support in Linq to Sql

I'm looking forward to your posts on Linq To Entities, especially to find out what the actual differences are between the two ORMs. Linq To Sql seems to have grown up a lot since it's original conception. I'm wondering if Linq To Entities will have anything more to offer (other than not being MS Sql only).

Chris.

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 5:11 PM by Danny Tuppeny

I have the same question as Martin Robins. In SQL 2005 we had support for a kind of hierarchical query. Is there somethign in Link that'll let us pull out a whole tree efficiently? eg. without selecting every record and without running a query for every row to get its children, but result in a tree?

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 5:24 PM by ScottGu

Hi Quoo,

>>>>>> I was wondering if you had thought of providing selections of your blogs as PDF docs for download, so that we could have, for example, this LINQ series of posts all together in one place in chronological order.

I've actually been thinking about doing something like this.  After I make more progress over the next few weeks with a few more posts I'll look into enabling this.

Thanks,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 5:28 PM by ScottGu

Hi Ben,

>>>>>>> a) As a DataContext (DC)become a container for my tables, will there be any event handling in the designer to handle events (i.e. OnNewRecord, OnUpdateRecord, BeforeDeleteRecord) that relate to each tables in the DC?

I'd recommend looking at my part 4 segment on updating.  It shows how to handle a bunch of partial methods that enable events both before and after a column is updated, and before additions/edits/deletes are made.

>>>>>>> b) When it comes to performance and connection time, does it matter the how many tables exist in a DC?

I don't think the number of tables makes any difference from a performance perspective.

>>>>>> c) Is it better to have less DCs with more tables in them or is it better to have more DCs with less tables in them?

I think you want to group tables based on whether they are related - that is the high order bit to optimize for.  If you have a single database with tables that have associations between them, you probably want a single DC.  If you have multiple databases then you'd want multiple DataContexts.

Hope this helps,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 5:33 PM by Arnaud Weil

A great explanation!

Looking forward to seeing how the data modification statements (update/delete/insert) are handled with LINQ!

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 5:34 PM by ScottGu

Hi Koistya,

>>>>>> Tell me please, is there a way to use SqlDependency with SPROCs & LINQ scenarios?

I need to investigate this more in the weeks ahead and figure out how to enable this.

Thanks,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 5:37 PM by ScottGu

Hi Martin,

>>>>>> For example, if I have a table that references back to itself through multiple levels (such as would normally be displayed in a treeview), how could I retrieve this using a LINQ query that would get to all levels?

You can have entities that have relationships with themselves (for example: Employee that references its manager and reports).  One of the things I need to put together a sample of is one that shows how best to retrieve this hiearchical data (without doing a separate query for each employee).

Thanks,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 8:08 PM by Moosdau

Hi Scott:

I have a problem :

e.g.:

var res=db.Tables.Single(p=>p.columnName==0);

if the record match the query condition exist, it works well, but if the record doesn't exist, it will throw an exception, but when I query something, I don't know if the record exist, and I don't think exception is a resonable way.

so how to know whether the record exist or not?

Thanks!!

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, August 16, 2007 8:29 PM by Steven Nagy

Hi Scott, you may have been asked this before, but can you use LINQ with Cubes, and if so, what kinds of things can you do? Can you replace MDX? (maybe there should be a CLINQ?)

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Friday, August 17, 2007 1:10 AM by How I can get StoreProcedure's ReturnValue?

How I can get StoreProcedure's ReturnValue?

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Friday, August 17, 2007 2:05 AM by Majid Shahabfar

Hi Scott,

Is there any benchmark which has compared the performance of using LINQ queries expressions and LINQ SPROC?

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Friday, August 17, 2007 2:05 AM by ScottGu

Hi Roger,

>>>>> Great post (as usual). I'm running into a wall trying to substitute stored procedures for dynamic T-SQL in a DataContext that serves as the data source for a LinqDataSource control, which would enable editing of a bound GridView or DetailsView. ISingleResult<EntityClass> implements IEnumerable<EntityClass> but not Table<EntityClass>, which is required for the EntityClass to be visible in the Data Source Wizard's second dialog. Is there a solution (other than moving to the ObjectDataSource) that isn't a hack?

Any chance you could send me an email (scottgu@microsoft.com) with a quick sample that demonstrates what you are trying to-do here?  If the object you are trying to edit is a Table on the DataContext, then I think you can handle this scenario by handling the Selecting event on the LinqDataSource.  Alternatively, the cleanest way might be to use the ObjecytDataSource.

Hope this helps,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Friday, August 17, 2007 2:07 AM by ScottGu

Hi Moosdau,

>>>>>> var res=db.Products.Single(p=>p.columnName==0);

>>>>>> if the record match the query condition exist, it works well, but if the record doesn't exist, it will throw an exception, but when I query something, I don't know if the record exist, and I don't think exception is a resonable way. so how to know whether the record exist or not?

You can handle this by using the "FirstOrDefault" operator instead of Single:

   var res=db.Products.FirstOrDefault(p=>p.columnName==0);

If no product matches the query then it will return null.  You can then check for this to detect whether the record exists or not.

Hope this helps,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Friday, August 17, 2007 2:09 AM by ScottGu

Hi Majid,

>>>>>> Hi Scott, Is there any benchmark which has compared the performance of using LINQ queries expressions and LINQ SPROC?

I haven't seen a specific benchmark on this yet.  Rico has a good series on LINQ to SQL performance, though, that you might want to check out here: blogs.msdn.com/.../dlinq-linq-to-sql-performance-part-5.aspx

Hope this helps,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Friday, August 17, 2007 2:12 AM by ScottGu

>>>>>> How I can get StoreProcedure's ReturnValue?

If you have a SPROC like this:

  CREATE PROCEDURE GetCustomerOrderCount(@CustomerID nchar(5))

  AS

  Declare @count int

  SELECT @count = COUNT(*) FROM ORDERS WHERE CustomerID = @CustomerID

  RETURN @count

Then you could invoke it and return the value like so:

  int value = DataContext.GetCustomerOrderCount("ALKAI");

Hope this helps,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Friday, August 17, 2007 2:45 AM by Chris

Hi Scott,

Is it possible to provide an example of how LINQ would work with a query which returns multiple tables (from joins). As often data is a lot more than just a single table.

eg.

SELECT C.*, O.*, OI.*

FROM Customer C

INNER JOIN Order O on O.CustomerID = C.CustomerID

INNER JOIN OrderItem OI on OI.OrderID = O.OrderID

Thanks.

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Friday, August 17, 2007 7:19 AM by Paul

Scott,

As always, great post as part of a great series.

My question is about the timing of the data load.  With a regular LINQ to SQL expression, the expression tree is built in the app, then when you iterate or call ToList<T>() or the like is when the SQL script is actually fired in the db, right?  I assume then, that the same is the case for a sproc call, so you create an object that maps to the expression calling the sproc, and then when you evaluate that object through iteration or other inspection is when the sproc actually fires.

My question, then, assuming I'm correct so far, is what happens if that object you've assigned your sproc results to is used in another LINQ expression, e.g.:

var products = db.GetAllProducts();

var chai = from p in products where p.ProductName == "Chai" select p;

Intuitively, the logical answer seems to be that since Sprocs (in the db) do not return table-valued objects, that the sproc has to execute and send its result to the app, and then the app would evaluate it in the second expression much like if it were a Linq to Objects expression. Is that correct?  I blogged a bit about my thoughts on it if that's the case, as well as my approach to the sproc vs. dynamic query question (my answer is Views for selects, sprocs for CUD), but I wanted to see if my intuition is correct or not.

Thanks,

Paul

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Friday, August 17, 2007 1:05 PM by Michael

Hi Scott,

Is it possible to create managed CLR stored procedures in C# using LINQ, rather than writing the stored procedures in SQL? Sometimes, I'd like the code to execute on the database, but still would like to use LINQ to write the code.

Thanks!

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Friday, August 17, 2007 1:43 PM by ScottGu

Hi Michael,

>>>>>> Is it possible to create managed CLR stored procedures in C# using LINQ, rather than writing the stored procedures in SQL? Sometimes, I'd like the code to execute on the database, but still would like to use LINQ to write the code.

Yes - you can created SPROCs using managed code now - which would also then allow you to use LINQ to SQL to invoke them.  VS 2005 has the support for creating managed SPROCS too.

Hope this helps,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Friday, August 17, 2007 1:45 PM by ScottGu

Hi Chris,

>>>>>> Is it possible to provide an example of how LINQ would work with a query which returns multiple tables (from joins). As often data is a lot more than just a single table.

LINQ definitely supports joins across multiple tables.  Anders has a good set of samples here that you might want to check out to see the syntax: forums.microsoft.com/.../ShowPost.aspx

Hope this helps,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Friday, August 17, 2007 3:31 PM by Rick

Great post!

What are the plans for handling updates to the Result object returned from a SP where the Result is from an underlying join? Are there Insert, Update, Delete methods on the Result object that can be pointed back to SPs that can handle this?

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Friday, August 17, 2007 6:54 PM by tuano007

Hi Scott, love this series.

The data objects created are not marked [Serializable], so binary formatting (and thus use with SQL server session state) is impossible on the objects this returns.  Even if I manually change the generated cs file, the EntitySets are sealed and not serializable.  Is there a workaround or will they be fully binary-formattable in the release version?

Until then, looks like we might have to just save to the non-session database?

Thanks!

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Saturday, August 18, 2007 5:48 AM by Vikram

Hi Scott,

Where is the connection string stored when we use LINQ to SQL. How can we change the connection string.

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Saturday, August 18, 2007 7:35 PM by Scott

Scott,

this has been a great series.  Just one thing seems to be missing.  How do we dispose of the DataContext class that we create for the SQL database?  For example, the NorthwindDataContext class is an IDisposable object, but if I actually call Dispose on it then I get ObjectDisposed exceptions when I access the data that came from that NorthwindDataContext.

Doesn't that leave huge amounts of resources open if I've made many calls to my database?

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Sunday, August 19, 2007 12:38 AM by Mehfuz Hossain

Cool! Now tell me, i see that you can drop a particular sp on a particular class in the designer, but what if someone drops a SP on to a wrong class, let's say someone drops the a CusOrderHistory on to the Order class instead of Product in the designer. shouldn't there be any validation , that will use some sort of  reflection to validate design-time.

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Sunday, August 19, 2007 1:11 AM by Omar

If an SP executes a dynamic query and returns rows from a table, can I explicitly map the result of the SP to an object?

for example,

CREATE PROCEDURE Something

AS

EXEC 'SELECT ID, Name FROM Product'

GO

Moreover, if an SP returns fields from different tables by inner joining them in a dynamic SQL, how can I map it to proper object?

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Sunday, August 19, 2007 10:31 AM by Matt Brooks

Hi Omar,

LINQ to SQL seems to use the SET FMTONLY ON option at design time in order to determine what a stored procedure is capable of returning. This seems to work with dynamic SQL as per my example below:

create proc sptest

as

begin

exec('select 1 as One, 2 as Two')

exec sp_executesql N'select 1 as One, 2 as Two'

end

go

set fmtonly on

exec sptest

set fmtonly off

Regarding your second question, I would guess that as long as the designer can match column names and data types in the result set to fields in your object then you should be able to apply the mapping.

Thanks,

Matt

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Sunday, August 19, 2007 4:53 PM by Paul

Mehfuz & Omar>  By default, your sprocs return types will be something custom based on the fields it returns, regardless of the underlying data source.  If you want them to be able to reference another existing object, you can add an association.  In the case where you accidentally try and drop a sproc on the 'wrong' object, one that it cannot properly cast the results to, you'll get an error from the designer that the schemas don't match.

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Sunday, August 19, 2007 8:48 PM by Fduch

Sorry for offtopic question.

I recently installed Beta2 and finally started playing with lambdas in VB. Please tell me why these same line of code comile to very different IL and return different results in VB and C#. Can we be sure that this would be fixed before release?

C#

delegate TReturn SelfApplicable0<TReturn>(SelfApplicable0<TReturn> self);

SelfApplicable0<Func<Func<Func<int, int>, Func<int, int>>, Func<int, int>>> YC = y => f => x => f(y(y)(f))(x);

VB

Delegate Function SelfApplicable0(Of TReturn)(ByVal self As SelfApplicable0(Of TReturn)) As TReturn

Dim YC As SelfApplicable0(Of Func(Of Func(Of Func(Of Integer, Integer), Func(Of Integer, Integer)), Func(Of Integer, Integer))) = Function(y) Function(f) Function(x) f(y(y)(f))(x)

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Sunday, August 19, 2007 10:03 PM by Dion

Hi Scott,

Could you please show us an example where you have a stored Procedure similar to the following, how to extract the value returned.

create Procedure sp_Customers

@country nvarchar(30)

as

set nocount on

select * from Customers

where country = @country

return 1

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Monday, August 20, 2007 1:45 AM by ScottGu

Hi Vikram,

>>>>>>> Where is the connection string stored when we use LINQ to SQL. How can we change the connection string.

By default the LINQ to SQL designer stores and retrieves the connection-string from the application's web.config file.  If you want to programmatically override this, you can pass a connection-string as an argument to the constructor of the Datacontext object.

Hope this helps,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Monday, August 20, 2007 1:47 AM by ScottGu

Hi Rick,

>>>>>>> What are the plans for handling updates to the Result object returned from a SP where the Result is from an underlying join? Are there Insert, Update, Delete methods on the Result object that can be pointed back to SPs that can handle this?

You can create a new Data Model object whose values are populated as the result of an underlying Join.  You could then use SPROCs for the update/insert/delete scenario to re-shred the values into separate tables.

Hope this helps,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Monday, August 20, 2007 1:49 AM by ScottGu

Hi Scott,

>>>>>> this has been a great series.  Just one thing seems to be missing.  How do we dispose of the DataContext class that we create for the SQL database?  For example, the NorthwindDataContext class is an IDisposable object, but if I actually call Dispose on it then I get ObjectDisposed exceptions when I access the data that came from that NorthwindDataContext.  Doesn't that leave huge amounts of resources open if I've made many calls to my database?

The Datacontext object actually doesn't hold open any connections to the database - so you don't have to explictly dispose of it.  Instead, it retrieves new connections from the connection pool only when it needs them, and then returns them as soon as it is done.

Hope this helps,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Monday, August 20, 2007 1:50 AM by ScottGu

Hi Mehfuz,

>>>>>> Cool! Now tell me, i see that you can drop a particular sp on a particular class in the designer, but what if someone drops a SP on to a wrong class, let's say someone drops the a CusOrderHistory on to the Order class instead of Product in the designer. shouldn't there be any validation , that will use some sort of  reflection to validate design-time.

If you try and drop a SP on a data model class whose shape doesn't match the output of the SPROC, then the designer will automatically raise a "schema doesn't match" error.

Hope this helps,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Monday, August 20, 2007 1:51 AM by ScottGu

Hi Omar,

>>>>>> If an SP executes a dynamic query and returns rows from a table, can I explicitly map the result of the SP to an object?  Moreover, if an SP returns fields from different tables by inner joining them in a dynamic SQL, how can I map it to proper object?

Yes - you can map the return of an SP to a different object - even one composed from a JOIN from multiple tables.

Hope this helps,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Monday, August 20, 2007 1:56 AM by ScottGu

Hi Howard,

>>>>> It makes for great demos where all the SQL is hidden away and all that. I looked into how I could add application level security into the datacontext - so that the datacontext knows how to restrict queries based on the user. For example i want to restrict the current user on northwind to customerID='ALFKI' (and related tables, e.g. orders), and if that user tried to modify a url e.g. CustomerID=AROUT he'd get no data.

The DataContext class allows you to customize default Load() methods for each entity you define in your data model.  This provides the ability for you to insert custom security logic to implement a security policy check should you want.

Note that an even better approach, though, would be to encapsulate the DataContext class with your own containing facade:

public class ProductManager {

  private NorthwindDataContext = new NorthwindDataContext();

  public List<Product> GetProductsBySomeQuery(string param) {

      // implement security check here and then return result

  }

}

This pattern allows you to fully secure access from anyone trying to retrieve the data.

Hope this helps,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Tuesday, August 21, 2007 10:22 PM by algoaddict

can i use linq in order to create user defined objects as SP, triggers etc in managed code for sql server ?

for instance, instead of using this code:

using System;

using System.Data.SqlTypes;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

public class StoredProcedures

{

  [Microsoft.SqlServer.Server.SqlProcedure]

  public static void PriceSum(out SqlInt32 value)

  {

     using(SqlConnection connection = new SqlConnection("context connection=true"))

     {

        value = 0;

        connection.Open();

        SqlCommand command = new SqlCommand("SELECT Price FROM Products", connection);

        SqlDataReader reader = command.ExecuteReader();

        using (reader)

        {

           while( reader.Read() )

           {

              value += reader.GetSqlInt32(0);

           }

        }        

     }

  }

}

i can use any linq code for creating SP, triggers, user-defined methods, compile everything and deploy in sql server ?

does the sql server clr has accsess to linq in the bcl ?

is there any link to guide me how its done ?

thanx

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Thursday, September 6, 2007 5:33 PM by Matt

Hi,

Calling stored procedures works well to load up basic entities.  However...

I don't know if anyone else has tried, but calling stored procedures for types that use InheritanceMapping doesn't work.  "Object not set to an instance of an object" (or whatever).  I'm attempting to fake a wide table for InheritanceMapping by using joins in the stored procedure. If anyone has managed to get this to work I'd be interested in hearing what modifications were needed.  My current workaround is to use a view instead of a table or stored procedure.

Thanks,

Matt.

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Saturday, September 8, 2007 3:42 PM by ScottGu

Hi Matt,

>>>>>>>> I don't know if anyone else has tried, but calling stored procedures for types that use InheritanceMapping doesn't work.  "Object not set to an instance of an object" (or whatever).  I'm attempting to fake a wide table for InheritanceMapping by using joins in the stored procedure. If anyone has managed to get this to work I'd be interested in hearing what modifications were needed.  My current workaround is to use a view instead of a table or stored procedure.

I haven't tried this yet with single table inheritance.  Have you tried posting a question in the LINQ forum on this: forums.microsoft.com/.../showforum.aspx

Thanks,

Scott

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Monday, September 10, 2007 4:40 AM by Kevin Farrio

I use IMultiResult to get two results from two tables in one sp and bind them to two dgvs. I found if I use var categories = result.GetResult<Category>(), and then dataGridView1.DataSource = categories, there'll be an exception. But if I use .ToList() after the method GetResult<Category>(), it successful.

Why? I know that the LINQ to SQL is lazy-execute but I think if I use .DataSource = categories, it'll be executed.

# re: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

Saturday, September 15, 2007 5:00 AM by Marcel

Hi,

I try to use this in VB. I have a dbml Countries. In this dbml I have a store procedure. The RAW XML look like this:

 <Function Name="dbo.SC_GetDataFromCountries" Method="SC_GetDataFromCountries">

   <Parameter Name="country" Type="System.String" DbType="NVarChar(50)" />

   <ElementType Name="SC_GetDataFromCountriesResult">

     <Column Name="id" Type="System.Int64" DbType="BigInt NOT NULL" CanBeNull="false" />

     <Column Name="city" Type="System.String" DbType="NVarChar(100)" CanBeNull="true" />

     <Column Name="region" Type="System.String" DbType="NVarChar(100)" CanBeNull="true" />

     <Column Name="address" Type="System.String" DbType="NVarChar(100)" CanBeNull="true" />

     <Column Name="zipcode" Type="System.String" DbType="NVarChar(10)" CanBeNull="true" />

   </ElementType>

 </Function>

When I use:

Dim db = New CountriesDataContext

dim data db.SC_GetDataFromCountries("2")

The function is not recognized. Also the intellisense is not showing the function? When I drop a table it is no problem and I can easly make the query?

Is this a bug or do I something wrong?