Solved

Linq, Entity - Join tables

Posted on 2015-01-03
6
150 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

932 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now