LINQ create generic List of nestet objects

2096 views c#
5

How can I get a List<Type1> which includes another List<Type2> from another List<Type3>?

Here is the situation:

I have a List<DbStruncture>. Each entry includes a DatabaseStructure

public partial class DatabaseStructure
{
    public string TableSchema { get; set; }
    public string TableName { get; set; }
    public string ColumnName { get; set; }
    public bool? IsPrimaryKey { get; set; }
}

I also have

public class Table
{
    public string Name { get; set; }
    public string Schema { get; set; }
    public List<Column> Columns { get; set; }
}

public class Column
{
    public string Name { get; set; }
    public bool? IsPrimaryKey { get; set; }
}

Now I want to fill the Data from the List<DatabaseStructure> into a List<Table> which includes a List<Column> with all the Columns of that Table.

I tried it with LINQ and this is how far I got:

var query =
            from t in result
            group t.TableName by t.TableName
            into tn
            select new
            {
                Table = tn.Key,
                Schema = from s in result where s.TableName == tn.Key select s.TableSchema.First(),
                Columns = from c in result where c.TableName == tn.Key select new Column
                {
                    Name = c.ColumnName,
                    IsPrimaryKey = c.IsPrimaryKey
                }
            };

The problem with my solution is, that my query is not a generic List...

Can anybody point me into the right direction? Is LINW the right way here? If yes, how do I get the wanted result?

Thanks in advance

answered question

2 Answers

1

  1. Preface: I prefer (and recommend) using Linq with the Extension Method syntax instead of using the from,group,into keywords because it's more expressive and if you need to do more advanced Linq operations you'll need to use Extension Methods anyway.
  2. To begin with, your input is denormalized (I presume the output of running SELECT ... FROM INFORMATION_SCHEMA.COLUMNS) where each row contains repeated table information, so use GroupBy to group the rows together by their table identifier (don't forget to use both the Table Schema and Table Name to uniquely identify a table!)
  3. Then convert each group (IGrouping<TKey: (TableSchema,TableName), TElement: DatabaseStructure>) into a Table object.
  4. Then populate the Table.Columns list by performing an inner Select from the IGrouping group and then .ToList() to get a concrete List<Column> object.

My expression:

List<DatabaseStructure> input = ...

List<Table> tables = input
    .GroupBy( dbs => new { dbs.TableSchema, dbs.TableName } )
    .Select( grp => new Table()
    {
        Name = grp.Key.TableName,
        Schema = grp.Key.TableSchema,
        Columns = grp
            .Select( col => new Column()
            {
                Name = col.Name,
                IsPrimaryKey = col.IsPrimaryKey
            } )
            .ToList()
    } )
    .ToList()

posted this
2

OK, just found the answer myself.

Here it is:

var query =
            (from t in result
             group t.TableName by t.TableName
            into tn
             select new Table
             {
                 Name = tn.Key,
                 Schema = (from s in result where s.TableName == tn.Key select s.TableSchema).First(),
                 Columns = (from c in result
                            where c.TableName == tn.Key
                            select new Column
                            {
                                Name = c.ColumnName,
                                IsPrimaryKey = c.IsPrimaryKey
                            }).ToList()
             });

posted this

Have an answer?

JD

Please login first before posting an answer.