LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

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 seven parts in this series:

In my last two posts (Part 6 and Part 7) I demonstrated how you can optionally use database stored procedures (SPROCs) to query, insert, update and delete data using a LINQ to SQL data model. 

One of the questions a few people have asked me since doing these posts has been "what if I want total control over the SQL expressions used by LINQ to SQL - but I don't want to use SPROCs to-do it?"  Today's blog post will cover that - and discuss how you can use custom SQL expressions that you provide to populate your LINQ to SQL data model classes, as well as perform insert, updates, and deletes.

Using LINQ Query Expressions with LINQ to SQL

For the purposes of this blog post, let's assume we've used the LINQ to SQL ORM designer in VS 2008 to define a set of data model classes like below for the Northwind database (note: read Part 2 of this series to learn how to use the LINQ to SQL ORM designer to do this):

In Part 3 of this blog series I covered how you can use the new LINQ language support in VB and C# to query the above data model classes and return back a set of objects that represent the rows/columns in the database. 

For example, we could add a "GetProductsByCategory" helper method to the DataContext class of our data model that uses a LINQ query to return back Product objects from the database:

VB:

C#:

Once we've defined our encapsulated LINQ helper method, we can then write code like below that uses it to retrieve the products, and iterate over the results:

VB:

 

When the LINQ expression within our "GetProductsByCategory" method is evaluated, the LINQ to SQL ORM will automatically execute dynamic SQL to retrieve the Product data and populate the Product objects.  You can use the LINQ to SQL Debug Visualizer to see in the debugger how this LINQ expression is ultimately evaluated.

Using Custom SQL Queries with LINQ to SQL

In our sample above we didn't have to write any SQL code to query the database and retrieve back strongly-typed Product objects.  Instead, the LINQ to SQL ORM automatically translated the LINQ expression to SQL for us and evaluated it against the database. 

But what if we wanted total control over the SQL that is run against our database, and don't want LINQ to SQL to-do it for us in this scenario?  One way to accomplish this would be to use a SPROC like I discussed in Part 6 and Part 7 of this series.  The other approach is to use the "ExecuteQuery" helper method on the DataContext base class and use a custom SQL expression that we provide.

Using the ExecuteQuery Method

The ExecuteQuery method takes a SQL query expression as an argument, along with a set of parameter values that we can use to optionally substitute values into the query.  Using it we can execute any raw SQL we want against the database (including custom JOINs across multiple tables).

What makes the ExecuteQuery method really useful is that it allows you to specify how you want the return values of your SQL expression to be typed.  You can do this either by passing a type-object as a parameter to the method, or by using a generic-based version of the method. 

For example, we could change the GetProductsByCategory() helper method we created earlier - using a LINQ expression - to instead use the ExecuteQuery method to execute our own raw SQL expression against the database and return "Product" objects as a result:

VB:

C#:

We can then call the GetProductsByCategory() helper method using the exact same code as before:

But unlike before it will be our custom SQL expression that will run against the database - and not dynamic SQL executed in response to using a LINQ query expression.

Custom SQL Expressions and Object Tracking for Updates

By default when you retrieve a data model object using LINQ to SQL, it will track all changes and updates you make to it.  If you call the "SubmitChanges()" method on the DataContext class, it will then transactionally persist all of the updates back to the database.  I cover this in more depth in Part 4 of this LINQ to SQL series.

One of the cool features of the ExecuteQuery() method is that it can fully participate in this object tracking and update model.  For example, we could write the code below to retrieve all products from a specific category and discount their prices by 10%:

Because we typed the return value of our ExecuteQuery call in the GetProductsByCategory method to be of type "Product", LINQ to SQL knows to track the Product objects we returned from it.  When we call "SubmitChanges()" on the context object they will be persisted back to the database.

Custom SQL Expressions with Custom Classes

The ExecuteQuery() method allows you to specify any class as the return type of a SQL query.  The class does not have to be created using the LINQ to SQL ORM designer, or implement any custom interface - you can pass in any plain old class to it.

