LINQ to SQL FAQ – mapping functions and methods

When trying to write a LINQ to SQL query you have to be mindful of what is supported as a translation from VB/C# into SQL. For example you can specify somestring.Trim() and this is translated into LTRIM(RTRIM(SOMESTRING)).

A lot of the time a suck-it-and-see approach is okay, but it can introduce runtime errors into programs. Fortunately there is a reference guide to help at http://msdn.microsoft.com/en-us/library/bb386970.aspx 

One interesting find here is the SQLmethods class. This allows you to perform specific SQL methods that have no direct translation from CLR into LINQ to SQL.

Most of these are DateDiff computations, to permit you to compute the differences between two date values directly in SQL.

LINQ to SQL – Links

LINQ to SQL FAQ – XML columns in SQL

XML datatype columns were added to Microsoft SQL server in version 2005. If your database contains an XML column, LINQ to SQL v1 will by default generate this as an XElement property in your table class.

However, LINQ to SQL does not appear to support ANY translations of XML queries on XElement to SQL – this mans that the following statement :

var filtered = from t in Table
                    where t.XmlCol.Descendants("SomeElement").Value == somevar
                    select t

will fail at runtime as the LINQ-to-SQL parser is unable to translate on our XML data column XmlCol query into an Xpath.

Workarounds

If the dataset isn’t large, it is possible to load all the values, and query them, e.g. :

var all = (from t in Table select t).ToList();
var filtered = from t in all
                    where t.XmlCol.Descendants("SomeElement").Value == somevar
                    select t

This includes the capability to query the XML using an Xpath query so that it is not necessary to load all the XML from an XML column to search or query it. This is obviously inefficient and not desirable.

The quick and dirty fix for this is to convert the XmlCol to a string and do a string search. This is possible but not really very safe since the search string is not using XML structure and can produce incorrect results.

Use a SQL Function ? Forget it

I thought that a way around this might be to create a SQL function that takes an XPath path and a value and can compare the XML Xpath query and the variable.

Alas this is not possible: SQL 2005 supports XMLcol.value(path, type) but does NOT support use of variables for ‘path’ position – so this cannot be written as a function – this is of course bizarre – it means all XPath queries have to be done either as fixed functions or as inline SQL. So, not only can we not query the XML column in LINQ to SQL we cannot even create a SQL scalar function to do this. It may be fixed in SQL 2008 as SQL support for XML data seems to have improved.

Updating XML Columns – Beware

A LINQ to SQL xml datatype column is represented as an XElement. If you modify this be careful as LINQ to SQL will not spot internal changes to XElement and save them to the database.

For example:

Dim myElement as XElement = Table.XMLdata
myElement.Value = "test"
db.SubmitChanges()

will not alter the instance XElement and so will not change the Table.XMLdata entry. The workaround for this is to write it back as a new element:

Dim myElement as XElement = Table.XMLdata
myElement.Value = "test"
Table.XMLdata = new XElement(myElement)
db.SubmitChanges()

Microsoft Efforts

This Oakleak article suggests that MS is aware of the inability to query XML fields. and a powerful extension called LINQ to Stored XML is under development. Let’s hope it arrives soon!

LINQ to SQL FAQ – Associations XML

Associations in LINQ to SQL dbml files are somewhat confusing. Here is my aide-memoir to how they work, having had to wrap my own head around these without much documentation on how they work..

Associations In Brief

  1. There are always two entries for any association, with the same name. They may be in different tables or in the same table in the reference is to the same table.
  2. It follows that there should always be a matching pair – otherwise you might get errors. If you add one, always add the reciprocal. If you remove one, always remove the reciprocal.
  3. To find the matching association:
      – use the Type attribute to locate the reciprocal table (from Table.Type.Name)
      – find an association in the reciprocal with the same name but where IsForeignKey is the reverse (e.g. find false if your association has it set to ‘true’ – see below)
  4. One association is always the parent (IsForeignKey is absent or "false"), and the other is the child (IsForeignKey="true") – even if one-to-one.
  5. If the relation is not One-to-Many (the default) the cardinality is always specified in the parent.
  6. The "Member" is how the association will appear inside the containing table: e.g. Orders is the member of the customer-order association in the Customer class. Ensure it does not conflict with
  7. If SQL specifies cascaded deletes on a child table the DeleteOnNull="true" attribute will be defined: this is always defined on the Child association.

