Flatten Linq to XML query result from dynamic data

In a database the data is stored dynamically. That means the metadata defines classes and attributes. The user data is stored in object and object attribute tables.

To simulate how the data is represented, I created a linq query in LinqPAD:

    XElement test = XElement.Parse (
@"<DynaDaten>
  <CLS ID='99' CODE='T_PERSON' NAME='T_PERSON' DESCRIPTION='PERSON'/>
  <CLS_ATTR ID='101' CLS_ID='99' CODE='ID' NAME='ID'/>
  <CLS_ATTR ID='102' CLS_ID='99' CODE='FirstName' NAME='FirstName'/>
  <CLS_ATTR ID='103' CLS_ID='99' CODE='LastName' NAME='LastName'/>
  <OBJ ID='200' NAME='T_PERSON' CLS_ID='99' CLSNAME='T_PERSON'/>
  <OBJ_ATTR ID='301' OBJ_ID='200' CLS_ID='99' CLS_ATTR_ID='101' VALUE='1111'/>
  <OBJ_ATTR ID='302' OBJ_ID='200' CLS_ID='99' CLS_ATTR_ID='102' VALUE='John'/>
  <OBJ_ATTR ID='303' OBJ_ID='200' CLS_ID='99' CLS_ATTR_ID='103' VALUE='Wayne'/>
  <OBJ ID='201' NAME='T_PERSON' CLS_ID='99' CLSNAME='T_PERSON'/>
  <OBJ_ATTR ID='304' OBJ_ID='201' CLS_ID='99' CLS_ATTR_ID='101' VALUE='1112'/>
  <OBJ_ATTR ID='305' OBJ_ID='201' CLS_ID='99' CLS_ATTR_ID='102' VALUE='Marilyn'/>
  <OBJ_ATTR ID='306' OBJ_ID='201' CLS_ID='99' CLS_ATTR_ID='103' VALUE='Monroe'/>
  <OBJ ID='202' NAME='T_PERSON' CLS_ID='99' CLSNAME='T_PERSON'/>
  <OBJ_ATTR ID='307' OBJ_ID='202' CLS_ID='99' CLS_ATTR_ID='101' VALUE='1113'/>
  <OBJ_ATTR ID='308' OBJ_ID='202' CLS_ID='99' CLS_ATTR_ID='102' VALUE='James'/>
  <OBJ_ATTR ID='309' OBJ_ID='202' CLS_ID='99' CLS_ATTR_ID='103' VALUE='Dean'/>
</DynaDaten>
");

var flattened = from obis in test.Descendants("OBJ")
  select new {
  ObjID = (int)obis.Attribute("ID"),
  Fields = from classes in test.Descendants("CLS_ATTR") 
  join objs in test.Descendants("OBJ_ATTR") on (int)classes.Attribute("ID") equals (int)objs.Attribute("CLS_ATTR_ID")
  where (int)obis.Attribute("ID") == (int)objs.Attribute("OBJ_ID")
  orderby (int)objs.Attribute("OBJ_ID"), (int)objs.Attribute("ID")
  select new
  {
    Id = (string)objs.Attribute("ID"),
    Name = (string)classes.Attribute("NAME"),
    Value = (string)objs.Attribute("VALUE") 
  }
  };
flattened.ToList();
flattened.Dump("flattened");

The query name implies that the result is flattened, but it is not:

5IEnumerable<> (3 items)  
ObjID Fields 
200
 5IEnumerable<> (3 items)  
Id Name Value 
301
 ID
 1111

302
 FirstName
 John

303
 LastName
 Wayne


201
 5IEnumerable<> (3 items)  
Id Name Value 
304
 ID
 1112

305
 FirstName
 Marilyn

306
 LastName
 Monroe


202
 5IEnumerable<> (3 items)  
Id Name Value 
307
 ID
 1113

308
 FirstName
 James

309
 LastName
 Dean

What I would expect as a result is this:

ObjectID    ID      FirstName LastName
200      1111   John      Wayne
201      1112   Marilyn  Monroe
202      1113   James      Dean

Does anyone know how to get the expected result with a linq query? Note that the number of attributes varies depending what class is queried. But the result should be limited to one class at a time to avoid jagged results.

Thanks

ansaurus

tags:

views:

answers:

Flatten Linq to XML query result from dynamic data

related questions