使用IsNull还是在Linq ..中选择COALESCE? [英] Using IsNull or select COALESCE in Linq..?
本文介绍了使用IsNull还是在Linq ..中选择COALESCE?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
可能重复:
等于LINQ中的SQL ISNULL吗?
Possible Duplicate:
Equivalent of SQL ISNULL in LINQ?
我有3张这样的桌子:
评论表:
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
m
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)
我试图将自己转换为:
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
};
但它返回null
请问有什么想法吗?
推荐答案
看来您在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
};
这篇关于使用IsNull还是在Linq ..中选择COALESCE?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文