问题的引出:
如下使用CASE的SQL如何用LINQ TO SQL 来实现呢?
select updateagaintime=(case when updateagaintime is null then begintime else updateagaintime end)
from workplan
熟悉LINQ的很快会写出这样的语句:
IQueryable<workplan> result = from w in dataContext.workplan
select new workplan
{
updateagaintime = w.updateagaintime == null ? w.begintime : w.updateagaintime
};
在MVC项目对应页面的Action中这样使用结果集:
var viewData = new MyDataView
{
workPlanPageDataView = result.ToList().ToPagedList(pageIndex, pageSize)
};
上面的用法报错“不允许在查询中显式构造实体类型”(其中ToPagedList方法参见链接:参考>>“ASP.NET MVC 分页”)
换了老赵的方法:http://jeffz.blog.51cto.com/309226/62391
代码如下(LINQ中使用SQL):
MyProjectDataContext dataContext = new MyProjectDataContext();
dataContext.Connection.Open();
SqlCommand command = new SqlCommand(
@"SELECT [workplanid],
[requirementid],
[statusname],
[endtime],
[sellerid],
[begintime],
[technicianid],
[updateagaintime]=(CASE WHEN [updateagaintime] IS NULL THEN [begintime] ELSE [updateagaintime] END),
[difftime], [assitantsellerid], [remark], [isdeleted], [createtime], [createby], [updateby], [groupid]" +
@"FROM [workplan]
ORDER BY [updateagaintime] DESC",
(SqlConnection)dataContext.Connection);
using (DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
result = dataContext.Translate<workplan>(reader).AsQueryable<workplan>();
}
结果集用的IQueryable<workplan>类型,之后又根据不同的条件对结果集进行了几次不同的查询,错误提示为“无法枚举查询结果多次”(先前的几个查询语句只查了一个字段是为了节省篇幅)。
将结果集改为List<workplan>类型,即ToList<workplan>()之后问题解决。