How to implement in linq an sql-like : select a.*, b.field1 as b_field from a join b on a.joinkey=b.join and receive c# datatable?

Question

My aim is to : linq-join two c# DataTables and to select all columns from the first table(without enumerating the columns) and only 1 column from the second and obtain result as DataTable:

  var result = from t in tt.AsEnumerable() join a in aa.AsEnumerable() on t.Field<DateTime>("time") equals a.Field<DateTime>("time")

 select new { t, aa_col1 = a.Field<int>("col1") }    
                ;

YET the result of that join can't be converted to DataTable.

The underlying idea of the issue is : not to enumerate all columns of tt manually while composing linq-join ( the number of tt columns may be large and dynamic)

I tried to solve the problem applying result.CopyToDataTable() method , but the compiler produced error: Type < anonymous type System.Data.DataRow t , int aa_col1> can't be used as parameter of type T in universal method "DataTableExtensions.CopyToDataTable(IEnumerable)"

Beneath is shown the code for the test tables tt and aa:

            DataTable tt = new DataTable();
            tt.Columns.Add("time", typeof(DateTime));
            tt.Columns.Add("col1", typeof(int));
            tt.Columns.Add("col2", typeof(int));
            tt.Columns.Add("col3", typeof(int));


            DataRow dr = tt.NewRow();
            dr["time"] = new DateTime(2018,10,5);     dr["col1"] = 24;            dr["col2"] = 14;            dr["col3"] = 15;
            tt.Rows.Add(dr);

            dr = tt.NewRow();
            dr["time"] = new DateTime(2018, 10, 6);  dr["col1"] = 4;            dr["col2"] = 43;            dr["col3"] = 58;
            tt.Rows.Add(dr);

            dr = tt.NewRow();
            dr["time"] = new DateTime(2018, 10, 6); dr["col1"] = 6; dr["col2"] = 3; dr["col3"] = 78;
            tt.Rows.Add(dr);

            dr = tt.NewRow();
            dr["time"] = new DateTime(2018, 10, 7);   dr["col1"] = 1; dr["col2"] = 4; dr["col3"] = 5;
            tt.Rows.Add(dr);

            //    -----
            DataTable aa = new DataTable();
            aa.Columns.Add("time", typeof(DateTime));
            aa.Columns.Add("col1", typeof(int));
            aa.Columns.Add("col2", typeof(int));
            aa.Columns.Add("col3", typeof(int));


            DataRow rr = aa.NewRow();
            rr["time"] = new DateTime(2018, 10, 4);
            rr["col1"] = 6; rr["col2"] = 34; rr["col3"] = 66;
            aa.Rows.Add(rr);

            rr = aa.NewRow();
            rr["time"] = new DateTime(2018, 10, 5);   rr["col1"] = 7; rr["col2"] = 43; rr["col3"] = 98;
            aa.Rows.Add(rr);

            rr = aa.NewRow();
            rr["time"] = new DateTime(2018, 10, 6);  rr["col1"] = 6; rr["col2"] = 3; rr["col3"] = 3;
            aa.Rows.Add(rr);

            rr = aa.NewRow();
            rr["time"] = new DateTime(2018, 10, 7); rr["col1"] = 16; rr["col2"] = 65; rr["col3"] = 12;
            aa.Rows.Add(rr);

The linq-join produces the type : {System.Linq.Enumerable.d__38f__AnonymousType0>}

See the figures showing "var result" in detail:

https://yadi.sk/i/X8K9HIq5hnavCg

and https://yadi.sk/i/MaViq23zqpRDXw

and https://yadi.sk/i/Re0DFJdVl02RjA

The target result of join should be a table that contains all columns from tt and first column of aa - aa.col1 :

  time             col1   col2  col3  aa_col1
   05.10.2018 0:00    24    14    15    7
   06.10.2018 0:00    4     43    58    6
   06.10.2018 0:00    6      3    78    6
   07.10.2018 0:00    1      4     5    16

Your query is returning collection each of which has a DataRow and another value. You can't directly add a column to a datarow, so you will need to add the rows to a DataTable, and then add each extra value. — stuartd, Apr 29 '19 at 11:06
Tahnk you Stuartd! can you somehow express your point in terms of linq? — Redballs Ishchenko, Apr 29 '19 at 14:02
I suggest you use your linq statement to get the results, then convert them into your desired format. I never use DataTables so I don't know offhand how you would do the conversion. — stuartd, Apr 29 '19 at 14:29

score 1 · Answer 1 · answered Apr 29 '19 at 20:46

