Linq, Entity - Join tables

Posted on 2015-01-03
Last Modified: 2016-02-15
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
                            Select Tbl1

Open in new window

Question by:VBdotnet2005
  • 3
  • 2

Expert Comment

ID: 40529532
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


Author Comment

ID: 40529561
Does using join works better than select all tables like my sample above? Would the performance be any difference?

Expert Comment

ID: 40529606
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.
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center


Author Comment

ID: 40529891
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("*"))...

Assisted Solution

Camillia earned 250 total points
ID: 40530144
Take a look at this for the error. Looks like you need to adjust your operator
LVL 11

Accepted Solution

louisfr earned 250 total points
ID: 40531687
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: (see section entitled "Perform a Left Outer Join by Using the Group Join Clause") and put the second test in the Where clause.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Convert Select to DropDownListFor MVC 5 2 29
Add a Condition in ASP Repeater 6 33
FInd Image Control Gridview 3 20
Visual studio 2015 1 21
User art_snob ( encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question