1

I want to apply join into command using a column and a variable value.

here's the code (the partial part of the query where I have an issue):

 join p in db.user_tests on a.id equals p.test_id into g3
 from x3 in g3.DefaultIfEmpty()

This code works, but I also need to filter the db.user_tests by the user_id. I have that user_id in a variable userId inside the function.

So I decided to write the query as follows:

join p in db.user_tests on a.id equals p.test_id && userId equals p.user_id into g3
from x3 in g3.DefaultIfEmpty()

But I get "Operator && cannot be applied to operands of type long and bool" error.

I tried with equal but it throws several errors.

I also tried with the two column join, but I use a variable in the comparison so it doesn't work.

How can I use the join into with a column comparison and a variable at the same time?

Community
  • 1
  • 1
Liron Harel
  • 10,819
  • 26
  • 118
  • 217

1 Answers1

1

If you want to join on several properties you should use anonymous objects for that:

join p in db.user_tests 
on new { a.id, userId } equals new { id = p.test_id, userId = p.user_id } into g3
from x3 in g3.DefaultIfEmpty()

Also make sure that anonymous objects have properties with same types and names.

But thus userId is not part of your a object it makes no sense to use that variable as part of join. You can simply join on test_id and use filter by user_id:

join p in db.user_tests.Where(x => x.user_id == userId) 
on a.id equals np.test_id into g3
from x3 in g3.DefaultIfEmpty()
Sergey Berezovskiy
  • 232,247
  • 41
  • 429
  • 459
  • Yep, I am checking with the same types, because it through an error before. so I do casting and checking again. – Liron Harel Nov 16 '15 at 15:17
  • @IdanShechter if order, names and types of anonymous objects are same, then query will be compiled and executed without any issues. And generated query will look like `ON a.Id = p.test_id AND @userId = p.user_id`, user Id will be passed as query parameter – Sergey Berezovskiy Nov 16 '15 at 15:22