c# - LINQ 的 SQL 脚本

Question

我需要一些帮助来将 SQL 脚本转换为 Linq。有2张大 table 。一个包含有关用户所在公司的信息，另一个包含有关用户登录时间和应用程序的信息。我需要的是特定公司用户的所有登录记录和用户本身的一些信息。

我的 SQL 查询看起来像这样并且执行得非常好。

SELECT 
UserLog.[ApplicationTypeId] AS [ApplicationTypeId], 
UserCompany.[EmployeeId] AS [EmployeeId], 
UserCompany.[LastLoggedOn] AS [LastLoggedOn], 
UserCompany.[ICalLastAccessedOn] AS [ICalLastAccessedOn], 
CASE WHEN ((UserLog.[Success] IS NOT NULL) AND (UserLog.[Success] = 1)) THEN 1 ELSE 0 END AS [C1]
FROM  
(select Enabled, CompanyId, EmployeeId, Userid, LastLoggedOn, ICalLastAccessedOn from UserCompany  WHERE [Enabled] = 1 AND CompanyId = CompanyId) AS UserCompany
LEFT JOIN 
(select * from [dbo].[UserLog] AS [Extent2] where [Extent2].[LoginDateTime] >= '2017-01-04 00:00:00' and [CompanyId] = CompanyId) as UserLog
ON UserCompany.[UserId] = UserLog.[UserId]

我在 C# 上的尝试看起来像这样，但表现非常糟糕。

var loginRecordsByEmployee = (
from uc in 
    (from userCompany in _repository.UserCompanies.FindAll()
     where userCompany.Enabled && userCompany.CompanyId == _user.CompanyId
     select new { userCompany.EmployeeId, userCompany.LastLoggedOn, userCompany.ICalLastAccessedOn, userCompany.UserId }) 
join ul in 
    (from log in _repository.UserLogs.FindAll()
     where log.LoginDateTime >= fromDate && log.CompanyId == _user.CompanyId
     select new { log.UserId , log.ApplicationTypeId, log.Success}) 
on uc.UserId equals  ul.UserId
select new
{
    uc.EmployeeId,
    uc.LastLoggedOn,
    uc.ICalLastAccessedOn,
    ul.ApplicationTypeId,
    Success = ul.Success.HasValue && ul.Success.Value ? 1 : 0
}).ToList();

查询 Entity Framework 生成的是使用 INNER JOIN 并在连接后添加 WHERE，而不是过滤每个选择。

exec sp_executesql N'SELECT 
[Extent2].[ApplicationTypeId] AS [ApplicationTypeId], 
[Extent1].[EmployeeId] AS [EmployeeId], 
[Extent1].[LastLoggedOn] AS [LastLoggedOn], 
[Extent1].[ICalLastAccessedOn] AS [ICalLastAccessedOn], 
CASE WHEN (([Extent2].[Success] IS NOT NULL) AND ([Extent2].[Success] = 1)) THEN 1 ELSE 0 END AS [C1]
FROM  [dbo].[UserCompany] AS [Extent1]
INNER JOIN [dbo].[UserLog] AS [Extent2] ON [Extent1].[UserId] = [Extent2].[UserId]
WHERE ([Extent1].[Enabled] = 1) AND ([Extent1].[CompanyId] = @p__linq__0) AND ([Extent2].[LoginDateTime] >= @p__linq__1) AND ([Extent2].[CompanyId] = @p__linq__2)',N'@p__linq__0 uniqueidentifier,@p__linq__1 datetime2(7),@p__linq__2 uniqueidentifier',@p__linq__0='EF9DB139-1C72-452F-BE88-6315E44DC2ED',@p__linq__1='2017-01-04 00:00:00',@p__linq__2=CompanyId

是否可以在 LINQ to SQL 中编写特定查询，或者像 Dapper 这样的东西是我唯一的方法吗？如果您碰巧看到任何性能问题或其他方法，我们将不胜感激并随时提供帮助。

Answer 1

感谢您的精彩评论。我根据您的意见想出了这个 LINQ。

    var loginRecordsByEmployee = (
from uc in _repository.UserCompanies.FindAll()
    join ul in _repository.UserLogs.FindAll() on uc.UserId equals ul.UserId into uls
    from log in uls.Where(u => u.LoginDateTime >= fromDate && u.CompanyId == _user.CompanyId).DefaultIfEmpty()
where uc.Enabled && uc.CompanyId == _user.CompanyId
select new
{
  uc.EmployeeId,
  uc.LastLoggedOn,
  uc.ICalLastAccessedOn,
  ApplicationTypeId = log == null ? 0 : log.ApplicationTypeId,
  Success = log != null && log.Success.HasValue && log.Success.Value ? 1 : 0,
  HasLog = log != null
}).ToList();

这会导致此查询并具有足够的性能。

exec sp_executesql N'SELECT 
1 AS [C1], 
[Extent1].[EmployeeId] AS [EmployeeId], 
[Extent1].[LastLoggedOn] AS [LastLoggedOn], 
[Extent1].[ICalLastAccessedOn] AS [ICalLastAccessedOn], 
CASE WHEN ([Extent2].[UserLogId] IS NULL) THEN 0 ELSE [Extent2].[ApplicationTypeId] END AS [C2], 
CASE WHEN (([Extent2].[UserLogId] IS NOT NULL) AND ([Extent2].[Success] IS NOT NULL) AND ([Extent2].[Success] = 1)) THEN 1 ELSE 0 END AS [C3], 
CASE WHEN ([Extent2].[UserLogId] IS NOT NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C4]
FROM  [dbo].[UserCompany] AS [Extent1]
LEFT OUTER JOIN [dbo].[UserLog] AS [Extent2] ON ([Extent1].[UserId] = [Extent2].[UserId]) AND ([Extent2].[LoginDateTime] >= @p__linq__0) AND ([Extent2].[CompanyId] = @p__linq__1)
WHERE ([Extent1].[Enabled] = 1) AND ([Extent1].[CompanyId] = @p__linq__2)',N'@p__linq__0 datetime2(7),@p__linq__1 uniqueidentifier,@p__linq__2 uniqueidentifier',@p__linq__0='2017-01-04 00:00:00',@p__linq__1='EF9DB139-1C72-452F-BE88-6315E44DC2ED',@p__linq__2='EF9DB139-1C72-452F-BE88-6315E44DC2ED'