For example, I could define a new ProductSummary class that has a subset of Product properties like below (notice the use of the new C# Automatic Properties feature):

We could then create a GetProductSummariesByCategory() helper method on our NorthwindDataContext that returns results based on it.  Notice how our SQL statement below requests just the subset of product values we need - the ExecuteQuery method then handles automatically setting these on the ProductSummay objects it returns:

We can then invoke this helper method and iterate over its results using the code below:

Custom SQL Expressions for Inserts/Updates/Deletes

In addition to using custom SQL expressions for queries, we can also execute them to perform custom Insert/Update/Delete logic.

We can accomplish this by creating the appropriate partial Insert/Update/Delete method for the entity we want to change in a partial class on our DataContext.  We can then use the ExecuteCommand method on the DataContext base class to write the SQL we want to execute.  For example, to override the Delete behavior for Product classes we could define this DeleteProduct partial method:

And now if we write the below code to remove a specific Product instance from our database, LINQ to SQL will call the DeleteProduct method - which will cause our custom SQL to execute in place of the default dynamic SQL that LINQ to SQL would otherwise use:

Summary

The LINQ to SQL ORM automatically generates and executes dynamic SQL to perform queries, updates, inserts and deletes against a database.

For advanced scenarios, or cases where you want total control over the SQL query/command executed, you also have the ability to customize the ORM to use either SPROCs, or your own custom SQL Expressions, instead.  This provides you with a great deal of flexibility when building and extending your data access layer.

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. 

Hope this helps,

Scott

Published Monday, August 27, 2007 1:04 AM by ScottGu

Comments

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Monday, August 27, 2007 4:46 AM by Owen

As usual, brilliant post.

I have one question though. Is there a way to programmatically create the dbml file on the fly?

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Monday, August 27, 2007 5:16 AM by Vikram

Hi scott,

I could not follow one thing in your example. In one of the images (third last) you have written the following code foreach(ProductSummary p in Products)

{

}

When you defined the class for ProductSummary there was no mention of the product class. How will the compiler(and VS 2008 designer) know that ProductSummary is a subset of product class?

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Monday, August 27, 2007 5:32 AM by NMarian

When using ExecuteQuery or ExecuteCommand method, is there any built-in protection against SQL injection attacks?

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Monday, August 27, 2007 5:35 AM by ScottGu

Hi Vikram,

>>>>>> I could not follow one thing in your example. In one of the images (third last) you have written the following code foreach(ProductSummary p in Products) { } When you defined the class for ProductSummary there was no mention of the product class

I didn't need to have a relationship between the two.  When calling ExecuteQuery it will automatically match up results based on the names of the columns returned and the property names of the type specified.  

Hope this helps,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Monday, August 27, 2007 7:09 AM by Oren Novotny

Scott,

In a future post, do you think you could talk about the interplay between LINQ to SQL, the ASP.Net cache and SqlCacheDependency's?  

I recall some issues doing just that with the May CTP last year, and it's a topic that I haven't yet seen many posts on.

If I have a site where I want to cache medium to large amounts of reference data, but still allow defered loading when needed, how/when should the Entities be attached/detached from a DataContext?  What kind of concurrency issues are there with potentially multiple threads trying to defer-load the same instance; would the last one to detach from the cache "win"?  

I think there's quite a bit of material along those lines to be covered and it would be great if your series could include it.

Thanks!

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Monday, August 27, 2007 7:28 AM by Ralph Shillington

I would concur with NMarian that one the surface it appears that the example queries are prone to an injection attack, or to put it another way the wrapper functions trust that the supplied paramter values and not malicious SQL code.  Of course in this specific example the parameters are of type int, so it would be rater difficult to supply a single integer value that would be malicious.  But the prinicple of the point remains.

Does this facility expose the command parameters collection so as to avoid this vulnerability?

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Monday, August 27, 2007 7:43 AM by Bernhard Grojer

Thanks for another great blog entry.

One question: How is mapping between the custom class and the result from the query done? Through reflection and equal property and columnname?

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Monday, August 27, 2007 2:59 PM by WooBoo

Hi Scott

I’ve been playing lately with LINQ to SQL and found a problem. I’ll describe shortly:

I have a DB with few tables. All tables have uniqueidentifier as a primary key which is RowGuid. So I drag/drop my tables to dbml file and everything looks fine. Try some queries – very nice a little upside-down but still nice.

I found a problem with inserting data. When I try to add new object and submit to db it inserts empty guid into ID column. After I change Auto Generated Value to true and Auto-Sync to OnInsert (was set-up to default) I get an sqlexception: { Explicit conversion from data type uniqueidentifier to decimal is not allowed.}

I can assign new guid to ID columns just after creating new instances but it adds new meaningless lines of code…

Is there any other (set-up not coding) way to solve this issue in my project?

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Monday, August 27, 2007 4:41 PM by D. Figuerres

Just a short "WOW" -- every time you are answering more of my linq questions!!  great stuff!!

One Linq to rule them all, One Linq to bind them

but we can skip the "Dark Lord" bits ;-)

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

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

