使用LINQ根据字符串中的id获取多个字段

本文关键字：获取 id 字段 LINQ 字符串 使用 | 更新日期: 2023-09-27 18:16:13

我有两个表:Table1: RolesTable和Table2: PrivilegeTable。

RolesTable中的样本数据 

Name |Description           |ModulePrivIds|FunctionPrivIds|Active
=================================================================
Role1|This is a Test Role   |1,2          |3,4            |1
Role2|This is another Role  |2,3          |1,3            |0

注意:ModulePrivId和 functionprivid 对应PrivilegeTable中的 id

PrivilegeTable

ID | Name |Description
========================
1  |Priv1 |This is Priv1
2  |Priv2 |This is Priv2
3  |Priv3 |This is Priv3
4  |Priv4 |This is Priv4

现在我想在网格中显示所有角色的数据。所以，代替ModulePrivId和functionprivid，我想用特权表来显示特权名。

这是我正在尝试，但无法达到预期的结果。

var answer = (from r in database.RolesRepository.Get()
            join p in database.PrivilegesRepository.Get()
            on r.ModulePrivilegeIds equals p.Id.ToString() into ModuleWisePrivileges
            from p1 in ModuleWisePrivileges.DefaultIfEmpty()
            from r1 in database.RolesRepository.Get()
            join p01 in database.PrivilegesRepository.Get()
            on r1.FunctionPrivilegeIds equals p01.Id.ToString() into FunctionWisePrivileges
            from p2 in FunctionWisePrivileges.DefaultIfEmpty()
            select new
            {
                Name = r.Name,
                Description = r.Description,
                ModuleWisePrivileges = ModuleWisePrivileges,
                FunctionWisePrivileges = FunctionWisePrivileges,
                Active = r.Active
            }).ToArray();
return answer;

我还尝试了以下操作，但没有成功。

var answer = (from r in database.RolesRepository.Get()
            join p in database.PrivilegesRepository.Get()
            on p.Id.ToList().Where(p => Id.Contains(r.ModulePrivilegeIds.ToString()) into ModuleWisePrivileges)
            from p1 in ModuleWisePrivileges.DefaultIfEmpty()
            from r1 in database.RolesRepository.Get()
            join p01 in database.PrivilegesRepository.Get()
            on r1.FunctionPrivilegeIds equals p01.Id.ToString() into FunctionWisePrivileges
            from p2 in FunctionWisePrivileges.DefaultIfEmpty()
            select new
            {
                Name = r.Name,
                Description = r.Description,
                ModuleWisePrivileges = ModuleWisePrivileges,
                FunctionWisePrivileges = FunctionWisePrivileges,
                Active = r.Active
            }).ToArray();

使用LINQ根据字符串中的id获取多个字段

如果你不能修复数据库，你就剩下一个多步骤的过程:

  1. 从RolesTable
    2. 中选择行
  2. 拆分ID字段
  3. 从数据库中选择权限

像这样:

var roles = database.RolesRepository.Get().ToArray();
var answers = roles.Select(r => new {
                Name = r.Name,
                Description = r.Description,
                ModuleWisePrivileges = r.ModulePrivilegeIds.Split(',')
                                        .Select(x => database.PrivilegesRepository.Get(x))
                                        .ToArray(),
                FunctionWisePrivileges = r.FunctionPrivilegeIds.Split(',')
                                          .Select(x => database.PrivilegesRepository
                                                               .Get(x))
                                          .ToArray(),
                Active = r.Active
            }

如果您的存储库允许您一次获得多个项目，基于id列表，则可以提高性能。
如果特权列表很小，最好先将它们全部放入内存:

var roles = database.RolesRepository.Get().ToArray();
var privileges = database.PrivilegesRepository.Get().ToDictionary(x => x.Id, x => x);
var answers = roles.Select(r => new {
                Name = r.Name,
                Description = r.Description,
                ModuleWisePrivileges = r.ModulePrivilegeIds.Split(',')
                                        .Select(x => privileges[x])
                                        .ToArray(),
                FunctionWisePrivileges = r.FunctionPrivilegeIds.Split(',')
                                          .Select(x => privileges[x])
                                          .ToArray(),
                Active = r.Active
            }

检查一次，用逗号分隔值更新上述代码

var roles = database.RolesRepository.Get().ToArray();
var answers = roles.Select(r => new {
            Name = r.Name,
            Description = r.Description,
            ModuleWisePrivileges = string.Join(", ", (r.ModulePrivilegeIds.Split(',')
                                    .Select(x => database.PrivilegesRepository.Get(x).FirstOrDefault())
                                    .ToArray()),
            FunctionWisePrivileges = string.Join(", ", (r.FunctionPrivilegeIds.Split(',')
                                      .Select(x => database.PrivilegesRepository.Get(x).FirstOrDefault())
                                      .ToArray()),
            Active = r.Active
        }

根据您的评论更新了查询

 var roles = database.RolesRepository.Get().ToArray();
 var answers = roles.Where(a => a.isDeleted == false).Select(r => new {
        Name = r.Name,
        Description = r.Description,
        ModuleWisePrivileges = string.Join(", ", (r.ModulePrivilegeIds.Split(',')
                                .Select(x => database.PrivilegesRepository.Get(x).FirstOrDefault())
                                .ToArray()),
        FunctionWisePrivileges = string.Join(", ", (r.FunctionPrivilegeIds.Split(',')
                                  .Select(x => database.PrivilegesRepository.Get(x).FirstOrDefault())
                                  .ToArray()),
        Active = r.Active
    }
相关文章：