linq to DataSet 多表查询的问题

2011-03-22 09:54:56 1.5k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

C# LINQ 多表查询在C#中使用linq实现多表查询，首先需要在c#中连接你的数据库首先我们先创建一个数据库和两个数据表然后在表中插入数据设置完毕后到c#中把数据库创建完毕后在main函数中连接字符串也就是连接自己的sqlsever string str = "server=DESKTOP-IOK5BR8;uid=sa;pwd=123456;database=db_SouthWind"; 然后实例化一个数据集对象！要引用命名空间using System.Data; DataSet ds =... 阅读详情

在“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>”。存在一个显式转换(是否缺少强制转换?)”
错误

那位大佬告诉我为什么出错要怎么解决

...全文

保姆级教程：在Jetson Nano上从零移植OV5647 CSI摄像头驱动（附完整调试记录）本文提供了一份详细的Jetson Nano OV5647 CSI摄像头驱动移植教程，涵盖硬件准备、驱动框架分析、设备树配置、驱动移植核心修改点及调试技巧。通过V4L2框架和Tegra平台适配，开发者可快速实现摄像头驱动的移植与优化，适用于边缘计算和嵌入式视觉应用。阅读详情

liuqiang_zh

LINQ 8,492 成员 4,736 内容

+ 关注 + 加入社区

切换为时间正序

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> ”。存在一个显式转换(是否缺少强制转换?)”

q107770540 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> ”。存在一个显式转换(是否缺少强制转换?)” 错误依旧

IE11下面经常卡到爆 2011-03-22

打赏
举报

DataRow的RowState属性（状态）取值有5种：
Detached 你这里的DataRow不属于任何显式声明的DataTable,无法直接Add()
Unchanged, Added, Deleted, Modified

liuqiang_zh 2011-03-22

打赏
举报

我把框架改为 4.0 但找不到 “.CopyToDataTable()”是否需要添加什么其他引用

IE11下面经常卡到爆 2011-03-22

打赏
举报

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;

我是根据这个做的

q107770540 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(""))来实现吧

IE11下面经常卡到爆 2011-03-22

打赏
举报

3.0里的匿名类型关键字 var

liuqiang_zh 2011-03-22

打赏
举报

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

DataView view = query.AsDataView(); 所以才使用“EnumerableRowCollection <DataRow> query = ” 请问有无办法实现

IE11下面经常卡到爆 2011-03-22

打赏
举报

Linq出来的结果一般都实现了IEnumerable接口
你可以用System.Collections.Generic.IEnumerable<DataRow>来声明，或者用其名类型，由编译器自己可以判定

q107770540 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;}

}

q107770540 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

IE11下面经常卡到爆 2011-03-22

打赏
举报

[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 里好像有问题

加载更多回复（6）

linq to DataSet 多表查询的问题

相关推荐