datatable linq 查询排序用法总结_c# datatable linq 查询

DataTable通过调用AsEnumerable()方法，从而运用Linq查询。其中AsEnumerable方法在System.Data.DataSetExtensions.dll中定义，一般VS会自动引用这个dll。

一、datatable linq查询实例

1. DataTable读取列表

DataSet ds = new DataSet();
// 省略ds的Fill代码
DataTable products = ds.Tables["Product"];
var rows = products.AsEnumerable()
    .Select(p => new
    {
        ProductID = p.Field<int>("ProductID"),
        ProductName = p.Field<string>("ProductName"),
        UnitPrice = p.Field<decimal>("UnitPrice")
    });
foreach (var row in rows)
{
    Console.WriteLine(row.ProductName);
}

2. DataTable linq where 查询

C# 代码    复制

var rows = products.AsEnumerable()
    .Where(p => p.Field<decimal>("UnitPrice") > 10m)
    .Select(p => new
    {
        ProductID = p.Field<int>("ProductID"),
        ProductName = p.Field<string>("ProductName"),
        UnitPrice = p.Field<decimal>("UnitPrice")
    });

3、DataTable linq 数据排序

C# 代码    复制

var rows = products.AsEnumerable()
    .Where(p => p.Field<decimal>("UnitPrice") > 10m)
    .OrderBy(p => p.Field<int>("SortOrder"))
    .Select(p => new
    {
        ProductID = p.Field<int>("ProductID"),
        ProductName = p.Field<string>("ProductName"),
        UnitPrice = p.Field<decimal>("UnitPrice")
    });

C# 代码    复制

var expr = from p in products.AsEnumerable()
            orderby p.Field<int>("SortOrder")
            select p;
IEnumerable<DataRow> rows = expr.ToArray();
foreach (var row in rows)
{
    Console.WriteLine(row.Field<string>("ProductName"));
}

C# 代码    复制

var expr = from p in ds.Tables["Product"].AsEnumerable()
           orderby p.Field<int>("SortOrder"), p.Field<string>("ProductName") descending
           select p;

4、DataTable分组 

C# 代码    复制

var query = from p in ds.Tables["Product"].AsEnumerable()
            group p by p.Field<int>("CategoryID") into g
            select new
            {
                CategoryID = g.Key,
                Products = g
            };

foreach (var item in query)
{
    Console.WriteLine(item.CategoryID);
    foreach (var p in item.Products)
    {
        Console.WriteLine(p.Field<string>("ProductName"));
    }
}

查询Product中每个CategoryID的数目

C# 代码    复制

var expr = from p in ds.Tables["Product"].AsEnumerable()
           group p by p.Field<int>("CategoryID") into g
           select new
           {
               CategoryID = g.Key,
               ProductsCount = g.Count()
           };

5、多个DataTable查询

C# 代码    复制

var query = from p in ds.Tables["Product"].AsEnumerable()
            from c in ds.Tables["Category"].AsEnumerable()
            where p.Field<int>("CategoryID") == c.Field<int>("CategoryID")
                && p.Field<decimal>("UnitPrice") > 10m
            select new
            {
                ProductID = p.Field<int>("ProductID"),
                ProductName = p.Field<string>("ProductName"),
                CategoryName = c.Field<string>("CategoryName")
            };

二、linq 对象转换为DataTable

通过CopyToDataTable()方法

C# 代码    复制

DataTable newD1t = query1.CopyToDataTable<DataRow>();
            foreach (DataRow item in newD1t.Rows)
            {
                System.Console.WriteLine(item["Name"]);
            }

需求：从DataTable中直接生成指定类的对象或对象列表

使用：datatable.ToListModel<T>();

代码：

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
 
/// <summary>
/// DataTableToModel 的摘要说明
/// </summary>
public static class DataTableToModel
{
    /// <summary>
    /// DataTable通过反射获取单个像
    /// </summary>
    public static T ToSingleModel<T>(this DataTable data) where T : new()
    {
        T t = data.GetList<T>(null, true).Single();
        return t;
    }
 
 
    /// <summary>
    /// DataTable通过反射获取单个像
    /// <param name="prefix">前缀</param>
    /// <param name="ignoreCase">是否忽略大小写，默认不区分</param>
    /// </summary>
    public static T ToSingleModel<T>(this DataTable data, string prefix, bool ignoreCase = true) where T : new()
    {
        T t = data.GetList<T>(prefix, ignoreCase).Single();
        return t;
    }
 
    /// <summary>
    /// DataTable通过反射获取多个对像
    /// </summary>
    /// <typeparam name="type"></typeparam>
    /// <param name="type"></param>
    /// <returns></returns>
    public static List<T> ToListModel<T>(this DataTable data) where T : new()
    {
        List<T> t = data.GetList<T>(null, true);
        return t;
    }
 
 
    /// <summary>
    /// DataTable通过反射获取多个对像
    /// </summary>
    /// <param name="prefix">前缀</param>
    /// <param name="ignoreCase">是否忽略大小写，默认不区分</param>
    /// <returns></returns>
    private static List<T> ToListModel<T>(this DataTable data, string prefix, bool ignoreCase = true) where T : new()
    {
        List<T> t = data.GetList<T>(prefix, ignoreCase);
        return t;
    }
 
 
 
    private static List<T> GetList<T>(this DataTable data, string prefix, bool ignoreCase = true) where T : new()
    {
        List<T> t = new List<T>();
        int columnscount = data.Columns.Count;
        if (ignoreCase)
        {
            for (int i = 0; i < columnscount; i++)
                data.Columns[i].ColumnName = data.Columns[i].ColumnName.ToUpper();
        }
        try
        {
            var properties = new T().GetType().GetProperties();
 
            var rowscount = data.Rows.Count;
            for (int i = 0; i < rowscount; i++)
            {
                var model = new T();
                foreach (var p in properties)
                {
                    var keyName = prefix + p.Name + "";
                    if (ignoreCase)
                        keyName = keyName.ToUpper();
                    for (int j = 0; j < columnscount; j++)
                    {
                        if (data.Columns[j].ColumnName == keyName && data.Rows[i][j] != null)
                        {
                            string pval = data.Rows[i][j].ToString();
                            if (!string.IsNullOrEmpty(pval))
                            {
                                try
                                {                                    
                                    // We need to check whether the property is NULLABLE
                                    if (p.PropertyType.IsGenericType && p.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                                    {
                                        p.SetValue(model, Convert.ChangeType(data.Rows[i][j], p.PropertyType.GetGenericArguments()[0]), null);
                                    }
                                    else
                                    {
                                        p.SetValue(model, Convert.ChangeType(data.Rows[i][j], p.PropertyType), null);
                                    }
                                }
                                catch(Exception x) {
                                    throw x;
                                }
                            }
                            break;
                        }
                    }
                }
                t.Add(model);
            }
        }
        catch (Exception ex)
        {
 
 
            throw ex;
        }
 
 
        return t;
    }
}