在 Linq 中使用 IsNull 或选择 COALESCE ..?
可能的重复:
LINQ中SQL ISNULL的等价物?
我有 3 张这样的桌子:
I have 3 tables like this :
评论表:
commentId pid sid text vid
1 1 null comment 1 1
2 null 1 comment 2 1
3 2 null comment 3 1
学生桌
sid firstname lastname
1 john adam
2 joan adam
教授表:
pid firstname lastname
1 mark abram
2 sean hoak
我想进行查询,以便结果像这样返回:
I want to make a query so the result to return like this :
firstname lastname
mark abram
john adam
sean hoak
米
if (select query ==null) then (selec query 1) else select (query 2)
我尝试了以下方法:
if((select pid from comment==null)
then select student.firstname , student.lastname from student where sid in (select sid from comment where vid=1)
else
(select professor.firstname ,professor.lastname from professor where pid in (select pid from comment where vid=1)
但没有用
然后其他人提供了这个解决方案查询
then others provided this solution queries
IsNull 查询
SELECT ISNULL(P.firstname, s.firstname) AS Expr1,ISNULL(P.lastname, s.lastname) AS Expr2
FROM comment AS C
LEFT OUTER JOIN professor AS P ON P.ID = C.PID
LEFT OUTER JOIN student AS s ON s.ID = C.SID
WHERE (C.VID = 2)
合并:
SELECT COALESCE(p.firstname, s.firstname), COALESCE(p.lastname, s.lastname)
FROM comment c
LEFT JOIN Professor p
ON c.pid = p.id
LEFT JOIN Student s
ON c.sid = s.id
WHERE c.vid = 2
在 SQL Management 中都可以正常工作,但在 Linq 中使用:
both works fine in SQL Management , but in Linq with the:
ObjectQuery<string> results1 = context.CreateQuery<string>(query1, parameters)
我已经尝试将它自己转换为:
I've tried to convert it my self to :
var qry = from c in ctx.comment
join p in ctx.professor
on c.PID equals p.ID into tree
join s in ctx.student
on c.SID equals s.ID into tree1
where c.VID == vID
from tmp in tree.DefaultIfEmpty()
from tmp1 in tree.DefaultIfEmpty()
select new
{
Firstnmae = tmp.firstname ?? tmp1.firstname,
LastName = tmp.lastname ?? tmp1.lastname
};
但它返回空
有什么想法吗?
推荐答案
看来你在 tmp1
附近有错字,你应该做 tree1.DefaultIfEmpty()
>
It seems you have a typo near tmp1
, you should do tree1.DefaultIfEmpty()
var qry = from c in ctx.comment
join p in ctx.professor
on c.PID equals p.ID into tree
join s in ctx.student
on c.SID equals s.ID into tree1
where c.VID == vID
from tmp in tree.DefaultIfEmpty()
from tmp1 in tree1.DefaultIfEmpty()
select new
{
Firstnmae = tmp.firstname ?? tmp1.firstname,
LastName = tmp.lastname ?? tmp1.lastname
};
相关文章