Hi Oren,

>>>>> In a future post, do you think you could talk about the interplay between LINQ to SQL, the ASP.Net cache and SqlCacheDependency's?  

This is a good suggestion - I will put it on my list!

Thanks,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

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

Hi NMarien/Ralph,

>>>>> Does this facility expose the command parameters collection so as to avoid [SQL Injection] vulnerability?

LINQ queries are themselves safe from SQL injection (because the queries are type-safe).  When passing in raw SQL queries like above you'll want to make sure you are a little more careful.

In my simple testing, it looks like the parameters passed in the ExecuteQuery and ExecuteCommand methods are automatically SQL encoded based on the value being supplied.  So if you pass in a string with a ' character, it will automatically SQL escape it to ''.  I believe a similar policy is used for other data types like DateTimes, Decimals, etc.

Hope this helps,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

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

Hi Oren,

>>>>>> If I have a site where I want to cache medium to large amounts of reference data, but still allow defered loading when needed, how/when should the Entities be attached/detached from a DataContext?  What kind of concurrency issues are there with potentially multiple threads trying to defer-load the same instance; would the last one to detach from the cache "win"?  

These last two questions (handling concurrency, and then doing so with offline entities that are reconnected back to the database) are ones I'll be dedicating two blog posts on.  I have two more LINQ to SQL posts that I want to get out before tackling these ones (since they are going to be the hardest ones for me to write!) - but I'll definitely be covering them in the future.

Thanks,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

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

Hi Bernhard,

>>>>>> One question: How is mapping between the custom class and the result from the query done? Through reflection and equal property and columnname?

I believe the mapping is done as a reflection lookup on the class being provided, and then mapped to the column names returned from the SQL query.

Hope this helps,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

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

Hi Ben,

>>>>> Would it be possible you can assign someone in your staff to create "One" document (perhaps PDF or Word.Doc) that contains everything? this could be done now and add the new stuff later as each new blogs comes along or wait till the series are finished!

This is something I've been thinking about doing at some point.  I might try and do something like this in another month or two once we get closer to RTM.  Something like "Scott's little book on LINQ". :-)

Thanks,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

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

Hi WooBoo,

>>>>>> I have a DB with few tables. All tables have uniqueidentifier as a primary key which is RowGuid. I found a problem with inserting data. When I try to add new object and submit to db it inserts empty guid into ID column. After I change Auto Generated Value to true and Auto-Sync to OnInsert (was set-up to default) I get an sqlexception: { Explicit conversion from data type uniqueidentifier to decimal is not allowed.}

Unfortunately there was a bug in Beta2 which causes RowGUID primary keys to not be handled correctly.  This will be fixed for RTM - until then you unfortunately need to write code to work around it.

Hope this helps,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Monday, August 27, 2007 7:45 PM by Clayton Powell

