LINQ to SQL FAQ – LinqDataSource

LinqDataSource is a new ASP.NET control in VS2008 that supports using you application’s DataContext to select, insert, update and delete data.

Beyond the Demos

While using this I found the following issues:

1. LinqDataSource (LDS) supports partial selects from Linq objects: e.g. selecting just CustomerID and CompanyName from Customers and not * is okay. However, this only works when doing read-only views – if you want to perform any insert/update/delete you have to select [table].* – this makes sense since LDS needs to construct the full object for any insert/update/deletes. It does however, generate efficient SQL – and will not update all columns if only one has changed.

2. If like me you are using a specialised DataContext, it can be incompatible with LinqDataSource for update/delete/insert operations. LinqDataSource only supports Table<T> objects in the datacontext. I have a modified TableView<T> – and even though TableView supports the ITable interface, and T is the generated LINQ class, it fails.

Workaround

This problem can be hacked by using the insecure (original) datacontext in the LinqDataSource – this still contains the original Table<T>.  However, the table is now insecure for select/insert/update/delete. The select I fixed by handling the LinqDataSource.Selecting() event. There is no obvious solution to fixing the update/insert/deletes at present. One possible approach is to define stored procedures for these functions rather than use the default LINQ-to-SQL behaviour. I’ve yet to test this, and it means writing a lot of procedures I’d rather not write.

3. This leads onto the last wrinkle – retuning Nothing/null to a LINQ query in Selecting event will make LinqDataSource assume you didn’t actually want to override the basic behaviour and will return the default select.

Advertisements

LINQ to SQL FAQ – Bulk Updates and Deletes

LINQ to SQL is mainly aimed at reading, querying grouping and sorting data. It can update, insert and delete individual records, but there is no direct support for bulk operations that are common in SQL. For example, there is no LINQ to SQL statement for this T-SQL:

DELETE Products WHERE ProductCategory=’Example’;

The basic solution is to do the SELECT and then delete – e.g. :

var toDelete = from p in db.Products
                      where p.ProductCategory=="example"
                      select p;
db.Products.DeleteAllOnSubmit(toDelete);
db.SubmitChanges();

The drawback of this method is that each of the records has to be loaded over the wire – which in this example is not necessary – imagine if the delete is a million records, and you’ll understand this approach is not scalable or even ideal.

Stored Procedures

A much better approach is of course to use a parameterised stored procedure, e.g.

db.SPDeleteProductsByCategory("Example");

and this then becomes accessible through the data context.

Direct SQL

The final alternative is to use a direct SQL command, executed through the ExecuteCommand method of the DataContext. This supports parameters, so you could equally do this:

const string DeleteSQL = "DELETE Products WHERE ProductCategory={0}";
db.ExecuteCommand(DeleteSQL, "Example");

Note the {0} entry in the SQL command – that’s the parameter, just like it is in string formatting. "Hold on!" I hear web security experts shout, you’re not passing an unsafe string into SQL are you? That’s so dangerous! What if someone types malicious values into a search box that does horrible things?

Well, breathe easy – this is covered. Those parameters are properly parameterised by the ExecuteCommand – so what is generated is "… WHERE ProductCategory = @p1" and the value passed as a SQL parameter.

Note however there is a bug with this ExecuteCommand (and also I believe for ExecuteQuery) where nulls are not properly handled by the function. See this bug report for more details.

Third Party Extensions to LINQ to SQL

Some people have been developing .Update and .Delete extensions to LINQ to SQL. See Aney’s blog and Zhao’s blog for more information.

LINQ to SQL FAQ – NULLs

Beware of the NULL

Null values are always something to be wary of in SQL, and LINQ to SQL is no different.

For example, given following expression on the Customers table in Northwind returns two rows:

var qry = from c in Customers where c.Country=="UK" && c.Fax == null select c;

However this query returns no rows!

string fax = null;
var qry = from c in Customers where c.Country=="UK"
   
&& c.Fax == fax select c;

Looking at the generated SQL (try LINQpad), the reason is that in SQL the first query has the filter

([t0].[Fax] IS NULL)

and the second query uses a parameter:

([t0].[Fax] = @p1)

In C# (and VB) you can say null == null, but in SQL NULL != anything-whatsoever-including-null

See http://blogs.msdn.com/dinesh.kulkarni/archive/2006/07/16/667968.aspx

 Workaround

There is a way to compensate for this I spotted in the sample queries with LINQPAD: using object.Equals(p1, p2) will correctly emit the correct SQL :

string fax = null;
var qry = from c in Customers where c.Country=="UK"
   
&& object.Equals (c.Fax, fax) select c;

LINQ to SQL FAQ – Associations to Views

Here are a few things I learned while developing for LINQ to SQL.

Associations to Views

Views can be added to the designed and manual associations can be added between them and other tables/views. However, the association will not create a parent/child property in the code unless you modify the view properties to define "primary keys".

Example: using NorthWind, create a blank LINQ-to-SQL model. Drag the Orders table and the Order_Subtotals view onto the designer, and create an association between "OrderID" on each.

Now compile – you should see a warning from the compiler that Order_Subtotal does not have primary keys and therefore the relationship could not be coded.

Solution: in Order_Subtotal object, set the IsPrimaryKey = true for column "OrderID". This will enabled the relationship to be created.