Solved

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

Posted on 2010-11-07
10
314 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

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.
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.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

691 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