linq to sql/xml - generate xml for linked tables

Question

i have alot of tables with alot of columns and want to generate xml using linq without having to specify the column names. here's a quick example:

users
---------------
user_id
name
email

user_addresses
---------------
address_id
user_id
city
state

this is the xml i want to generate with linq would look like

<user>
 <name>john</name>
 <email>[email protected]</email>
 <address>
  <city>charleston</city>
  <state>sc</state>
 </address>
 <address>
  <city>charlotte</city>
  <state>nc</state>
 </address>
</user>

so i'm guessing the code would look something like this:

var userxml = new XElement("user",
 from row in dc.Users where user.id == 5
 select (what do i put here??)
 );

i can do this for one table but can't figure out how to generate the xml for a linked table (like user_addresses).

any ideas?

user468452 · Accepted Answer · 2010-11-05 15:48:37Z

ok found a way to get the xml i want, but i have to specify the related table names in the query...which is good enough for now i guess. here's the code:

XElement root = new XElement("root",
    from row in dc.users
    where row.user_id == 5
    select new XElement("user",
        row.AsXElements(),
        new XElement("addresses",
            from row2 in dc.user_addresses
            where row2.user_id == 5
            select new XElement("address", row2.AsXElements())
        )
    )
);


// used to generate xml tags/elements named after the table column names
public static IEnumerable<XElement> AsXElements(this object source)
{
  if (source == null) throw new ArgumentNullException("source");

  foreach (System.Reflection.PropertyInfo prop in source.GetType().GetProperties())
  {
    object value = prop.GetValue(source, null);

    if (value != null)
    {
      bool isColumn = false;

      foreach (object obj in prop.GetCustomAttributes(true))
      {
        System.Data.Linq.Mapping.ColumnAttribute attribute = obj as System.Data.Linq.Mapping.ColumnAttribute;
        if (attribute != null)
        {
          isColumn = true;
          break;
        }
      }

      if (isColumn)
      {
        yield return new XElement(prop.Name, value);
      }
    }
  }
}

Ahmad Mageed · Accepted Answer · 2010-11-05 15:26:29Z

0

You need to use a join. Here's one way:

var query = from user in dc.Users
            from addr in dc.UserAddress
            where user.Id == addr.UserId
            select new XElement("user",
                new XElement("name", user.Name),
                new XElement("email", user.Email), 
                new XElement("address",
                    new XElement("city", addr.City),
                    new XElement("state", addr.State)));

foreach (var item in query)
    Console.WriteLine(item);

i have alot of tables with alot of columns and want to generate xml using linq without having to specify the column names.

Not quite sure how you want to achieve that. You need to state the column names that go into the XML. Even if you were to reflect over the field names, how would you filter the undesired fields out and structure them properly without specifying the column names? For example how would you setup the address part? You could get the fields by using this on your User and UserAddress classes: User.GetType().GetFields() and go through the Name of each field, but then what?

edited Nov 5, 2010 at 15:26

answered Nov 5, 2010 at 15:19

Ahmad Mageed

96.9k20 gold badges170 silver badges177 bronze badges

1 Comment

user468452 Over a year ago

hey thanks for the quick reply. for the site i'm working on i'm using xslt style sheets to generate html from the xml. so with xslt i'll be specifying the column names i want to show and the rest will be filtered out automatically. will also handle address formatting...etc in the style sheet. reflection is working great for generating the tag names from the column names, but i can't figure how do the same for the associated tables.

Collectives™ on Stack Overflow

linq to sql/xml - generate xml for linked tables

this is the xml i want to generate with linq would look like

so i'm guessing the code would look something like this:

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

this is the xml i want to generate with linq would look like

so i'm guessing the code would look something like this:

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related