So I had an extension method for doing joins with DataTables that took an anonymous object consisting of DataRows and creating a new DataTable holding the joined data, but being able to project some of the join tables is nice, so I added that. It uses Reflection so performance is not ideal, but unless you are doing this a lot, it should be fine.

The code uses a number of extension methods I have for making Reflection easier and working with DataTables and IEnumerables.

public static class DataTableJoinExt {
    // ***
    // *** Type Extensions
    // ***
    public static List<MemberInfo> GetPropertiesOrFields(this Type t, BindingFlags bf = BindingFlags.Public | BindingFlags.Instance) =>
        t.GetMembers(bf).Where(mi => mi.MemberType == MemberTypes.Field || mi.MemberType == MemberTypes.Property).ToList();

    // ***
    // *** MemberInfo Extensions
    // ***
    public static Type GetMemberType(this MemberInfo member) {
        switch (member) {
            case FieldInfo mfi:
                return mfi.FieldType;
            case PropertyInfo mpi:
                return mpi.PropertyType;
            case EventInfo mei:
                return mei.EventHandlerType;
            default:
                throw new ArgumentException("MemberInfo must be if type FieldInfo, PropertyInfo or EventInfo", nameof(member));
        }
    }

    public static object GetValue(this MemberInfo member, object srcObject) {
        switch (member) {
            case FieldInfo mfi:
                return mfi.GetValue(srcObject);
            case PropertyInfo mpi:
                return mpi.GetValue(srcObject);
            default:
                throw new ArgumentException("MemberInfo must be of type FieldInfo or PropertyInfo", nameof(member));
        }
    }
    public static T GetValue<T>(this MemberInfo member, object srcObject) => (T)member.GetValue(srcObject);

    // ***
    // *** IEnumerable Extensions
    // ***

    public static IEnumerable<T> AsSingleton<T>(this T first) {
        yield return first;
    }

    // ***
    // *** DataTable Extensions
    // ***
    public static IEnumerable<DataColumn> DataColumns(this DataTable aTable) => aTable.Columns.Cast<DataColumn>();
    public static IEnumerable<string> ColumnNames(this DataTable aTable) => aTable.DataColumns().Select(dc => dc.ColumnName);

    // Create new DataTable from LINQ join results on DataTable
    // Expect T to be anonymous object of form new { [DataRow or other] d1, [DataRow or other] d2, ... }
    public static DataTable FlattenToDataTable<T>(this IEnumerable<T> src) {
        var res = new DataTable();
        if (src.Any()) {
            var firstRow = src.First();
            var memberInfos = typeof(T).GetPropertiesOrFields();
            var allDC = memberInfos.SelectMany(mi => (mi.GetMemberType() == typeof(DataRow)) ? mi.GetValue<DataRow>(firstRow).Table.DataColumns() : new DataColumn(mi.Name, mi.GetMemberType()).AsSingleton());

            foreach (var dc in allDC) {
                var newColumnName = dc.ColumnName;
                if (res.ColumnNames().Contains(newColumnName)) {
                    var suffixNumber = 1;
                    while (res.ColumnNames().Contains($"{newColumnName}.{suffixNumber}"))
                        ++suffixNumber;
                    newColumnName = $"{newColumnName}.{suffixNumber}";
                }
                res.Columns.Add(new DataColumn(newColumnName, dc.DataType));
            }

            foreach (var objRows in src)
                res.Rows.Add(memberInfos.SelectMany(mi => (mi.GetMemberType() == typeof(DataRow)) ? mi.GetValue<DataRow>(objRows).ItemArray : mi.GetValue(objRows).AsSingleton()).ToArray());
        }
        return res;
    }
}

Now with this extension defined, you can do:

var result = (from t in tt.AsEnumerable()
              join a in aa.AsEnumerable() on t.Field<DateTime>("time") equals a.Field<DateTime>("time")
              select new { t, aa_col1 = a.Field<int>("col1") }).FlattenToDataTable();

Net_Mage - your extension works perfectly , it can even perform: select * from a join b on a.id= b.id — Redballs Ishchenko, Apr 30 '19 at 09:37
Net_Mage , I read you post https://stackoverflow.com/a/49245786/10996602 on how to convert plain SQL to linq-sql. Think I've met a soul-mate that is enjoying to implement plain SQL queries via LINQ as well ))) — Redballs Ishchenko, Apr 30 '19 at 09:47
@RedballsIshchenko Yes, I vastly prefer LINQ to SQL. See this code for [full outer join](https://stackoverflow.com/a/43669055/2557128) :) — NetMage, Apr 30 '19 at 17:39

How to implement in linq an sql-like : select a.*, b.field1 as b_field from a join b on a.joinkey=b.join and receive c# datatable?

1 Answers1