C# Complex Linq - 在子查询中连接两个表

Question

我目前正在开发一个使用非常复杂的数据库的系统。

我需要从多个表和关联中编译信息以显示这样的列表(我有很多):

[List] (Class `List`)
     [Id]
     [EntityId]
     [Name]
     [Description]
     [Users] (From `List<User> List.AssignedUsers` joined to `List<ListMember> List.Members`)
         [Id]
         [EntityId]
         [Username]
         [FullName]
         [Email]
         [ListId]
         [Order] (From `ListOrder` property in `ListMember` object)
         [Status] (From `Status` property in `ListMember` object)
             [Id]
             [NameEn]
             [NameFr]
             [Color]
             [Type]
             [Tag]
             [Options]

其中，List.AssignedUsers 是可以访问列表的用户列表。 List.Members 是列表中当前活跃用户的列表，包含他们的状态。如果 AssignedUser 不在 Members 中，则 Status 和 Order 属性必须为空。

数据库是这样设计的:

这是我尝试使用的代码:

var lists =
    await (from l in db.Lists.Include("Members").Include("AssignedUsers")
           where l.DateDeleted == null
           select new ListModel
           {
               Id = l.Id,
               EntityId = l.EntityId,
               Name = l.Name,
               Description = l.Description,
               Users = (
                   from u in l.AssignedUsers
                   join lms in l.Members on u.Id equals lms.UserId into members
                   from lm in members.DefaultIfEmpty()
                   select new UserModel
                   {
                       Id = u.Id,
                       EntityId = u.EntityId,
                       Username = u.Username,
                       FullName = u.FullName,
                       Email = u.Email,
                       ListId = l.Id,
                       Order = (lm == null ? lm.ListOrder : -1),
                       Status = (lm != null ? new StatusModel
                       {
                           Id = lm.Status.Id,
                           Type = lm.Status.Type,
                           Color = lm.Status.Color,
                           Tag = lm.Status.Tag,
                           NameEn = lm.Status.NameEn,
                           NameFr = lm.Status.NameFr,
                           Options = lm.Status.AvailableOptions
                       } : null)
                   }
               ).ToList()
           }).ToListAsync();

return lists;

问题是这个查询非常慢，我的 w3wp.exe 进程没有返回结果，而是崩溃了，一个漂亮的窗口告诉我 .NET 崩溃了。错误日志除了以下内容什么也没说:

Faulting application name: w3wp.exe, version: 10.0.10240.16384, time stamp: 0x559f3dad
Faulting module name: ntdll.dll, version: 10.0.10240.16430, time stamp: 0x55c599e1
Exception code: 0xc00000fd
Fault offset: 0x0003dcb9
Faulting process id: 0x4998
Faulting application start time: 0x01d1134884f4b8dd
Faulting application path: C:\Windows\SysWOW64\inetsrv\w3wp.exe
Faulting module path: C:\Windows\SYSTEM32\ntdll.dll
Report Id: 52069c20-912c-428a-b4fc-271851dbaba0
Faulting package full name: 
Faulting package-relative application ID: 

现在，我尝试了其他几种方法，但我正在寻找性能最高的方法，如果 LINQ 做不到，我会直接用 SQL 做!

Answer 1

它很慢，因为您阻止库使用嵌套的 ToList 调用来使用单个查询。

相反，如果您只是为您的对象、连接和所有内容建立一个很好的描述，并在最后调用 ToArray(为什么要列出？)，那会很好，但是您的方式调用了查询的第一部分在开头，然后为返回的每个项目调用子查询来填充结果。

至于它崩溃的原因，它是同样写得很好的条件:lm == null ？ lm.ListOrder : -1 -- 您可以通过简单地调试过程轻松地看到它。