linq to DataSet 多表查询的问题

liuqiang_zh 2011-03-22 09:54:56
在“LinqPad” 中以下代码成功执行
from TsUp in Ts_UserPurviews
join TsM in Ts_Menus on TsUp.MenuID equals TsM.MenuID into TsMJoin
from TsMJoinT in TsMJoin.DefaultIfEmpty()
join TsU in Ts_Users on TsUp.UserID equals TsU.UserID into TsUJoin
from TsUJoinT in TsUJoin.DefaultIfEmpty()
select new
{
TsUp.UserID,
TsUp.MenuID,
TsUp.Purview,
TsUJoinT.UserName,
TsMJoinT.MenuPath,
TsMJoinT.SortID,
}

但在代码中写成如下
EnumerableRowCollection<DataRow> query =
from TsUp in Dt.AsEnumerable()
join TsM in DtM.AsEnumerable() on TsUp.Field<string>("MenuID") equals TsM.Field<string>("MenuID") into TsMJoin
from TsMJoinT in TsMJoin.DefaultIfEmpty()
join TsU in DtU.AsEnumerable() on TsUp.Field<string>("UserID") equals TsU.Field<string>("UserID") into TsUJoin
from TsUJoinT in TsUJoin.DefaultIfEmpty()
select new
{
UserID=TsUp.Field<string>("UserID"),
MenuID = TsUp.Field<string>("MenuID"),
Purview = TsUp.Field<string>("Purview"),
UserName = TsUJoinT.Field<string>("UserName"),
MenuPath = TsMJoinT.Field<string>("MenuPath"),
SortID = TsMJoinT.Field<string>("SortID"),
};

报“错误 3 无法将类型“System.Collections.Generic.IEnumerable<AnonymousType#1>”隐式转换为“System.Data.EnumerableRowCollection<System.Data.DataRow>”。存在一个显式转换(是否缺少强制转换?)”
错误

那位大佬告诉我 为什么出错 要怎么解决
...全文
1297 点赞 收藏 27
写回复
27 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
liuqiang_zh 2011-03-23
var query =
from TsUp in Dt.AsEnumerable()
join TsM in DtM.AsEnumerable() on TsUp["MenuID"] equals TsM["MenuID"] into TsMJoin
from TsMJoinT in TsMJoin.DefaultIfEmpty()
join TsU in DtU.AsEnumerable() on TsUp["UserID"] equals TsU["UserID"] into TsUJoin
from TsUJoinT in TsUJoin.DefaultIfEmpty()
select new
{
UserID = TsUp["UserID"],
MenuID = TsUp["MenuID"],
Purview = TsUp["Purview"],
UserName = TsUJoinT["UserName"],
MenuPath = TsMJoinT["MenuPath"],
SortID = TsMJoinT["SortID"]
};

DataTable dt = new DataTable();
query.AsEnumerable().ToList().ForEach((dr) => dt.ImportRow(dr));
DataView dv = dt.DefaultView;

在“dt.ImportRow(dr)”处报2个错误 1.“错误 569 与“System.Data.DataTable.ImportRow(System.Data.DataRow)”最匹配的重载方法具有一些无效参数” 和 2.“错误 570 参数 1: 无法从“AnonymousType#1”转换为“System.Data.DataRow” ”

测试以下代码是可以通过的
EnumerableRowCollection<DataRow> query =
from TsUp in Dt.AsEnumerable()
select TsUp;

DataView view = query.AsDataView();
this.DataList.DataSource = view

所以感觉问题主要集中在 “Select New{ }”上 如何把想要的字段转换为 “EnumerableRowCollection<DataRow> ” 这个查了很多资料都不知道所以然
还有个问题是 “多表连接” 不知道是否也有关系
回复
liuqiang_zh 2011-03-23
问题 还没解决 各位大虾 积极发言
回复
liuqiang_zh 2011-03-22
是否 和 我使用了左连接有关 但 我确认 我使用的Linq 语句是通过的
回复
liuqiang_zh 2011-03-22
我的引用

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Collections;
using System.Data;
using System.Web;
using System.IO;
using System.Xml;
using System.Xml.Linq;
回复
liuqiang_zh 2011-03-22
To:q107770540 , dongxinxi

当我 “IEnumerable<DataRow> query =...”如此定义时

在代码
IEnumerable <DataRow> query =
from TsUp in Dt.AsEnumerable()
join TsM in DtM.AsEnumerable() on TsUp.Field <string> ( "MenuID ") equals TsM.Field <string> ( "MenuID ") into TsMJoin
from TsMJoinT in TsMJoin.DefaultIfEmpty()
join TsU in DtU.AsEnumerable() on TsUp.Field <string> ( "UserID ") equals TsU.Field <string> ( "UserID ") into TsUJoin
from TsUJoinT in TsUJoin.DefaultIfEmpty()
select new
{
UserID=TsUp.Field <string> ( "UserID "),
MenuID = TsUp.Field <string> ( "MenuID "),
Purview = TsUp.Field <string> ( "Purview "),
UserName = TsUJoinT.Field <string> ( "UserName "),
MenuPath = TsMJoinT.Field <string> ( "MenuPath "),
SortID = TsMJoinT.Field <string> ( "SortID "),
};

