如何将此SQL查询转换为LINQ或Lambda表达式? [英] How to convert this SQL query to LINQ or Lambda expression?

问题描述

我有以下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表达式?

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

感谢您的帮助！

推荐答案

这可能会满足您的要求:

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 }
    );

但是，我同意乔恩所说，要真正简化生活，应该通过创建"Layout_Position"表来更改处理多对多关系的方式.

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.

这篇关于如何将此SQL查询转换为LINQ或Lambda表达式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！