You mention in one of your answers that reflection is used to map fields used by LINQ-to-SQL. What sort of impact will this have on the use of obfuscators such as DotFuscator? Will we need to exclude all ORM classes from obfuscation?

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Monday, August 27, 2007 7:57 PM by Clayton Powell

I was wondering if you could give some advice on some practical aspects for building a good business logic layer.

The current DataSet approach works well. The dataset has a collection for each table that you can keep adding records to then write them all out when finished. Our code often needs to gradually add rows to a collection as they are required (without reading them all in). How would you do this sort of thing with LINQ?

Each time you do a query with LINQ it goes back to the database. So the new objects created won't match ones you read earlier and it won't have updates you have made to the objects but not committed yet.

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Monday, August 27, 2007 8:33 PM by Matt

Scott, can you shed some light on LINQ to Entities and how this differs to LINQ to SQL? Also, can you confirm what LINQ providers will be available at the release of VS 2008?

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Monday, August 27, 2007 8:51 PM by D. Figuerres

by the way how do i create a Where ID in ( Select ID from ...)

in dlinq ??

Where ID not in ( Select PersonID from PersonXEvent Where EventID=@EventID  )

??

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Monday, August 27, 2007 11:47 PM by Vikas

Scott,

I have got a problem with the add and attach methods of the Datacontext. At the very basic, while working on a multi tier application you fetch the data and pass it on to front end (In this process we lose the datacontext and associated tracking). Once the user modifies the data at the front end you pass it to business tier and after enforcing business rule you proceed to save the entities to the database. Since we don't have any data context tracking, we have to use Add or Attach method of the datacontext for Insert or Update method to enable it to start tracking the objects. The problem is that the Add/Attach method traverses the relationship and start tracking all the related method. If you call the data context submit method it will generate insert/update insert statements for all the objects in the hierarchy, even though you are interested in saving only few of them. How can you overcome this problem? Is there any way to tell datacontext to stop tracking the related objects?

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Tuesday, August 28, 2007 1:59 AM by Ben Hayat

>> I might try and do something like this in another month or two once we get closer to RTM

Scott, will the next version be the "RTM" version? If yes, will the MSDN subscriber get that version as an official VS2008 version? or do we have to wait till Feb 2008 for MSDN copy?

Secondly, I haven't heard you mentioning anything about Linq and SQL2008. Is everything we do now with linq and SQL2005 going to be the same with SQL2008?

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Tuesday, August 28, 2007 8:19 AM by Majid

Hi Scott,

I use to not to exploit foreach iterator in my codes, so I create a List(Of T) from an IQueryable(Of T) using ToList() method.

I want to use LINQ-to-SQL to create a class library in which I intend to limit the developers to access or view the database tables name. Consequently, I've done the following:

public class MyList : List<DB_TableName> { }

// Getting all items from DB_TableName

MyList myList = (MyList)CompiledQuery_getAllItems(db_context).ToList();

but I got InvalidCastException at runtime.

How can I change my code to resolve this exception or what's your solution to hide the database table name.

Thanks.

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Tuesday, August 28, 2007 8:26 AM by Boris Yeltsin

Does this not defeat one of the main features of LINQ though? For me, the biggest benefit is strongly-typed query construction.

I'm interested in another thing.. how do I dynamically build a WHERE clause with LINQ?

e.g.

var f = From p in Products Select p

Then I want something like:

If QueryType = QueryTypes.Category Then

f += Where p.CategoryId = CategoryId

Else

f += Where p.GenreId = GenreId

End If

Is it possible?

Thanks!

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Tuesday, August 28, 2007 11:59 AM by Larry Charlton

Scott,

Any chance you could discuss multiple result sets from a stored procedure / SQL.  For example:

Create Procedure GetCustomer

 @id INT

AS

SELECT * FROM Customers WHERE customerId=@id;

SELECT * FROM CustomerTypes;

When editing a record, I commonly get the record and all lookup tables with a single query to reduce network traffic.  In ADO.Net I use data adapters to populate a dataset with all the tables, how would I do something similar in LINQ?

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Tuesday, August 28, 2007 1:35 PM by Pete

