?
Solved

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

Posted on 2010-11-07
10
Medium Priority
?
315 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 700 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 10

Assisted Solution

by:Mathiyazhagan
Mathiyazhagan earned 300 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

Major Serverless Shift

Comparison of major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform

Question has a verified solution.

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

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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

765 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