从LINQ转换为SQL查询再转换为SQL查询

Convert from LINQ to SQL query to SQL query
2021-07-21
  •  译文(汉语)
  •  原文(英语)

我想将LINQ转换为SQL到SQL查询.我知道简单的LINQ to SQL,但不了解更多.

下面给出的是我的Linq to Sql,我想将其转换为Sql查询.

from objSql in objContext.DoctorNotes
join objCreatedU in objContext.Users on objSql.CreatedByUserFK equals objCreatedU.UserID into objCU
from tblC in objCU.DefaultIfEmpty()
join objModifiedU in objContext.Users on tblC.ModifiedByUserFK equals objModifiedU.UserID into objMU
from tblM in objMU.DefaultIfEmpty()
select new DoctorNoteBind(objSql)
{
    CreatedBy = tblC.UserName,
    ModifiedBy = tblM.UserName
}).ToList();
速聊1:
什么objpredicate啊 确切地说,它在哪里或如何定义的?
速聊2:
在其中设置"我的位置"线索.如果您愿意,请Plz忽略它.我仅对"从objCU.ttlC中的tblC加入objContext.DefaultIfEmpty()加入objContext.User中的objModifiedU感到困惑".
速聊3:
为什么将如此艰苦的查询语法和扩展方法语法混合使用?同样,first OrderByDescending()和first ToList()毫无意义且有害(执行不必要的操作,浪费资源不会产生任何效果).
速聊4:
顺便说一下...这不是Linq to SQLLinq to EntityFramework
速聊5:
LINQ to Entities是正确的.
解决过程1

.DefaultIfEmpty()用于表示LEFT JOINs(http://msdn.microsoft.com/zh-cn/library/ms187518 (v=sql.100) .aspx).您希望等效的SQL采用以下形式:

SELECT
    U1.UserName AS CreatedBy,
    U2.UserName AS ModifiedBy
FROM
    DoctorNotes DN
LEFT JOIN Users U1 ON
    U1.UserID = DN.CreatedByUserFK
LEFT JOIN Users U2 ON
    U2.UserID = DN.ModifiedByUserFK
-- WHERE ?
-- ORDER BY ?
速聊1:
嗨,还有一个问题.为什么我们使用"在objContext.Users中将objModifiedU.tblC.ModifiedByUserFK上的objModifiedU.UserID等于从objMU.DefaultIfEmpty()中的tblM到objMU中的objModifiedU.UserID".它是什么意思.
速聊2:
这就是您LEFT JOIN使用T-SQL表示的方式.我在回答中提到了这一点.
速聊3:
为什么我们使用由"onjSql.ModifiedByUserFK等于objModifiedU.UserID"插入的"tblC.ModifiedByUserFK等于objModifiedU.UserID".我不只知道这部分.
速聊4:
松散地说,该表达式from tblC in objCU.DefaultIfEmpty()表示"left-joining"objSql和之后的结果集objCreatedU.它结合了所有列的DoctorNotesUsers.您需要将下一个联接"附加"到该结果集,因此要在第二个联接表达式中使用tblC而不是objSql
速聊5:
那么如何在Sql中使用它呢?我如何才能将DoctorNotes和Users中的所有列合并到Sql中,然后如何使用"tblC.ModifiedByUserFK等于objModifiedU.UserID"之类的对象?谢谢

I want to convert LINQ to SQL to SQL query. I know simple LINQ to SQL but don't know about more.

Given below is my Linq to Sql and I want to convert it into Sql query.

from objSql in objContext.DoctorNotes
join objCreatedU in objContext.Users on objSql.CreatedByUserFK equals objCreatedU.UserID into objCU
from tblC in objCU.DefaultIfEmpty()
join objModifiedU in objContext.Users on tblC.ModifiedByUserFK equals objModifiedU.UserID into objMU
from tblM in objMU.DefaultIfEmpty()
select new DoctorNoteBind(objSql)
{
    CreatedBy = tblC.UserName,
    ModifiedBy = tblM.UserName
}).ToList();
Talk1:
What is objpredicate? Or rather, where or how is it defined?
Talk2:
Set my Where clues in it. Plz ignore it If you want.I am confused for only "from tblC in objCU.DefaultIfEmpty() join objModifiedU in objContext.Users on tblC.ModifiedByUserFK equals objModifiedU.UserID into objMU from tblM in objMU.DefaultIfEmpty() ".
Talk3:
Why do you mix so hard query syntax and extension methods syntax? Also first OrderByDescending() and first ToList() are pointless and harmful (perform unnecessary operations spending resources producing no effect).
Talk4:
by the way...this isn't Linq to SQL it is Linq to EntityFramework
Talk5:
LINQ to Entities to be correct.
Solutions1

The .DefaultIfEmpty() are used to represent LEFT JOINs (http://msdn.microsoft.com/en-us/library/ms187518(v=sql.100).aspx). You'd expect the equivalent SQL to take the following form:

SELECT
    U1.UserName AS CreatedBy,
    U2.UserName AS ModifiedBy
FROM
    DoctorNotes DN
LEFT JOIN Users U1 ON
    U1.UserID = DN.CreatedByUserFK
LEFT JOIN Users U2 ON
    U2.UserID = DN.ModifiedByUserFK
-- WHERE ?
-- ORDER BY ?
Talk1:
Hi One more question. Why we use "join objModifiedU in objContext.Users on tblC.ModifiedByUserFK equals objModifiedU.UserID into objMU from tblM in objMU.DefaultIfEmpty() ". what is the meaning of it.
Talk2:
That's how you express a LEFT JOIN using T-SQL. I mentioned that in the answer.
Talk3:
Why We use " tblC.ModifiedByUserFK equals objModifiedU.UserID" inseted of "onjSql.ModifiedByUserFK equals objModifiedU.UserID". I don't know only this part.
Talk4:
Loosely put, the expression from tblC in objCU.DefaultIfEmpty() represents the result set after "left-joining" objSql and objCreatedU. It combines all columns from DoctorNotes and Users. You'll want the next join to "append" to this result set hence the usage of tblC and not objSql in the second join expression
Talk5:
So How can I Use it in Sql? How can I get Object of combines all columns from DoctorNotes and Users into Sql and then how can I use it like " tblC.ModifiedByUserFK equals objModifiedU.UserID" ? Thanks
转载于:https://stackoverflow.com/questions/17397761/convert-from-linq-to-sql-query-to-sql-query

本人是.net程序员,因为英语不行,使用工具翻译,希望对有需要的人有所帮助
如果本文质量不好,还请谅解,毕竟这些操作还是比较费时的,英语较好的可以看原文

留言回复
我们只提供高质量资源,素材,源码,坚持 下了就能用 原则,让客户花了钱觉得值
上班时间 : 周一至周五9:00-17:30 期待您的加入