CODEWITHSUNDEEP
c#entity-frameworkt-sql
devzero
devzero

Reputation: 2670

How to do a JOIN in EF when the join condition is a like

I have the following SQL query I would like to convert into a EF Linq query, and I don't know how I can write the "like" part. 

SELECT DepartmentsWithChildren.*
FROM dbo.People
    INNER JOIN dbo.DepartmentPersons
        ON DepartmentPersons.Person_Id = People.Id
    INNER JOIN dbo.DepartmentsWithChildren
        ON DepartmentsWithChildren.lvl LIKE '%,' + CAST(DepartmentPersons.Department_Id AS VARCHAR(50)) + ',%'
WHERE UserId = '01cb89b4-9f81-4012-a4d9-5a38468a7433'

Example result from the above query

Id  ParentDepartmentId  lvl
58  27  ,27,3,1,
64  27  ,27,3,1,
67  27  ,27,3,1,
77  27  ,27,3,1,
90  27  ,27,3,1,
93  27  ,27,3,1,
100 27  ,27,3,1,
102 27  ,27,3,1,
106 27  ,27,3,1,
134 27  ,27,3,1,
137 27  ,27,3,1,
396 27  ,27,3,1,
414 27  ,27,3,1,
171 67  ,67,27,3,1,
206 67  ,67,27,3,1,
219 67  ,67,27,3,1,

To clarify, I want to know how to do a LIKE part of a JOIN, I'm aware that in a WHERE clause I can use contains, but for JOINS, every example I find only uses equality, f.eks: How to join tables in EF LINQ

Upvotes: 1

Views: 1967

Answers (2)

Isma
Isma

Reputation: 15190

You can only use the equals operator when using join in Linq, below is a possible workaround.

Assuming your DbContext is called db:

var result = from people in db.People
             from departmentWithChildren in db.DepartmentsWithChildren
             join departmentPerson in db.DepartmentPersons 
                             on people.Id equals departmentPerson.Person_Id
              where departmentWithChildren.Any(d => d.lvl.Contains("," + 
                             departmentPerson.Deparment_Id + ",")) &&
                             people.UserId = "01cb89b4-9f81-4012-a4d9-5a38468a7433"
              select departmentWithChildren;

Upvotes: 2

Thomas Koelle
Thomas Koelle

Reputation: 3742

A good and easy way is to do it with two linq queries.

var dict = from row in entities.departments where yada yada select row.Id;

var query = from row in entities.departmentpersons where
            dict.Contains(row.departmentId)
            select row;

Upvotes: 1

Related Questions