LINQ 中的联接和子查询

发布于 2024-08-25 18:31:11 字数 618 浏览 3 评论 0原文

我正在尝试与子查询进行连接,但似乎无法得到它。这是在 sql 中工作的样子。我如何开始在 linq 中工作?

SELECT po.*, p.PermissionID
FROM PermissibleObjects po
 INNER JOIN PermissibleObjects_Permissions po_p ON (po.PermissibleObjectID = po_p.PermissibleObjectID)
 INNER JOIN Permissions p ON (po_p.PermissionID = p.PermissionID)
 LEFT OUTER JOIN
  (
  SELECT u_po.PermissionID, u_po.PermissibleObjectID
  FROM Users_PermissibleObjects u_po
  WHERE u_po.UserID = '2F160457-7355-4B59-861F-9871A45FD166'
  ) used ON (p.PermissionID = used.PermissionID AND po.PermissibleObjectID = used.PermissibleObjectID)
WHERE used.PermissionID is null

I am trying to do a join with a sub query and can't seem to get it. Here is what is looks like working in sql. How do I get to to work in linq?

SELECT po.*, p.PermissionID
FROM PermissibleObjects po
 INNER JOIN PermissibleObjects_Permissions po_p ON (po.PermissibleObjectID = po_p.PermissibleObjectID)
 INNER JOIN Permissions p ON (po_p.PermissionID = p.PermissionID)
 LEFT OUTER JOIN
  (
  SELECT u_po.PermissionID, u_po.PermissibleObjectID
  FROM Users_PermissibleObjects u_po
  WHERE u_po.UserID = '2F160457-7355-4B59-861F-9871A45FD166'
  ) used ON (p.PermissionID = used.PermissionID AND po.PermissibleObjectID = used.PermissibleObjectID)
WHERE used.PermissionID is null

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

呆萌少年 2024-09-01 18:31:11

如果没有看到您的数据库和数据模型,就几乎不可能提供任何真正的帮助。但是,最好的方法可能是:

  • 下载 linqpad - http://www.linqpad.net/
  • 从最里面的部分开始创建到数据库的连接
  • - 带有“where”子句的子查询
  • 使每个小查询正常工作,然后将它们连接起来。 Linqpad 将向您显示生成的 SQL 以及结果,因此构建您的小查询直到它们正确。

所以,基本上,将您的问题分解为更小的部分。 Linqpad 非常棒,因为它可以让您测试这些东西,并随时检查结果

希望这有帮助,祝

托比好运

Without seeing your database and data model, it's pretty impossible to offer any real help. But, probably the best way to go is:

  • download linqpad - http://www.linqpad.net/
  • create a connection to your database
  • start with the innermost piece - the subquery with the "where" clause
  • get each small query working, then join them up. Linqpad will show you the generated SQL, as well as the results, so build your small queries up until they are right

So, basically, split your problem up into smaller pieces. Linqpad is fantastic as it lets you test these things out, and check your results as you go

hope this helps, good luck

Toby

倾`听者〃 2024-09-01 18:31:11

您的查询的 LINQ 翻译非常简单:

from pop in PermissibleObjectPermissions
where !pop.UserPermissibleObjects.Any (
  upo => upo.UserID == new Guid ("2F160457-7355-4B59-861F-9871A45FD166"))
select new { pop.PermissibleObject, pop.PermissionID }

换句话说:“从所有对象权限中,检索至少具有一项用户权限且 UserID 为 2F160457-7355-4B59-861F-9871A45FD16 的对象权限”。

您会注意到,此查询使用关联属性来导航关系 - 这避免了“连接”的需要并简化了查询。因此,LINQ 查询比原始 SQL 查询更接近其英文描述。

编写 LINQ 查询时的技巧是改掉将 SQL“音译”为 LINQ 的习惯。

The LINQ translation for your query is suprisingly simple:

from pop in PermissibleObjectPermissions
where !pop.UserPermissibleObjects.Any (
  upo => upo.UserID == new Guid ("2F160457-7355-4B59-861F-9871A45FD166"))
select new { pop.PermissibleObject, pop.PermissionID }

In words: "From all object permissions, retrieve those with at least one user-permission whose UserID is 2F160457-7355-4B59-861F-9871A45FD16".

You'll notice that this query uses association properties for navigating relationships - this avoids the need for "joining" and simplfies the query. As a result, the LINQ query is much closer to its description in English than the original SQL query.

The trick, when writing LINQ queries, is to get out of the habit of "transliterating" SQL into LINQ.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文