Boris, yes, that's possible.

var f = From p in Products Select p

If QueryType = QueryTypes.Category Then

f = From p in f Where p.CategoryId = categoryId

'etc.

You can compose queries together like this and then have the final query execute just once thanks to LINQ's deferred execution.

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Wednesday, August 29, 2007 4:01 AM by Owen

Thanks Scott and Granville :)

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Wednesday, August 29, 2007 9:02 AM by sunnyglen

Hi Scott,

Would you say sth about 'LINQ to Entity' against 'LINQ to SQL'.

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Wednesday, August 29, 2007 1:03 PM by Dioni

Hi Scott,

Great series. One question, though. How do I connect the output of one of those IENumberable methods (i.e. GetProductsByCategory) to a grid through an ObjectDataSource, in such a way that the grid is automatically sortable?

What I have is:

[System.ComponentModel.DataObject]

class myClassName

{

       [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]

       public IEnumerable myMethodName(string myParam)

          {

             ....

          }

}

Thanks a lot,

Dioni

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Wednesday, August 29, 2007 2:32 PM by Josh Stodola

Another fabulous LINQ post, Scott!  I wouldn't mind if this series never ended.  And the "Scott's little book on LINQ" is a very very good idea.  That belongs over there on the navigational bar below "My ASP.NET 2.0 Tips, Tricks, Recipes and Gotchas Series"

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Thursday, August 30, 2007 2:48 AM by ahking

I wonder if .net 3.5 would be integrated into vista sp1?

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Thursday, August 30, 2007 4:25 AM by ScottGu

Hi Clayton,

>>>>>> You mention in one of your answers that reflection is used to map fields used by LINQ-to-SQL. What sort of impact will this have on the use of obfuscators such as DotFuscator? Will we need to exclude all ORM classes from obfuscation?

Code obfuscators like DotFuscator help scramble the internal implementation of classes - but keep their external APIs the same.  Since in this case LINQ to SQL is using the external API to map properties it will work just fine even if the code is obfuscated.

Hope this helps,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Thursday, August 30, 2007 4:26 AM by ScottGu

Hi Clayton,

>>>>>> The current DataSet approach works well. The dataset has a collection for each table that you can keep adding records to then write them all out when finished. Our code often needs to gradually add rows to a collection as they are required (without reading them all in). How would you do this sort of thing with LINQ?

You can add new objects into a DataContext's tables collections without hitting the database.  Instead, you'll hit the database when you call SubmitChanges() on the DataContext.

Hope this helps,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Thursday, August 30, 2007 4:29 AM by ScottGu

Hi Matt,

>>>>> Scott, can you shed some light on LINQ to Entities and how this differs to LINQ to SQL? Also, can you confirm what LINQ providers will be available at the release of VS 2008?

I'll probably be doing a LINQ to Entities series in a few months and will cover it in more detail then. In the meantime, you might want to check out Mike Taulty's blog here: mtaulty.com/.../default.aspx  He is doing a ton of good LINQ to Entities posts.

When VS 2008 and .NET 3.5 ships later this year, there will be a LINQ to Objects, LINQ to XML, and LINQ to SQL providers in the box.  LINQ to Entities will then ship in calendar year 2008.

Hope this helps,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Thursday, August 30, 2007 4:31 AM by ScottGu

Hi D. Figuerres,

>>>>>> by the way how do i create a Where ID in ( Select ID from ...)

What I think you are looking for is how to handle a "contains" scenario.  Mike has a good blog post here that shows this in action: mtaulty.com/.../9552.aspx

Hope this helps,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Thursday, August 30, 2007 4:33 AM by ScottGu

Hi Eden,

