Linq, Entity - Join tables

Posted on 2015-01-03
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


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 1000 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 1000 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: 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.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

User art_snob (http://www.experts-exchange.com/M_6114203.html) 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.…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

719 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