LINQ查询代码整理(二)

问题的引出:

如下使用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>()之后问题解决。

posted @ 2010-07-27 21:24  志在必得Shaun  阅读(350)  评论(0编辑  收藏  举报