>>>>>>> Would it be possible to provide some guidance on disconnected LINQ objects? So deal with scenarios where you have a data access layer and do not want to expose your UI to the DataContext.  What are best practices when doing this (or is it even advisable) and what are the problems one will encounter? So for example, if you have data layer objects that have methods like: IEnumerable<Product> LoadList() and Save(Product product). What problems may you experience when reattaching the objects to the LINQ DataContext? What problems do you run into with child collections and deletion of items? Would you suggest adding a Timestamp column to each table for optimistic locking in this scenario? And so on...

I'll be covering this more in an upcoming blog post (probably about 2 weeks away now).  In general I'd probably recommend adding a timestamp column to your tables to help with optimistic concurrency - this makes it much easier to track changes when they are disconnected (and avoids you having to round-trip old values).

Hope this helps,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Thursday, August 30, 2007 4:35 AM by ScottGu

Hi Ben,

>>>>>>> Scott, will the next version be the "RTM" version? If yes, will the MSDN subscriber get that version as an official VS2008 version? or do we have to wait till Feb 2008 for MSDN copy?

The next broad public version will be the RTM version.  We will, though, have a release candidate that we send out to a few folks before hand to test out last minute fixes/changes.  Once VS 2008 hits RTM, all MSDN subscribers will be able to download it immediately (and won't have to wait until Feb 2008 to get it).

>>>>>> Secondly, I haven't heard you mentioning anything about Linq and SQL2008. Is everything we do now with linq and SQL2005 going to be the same with SQL2008?

All of the features I've been showing work with both SQL 2005 and SQL 2008.

Hope this helps,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Thursday, August 30, 2007 4:41 AM by ScottGu

Hi Majid,

>>>>> I want to use LINQ-to-SQL to create a class library in which I intend to limit the developers to access or view the database tables name. Consequently, I've done the following:

One approach to limit a developer's access to the Datacontext is to create a wrapper class like so:

public StoreFrontFacade {

  private NorthwindDataContext db = new NorthwindDataContext();

  public Product GetProductByID(int productId) {

      return db.Products.Single(p=>p.ProductID == productId);

  }

  public List<Product> GetProductsByCategory(int categoryId) {

      return db.Products.Where(p=>p.CategoryID == categoryId).ToList();

  }

  public void AddProduct(Product p) {

      db.Products.Add(p);

  }

  public void SubmitChanges() {

      db.SubmitChanges();

  }

}

This enables you to only grant access to perform the database operations you want the developer consuming the class library to be able to-do.  You could then mark the NorthwindDataContext as an internal class - which means that only the StoreFrontFacade class can create it.

Hope this helps,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Thursday, August 30, 2007 4:48 AM by ScottGu

Hi Larry,

>>>>> Any chance you could discuss multiple result sets from a stored procedure / SQL.  

In my blog post on retrieving data from SPROCs here: weblogs.asp.net/.../linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx I talked about an interface called IMultipleResult.  You can use this in cases where a SPROC returns multiple types of values (for example: both Products and Categories depending on a parameter input).  You can also use this to handle cases where the SPROC returns multiple return sets of data.  You'd use the same pattern I showed in the article for this scenario - just retrieve the data based on the type you are retrieving and you should be good to go.

Hope this helps,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Thursday, August 30, 2007 4:50 AM by ScottGu

Hi Dioni,

>>>>>> Great series. One question, though. How do I connect the output of one of those IENumberable methods (i.e. GetProductsByCategory) to a grid through an ObjectDataSource, in such a way that the grid is automatically sortable?

You can add optional parameters to the class method that the ObjectDataSource calls.  These parameters include both paged index information as well as sort direction information.  You could then use these parameters as part of your LINQ query to page/sort the data.

Hope this helps,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Thursday, August 30, 2007 4:54 AM by ScottGu

Hi Steve,

>>>>>>> One question though, is it possible to intercept linq selects in the business layer and augment / validate them?Specifically I'd like to know if it is possible to add security to the expressions, so if at teh presentation layer we bound a grid to all products, we could then catch (within a partial class) this query and add in a link to their account so they can only view products they have access to?  If that i not possible, could you blog or point us to some examples / guidance patterns on how to gain the compile time / intellisense of linq, without exposing the entire db to arbitary selects from the presentation code - I can see how we can lock this down for insert / update / deletes, but not selects

One approach to limit a users/presentation developer's access to the Datacontext is to create a wrapper class like so:

public StoreFrontFacade {

 private NorthwindDataContext db = new NorthwindDataContext();

 public Product GetProductByID(int productId) {

     return db.Products.Single(p=>p.ProductID == productId);

 }

 public List<Product> GetProductsByCategory(int categoryId) {

     return db.Products.Where(p=>p.CategoryID == categoryId).ToList();

 }

 public void AddProduct(Product p) {

     db.Products.Add(p);

 }

 public void SubmitChanges() {

     db.SubmitChanges();

 }

}

This enables you to only grant access to perform the database operations you want the developer consuming the class library to be able to-do.  You could also then have the LINQ queries within this class automatically filter based on the current authenticated user's permissions.  You could then mark the NorthwindDataContext as an internal class - which means that only the StoreFrontFacade class/assembly can create it - and not the presentation tier directly.

Hope this helps,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Thursday, August 30, 2007 8:19 AM by Matt

>>>>>>>When VS 2008 and .NET 3.5 ships later this year, there will be a LINQ to Objects, LINQ to XML, and LINQ to SQL providers in the box.  LINQ to Entities will then ship in calendar year 2008.

Which of these providers will work with Oracle?

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Thursday, August 30, 2007 11:26 AM by ScottGu

Hi Matt,

>>>>>> Which of these providers will work with Oracle?

I know that there will be a LINQ to Entities provider that works with Oracle.  I'm not sure of an ETA for a LINQ to SQL provider that works with Oracle.

Hope this helps,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Thursday, August 30, 2007 2:01 PM by Michael

Hey Scott,

Great articles and examples, and kudos to you and your team - all the new functionality in 3.5 is fantastic!

Two questions -

1 - is it possible to devleop a way to intercept the SQL that is generated by linq to SQL update/Insert statements so that it can be logged, especially when an exception is thrown? I know you mentioned profiler but in many situations a developer can not use profiler, and in a production environment it is very helpful to know what SQL statement caused the exception, as SQL error messages are ambiguous most of the time. Could events be exposed on the base class that we can hook into to give us this information, and also to give us start/end execute time? This information is invaluable in a production environment and helps us debug problems very quickly without the need to run profiler.

2 - I have a table with a datetime column in it, and the column has a default value of GetUTCDate(). When I drag the table over to the designer, the "Auto Generated Value" property for the column is set to false. When I try to do an insert, the insert fails because the column value is trying to be set to 01/01/0001. When I manually set the Auto Generated Value to true, it works as expected, but if I regenerate the object of course I have to remember to keep resetting the property for all the columns set up like this. Is this a bug or am I doing something wrong?

Thanks,

Michael

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Friday, August 31, 2007 2:33 AM by Pini Usha

Hi,

I created a Linq to SQL and after a few days I had to change the DB schema (change one of the field name).

After running the code I got run time exception obviously because the change of the field name.

Is there any way to refresh the dbml like in web service after a change.

The only way I could think of (but didn't like) is to drop the table again on the designer.

Thanks, Pini.

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Friday, August 31, 2007 9:44 AM by monty

Is there a synonym for the SQL function CONVERT(...) in Linq? (i.e. Convert(varchar(2), "PA"))

I am attempting to solve a type impedance between nvarchar and varchar.

Specifically, I'm running Linq queries against a non-Unicode DB. Since Linq produces statements with Unicode parameters, SQL Server does not employ an index for the query, even though one is available.

The last comment in the following help article describes the problem:

ms-help://MS.VSCC.v90/MS.MSDNQTR.v90.en/wd_linqadonet/html/0a90c33f-7ed7-4501-ad5f-6224c5da8e9b.htm

Any insight's appreciated...

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Monday, September 3, 2007 7:37 AM by Pini Usha

How can i update my dbml in case of a change in the DB schema?

thanks.

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Tuesday, September 4, 2007 12:02 AM by James

Hi Scott,

One thing that has come up repeatedly here is serializing entity objects and deserializing and operate on them at a later stage, in a different OS process, different AppDomain, etc.

What implications does serialization/deserialization have for change tracking? Are LINQ entity objects serializable at all? Say I made two modifications to a deserialized object, will only those to modifications get sent as an UPDATE?

Thanks

James

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Thursday, September 6, 2007 12:57 PM by Ram

Scott,

How to join an object and a SQL table in LINQ?

For example, say I have a folder full of images and the folder link is stored in the database.  Now I would like to upload images onto the database using a LINQ query that will join a table in the SQL database and the images using the filename (filename of the images is numeric).  The table in the db would have two columns fileid, img.  I would be joining the table and the object (in this case For Each ImgFiles in My.Computer.filesystem.Getfiles( folder ) etc).  How to do this in LINQ?  I tried, but does not work.  I would appreciate if you could give me a working example.  Thanks.

Ram

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Friday, September 7, 2007 12:52 PM by Mihai Dinculescu

This article is just like the res, simply great.

But I have one question though. How can I make the ASP:LinqDataSource Control bind the GridView using a sproc that I already added to the DataContext ?

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Saturday, September 8, 2007 12:45 PM by ScottGu

Hi Pini,

>>>>>>> How can i update my dbml in case of a change in the DB schema?

If you database schema changes the easiest way to update your DBML is to use the LINQ to SQL designer and delete and then re-add the table that changed.

Hope this helps,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Saturday, September 8, 2007 12:46 PM by ScottGu

Hi Rob,

>>>>> One question though... I understand that the mapping could be done as either attributes that decorate the classes, or could be set from an XmlMappingSource xml file.  The second approach separates the mapping from our business object classes, and to me seems a little more elegant.  Are there any performance issues to consider when using an xml mapping file?  I take it that the mapping would be an additional overhead at runtime?

I believe LINQ to SQL always caches the meta-data mapping layer after it is first used - regardless of whether it is attribute based on external XML file based.  So there shouldn't be any performance differences between the two.

Hope this helps,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Saturday, September 8, 2007 12:48 PM by ScottGu

Hi Monty,

>>>>>>>> Is there a synonym for the SQL function CONVERT(...) in Linq? (i.e. Convert(varchar(2), "PA"))  I am attempting to solve a type impedance between nvarchar and varchar. Specifically, I'm running Linq queries against a non-Unicode DB. Since Linq produces statements with Unicode parameters, SQL Server does not employ an index for the query, even though one is available.

There are ways you can convert data types within a LINQ expression.  In general you should use the .NET Convert static class to help with these (as well as use standard cast operators).

Send me email (scottgu@microsoft.com) with a more detailed scenario if you are still stuck with this and I can have someone help you with it.

Thanks,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Saturday, September 8, 2007 12:50 PM by ScottGu

Hi James,

>>>>>>> What implications does serialization/deserialization have for change tracking? Are LINQ entity objects serializable at all? Say I made two modifications to a deserialized object, will only those to modifications get sent as an UPDATE?

There are a couple of different strategies that you can do to handle change tracking in a disconnected way.  Unfortunately they are hard to describe, though, in only a few sentences. <g>  

I'm going to be covering this much more in an upcoming blog post in this series that discusses the Attach() method and optimistic concurrency.

Thanks,

Scott

# re: LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

Saturday, September 8, 2007 2:55 PM by ScottGu

Hi Mihai,

>>>>>> How can I make the ASP:LinqDataSource Control bind the GridView using a sproc that I already added to the DataContext ?

I justed posted an update to this series that covers how you could do this using the LinqDataSource control's "Selecting" event: weblogs.asp.net/.../linq-to-sql-part-9-using-a-custom-linq-expression-with-the-lt-asp-linqdatasource-gt-control.aspx

Hope this helps,

Scott