LINQ.Contains超过2100记录报异常Exception的问题
LINQ的异常: 传入的表格格式数据流(TDS)远程过程调用(RPC)协议流不正确。此 RPC 请求中提供了过多的参数。最多应为 2100。
代码:标红的地方会报错误
var pid = (from p in PSLVList
select p.ProductID).Distinct().ToList();
var AllSortListTemp = (from p in pdc.Product_Standard
join j in pdc.Product_Journey on p.Product_Id equals j.Product_Id
where pid.Contains(p.Product_Id)
orderby p.Duration ascending
select new AllSortListTempView
{
Begin_City_Id = p.Begin_City_Id,
End_City_Id = p.End_City_Id,
Product_Id = p.Product_Id,
Duration = p.Duration,
Pre_Booking_Day = p.Pre_Booking_Day,
Arrive_City_ID = j.Arrive_City_ID,
Day_No = j.Day_No,
Product_Sub_Type = p.Product_Sub_Type
}
).Distinct().ToList();
报错的地方就是.Contains函数,当AnotherList的元素个数超过2100,LINQ的.Contains最大支持2100,超过就会报这个异常。
解决办法很简单,只要将调用数据源的AsEnumerable()运算符就可以了。例如:
var AllSortListTemp = (from p in pdc.Product_Standard.AsEnumerable()
join j in pdc.Product_Journey on p.Product_Id equals j.Product_Id
where pid.Contains(p.Product_Id)
orderby p.Duration ascending
select new AllSortListTempView
{
Begin_City_Id = p.Begin_City_Id,
End_City_Id = p.End_City_Id,
Product_Id = p.Product_Id,
Duration = p.Duration,
Pre_Booking_Day = p.Pre_Booking_Day,
Arrive_City_ID = j.Arrive_City_ID,
Day_No = j.Day_No,
Product_Sub_Type = p.Product_Sub_Type
}
).Distinct().ToList();
代码:标红的地方会报错误
var pid = (from p in PSLVList
select p.ProductID).Distinct().ToList();
var AllSortListTemp = (from p in pdc.Product_Standard
join j in pdc.Product_Journey on p.Product_Id equals j.Product_Id
where pid.Contains(p.Product_Id)
orderby p.Duration ascending
select new AllSortListTempView
{
Begin_City_Id = p.Begin_City_Id,
End_City_Id = p.End_City_Id,
Product_Id = p.Product_Id,
Duration = p.Duration,
Pre_Booking_Day = p.Pre_Booking_Day,
Arrive_City_ID = j.Arrive_City_ID,
Day_No = j.Day_No,
Product_Sub_Type = p.Product_Sub_Type
}
).Distinct().ToList();
报错的地方就是.Contains函数,当AnotherList的元素个数超过2100,LINQ的.Contains最大支持2100,超过就会报这个异常。
解决办法很简单,只要将调用数据源的AsEnumerable()运算符就可以了。例如:
var AllSortListTemp = (from p in pdc.Product_Standard.AsEnumerable()
join j in pdc.Product_Journey on p.Product_Id equals j.Product_Id
where pid.Contains(p.Product_Id)
orderby p.Duration ascending
select new AllSortListTempView
{
Begin_City_Id = p.Begin_City_Id,
End_City_Id = p.End_City_Id,
Product_Id = p.Product_Id,
Duration = p.Duration,
Pre_Booking_Day = p.Pre_Booking_Day,
Arrive_City_ID = j.Arrive_City_ID,
Day_No = j.Day_No,
Product_Sub_Type = p.Product_Sub_Type
}
).Distinct().ToList();
为什么.AsEnumerable()这么神奇?
从MSDN看到,调用这个方法会返回一个 IEnumerable<T> 对象,而大家知道,和IQueryable比较起来,IEnumerable<T>是用了LINQ2Object的方式,而不是LINQ2SQL的方式,前者数据是在内存中处理的,而后者是在server上运 行的。换句话说,本地数据源用IEnumerable<T>,数据在内存中,并且查询的逻辑可以直接用你所定义的方法的逻辑(因为有上下 文);远程数据源用IQueryable<T>,无法直接使用你所定义的方法的逻辑,必须先生成表达式树,查询由源对象处 理;IQueryable<T>类型可以在最终的执行前加一些方法,然后再返回最终的IQueryable<T>;而 IEnumerable<T>已经包含了你执行查询后的所有结果,这时你的添加方法都是在已经有的结果集里进行。一言以蔽之,上面的2100的问题解决方法就是避免了LINQ2SQL生成表达式树查询远程服务器!
这个方法有个副作用:调用dataContext.GetTable<MyEntity>().AsEnumerable()的副作用是吃内存,如果这个table很大,估计内存空间会消耗很大。