Linq, Entity - Join tables

This code below works. Instead of using "," when need joining two tables (tbl_report1 and tbl_report2), is there a way to use "join" instead? Like Tbl1 In ctx.tbl_report1 Join Tbl2 In ctx.tbl_report2 ? If I can use "Join", what would happen in Where? Do I still need everything?  Also, would the performance be different? Sorry, I am new to Linq or entity.

From Tbl1 In ctx.tbl_report1,
                            Tbl2 In ctx.tbl_report2
                            Where (Tbl2.TranValue.Equals(Tbl1.UnitID.ToString) Or Tbl2.TranValue.Equals("*")) And
                            (Tbl1.ShipYear.Equals(ASPxComboBoxYear.Value) Or Tbl1.ShipYear.Equals(ASPxComboBoxYear.Value - 1)) And
                            Tbl2.LoginID.Equals(Session("LoginId"))
                            Select Tbl1

Open in new window

VBdotnet2005Asked:
Who is Participating?
 
louisfrConnect With a Mentor Commented:
Linq's Join operator only works with equality. You cannot join on a complex condition. Instead, use a left outer join, like in this page: http://msdn.microsoft.com/en-us/library/bb918093.aspx (see section entitled "Perform a Left Outer Join by Using the Group Join Clause") and put the second test in the Where clause.
0
 
CamilliaCommented:
You can use join

From Tbl1 In ctx.tbl_report1
  join Tbl2 In ctx.tbl_report2 on (Tbl2.TranValue.Equals(Tbl1.UnitID.ToString) Or Tbl2.TranValue.Equals("*")) ....

Open in new window

0
 
VBdotnet2005Author Commented:
Does using join works better than select all tables like my sample above? Would the performance be any difference?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
CamilliaCommented:
I'm not sure. Run both in your code, grab the SQL statement and look at the difference when you run them in your database.
0
 
VBdotnet2005Author Commented:
I got this error below.

You must reference at least one range variable on both side of the 'Equals' operator.range.variables('Tbl2')

From Tbl1 In ctx.tbl_report1
  join Tbl2 In ctx.tbl_report2 on (Tbl2.TranValue.Equals(Tbl1.UnitID.ToString) Or Tbl2.TranValue.Equals("*"))...
0
 
CamilliaConnect With a Mentor Commented:
Take a look at this for the error. Looks like you need to adjust your operator

http://msdn.microsoft.com/en-us/library/bb763107(v=vs.90).aspx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.