如何将此 SQL 查询转换为 LINQ 或 Lambda 表达式？

发布于 2024-09-26 03:26:16 字数 697 浏览 4 评论 0原文

我有以下 SQL 查询：

SELECT C.ID, C.Name FROM Category C JOIN Layout L ON C.ID = L.CategoryID
JOIN Position P ON L.PositionID LIKE '%' + CAST(P.ID AS VARCHAR) + '%'
WHERE P.Code = 'TopMenu'

和以下数据

位置：

ID      Code

1       TopMenu
2       BottomMenu

类别

ID      Name

1       Home
2       Contact
3       About

布局

ID      CategoryID     PositionID
1       1              1
2       2              1,2
3       3              1,2

有了上述数据，是否可以将 SQL 查询转换为 LINQ 或 Lambda 表达式？

任何帮助表示赞赏！

原文

I have the following SQL query:

SELECT C.ID, C.Name FROM Category C JOIN Layout L ON C.ID = L.CategoryID
JOIN Position P ON L.PositionID LIKE '%' + CAST(P.ID AS VARCHAR) + '%'
WHERE P.Code = 'TopMenu'

and following data

Position:

ID      Code

1       TopMenu
2       BottomMenu

Category

ID      Name

1       Home
2       Contact
3       About

Layout

ID      CategoryID     PositionID
1       1              1
2       2              1,2
3       3              1,2

With the above data, is it possible to convert the SQL query to LINQ or Lambda expression?

Any help is appreciated!

分享到QQ

分享到微博

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

发布评论

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

凹づ凸ル 2024-10-03 03:26:16

这可能会满足您的要求：

Layout
    .Where(x => Position
        .Where(y => y.Code == "TopMenu")
        .Select(y => SqlClient.SqlMethods.Like(x.PositionID, "%" + y.ID.ToString() + "%")
        ).Count() > 0
    ).Join(
        Category,
        x => x.CategoryID,
        x => x.ID,
        (o,i) => new { ID = i.ID, Name = i.Name }
    )

尽管您可能希望实现“位置”子查询以节省时间，如下所示：

var innerSubQuery = Position.Where(y => y.Code == "TopMenu");

Layout
    .Where(x => innerSubQuery
        .Select(y => SqlClient.SqlMethods.Like(x.PositionID, "%" + y.ID.ToString() + "%")
        ).Count() > 0
    ).Join(
        Category,
        x => x.CategoryID,
        x => x.ID,
        (o,i) => new { ID = i.ID, Name = i.Name }
    );

但是，我同意乔恩的观点，为了真正让您的生活更简单，您应该改变处理方式通过创建“Layout_Position”表来建立多对多关系。

This might do what you want:

Layout
    .Where(x => Position
        .Where(y => y.Code == "TopMenu")
        .Select(y => SqlClient.SqlMethods.Like(x.PositionID, "%" + y.ID.ToString() + "%")
        ).Count() > 0
    ).Join(
        Category,
        x => x.CategoryID,
        x => x.ID,
        (o,i) => new { ID = i.ID, Name = i.Name }
    )

Although you might want to materialize the 'Position' sub query to save on time like so:

var innerSubQuery = Position.Where(y => y.Code == "TopMenu");

Layout
    .Where(x => innerSubQuery
        .Select(y => SqlClient.SqlMethods.Like(x.PositionID, "%" + y.ID.ToString() + "%")
        ).Count() > 0
    ).Join(
        Category,
        x => x.CategoryID,
        x => x.ID,
        (o,i) => new { ID = i.ID, Name = i.Name }
    );

I do, however, agree with Jon that to really make your life simpler you should change the way you're handling the many-to-many relationship by creating a 'Layout_Position' table.

回复收藏 0 原文

轻许诺言 2024-10-03 03:26:16

好吧，您将无法将第二个连接表示为连接，因为它不是等值连接，但这应该可以做到：

from c in category
join l in layout on c.Id equals l.CategoryId
from p in position
where p.Id.Contains(l.PositionId)
select new { c.Id, c.Name };

请注意，当您有超过9 个职位。对于多对多关系，有比使用逗号分隔列表更好的方法。（例如中间表。）

Well, you won't be able to express the second join as a join, because it's not an equijoin, but this should do it:

from c in category
join l in layout on c.Id equals l.CategoryId
from p in position
where p.Id.Contains(l.PositionId)
select new { c.Id, c.Name };

Note that your "contains/LIKE" clause will give you bad results when you've got more than 9 positions. There are better approaches to many-to-many relations than using a comma-separated list. (Such as an intermediate table.)

回复收藏 0 原文

~没有更多了~