Solved

Help with Left Joins / Cross Joins T-SQL to LINQ

Posted on 2010-11-07
10
312 Views
Last Modified: 2013-12-16
Hi Experts,

Newbie to LINQ and would love to know how to replicate this little query as a LINQ query.

I just can't seem to get it

Thanks

C
SELECT tt.intTagID, tt.vchTag as [Tag], count(ttt.intTagID2) as [Count Tags]
FROM DT_tblTags tt LEFT JOIN (
							SELECT intTagID, intTagID2
							FROM DT_tblRelTags_Tags ttin
							WHERE ttin.intTagID = ISNULL(@intTagID, ttin.intTagID)
							) ttt
	ON tt.intTagID = ttt.intTagID
GROUP BY tt.intTagID, tt.vchTag, tt.intCompanyID
HAVING tt.vchTag LIKE '%' + @vchTagPart + '%'
AND tt.intCompanyID = @intCompanyID

Open in new window

0
Comment
Question by:Craig Lambie
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34082195
Hi,

I found one change in query
SELECT tt.intTagID, tt.vchTag as [Tag], count(ttt.intTagID2) as [Count Tags]
FROM DT_tblTags tt LEFT JOIN (
                                                        SELECT intTagID, intTagID2
                                                        FROM DT_tblRelTags_Tags ttin
                                                        WHERE ttin.intTagID = ISNULL(@intTagID, ttin.intTagID)
                                                        ) ttt
        ON tt.intTagID = ttt.intTagID
WHERE tt.vchTag LIKE '%' + @vchTagPart + '%'
AND tt.intCompanyID = @intCompanyID
GROUP BY tt.intTagID, tt.vchTag, tt.intCompanyID

Open in new window

0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 175 total points
ID: 34082281
I don't have a DB connection to test ATM, but see if this works for you:
var data = from tagsRow in DT_tblTags
           join relTagsRow in
               from r in DT_tblRelTags
               where r.intTagID = intTagID == null ? r.intTagID : intTagID
               select r
           on tagsRow.intTagID equals relTagsRow.intTagID
           group tagsRow by tagsRow.vchTag.Contains(vchTagPart) && tagsRow.intCompanyID == intCompanyID into g
           select g;

Open in new window

0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 34082288
P.S.

"intTagID" (line 4), "vchTagPart" (line 7), and "intCompanyID" (line 7) are all variables declared outside of this Linq statement. I used those to mimic your SQL's parameters.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 10

Assisted Solution

by:Mathiyazhagan
Mathiyazhagan earned 75 total points
ID: 34093911
I made some changes to Kaufmed's queryto group by based on all three columns and get count of them as you posted in SQL .hope the attcahed Query will help.

 
int? argIntTagID = null;
string argVchTagPart="";
int? argIntCompanyID =1;

var data = from tagsRow in DT_tblTags
   join relTagsRow in
         from r in DT_tblRelTags_Tags
         where r.intTagID == (argIntTagID == null ? r.intTagID : argIntTagID)
         select r
   on tagsRow.intTagID equals relTagsRow.intTagID
   where tagsRow.vchTag.Contains(argVchTagPart) && tagsRow.intCompanyID == argIntCompanyID
   group tagsRow by new {tagsRow.intTagID, tagsRow.vchTag, tagsRow.intCompanyID}
   into g    
   select new { g.Key, Count = g.Count() };

Open in new window

0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 34098072
Thanks guys, I will try to get back to this today.
I unfortunately have am dealing in VB.net for this one, so I have to make the decision to just change to c# and rewrite the project, or convert to VB.net - my mistake I put tagged it with c#, habits...
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34221900
Hi Author,

Does your issue is resolved?
Please update on this.
0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 34223545
Those points got distributed wrong, sorry.
Mathiyazhagan ID: 34093911 was supposed to get the 100 points, not Brichsoft the moderator.... Sorry Mathiyazhagan, if you can fix this, please do.

0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 34229525
You can use the "Request Assistance" feature above to have the site mods alter the points assignment(s).
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 34229528
Sorry, "Request Attention".
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

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.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

830 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