Section 5.3. Create, Read, Update, and Delete with LINQ

5.3. Create, Read, Update, and Delete with LINQ

Most of the discussion thus far has focused on data querying, and not data modification. Don't be mistaken, LINQ provides full support for read/write data access, commonly referred to as CRUD.

Interestingly, while data is read using an SQL-like query language, data modification is approached using more traditional, object-oriented mechanisms. For example, to schedule the doctor mbl for call on November 30, 2006 in our SchedulingDocs database, we do two things. First, we add a new row to the object representing the Calls table:

db.Calls.Add( new Databases.Calls{Initials="mbl",
                                  DateOfCall=new DateTime(2006, 11, 30} );

Second, we flush the change back to the database:

db.SubmitChanges();

The first step makes a local, in-memory change only; the second step is what triggers the underlying SQL (or stored procedure) to update the database. LINQ to SQL will automatically generate the appropriate SQL for inserts , updates, and deletes, or interoperate with your custom stored procedures.

To delete a call, we find the corresponding object, remove it from the table, and update the database:

var del = from c in db.Calls
          where c.Initials == "mbl" && c.DateOfCall == new DateTime(2006, 11, 30)
          select c;

foreach (var c in del)
  db.Calls.Remove(c);

db.SubmitChanges();

Since there is at most one doctor on call for any given day, we know the above query will return exactly one record. In this case we can use the standard query operator Single , passing a lambda expression for the search criteria:

var call = db.Calls.Single( c => c.Initials == "mbl" &&
                                 c.DateOfCall == new DateTime(2006, 11, 30) );
db.Calls.Remove(call);
db.SubmitChanges();

If it's possible that the search may fail, use the query operator SingleOrDefault , and check the query result for null.

Finally, to update existing data, the approach is (1) query to find the corresponding objects, (2) update those objects, and (3) flush the changes. For example, if the doctor ay 's pager number changes to 53301, we update the database as follows :

var ay = db.Doctors.Single( d => d.Initials == "ay" );
ay.PagerNumber = 53301;
db.SubmitChanges();

The same logic applies to other LINQ scenarios, such as XML documents and DataSets. For example, with the typed DataSet SchedulingDocs (see Figure 2), scheduling a doctor on call is simply a matter of adding a new row to the Calls table:

ds.Calls.AddCallsRow( "mbl", new DateTime(2006, 11, 30) );

Much like the database objects, DataSets are a local, in-memory collection of objects. To persist your changes, an updated DataSet must be written to some durable medium, such as the file system or a database:

dbConn.Open();
callsAdapter.Update( ds.Calls );
dbConn.Close();

Here we re-open the connection, update the database to match, and close the connectionthe equivalent of db.SubmitChanges() . The key difference is that in the case of LINQ to SQL, the SQLMetal tool generates the necessary code to update the underlying database. In the case of DataSets and XML documents (and other flavors of LINQ), it's typically our responsibility to load the data, and consequently to persist it back.

5.4. LINQ to XML

From its beginnings, LINQ was designed to manipulate XML data as easily as it manipulates relational data. LINQ to XML represents a new API for XML-based development, equivalent in power to XPath and XQuery yet far simpler for most developers to use.

For example, let's assume the data source for our hospital scheduling application is an XML document stored in the file SchedulingDocs.xml . Here's the basic structure of the document:

<?xml version="1.0" standalone="yes"?>
<SchedulingDocs>
  <Calls>
     <Call>
        <Initials>mbl</Initials>
        <DateOfCall>2006-10-01T00:00:00-05:00</DateOfCall>
     </Call>
     .
     .
     .
  </Calls>
  <Doctors>
     <Doctor>
        <Initials>ay</Initials>
        <GivenFirstName>Amy</GivenFirstName>
        <FamilyLastName>Yang</FamilyLastName>
        <PagerNumber>53300</PagerNumber>
        <EmailAddress>ayang@uhospital.edu</EmailAddress>
        <StreetAddress>1400 Ridge Ave.</StreetAddress>
        <City>Evanston</City>
     </Doctor>
     .
     .
     .
  </Doctors>
  <Vacations>
     <Vacation>
        <Initials>jl</Initials>
        <DateOfDayOff>2006-10-03T00:00:00-05:00</DateOfDayOff>
     </Vacation>
     .
     .
     .
  </Vacations>
</SchedulingDocs>

Using LINQ, we load this document as follows :

import System.Xml.XLinq;  // LINQ to XML

XElement root, calls, doctors, vacations;

root

=

XElement.Load

("SchedulingDocs.xml");

calls

= root.Element("Calls");

doctors

= root.Element("Doctors");

vacations

= root.Element("Vacations");

We now have access to the three main elements of the XML document: calls , doctors , and vacations . To select all the doctors, it's a simple query expression:

var

docs

= from doc in doctors.Elements()
           select doc;

And to find just those doctors living in Chicago:

var

chicago

= from doc in doctors.Elements()
              where doc.Element("City").Value == "Chicago"
              orderby doc.Element("Initials").Value
              select doc;

As you can see, querying XML documents with LINQ is conceptually the same as that of relational databases, DataSets, and other objects. The difference is that the structure of the XML document must be taken into account, e.g., in this case the document's hierarchical design and its use of elements over attributes.

An important aspect of LINQ is the ability to easily transform data into other formats. In the world of XML, transformation is commonplace given the need to create XML documents as well as translate from one schema to another. For example, suppose we need to produce a new XML document containing just the names of the doctors, with their initials as an attribute:

<?xml version="1.0" standalone="yes"?>
<Doctors>
  <Doctor Initials="bb">Boswell, Bryan</Doctor>
  <Doctor Initials="lg">Goldstein, Luther</Doctor>
  .
  .
  .
</Doctors>

This document is easily produced by the following query, which simply projects new XElements :

var

docs

= from doc in doctors.Elements()
           orderby doc.Element("FamilyLastName").Value,
                   doc.Element("GivenFirstName").Value

select new XElement("Doctor",


new XAttribute("Initials", doc.Element("Initials").Value),


doc.Element("FamilyLastName").Value +


", " +


doc.Element("GivenFirstName").Value)

;

XElement

newroot

=

new XElement("Doctors", docs)

;

The last statement creates the root element <Doctors> , using the query to generate the <Doctor> sub-elements.

Finally, here's a real-world example of translating a text-based IIS logfile into an XML document. This example comes from a series of posts to the MSDN LINQ Project General Forum, "Transforming a TXT file into XML with LINQ to XML," http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=574140&SiteID=1. The logfile contains 0 or more lines of the form:

Time  IP-address  Method  URI  Status

For example:

#Software: Microsoft Internet Information Services 5.1
#Version: 1.0
#Date: 2006-06-23 12:37:18
#Fields: time c-ip cs-method cs-uri-stem sc-status
12:37:18 127.0.0.1 GET /cobrabca 404
12:37:25 127.0.0.1 GET /cobranca 401
.
.
.

Here's the LINQ query to produce an XML document from such a log:

var

logIIS

= new XElement("LogIIS",

from line in File.ReadAllLines("file.log")

where !line.StartsWith("#")
               let items = line.Split(' ')

select new XElement("Entry",


new XElement("Time", items[0]),


new XElement("IP", items[1]),


new XElement("Url", items[3]),


new XElement("Status", items[4])


)

);

LINQ over a text file? The next section will discuss this, and other examples, in more detail.

NOTE

The general focus of this Short Cut precludes an in-depth treatment of LINQ to XML. For more details, we encourage you to read the forthcoming Part 3 of this Short Cut series (expected Q4 2006), which focuses exclusively on LINQ to XML. Watch for an announcement at http://oreilly.com