Entity Framework join query with int array

Question

i have got 3 tables on my database.

---Student---

id - name

---Language---

id - lang

---StudentLanguage---(Common Table)

id - studentId - langId

Students can have more languages. i want to search students with an int[] array values. But not with IN() - Contains(), it must be with and - && operator and this operator take int[] values.

in sql =

`select t1.id, t1.name from Student t1 join StudentLanguage t2 
ON(t1.id=t2.studentId) where (t2.langId=1 and t2.langId=3 and t2.langId=5);`

so how can i do this query with Entity Framework? (...where new int[] { 1,3,5 })

Answer 1

This code generate some clumsy sql query...

        int[] ids = new[] { 1, 3, 5 };
        var acc = from st in db.Students select st;

        foreach (var id in ids)
        {
            int id1 = id;
            var res =
                from st in db.Students
                from lng in st.Language
                where lng.Id == id1
                select st;
            acc =
                from a in acc
                join st in res on a.Id equals st.Id
                select a;
        }

        acc.ToList();

... sql query :

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name]
FROM    [dbo].[Student] AS [Extent1]
INNER JOIN [dbo].[StudentLanguage] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Student_Id]
INNER JOIN [dbo].[StudentLanguage] AS [Extent3] ON [Extent1].[Id] = [Extent3].[Student_Id]
INNER JOIN [dbo].[StudentLanguage] AS [Extent4] ON [Extent1].[Id] = [Extent4].[Student_Id]
WHERE ([Extent2].[Language_Id] = @p__linq__0) 
       AND ([Extent3].[Language_Id] = @p__linq__1) 
       AND ([Extent4].[Language_Id] = @p__linq__2)