In linq to entity, how to retrieve objects matching a list of conditions without looping ???

Question

Dear all,

I play with a mysql database using ADO entity framework (through devart dot.connect).

I get lists of entity objects (a) from listboxs and I would like to make a linq query retrieving all object from a given entity table (b) where there is a b foreign key:

//THIS WORKS :
IQueryable<Nb_student> nbstud = MainForm.context.Nb_student_set;
IQueryable<Cat_student> catstud = MainForm.context.Cat_student_set.Where(c => c.id == 2);

var test1 = from nb in nbstud
            join bb in catstud on nb.cat_student_id equals bb.id
            select nb;

=> this gives me only matching nb_students (this is what I want...)

Now, suppose instead of catstud, I have a list populated through a listbox.

IEnumerable<Cat_student> catstud2 = param_cat_students.AsEnumerable();
var ttt2 = from nb in nbstud
           join bb in catstud2 on nb.cat_student_id equals bb.id
           select nb;

=> it does't work, the error is the following: {"Unable to create a constant value of type 'System.Collections.Generic.IEnumerable`1'. Only primitive types ('such as Int32, String, and Guid') are supported in this context."} it seems there is a problem with the ids (int32) for the join ???

More generally, my question is : In Linq to Entity, how to select items from a table matching various lists of criterias (lists are not knowed in advance since they are choosen by the users) without making lot of loops. I thougth Join was the solution, I also tried the .any and .contains syntaxes without success :

var tt3 = from nb in nbstud.Where
          (nbx => catstud2.Any(cat => cat.id == nbx.cat_student_id)
          select nb;

If you could advice me... This is I suppose a very common problem but I don't know how to proceed. Thanking you in advance,

Pierre

Answer 1

If you are using Linq 2 Entities 4 I think you can do

/* Get ids of selected elements */
IEnumerable<int> catstud2 = param_cat_students.AsEnumerable().Select(s => s.id);

/* Select the elements with the given ids from the database */
var ttt2 = from nb in nbstud where catstud2.Contains(nb.id);

If I remember correctly the .Contains will be translated to a SQL IN statement. However, I think there is an upper bound on the number of elements allowed in catstud2. You can also do this with Linq2SQL, while .Contains is not supported in Linq2Entities 1.0.

If you are using Linq2Entities 1.0 I think you will have to make a choice between looping (and doing multiple single-element-lookups) and retrieving all of the contents of the table from the database into memory (using .AsEnumerable()), doing the join in memory.