CODEWITHSUNDEEP
sqlxmllinq
user468452
user468452

Reputation: 41

linq to sql/xml - generate xml for linked tables

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?

Upvotes: 0

Views: 815

Answers (2)

user468452
user468452

Reputation: 41

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);
      }
    }
  }
}

Upvotes: 1

Ahmad Mageed
Ahmad Mageed

Reputation: 96477

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?

Upvotes: 0

Related Questions