One thing I really love about Linq is how succinctly you can write import and export code. I've had to do this many times before Linq and it always felt kludgy no matter how clean you did it (Especially building xml documents). I think the decision to bake a set based syntax into C# and VB.NET was a great idea. So here is yet another Linq code sample, aren't there already a million of them out there??

Exporting data from a SQL Server table to xml using Linq2Sql and Link2Xml:

TestDataContext dataContext =
    new TestDataContext(@"Data Source=localhost;
        Initial Catalog=Test;Integrated Security=True");

XDocument export = new XDocument(
    new XElement(
        "clients",
            new XComment("This is a comment!"),
            from client in dataContext.Clients
            orderby client.State, client.City 
            select new XElement(
                "client",
                    new XAttribute("id", client.Id.ToString()),
                    new XElement("name", new XText(client.Name)),
                    new XElement("address", new XText(client.Address)),
                    new XElement("address2", new XText(client.Address2)),
                    new XElement("city", new XText(client.City)),
                    new XElement("state", new XText(client.State)),
                    new XElement("zip", new XText(client.Zip)),
                    new XElement("comments", new XCData(
                        string.Format("{0} is based out of {1}.", 
                        client.Name, client.State)))
                )
            )
        );
export.Save(@"D:\Temp\Clients.xml");

Importing data from an xml document to a SQL Server table using Linq2Xml and Linq2Sql (And as Anon pointed out we can do a direct cast since the XAttribute (And XElement) class define explicit conversion operators for primitive types. On that note we also dont need to check if the XElement or XAttribute is null. The Linq2Sql entity defines fields (Or properties) as nullable types and the XElement and XAttribute classes define conversion operators for nullable primitive types. So if an attribute or element does not exist the entity value is simply set to null.):

TestDataContext dataContext =
    new TestDataContext(@"Data Source=localhost;
        Initial Catalog=Test;Integrated Security=True");

XDocument document = XDocument.Load(@"D:\Temp\Clients.xml");

var clients = from client in document.Element("clients").Elements("client")
    select new Client()
    {
        Id = (int)client.Attribute("id"),
        Name = (string)client.Element("name"),
        Address = (string)client.Element("address"),
        Address2 = (string)client.Element("address2"),
        City = (string)client.Element("city"),
        State = (string)client.Element("state"),
        Zip = (string)client.Element("zip")
    };

foreach (var client in clients)
    dataContext.Clients.InsertOnSubmit(client);

dataContext.SubmitChanges();

If you have a host name set in your hosts file pointing to your loop back address and you are trying to connect to your local SQL Server instance with this host name using Windows Auth (SSPI) you may receive one of the following errors depending on how you have things configured:

"Login failed for user ''. The user is not associated with a trusted SQL Server connection."

Or

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

There could be a number of things causing this error (here, here, here and here) but it in the specific scenario noted above it's probably that an alias is not set for the SQL Server. I'm not sure exactly why or how this works or why the above scenario would have an issue but creating an alias seems to resolve it. In fact if you specify "localhost" or the loopback address as the server name it works fine (Without the alias). Also when connecting to a remote SQL Server with a host name defined in the hosts file it works fine as well. hmmmm.... I really couldn't find any info on this specific scenario and creating the alias seems to resolve it so I finally gave up. If you have any more info on why this would happen please leave a comment. You can set the alias as follows:

1. 1) Open the SQL Server Configuration Manager under Start|Programs| Microsoft SQL Server 20xx| Configuration Tools| SQL Server Configuration Manager.
2. 2) Under the SQL Native Client Configuration node select Aliases.
3. 3) Right-click and select “New Alias…”.
4. 4) Enter the following information and press OK:
1. Alias Name: sql.somedomain.com
2. Port No: 1433
3. Protocol: TCP/IP
4. Server: 127.0.0.1

The connection string would be as follows:

server=sql.somedomain.com;database=SomeDatabase;Integrated Security=SSPI

