How to merge two collections with adding new field inside it. [Dapper ORM with Query<dynamic>]

1
votes

I have two collections returned from Dapper Query

Table1_Collection = Query<dynamic>("SELECT * FROM Table1");    // I can make Select T1, *
Table2_Collection = Query<dynamic>("SELECT * FROM Table2");    // I can make Select T1, *

See comment above I can just fix it with adding Select T1 with query. but how to do that in .NET Collections.

Now I need to merge both Collections:

List<dynamic>CombinedCollection = new List<dynamic>();
CombinedCollection.AddRange(Table1_Collection);
CombinedCollection.AddRange(Table2_Collection);

Above collection contains columns in both table1 and table2. I need to add a field to that collection in .NET to specify which table that row belongs.

Something Like:

CombinedCollections.AddRange(Table1_Collections).CombineWith("T1");
CombinedCollections.AddRange(Table2_Collections).CombineWith("T2");

Note that for DataTables, It have Columns.Add(DataColumn) with default value. But in Dapper its only .NET Collections...

2
c#sql.netcollectionsdapper

2 Answers

1
votes

Adding a new property is very straight forward when we are using dynamics and ExpandoObject. To simplify the process, let's add a function to create and return ExpandoObject with the added property

public ExpandoObject GetDynamic(dynamic obj, string tableName)
{
      var exp = new ExpandoObject();
      var objnew = (IDictionary<String, Object>)obj;
      foreach (var item in objnew)
      {
          exp.TryAdd(item.Key, item.Value);
      }
      exp.TryAdd("Table", tableName);
      return exp;
}

Then try this:

CombinedCollections.AddRange(Table1_Collections.Select(t => GetDynamic(t, "T1")));
CombinedCollections.AddRange(Table2_Collections.Select(t => GetDynamic(t, "T2")));

However, you can also do something like this without using the ExpandoObject for even cleaner segregation:

CombinedCollections.AddRange(Table1_Collections.Select(t => new { t, Table = "T1" }));
CombinedCollections.AddRange(Table2_Collections.Select(t => new { t, Table = "T2" }));

This will give you a collection of elements having 2 properties: The row and the Table

1
votes

If you trying to add a default value you could do something like the below

 orderDetails = connection.Query("SELECT * FROM Table1").Select(x =>
 {
     dynamic y = x;
     y.table= "T1";
     return y;
 }).ToList();

However if you want a default value already instantiated it would be much easyier to change the query like you have mentioned or have a class with a default value so that instead of getting a dynamic back you get an instance of your class.

Edit:

If you really want the object bound / converted to a data-grid, then you would be better off following the expandoObject example that Anup Sharma, mentioned.

The below questions are related to this:

How can I use a List<Dynamic> as with DataGridView.DataSource?

Binding a GridView to a Dynamic or ExpandoObject object