Solved

Linq, Entity - Join tables

Posted on 2015-01-03
6
157 Views
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
                            Tbl2.LoginID.Equals(Session("LoginId"))
                            Select Tbl1

Open in new window

0
Comment
Question by:VBdotnet2005
  • 3
  • 2
6 Comments
 
LVL 7

Expert Comment

by:Camillia
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

0
 

Author Comment

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

Expert Comment

by:Camillia
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:VBdotnet2005
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("*"))...
0
 
LVL 7

Assisted Solution

by:Camillia
Camillia earned 250 total points
ID: 40530144
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
 
LVL 11

Accepted Solution

by:
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: 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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
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…

685 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