Solved

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

Posted on 2010-11-07
10
311 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

770 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