LinqはサイコーだしLinqToObjectはサイコー。なのに使用を強いられるレガシーなライブラリの受け入れがDataTableやDataSetでその部分だけクソレガシーなsqlを書かなきゃいけないのが大変にストレス。
そんな場合に使えるIEnumerable→DataTable変換
コード
ExtensionForDataTable.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
namespace qyen.tools {
public static class ExtensionForDataTable {
private const string FieldConnectorChar = "_";
/// <summary>
/// Convert IEnumerable<T> To System.Data.DataTable
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="datas"></param>
/// <param name="expandPropertyOptions"></param>
/// <returns></returns>
public static System.Data.DataTable ToDataTable<T>(this IEnumerable<T> datas, ExpandPropertyOptions expandPropertyOptions = null) {
return datas.ToDataTable(expandPropertyOptions != null, expandPropertyOptions);
}
/// <summary>
/// Convert IEnumerable<T> To System.Data.DataTable
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="datas"></param>
/// <param name="ExpandNonPrimitiveClass"></param>
/// <param name="expandPropertyOptions"></param>
/// <returns></returns>
public static System.Data.DataTable ToDataTable<T>(this IEnumerable<T> datas, bool ExpandNonPrimitiveClass = false, ExpandPropertyOptions expandPropertyOptions = null) {
var baseType = typeof(T);
var hlist = baseType.BuildPropertyList(ExpandNonPrimitiveClass, expandPropertyOptions);
var res = new System.Data.DataTable();
//build DataTable Schema
foreach (var prop in hlist.Where(x => !hlist.Any(y => y.parent == x))) {
res.Columns.Add(new System.Data.DataColumn() {
ColumnName = prop.PropertyName,
DataType = NonNullable(prop.Property.PropertyType),
});
}
//Add Contents
foreach (var data in datas) {
var row = res.NewRow();
foreach (var prop in hlist.Where(x => !hlist.Any(y => y.parent == x))) {
var d = prop.GetHierarchicalValue(data);
if (d != null)
row[prop.PropertyName] = d;
}
res.Rows.Add(row);
}
return res;
}
/// <summary>
/// Build hierarchical Property Info
/// </summary>
/// <param name="baseType"></param>
/// <param name="ExpandNonPrimitiveClass"></param>
/// <param name="expandPropertyOptions"></param>
/// <param name="parent"></param>
/// <returns></returns>
internal static IEnumerable<HierarchyPropertyNode> BuildPropertyList(this Type baseType, bool ExpandNonPrimitiveClass = false, ExpandPropertyOptions expandPropertyOptions = null, HierarchyPropertyNode parent = null) {
if (expandPropertyOptions == null) {
expandPropertyOptions = new ExpandPropertyOptions();
}
var props = baseType.GetProperties();
var res = new List<HierarchyPropertyNode>();
foreach (var prop in props) {
var hpn = new HierarchyPropertyNode() {
parent = parent,
Property = prop,
};
res.Add(hpn);
if (ExpandNonPrimitiveClass
&& prop.PropertyType.CanExpand()
&& expandPropertyOptions.CanExpand(hpn)) {
res.AddRange(prop.PropertyType.BuildPropertyList(ExpandNonPrimitiveClass, expandPropertyOptions, hpn));
}
}
return res;
}
/// <summary>
/// Option settings
/// </summary>
public class ExpandPropertyOptions {
public bool ExpandCircularReference { get; set; } = false;
public int MaxReferenceLevel { get; set; } = 3;
public IList<string> IncludeProperties { get; private set; } = new List<string>();
public ExpandPropertyOptions Include(string Property) {
if (!string.IsNullOrEmpty(Property))
IncludeProperties.Add(Property.Replace(".", "_"));
return this;
}
internal bool CanExpand(HierarchyPropertyNode hpn) {
bool result = true;
if (hpn.Level >= MaxReferenceLevel)
result = false;
if (!ExpandCircularReference && hpn.IsCircularReference)
result = false;
if (IncludeProperties.Any() && !IncludeProperties.Contains(hpn.PropertyName))
result = false;
return result;
}
}
/// <summary>
/// Hierarchical Property Info
/// </summary>
/// <remarks>
/// parent : key
/// </remarks>
internal class HierarchyPropertyNode {
public HierarchyPropertyNode parent { get; set; }
public PropertyInfo Property { get; set; }
public string PropertyName {
get {
return string.Join(FieldConnectorChar, this.Hierarchy.Select(x => x.Property.Name));
}
}
private IEnumerable<HierarchyPropertyNode> Parents {
get {
var current = this.parent;
var res = new List<HierarchyPropertyNode>();
while (current != null) {
res.Add(current);
current = current.parent;
}
res.Reverse();
return res;
}
}
private IEnumerable<HierarchyPropertyNode> Hierarchy {
get {
var list = Parents.ToList();
list.Add(this);
return list;
}
}
internal int Level {
get {
return Hierarchy.Count();
}
}
internal bool IsCircularReference {
get {
return ParentHasSameType(Property.PropertyType);
}
}
private bool ParentHasSameType(Type type) {
return Parents.Any(x => x.Property.PropertyType == type);
}
/// <summary>
/// Get value from root object
/// </summary>
/// <param name="root"></param>
/// <returns>if any parent gets null,return null </returns>
internal object GetHierarchicalValue(object root) {
object value = root;
foreach (var h in Hierarchy) {
try {
value = h.Property.GetValue(value, null);
} catch {
return null;
}
}
return value;
}
}
/// <summary>
/// If Type is Nullable<T> return T,else return t
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
private static Type NonNullable(Type t) {
return Nullable.GetUnderlyingType(t) ?? t;
}
/// <summary>
/// check t is Nullable
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
private static bool IsNullable(Type t) {
return Nullable.GetUnderlyingType(t) != null;
}
/// <summary>
///
/// </summary>
private static Type[] ExcludeExpands = new Type[] { typeof(DateTime), typeof(string), typeof(decimal) };
/// <summary>
/// Check objClass can expand
/// </summary>
/// <param name="objClass"></param>
/// <returns></returns>
private static bool CanExpand(this Type objClass) {
return !objClass.IsPrimitive
&& !ExcludeExpands.Contains(objClass)
&& !IsNullable(objClass)
&& !objClass.GetInterfaces().Any(t => t == typeof(IEnumerable<>));
}
}
}
つかいかた
public class A{
public string Name{get;set;}
public decimal DecimalValue{get;set;}
public B b{get;set;}
public C c{get;set;}
}
public class B{
public string Name{get;set;}
public DateTime Date{get;set;}
public C c{get;set;}
}
public class C{
public string Name{get;set;}
public A a{get;set;}
}
void Main(){
var cList = Enumerable.Range(0,3).Select(x=>new C(){Name=$"C of{x}"}).ToList();
var bList = Enumerable.Range(0,5).Select(x=>new B(){Name=$"B of {x}",Date=DateTime.Today.AddDays(x),c=cList[x%2]}).ToList();
var aList = Enumerable.Range(0,10).Select(x=>new A(){
Name=x.ToString(),
DecimalValue =(decimal)(x*1.121),// ←てきとう
b = bList[x % 5],
c=cList[x % 3]
});
}
みたいな場合。
特にオブジェクトを展開しないで単純にDataTableにしたい
var dataTable = aList.ToDataTable();
オブジェクトはそのままDataTableに格納される。
オブジェクトを展開してDataTableに
var dataTable = aList.ToDataTable(true);
1階層だけ展開してDataTableに
var option = new ExpandPropertyOptions(){MaxReferenceLevel=2}; //2階層目までOK
var dataTable = aList.ToDataTable(true,option);
a.b a.cだけ展開されてそれ以降のa.b.c a.c.a は展開されていない
特定のプロパティだけ展開してDataTableにしたい
var option = new ExpandPropertyOptions().Include("b").Include("c").Include("c_a");
var dataTable = aList.ToDataTable(true,option);