中的 最后一个“from” 中报“错误 3 无法将类型“System.Collections.Generic.IEnumerable <AnonymousType#1> ”隐式转换为“System.Data.EnumerableRowCollection <System.Data.DataRow> ”。存在一个显式转换(是否缺少强制转换?)”
回复
兔子党-督察 2011-03-22
IEnumerable<DataRow> query =...


DataTable boundTable = query.CopyToDataTable<DataRow>();
http://msdn.microsoft.com/zh-cn/library/bb396189(v=vs.90).aspx
回复
liuqiang_zh 2011-03-22
按照 “dongxinxi” 代码如下

IEnumerable <DataRow> query =
from TsUp in Dt.AsEnumerable()
join TsM in DtM.AsEnumerable() on TsUp.Field <string> ( "MenuID ") equals TsM.Field <string> ( "MenuID ") into TsMJoin
from TsMJoinT in TsMJoin.DefaultIfEmpty()
join TsU in DtU.AsEnumerable() on TsUp.Field <string> ( "UserID ") equals TsU.Field <string> ( "UserID ") into TsUJoin
from TsUJoinT in TsUJoin.DefaultIfEmpty()
select new
{
UserID=TsUp.Field <string> ( "UserID "),
MenuID = TsUp.Field <string> ( "MenuID "),
Purview = TsUp.Field <string> ( "Purview "),
UserName = TsUJoinT.Field <string> ( "UserName "),
MenuPath = TsMJoinT.Field <string> ( "MenuPath "),
SortID = TsMJoinT.Field <string> ( "SortID "),
};

DataTable dt = new DataTable();
query.ToList().ForEach((dr) => dt.ImportRow(dr));
DataView dv = dt.DefaultView;

“错误 3 无法将类型“System.Collections.Generic.IEnumerable <AnonymousType#1> ”隐式转换为“System.Data.EnumerableRowCollection <System.Data.DataRow> ”。存在一个显式转换(是否缺少强制转换?)” 错误依旧
回复
DataRow的RowState属性(状态)取值有5种:
Detached 你这里的DataRow不属于任何显式声明的DataTable,无法直接Add()
Unchanged, Added, Deleted, Modified
回复
liuqiang_zh 2011-03-22
我把框架改为 4.0 但找不到 “.CopyToDataTable()”是否需要添加什么其他引用
回复
Add()应该不行的,可以用导入,或者逐行复制DataRow.ItemArray

