How to convert this SQL query to LINQ or Lambda expression?

Question

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 the following data

Position

ID      Code

1       TopMenu
2       BottomMenu

Category

ID      Name

1       Home
2       Contact
3       About

Markup

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

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

Any help is appreciated!

Answer 1

This can 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 can materialize the “Position” subheading to save over time as follows:

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".

Answer 2

, , , :

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

, "/LIKE" , 9 . " ", , . (, .)