How to improve this LINQ TO SQL queryable?

Question

I've got a Student list where I would like to create a query where gets all the items and not 1 by 1 as I show the code below:

            IEnumerable<Student> = ...
            List<AnsweredTest> ats = new List<AnsweredTest>();

            foreach (var s in students)
            {
                query = from at in Database.Current.AnsweredTests
                        where at.StudentId == s.StudentId
                        select at;

                ats.Add(query.Single());
            }

            listView.ItemsSource = ats;

This is not good for the performance. I hope make myself clear, if not please let me know.

score 1 · Answer 1 · answered Jan 07 '13 at 21:11

1

var ats = Database.Current.AnsweredTests
    .Where(at => students.Any(s => s.StudentId == at.StudentId).ToList()

answered Jan 07 '13 at 21:11

lante

7,192
4
37
57

score 1 · Accepted Answer · answered Jan 07 '13 at 21:13

1

Either do a Join:

   query = from at in Database.Current.AnsweredTests 
    join s in Database.Current.Students on at.StudentId == s.StudentId
    where s... // filter s if needed.
    select at;

Or grab the list of student id's and pass it into the query:

   int[] studentIds = students.Select(s => s.StudentId).ToArray();
   query = from at in Database.Current.AnsweredTests
     where studentIds.Any(id => at.StudentId == id)
     select at;

answered Jan 07 '13 at 21:13

jessehouwing

106,458
22
256
341

1

this works! In the another answers I was receiving a error like this: http://stackoverflow.com/questions/10862491/unable-to-create-a-constant-value-only-primitive-types – Darf Zon Jan 07 '13 at 21:17

Johan Lindqvist · Answer 3 · 2013-01-07T21:24:54.923

0

Maybe something like this:

var studentIds = students.Select(s => s.StudentId);
var ats = Database.Current.AnsweredTests.Where(at => studentIds.Contains(at.StudentId));

edited Jan 07 '13 at 21:24

answered Jan 07 '13 at 21:19

Johan Lindqvist

56
3

How to improve this LINQ TO SQL queryable?

3 Answers3