Entity Framework attaches query with int array

Question

I have 3 tables in my database.

--- Student ---

id - name

--- Tongue ---

id - lang

--- StudentLanguage --- ( General table )

id - studentId - langId

Students may have more languages. I want to search for students with int[] array values. But not with IN() - Contains() , it must be with the and - && operator, and this operator takes 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 make this request with Entity Framework? (... where new int[] { 1,3,5 } )

Answer 1

This code generates some awkward sql request ...

  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) 

Answer 2

 List<int> langIds = new int[] { 1, 3, 5 }.ToList(); var c = langIds.Count; var res2 = _context.Students .Where(x => x.StudentLanguages .Where(l => langIds.Contains(l.langId)).Select(y => y.langId).Distinct().Count() >= c); 

result SQL:

 SELECT [t0].[id], [t0].[name] FROM [dbo].[Student] AS [t0] WHERE (( SELECT COUNT(*) FROM ( SELECT DISTINCT [t1].[langId] FROM [dbo].[StudentLanguage] AS [t1] WHERE ([t1].[langId] IN (1, 3, 5)) AND ([t1].[studentId] = [t0].[id]) ) AS [t2] )) >= 3 

Use Distinct if StudentLanguages could theoretically have several overlapping StudentId packages - LangId .

Answer 3

Try something like this ...

 resultArr = []; For(int i = 0; i<arr.length; i++) { var result = db.Student.Where(s=>s.StudentLanguage.langId == arr[i]); resultArr.append(result); } 

Answer 4

I am trying to find how to do this, and here is what I get:

 int[] langIds = new int[] { 1, 3, 5 }; var lang = _context.Languages.Where(x => langIds.Contains(x.id)); var result = _context.Students.Where(x => !lang .Except(x.StudentLanguages .Select(y => y.Language) .Intersect(lang)).Any()); 

Here I use the Except and Intersect linqToSQL extension methods.

It produces this SQL statement:

 SELECT [t0].[id], [t0].[name] FROM [dbo].[Student] AS [t0] WHERE NOT (EXISTS( SELECT NULL AS [EMPTY] FROM ( SELECT DISTINCT [t1].[id], [t1].[name] FROM [dbo].[Language] AS [t1] WHERE (NOT (EXISTS( SELECT NULL AS [EMPTY] FROM ( SELECT DISTINCT [t3].[id], [t3].[name] FROM [dbo].[StudentLanguage] AS [t2] INNER JOIN [dbo].[Language] AS [t3] ON [t3].[id] = [t2].[langId] WHERE (EXISTS( SELECT NULL AS [EMPTY] FROM [dbo].[Language] AS [t4] WHERE ([t3].[id] = [t4].[id]) AND ([t4].[id] IN (@p0, @p1, @p2)) )) AND ([t2].[studentId] = [t0].[id]) ) AS [t5] WHERE [t1].[id] = [t5].[id] ))) AND ([t1].[id] IN (@p3, @p4, @p5)) ) AS [t6] )) 

Please note that I populate the languages ​​from the database. Unfortunately, you cannot use local collections in your query , since LinqToSQL does not know how to translate them into SQL. If you do the same with a local int array or any other collection, you will get this exception:

LINQ To SQL exception: A local sequence cannot be used in LINQ to SQL implementation of query statements other than the statement Contains

Answer 5

 var db = new MyDbContext(); var langIds = new[] { 1, 3, 5 }; IEnumerable<Student> student = from st in db.student select st; foreach (var item in langIds) { student = student.Join(db.StudentLanguage.Where(w => w.langId == item), st => st.studentId, stMap => stMap.id, (st, stMap) => new { Student= stMap, StudentLanguage = st}) .Select(x => x.Student).Distinct(); } 

I can do it like this. Maybe foreach can rotate linq, and this code can only be one line and a little short.