SQL to Linq Conversion Issue

Question

Can someone help me convert a sql statement to linq? I can do very basic statement but I get confused when dealing with several joins and unions when using linq. Here is the SQL statement that I am trying to convert:

SELECT UserName, UPPER(FirstName) as FirstName, UPPER(LastName) as LastName 
FROM PDDA.dbo.Users 
WHERE Cono = " & Cono & " 

UNION 

SELECT u.UserName, UPPER(FirstName) as FirstName, UPPER(LastName) as LastName 
FROM PDDA.dbo.GroupMembers g 
JOIN PDDA.dbo.UserGroups ug
  on ug.groupid = g.groupid 
JOIN PDDA.dbo.Users u 
  on u.username = g.username 
WHERE ug.GroupName = 'AP Department' OR ug.GroupName = 'MIS' 
ORDER BY LastName, FirstName")

And here is my attempt so far:

    var userDb = new PDDAEntities();
    var users =
        ((from user in userDb.Users select user.UserName).Union(from gm in userDb.GroupMembers
            join ug in userDb.UserGroups on gm.GroupID equals ug.GroupID
            (from u in userDb.Users join ))
            select user.UserName;

http://stackoverflow.com/questions/296972/sql-to-linq-tool Please see here. — Michael McGriff, Jul 01 '14 at 19:08

Erik Funkenbusch · Accepted Answer · 2014-07-01T23:18:44.327

Break it down and it becomes easier. First step, the first part:

SELECT UserName, UPPER(FirstName) as FirstName, UPPER(LastName) as LastName 
FROM PDDA.dbo.Users WHERE Cono = " & Cono & "

This is pretty easy.

var query1 = from u in Users where u.Cono == yourCono 
     select new { u.UserName, u.FirstName, u.LastName }

The second part is a little harder, but not much.

SELECT u.UserName, UPPER(FirstName) as FirstName, UPPER(LastName) as LastName 
FROM PDDA.dbo.GroupMembers g 
JOIN PDDA.dbo.UserGroups ug on ug.groupid = g.groupid 
JOIN PDDA.dbo.Users u on u.username = g.username 
WHERE ug.GroupName = 'AP Department' OR ug.GroupName = 'MIS' 
ORDER BY LastName, FirstName

This would look something like this (not tested, but should be similar):

var query2 = from gm in GroupMembers 
   join ug in UserGroups on gm.groupid equals ug.groupid
   join u in Users on gm.UserName equals u.UserName
   where ug.GroupName == 'AP Department' || ug.GroupName == 'MIS'
   orderby u.LastName, u.FirstName
   select new { UserName = u.UserName, FirstName = u.FirstName, LastName = u.LastName }

Then just combine them.

var query3 = query1.Union(query2);

I'm not sure if the Order by orders the unioned set or not, if so, then you would just do this instead:

var query3 = query1.Union(query2).OrderBy(x => x.LastName).ThenBy(x => x.FirstName);

And you can remove the OrderBy from the query2 above.

EDIT:

Based on Striplings Query, you could probably even do this as well (assuming you have UserGroups navigation property configured):

from u in userDb.Users
where u.UserGroups.Any(ug => ug.GroupName == "AP Department" || ug.GroupName == "MIS") 
      || u.Cono == yourCono
orderby u.LastName, u.FirstName
select new {u.UserName, u.FirstName, u.LastName}

No Union required at all then.

@user3788671 - it helps to format your sql queries so you can get a grasp of the individual operations. When you format them like that, it becomes pretty obvious how to proceed. — Erik Funkenbusch, Jul 01 '14 at 19:35
Two notes. Linq's `Union` is actually the same as SQL `Union`. (`Concat` translates to `Union All`). `orderby` is written as: `orderby u.LastName, u.FirstName` in query syntax and `.OrderBy(u => c.LastName).ThenBy(u => u.FirstName)` with lambda. — Magnus, Jul 01 '14 at 20:53
@Magnus - Thanks for the correction, I didn't have anything to verify the syntax at the moment. And you're absolutely right about Union, not sure why I remembered that one wrong. — Erik Funkenbusch, Jul 01 '14 at 21:09

score 1 · Answer 2 · edited Jul 01 '14 at 21:11

1

I find LINQ to be far more intuitive than SQL, and most people's difficulty with it arises from trying to do a direct translation rather than letting LINQ do the work for them. Assuming you've got your table relationships set up right, you can probably do something like this:

from u in userDb.Users
where u.UserGroups.Any(ug => ug.GroupName == "AP Department" || ug.GroupName == "MIS")
orderby u.LastName, u.FirstName
select new {u.UserName, u.FirstName, u.LastName};

edited Jul 01 '14 at 21:11

Erik Funkenbusch

92,674
28
195
291

answered Jul 01 '14 at 19:21

StriplingWarrior

151,543
27
246
315

Thank you so much for the help. I will use your solution as Erik's to get something working. Thank you again! – user3788671 Jul 01 '14 at 19:33
Ahh.. yes, I like your use of Any over joining... not sure if that's more performant or not... – Erik Funkenbusch Jul 01 '14 at 19:36
Also, note that this requires that there be a UserGroups navigation property configured. – Erik Funkenbusch Jul 01 '14 at 19:44
To produce the same result as the SQL in the question (with `union`) you need to add a `.Distinct()` the the end of the linq query also. – Magnus Jul 01 '14 at 21:24
@ErikFunkenbusch: .Any() in this case is translated into a WHERE EXISTS, which last I checked is barely faster than a join--typically not enough to worry about. The big key is the improved readability. – StriplingWarrior Jul 01 '14 at 23:08
1

@Magnus - if implemented like I do above, then no Distinct is necessary because no Union is used... – Erik Funkenbusch Jul 01 '14 at 23:20
@StriplingWarrior - I can agree with that. – Erik Funkenbusch Jul 01 '14 at 23:21
@StriplingWarrior Im not saying its wrong just that it wont produce the same result as the query in the question as `union` will remove all duplicates from the result set (no matter which of the two queries in the union the duplicates belong to) and your query will not. – Magnus Jul 02 '14 at 07:19
@Magnus: I think you're replying to Erik's comment, not mine. Regardless, while you're technically correct, I find it highly unlikely that multiple users will be allowed to have the same username. And if they did, it's not entirely obvious whether the original behavior was more correct. Anyway, it's just splitting hairs--I think Erik did a fine job of answering the question. – StriplingWarrior Jul 02 '14 at 15:28

SQL to Linq Conversion Issue

2 Answers2