Common Table Expressions (CTE's) are a handy feature introduced in SQL Server 2005. Making them more handy is the fact that they can be used recursively. This opens up a number of possibilities; one of which being a set based way to build a virtual table out of a comma separated list and filter off of that virtual table. Lets say for example we have a comma separated list of ID's we want to use to filter the results of a query.

DECLARE @Filter varchar(1000)

SET @Filter = '4,8,23,56,72';

You'll notice the semicolon at the end of the SET statement. The next statement in this example will be the CTE and TSQL requires the CTE and the previous statement to be separated by a semicolon. Now for the the first rendition of the CTE.

WITH Filter(FilterId, Position) AS
(
    SELECT 
    CAST(SUBSTRING(@Filter, 1, 
        CASE CHARINDEX(',', @Filter) 
        WHEN 0 THEN LEN(@Filter) 
        ELSE CHARINDEX(',', @Filter) - 1 END
    ) AS int) AS FilterId,
    CHARINDEX(',', @Filter) AS Position
    WHERE @Filter IS NOT NULL AND LEN(@Filter) > 0
)

SELECT * FROM Filter

The CTE, so far, only contains one query which will serve as the "anchor". If you run this it only returns one result, the very first id and the current position in the string:

Next we will add in the recursive query. This query must come immediatly after the anchor and be separated from it by the UNION ALL operator. You can have multiple anchors and multiple recursive queries which can make use of other combination operators, but anchors must be grouped before the recursive queries and the two groups must be separated by the UNION ALL operator.

WITH Filter(FilterId, Position) AS
(
    SELECT 
    CAST(SUBSTRING(@Filter, 1, 
        CASE CHARINDEX(',', @Filter) 
        WHEN 0 THEN LEN(@Filter) 
        ELSE CHARINDEX(',', @Filter) - 1 END
    ) AS int) AS FilterId,
    CHARINDEX(',', @Filter) AS Position
    WHERE @Filter IS NOT NULL AND LEN(@Filter) > 0

    UNION ALL

    SELECT 
    CAST(SUBSTRING(@Filter, 
        Position + 1, 
        CASE CHARINDEX(',', @Filter, Position + 1) 
        WHEN 0 THEN LEN(@Filter) - Position 
        ELSE CHARINDEX(',', @Filter, Position + 1) - Position - 1 END
    ) AS int) AS FilterId,
    CHARINDEX(',', @Filter, Position + 1) AS Position
    FROM Filter WHERE Position > 0
)

SELECT * FROM Filter

The recursive query makes use of the last position to "move" to the next id in the string. When it has hit the end of the string the last position is set to zero which terminates the recursion. The results are as follows:

You'll also notice that the query that selects from the CTE immediately follows it; this is another requirement for CTE's. Now lets select some employees from the AdventureWorks database:

DECLARE @Filter varchar(1000)

SET @Filter = '4,8,23,56,72';

WITH Filter(FilterId, Position) AS
(
    SELECT 
    CAST(SUBSTRING(@Filter, 
        1, 
        CASE CHARINDEX(',', @Filter) 
        WHEN 0 THEN LEN(@Filter) 
        ELSE CHARINDEX(',', @Filter) - 1 END
    ) AS int) AS FilterId,
    CHARINDEX(',', @Filter) AS Position
    WHERE @Filter IS NOT NULL AND LEN(@Filter) > 0

    UNION ALL

    SELECT 
    CAST(SUBSTRING(@Filter, 
        Position + 1, 
        CASE CHARINDEX(',', @Filter, Position + 1) 
        WHEN 0 THEN LEN(@Filter) - Position 
        ELSE CHARINDEX(',', @Filter, Position + 1) - Position - 1 END
    ) AS int) AS FilterId,
    CHARINDEX(',', @Filter, Position + 1) AS Position
    FROM Filter WHERE Position > 0
)

SELECT EmployeeID, LoginID, Title 
FROM HumanResources.Employee
WHERE EmployeeID IN (SELECT FilterId FROM Filter)

And here are the results: