条件linq查询

本文关键字：查询 linq 条件 | 更新日期: 2023-09-27 18:21:11

将此SQL转换为linq的最佳方法是什么？

我之前在数据库中基于这样一个sql创建了视图，然后用linq查询视图。但我想知道是否还有其他方法。

SQL查找为任务分配的对象。任务表包含三个外键列，因为分配给的可能来自部门、职位或人员。只允许一个。

SQL:

SELECT   id,
         title,
         assigned_to = (case
            when idAssignedDepartment is not null then (select description from department where id = idAssignedDepartment)
            when idAssignedPosition is not null then (select description from position where id = idAssignedPosition )
            when idAssignedPerson is not null then (select fullname from person where id = idAssignedPerson )               
            end)
FROM     task

使用LinqToEF

条件linq查询

你可以这样写：

var q = from t in task
        from dep in department.Where(x => x.id == t.idAssignedDepartment).DefaultIfEmpty()
        from pos in position.Where(x => x.id == t.idAssignedPosition).DefaultIfEmpty()
        from per in person .Where(x => x.id == t.idAssignedPerson).DefaultIfEmpty()
        select new
        {
            t.id,
            t.title,
            assigned_to = t.idAssignedDepartment != null ? dep.description :
                          t.idAssignedPosition != null ? pos.description :
                          t.idAssignedPerson != null ? per.fullname : 
                          null
        };
相关文章：