Solved

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

Posted on 2010-11-07
10
313 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
[X]
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
  • 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Nested forach loop to linq 3 41
denied execute as 13 57
calculate running total 8 51
Help with Report Viewer in VS2013 6 30
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

739 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