.net - Linq - 'Queries with local collections are not supported' 时该怎么办

Question

我正在尝试将 SQL 查询转换为 Linq，但出现异常:

System.NotSupportedException:不支持使用本地集合的查询。

那么有没有办法以“合法”的方式重新表述查询？

感谢您的任何建议，

安德斯，丹麦

PS:我已经看到了在调用 .Contains() 之前对任何查询调用 .ToList() 的“解决方案”——但这只会导致四个单独的查询，对吗？

底部是我执行失败的代码。

我试图解决的逻辑问题是找到用户有权访问的“结构”，无论是通过直接访问还是通过具有访问权限的组的成员身份。我们没有嵌套组。

换句话说，如果满足以下任一条件，则用户应该具有访问权限:

• The user created the structure (id is 'stamped into' the structure record).
• An entry exists in the PermissionUsers table, linking the Permissions Table with the users table.
• an entry exists in the PermissionGroups table, linking the Permissions table with the Groups table and the user is referenced in the UsersGroups table, linking Users table with the Users table.

将权限/限制结果应用于可访问的项目是一个相对较新的事物，我们希望数据库能够完成繁重的工作；)。

[TestFixture]
public class LinqTest : CommandBasedIntegrationTestBase
{
    [Test, Category("SuperIntegration"), RequiresSTAAttribute]
    public void TestThat()
    {
        DbIntegrationTestHelper.SetState();

        var dataContextProvider = ObjectFactory.GetInstance<IDataContextProvider>();

        const int userId = 1;

        var environment = ObjectFactory.GetInstance<IState>().DatabaseEnvironment;
        var dataClasses1DataContext = dataContextProvider.GetContext(environment);

        var idsByCreator = from r in dataClasses1DataContext.Structures where r.CreatedUserId == userId select r.StructureId;
        var idsByUserAccess = from r in dataClasses1DataContext.PermissionUsers where r.UserId == userId select r.StructureId;
        var idsOfGroupsContainingUser = from r in dataClasses1DataContext.UsersGroups where r.UserId == userId select r.GroupId;
        var idsByGroupAccess = from r in dataClasses1DataContext.PermissionGroups where idsOfGroupsContainingUser.Contains( r.GroupId ) select r.StructureId;


        var res = from s in dataClasses1DataContext.Structures
                  where
                      idsByCreator.Contains(s.StructureId) || 
                      idsByUserAccess.Contains(s.StructureId) ||
                      idsByGroupAccess.Contains(s.StructureId)
                  select s;

        foreach (var structure in res)
        {
            Debug.WriteLine(structure.Name);
        }
    }
}

Answer 1

尝试JOIN、UNION

示例

var ctx = dataContextProvider.GetContext(environment);

var byCreator = from r in ctx.Structures where r.CreatedUserId == userId select r;

var byUserAccess = from r in ctx.PermissionUsers 
                   join s in ctx.Structures on r.StructureId equals s.StructureId
                   where r.UserId == userId select s;

var byGroups = from ug in ctx.UsersGroups 
               join pg in ctx.PermissionGroups on ug.GroupId equals pg.GroupId
               join s in ctx.Structures on pg.StructureId equals s.StructureId
               where ug.UserId == userId select s;

var res = byCreator.Union(byUserAccess).Union(byGroups);

更新

根据@Magnus 的评论，我已经删除了对 Distinct() 的不必要的调用。仅当使用 Concat() 而不是 Union() 时，才需要调用 Distinct()。