SQL到Linq转换问题

本文关键字：问题 转换 Linq SQL | 更新日期: 2023-09-27 18:10:57

有人能帮我转换sql语句linq吗?我可以做非常基本的声明，但我很困惑，当处理几个连接和联合时，使用linq。下面是我要转换的SQL语句:

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")

这是我到目前为止的尝试:

    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;

SQL到Linq转换问题

分解它，它变得更容易。第一步，第一部分:

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

这很简单。

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

第二部分稍微难一点，但不多。

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

这看起来像这样(没有测试，但应该是类似的):

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 }

那就把它们组合起来。

var query3 = query1.Union(query2);

我不确定Order by是否为并集排序，如果是，那么您只需这样做:

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

您可以从上面的query2中删除OrderBy。

编辑:

基于stripling Query，你甚至可以这样做(假设你配置了UserGroups导航属性):

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}

我发现LINQ比SQL更直观，大多数人使用它的困难来自于试图进行直接翻译，而不是让LINQ为他们完成工作。假设您已经正确设置了表关系，您可能可以执行如下操作:

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};
相关文章：