System.Collections.Generic.IEnumerable<DataRow> query;
DataTable dt = new DataTable();
query.ToList().ForEach((dr) => dt.ImportRow(dr));
DataView dv = dt.DefaultView;
回复
liuqiang_zh 2011-03-22
不能理解 ForEch(q=>dt.Rows.Add("")
回复
liuqiang_zh 2011-03-22
看到 MSDN 里有提供一个例子 代码如下

DataTable orders = dataSet.Tables["SalesOrderHeader"];

EnumerableRowCollection<DataRow> query =
from order in orders.AsEnumerable()
where order.Field<bool>("OnlineOrderFlag") == true
orderby order.Field<decimal>("TotalDue")
select order;

DataView view = query.AsDataView();

bindingSource1.DataSource = view;

我是根据这个做的
回复
兔子党-督察 2011-03-22
[Quote=引用 4 楼 liuqiang_zh 的回复:]

感谢回答 问题是我想把查询结果转换为“DataView”

DataView view = query.AsDataView(); 所以才使用“EnumerableRowCollection <DataRow> query = ” 请问有无办法实现
[/Quote]
如果你使用的是 .net 4.0
直接 DataTable dt=query.CopyToDataTable()即可
如果不是 那就 query.ToList().ForEch(q=>dt.Rows.Add(""))来实现吧
回复
3.0里的匿名类型关键字 var
回复
liuqiang_zh 2011-03-22
感谢回答 问题是我想把查询结果转换为“DataView”

DataView view = query.AsDataView(); 所以才使用“EnumerableRowCollection <DataRow> query = ” 请问有无办法实现
回复
Linq出来的结果一般都实现了IEnumerable接口
你可以用System.Collections.Generic.IEnumerable<DataRow>来声明,或者用其名类型,由编译器自己可以判定
回复
兔子党-督察 2011-03-22
或者你也可以这样:

EnumerableRowCollection <Temp> query =
from TsUp in Dt.AsEnumerable()
join TsM in DtM.AsEnumerable() on TsUp.Field <string> ( "MenuID ") equals TsM.Field <string> ( "MenuID ") into TsMJoin
from TsMJoinT in TsMJoin.DefaultIfEmpty()
join TsU in DtU.AsEnumerable() on TsUp.Field <string> ( "UserID ") equals TsU.Field <string> ( "UserID ") into TsUJoin
from TsUJoinT in TsUJoin.DefaultIfEmpty()
select new Temp
{
UserID=TsUp.Field <string> ( "UserID "),
MenuID = TsUp.Field <string> ( "MenuID "),
Purview = TsUp.Field <string> ( "Purview "),
UserName = TsUJoinT.Field <string> ( "UserName "),
MenuPath = TsMJoinT.Field <string> ( "MenuPath "),
SortID = TsMJoinT.Field <string> ( "SortID ")
};


class Temp
{
public string UserID{get;set;}
public string MenuID {get;set;}
public string Purview {get;set;}
public string UserName {get;set;}
public string MenuPath {get;set;}
public string SortID {get;set;}
}
回复
兔子党-督察 2011-03-22
EnumerableRowCollection <DataRow> query =
======
修改成:
var query=

原因是:
select new
{
UserID=TsUp.Field <string> ( "UserID "),
MenuID = TsUp.Field <string> ( "MenuID "),
Purview = TsUp.Field <string> ( "Purview "),
UserName = TsUJoinT.Field <string> ( "UserName "),
MenuPath = TsMJoinT.Field <string> ( "MenuPath "),
SortID = TsMJoinT.Field <string> ( "SortID "),
};

匿名类无法隐匿转换成DataRow
回复
[Quote=引用 18 楼 liuqiang_zh 的回复:]
我这样做的原因是 DataView 排序和筛选 比较方便

我使用 “var query” linq语句 通过 了 但 不知道如何把这个变量转换为
"DataView"

问题还没解决 希望大家踊跃发言
[/Quote]

排序和筛选用Linq也很方便
from user in db.Userwhere user.ID < 30orderby user.CreateDate descending,user.Username descendingselect user;
或者你用Linq扩展方法OrderBy()和Where()

query.ToList().ForEach(dr => dt.ImportRow(dr));
点后面都是Linq中的扩展方法,最后还包含了一个Lambda表达式,ForEach(Action<T>())需要一个无返回类型的委托,.Net中已经有一个现在的Action<T>()
MSDN官方解释:Lambda表达式是一个能够包含表达式和语句,并且能够用来创建委托或表达式树类型的匿名函数
它最终会被编译为一个委托+匿名方法,这些你可以慢慢再了解

上面dr是匿名方法的传入参数,类型决定于IEnumrable<T>.ForEach(),所以还得看query的类型
我上面说错了,这里你有两种方式来声明:
显式:System.Data.EnumerableRowCollection<System.Data.DataRow>
隐式:var
在下面的Linq中,需要先转化为IEnumrable<T>,所以中间还得再加一个扩展方法
DataTable dt = new DataTable();
query.AsEnumerable().ToList().ForEach((dr) => dt.ImportRow(dr));
DataView dv = dt.DefaultView;
回复
liuqiang_zh 2011-03-22
EnumerableRowCollection <DataRow> query =
(from TsUp in Dt.AsEnumerable()
join TsM in DtM.AsEnumerable() on TsUp.Field <string> ( "MenuID ") equals TsM.Field <string> ( "MenuID ") into TsMJoin
from TsMJoinT in TsMJoin.DefaultIfEmpty()
join TsU in DtU.AsEnumerable() on TsUp.Field <string> ( "UserID ") equals TsU.Field <string> ( "UserID ") into TsUJoin
from TsUJoinT in TsUJoin.DefaultIfEmpty()
select new
{
UserID=TsUp.Field <string> ( "UserID "),
MenuID = TsUp.Field <string> ( "MenuID "),
Purview = TsUp.Field <string> ( "Purview "),
UserName = TsUJoinT.Field <string> ( "UserName "),
MenuPath = TsMJoinT.Field <string> ( "MenuPath "),
SortID = TsMJoinT.Field <string> ( "SortID "),
}) as EnumerableRowCollection<DataRow>;

这样语句是同过了 但 查询不出数据


var query =
(from TsUp in Dt.AsEnumerable()
join TsM in DtM.AsEnumerable() on TsUp.Field <string> ( "MenuID ") equals TsM.Field <string> ( "MenuID ") into TsMJoin
from TsMJoinT in TsMJoin.DefaultIfEmpty()
join TsU in DtU.AsEnumerable() on TsUp.Field <string> ( "UserID ") equals TsU.Field <string> ( "UserID ") into TsUJoin
from TsUJoinT in TsUJoin.DefaultIfEmpty()
select new
{
UserID=TsUp.Field <string> ( "UserID "),
MenuID = TsUp.Field <string> ( "MenuID "),
Purview = TsUp.Field <string> ( "Purview "),
UserName = TsUJoinT.Field <string> ( "UserName "),
MenuPath = TsMJoinT.Field <string> ( "MenuPath "),
SortID = TsMJoinT.Field <string> ( "SortID "),
}).ToList();

能得到想要的数据 但数据绑定到 DataGrid 里好像有问题
回复
加载更多回复
相关推荐
发帖
LINQ
创建于2007-12-13

8495

社区成员

.NET技术 LINQ
申请成为版主
帖子事件
创建了帖子
2011-03-22 09:54
社区公告
暂无公告