ThisKey, OtherKey

  1. A parent association always specifies "OtherKey", which relates to the reciprocal table
  2. A child association always specifies "ThisKey" which relates to its own table
  3. A parent won’t specify "ThisKey" if the key is the Primary Key of the table
  4. A child association won’t specify "OtherKey" unless it isn’t the PrimaryKey on the other table.
  5. The value of the ThisKey and OtherKey attributes represent the Member value of a column – this is blank on the table unless you’ve renamed the column to something other than the database column name.
  6. If an association has multiple column references, these are listed in the attribute comma-separated values in the attribute value.

 

More later if I find any other things of note.

LINQ to SQL FAQ – Refreshing the dbml

How to Refresh the .dbml when the Database changes

I make many changes to my database while developing – adding or renaming columns, new tables, new views etc. The problem is that LINQ-to-SQL’s designer tool doesn’t have a Refresh button. The recommendation is that you delete the changed entity from the designer, and re-add it.

This is obviously a kludge by Microsoft, since this loses any changes you’ve made to the old entity, e.g. if you customise it by modifying the access, renaming mapped columns, or add associations e.g. between tables and views.

I also had another issue – my database has above 250 tables and views in it. If you drag all these onto the design surface, then it somewhat resembles meatballs in spaghetti – it’s unusable for design work.

Then bear in mind that if you want to create an association between a view and a table (see my other FAQ about this), so that the view becomes a child entity of the table in the code, you have to

  • try and find both tables in this massive diagram, and
  • somehow drag a relationship between them.

The designer is therefore utterly useless in anything beyond about 20 or so tables.

Solution

I decided to write a WinForm application to make managing my .dbml easier. Reading the .dbml is very easy as it’s just an XML file with a published schema. I also needed a way to read the database structure – I already had some code for reading a SQL database structure but it could not replicate the clever work that goes on when reading StoredProcedure output formats. I found that the simplest way was to use the SqlMetal.exe command line tool that comes in the .NET Framework to generate a new .dbml with all tables, views and stored procedures.

I then managed to write some code that compared the existing .dbml with the generated one and updated it accordingly. I may one day re-write this to make it available but it’s not completed.

Managing the DBML

To manage the .dbml my WinForm application just lists the tables and procedures as A-Z and shows the properties I wanted and allow me to create or modify the unsupported properties.

LINQ to SQL FAQ – Designer Issues

Visual Studio Tools

LINQ to SQL has a pretty designer view in Visual Studio 2008 which allows users to create modify and delete tables, views and associations. However, it has several deficiencies. Some of the features not supported by the designer (but part of LINQ-to-SQL) are listed in Dinesh’s blog.

My biggest gripe with the designer is that ALL the tables and views must appear on the same design surface – there is no way to create subset views (like in SQL manager) of the data context.

This is fine for small databases (like when Northwind is used for demonstrations), but put any serious database into the designer (along with all the views) and it becomes a real nightmare. Look at this picture on the right – this is the designer view for my current Anvil application.

The first issue is finding a table in this mess. It’s not easy. The second is trying to create an association between a table and a view – trying to drag and association between two columns which may not be on the same part of the screen is almost impossible. You can redesign this view and move tables about but this is not an easy process – and if I amend the .dbml outside the designer it may not be retained anyway, leading to more frustration. Something akin to SQL Enterprise Managers subset views of the database would be more usable here.

Workaround

You can access the XML source of the .dbml by right-clicking the .dbml file in VS and select "Open With" and then select "XML editor", and you’ll see the .dbml as an XML document. Editing the XML directly isn’t great though, and it’s easy to break the model, e.g. by failing to create matching association pairs.

Another issue with the design is that if you need to use the very useful inheritance feature (see here) then you will have to edit the .dbml code by hand – the properties don’t support it.

LINQ to SQL FAQ – Mapping Inheritance in LINQ

Mapping table entities to different types

By default LINQ-to-SQL will map each table to a single class, usually the singular name of the table. So for example the Employees table in NorthWind maps to an Employee class.

However, you may well be creating subclasses to deal with different types of order, for example, and you don’t want a plain old ‘Employee; object returned, you really want the subclass. This is supported in LINQ-to-SQL using the InheritanceMapping Attribute and the  IsDiscriminator setting on a column. It allows you to specify one field of the database table as a selector, and then return different classes based on the value of the selector.

I won’t go into a long example of this since David Hayden’s blog has a really nice one and it would be a waste of bandwidth to just rehash this.

For more info see the Microsoft web article on MSDN.