How to use LINQ to XML together with LINQ to SQL.
Today I’ll give you an example on how easy it is to create XML using LINQ to XML in conjunction with LINQ to SQL.
I will use the data and the classes defined in my last post:
"How to use LINQ to SQL without using the designer generated classes"
So setup everything from that post. Then clear out all code in the Main method. (The full code including DataContext classes etc. is at the end of this post)
You now have two tables that are not related in the database, we have created more readable column/properties names.
So, for example, say that we wish to list all the records released before 2003 and show information about this in XML form.
Simply run this code:
String cs = @"Data Source=<your server>;Initial Catalog=<your database>;Integrated Security=SSPI";
SqlConnection con = new SqlConnection(cs);
ArtistRecordDataContext dc = new ArtistRecordDataContext(con);
// This will create a query that gets all records released earlier than 2003
// and present them as XML in a ascending order, showing information about it.
XElement oldiesXmlQuery = new XElement("Records",
new XElement("Oldies",
from o in dc.Record
where o.Year < 2003
orderby o.Year ascending
select new XElement("Record",
new XAttribute("Id", o.RecordId),
new XElement("Name", o.RecordName),
new XElement("Year", o.Year),
new XElement("Artist", o.Artist.ArtistName)
)));
Console.WriteLine(oldiesXmlQuery.ToString());
This should produce the following XML:
<Records>
<Oldies>
<Record Id="5">
<Name>The Debut</Name>
<Year>1995</Year>
<Artist>Tommy Rocker</Artist>
</Record>
<Record Id="3">
<Name>Column Love</Name>
<Year>1998</Year>
<Artist>The Tables</Artist>
</Record>
<Record Id="1">
<Name>Rockfest</Name>
<Year>2001</Year>
<Artist>Tommy Rocker</Artist>
</Record>
</Oldies>
</Records>
Or say that you wish to list all artists and their respective records, simply run the following code:
String cs = @"Data Source=<your server>;Initial Catalog=<your database>;Integrated Security=SSPI";
SqlConnection con = new SqlConnection(cs);
ArtistRecordDataContext dc = new ArtistRecordDataContext(con);
// This will create a query that gets all artists, show information about the artist
// and then query for all records made by that artist and presenting the info for each record.
XElement artistXmlQuery = new XElement("Artists",
from a in dc.Artist
select new XElement("Artist",
new XAttribute("Id", a.ArtistId),
new XElement("Name", a.ArtistName),
new XElement("Alias", a.ArtistAlias),
new XElement("Records",
new XComment("Ordered by release year"),
from r in a.Records
orderby r.Year ascending
select new XElement("Record",
new XAttribute("Id", r.RecordId),
new XElement("Name", r.RecordName),
new XElement("Year", r.Year)
))));
Console.WriteLine(artistXmlQuery.ToString());
This should produce the following XML:
<Artists>
<Artist Id="1">
<Name>Tommy Rocker</Name>
<Alias>The Rock</Alias>
<Records>
<!--Ordered by release year-->
<Record Id="5">
<Name>The Debut</Name>
<Year>1995</Year>
</Record>
<Record Id="1">
<Name>Rockfest</Name>
<Year>2001</Year>
</Record>
<Record Id="2">
<Name>Rockfest II</Name>
<Year>2005</Year>
</Record>
</Records>
</Artist>
<Artist Id="2">
<Name>The Tables</Name>
<Alias />
<Records>
<!--Ordered by release year-->
<Record Id="3">
<Name>Column Love</Name>
<Year>1998</Year>
</Record>
<Record Id="4">
<Name>Key party</Name>
<Year>2003</Year>
</Record>
</Records>
</Artist>
</Artists>
As you can see, not that hard J
More info
"Language-Integrated Query (LINQ) - LINQ to XML"
https://msdn.microsoft.com/en-us/library/bb387098.aspx
Full code:
#region Table, Support classes and DataContext classes
// Here we are saying that the class Record maps to the datbase table Records
[Table(Name = "Records")]
public class Record
{
[Column(Name = "rid", IsPrimaryKey = true)] // Column "rid" in database is mapped to "RecordId"
public int RecordId;
[Column(Name = "aid", CanBeNull = false)]
public int ArtistId;
[Column(Name = "name", CanBeNull = false)]
public string RecordName;
[Column(Name = "releaseyear", CanBeNull = false)]
public int Year;
// This allows us to get the related Artist for the Record at hand
private EntityRef<Artist> _Artist;
[Association(Storage = "_Artist", ThisKey = "ArtistId", OtherKey = "ArtistId")]
public Artist Artist
{
get { return this._Artist.Entity; }
set { this._Artist.Entity = value; }
}
}
[Table(Name = "Artists")]
public class Artist
{
[Column(Name = "aid", IsPrimaryKey = true)]
public int ArtistId;
[Column(Name = "name", CanBeNull = false)]
public string ArtistName;
[Column(Name = "Alias")]
public string ArtistAlias;
// Here we create the EntitySet that lets us have an Artist.Record list, this will be populated automatically
// since we have mapped "ArtistId" in this class with the "ArtistId" in the Record class.
private EntitySet<Record> _Records;
[Association(Storage = "_Records", ThisKey = "ArtistId", OtherKey = "ArtistId")]
public EntitySet<Record> Records
{
get { return this._Records; }
set { this._Records.Assign(value); }
}
}
public class ArtistRecordDataContext : DataContext
{
// Inherit from the DataContext class.
// One constructor accepts an SqlConnection, or we can pass the connectionstring directly.
public ArtistRecordDataContext(string cs) : base(cs) { }
public ArtistRecordDataContext(SqlConnection con) : base(con) { }
// Create the tables for the database (datacontext)
public Table<Artist> Artist;
public Table<Record> Record;
}
#endregion
class Program
{
static void Main(string[] args)
{
String cs = @"Data Source=<your server>;Initial Catalog=<your database>;Integrated Security=SSPI";
SqlConnection con = new SqlConnection(cs);
ArtistRecordDataContext dc = new ArtistRecordDataContext(con);
// This will create a query that gets all records released earlier than 2003
// and present them as XML in a ascending order, showing information about it.
XElement oldiesXmlQuery = new XElement("Records",
new XElement("Oldies",
from o in dc.Record
where o.Year < 2003
orderby o.Year ascending
select new XElement("Record",
new XAttribute("Id", o.RecordId),
new XElement("Name", o.RecordName),
new XElement("Year", o.Year),
new XElement("Artist", o.Artist.ArtistName)
)));
Console.WriteLine(oldiesXmlQuery.ToString());
Console.WriteLine();
// This will create a query that gets all artists, show information about the artist
// and then query for all records made by that artist and presenting the info for each record.
XElement artistXmlQuery = new XElement("Artists",
from a in dc.Artist
select new XElement("Artist",
new XAttribute("Id", a.ArtistId),
new XElement("Name", a.ArtistName),
new XElement("Alias", a.ArtistAlias),
new XElement("Records",
new XComment("Ordered by release year"),
from r in a.Records
orderby r.Year ascending
select new XElement("Record",
new XAttribute("Id", r.RecordId),
new XElement("Name", r.RecordName),
new XElement("Year", r.Year)
))));
Console.WriteLine(artistXmlQuery.ToString());
}
}