CODEWITHSUNDEEP
c#linqpostgresqlentity-framework-corenpgsql
Ryan
Ryan

Reputation: 2065

LINQ to SQL [npgsql] Generating incorrect query from SelectMany

I'm currently using Linq to SQL within ASP.NET Core 1.0 with the npgsql PostgreSQL adapter.

I have the following LINQ query which is expected to return a list of type Device

var devices = DeviceConfigurationDbContext.UserGroupMembershipList
            .AsNoTracking()
            .Where(ugml => ugml.UserId == currentUser.Id)
            .Select(ugml => ugml.UserGroup)
            .SelectMany(ug => ug.UserGroupAccessList)
            .Select(uga => uga.DeviceGroup)
            .SelectMany(dg => dg.Devices);

The intention of this code is to find all Devices that a certain user is allowed to access by doing a Where against the UserGroupMembershipList, and then joining the rest of the tables in until a list of Device is reached.

The relationship between the entities is :

UserGroupMembershipList -(many to one)-> UserGroup -(one to many)-> UserGroupAccessList -(many to one)-> DeviceGroup -(one to many)-> Device

UserGroupAccessList is an ACL that acts as a many to many join table between UserGroup and DeviceGroup.

This then generates the SQL query:

SELECT "ugml"."Id", "ugml"."DeviceGroupId", "d"."DeviceGroupId", "d"."Name", "uga.DeviceGroup"."Id"
FROM "Device" AS "ugml"
INNER JOIN "UserGroup" AS "ugml.UserGroup" ON "ugml"."UserGroupId" = "ugml.UserGroup"."Id"
CROSS JOIN "UserGroupAccess" AS "uga"
INNER JOIN "DeviceGroup" AS "uga.DeviceGroup" ON "uga"."DeviceGroupId" = "uga.DeviceGroup"."Id"
CROSS JOIN "Device" AS "d"
WHERE ("ugml"."UserId" = @__currentUser_Id_0) AND ("ugml.UserGroup"."Id" = "ugml"."UserGroupId")

Which in turn produces the error

An exception occurred in the database while iterating the results of a query. Npgsql.NpgsqlException: 42703: column ugml.UserGroupId does not exist

Which appears to be because the SQL query is for some reason doing a SELECT FROM "Device" AS ugml instead of SELECT FROM "UserGroupMembershipList" AS ugml. Additionally the where clause appears to be incorrect because of this.

Am I doing anything wrong when it comes to the Linq query? Are there any other ways to go about what I'm trying to accomplish that might avoid this error?

EDIT:

I have found a work around, although it is less than ideal.

var devices = (await DeviceConfigurationDbContext.UserGroupMembershipList
            .AsNoTracking()
            .Where(ugml => ugml.UserId == currentUser.Id)
            .Include(o => o.UserGroup)
                .ThenInclude(o => o.UserGroupAccessList)
                .ThenInclude(o => o.DeviceGroup)
                .ThenInclude(o => o.Devices)
                .ToListAsync())
            .Select(ugml => ugml.UserGroup)
            .SelectMany(ug => ug.UserGroupAccessList)
            .Select(uga => uga.DeviceGroup)
            .SelectMany(dg => dg.Devices);

This makes the query join the tables after the WHERE, and then return the entire result set as a List, from which standard Linq can operate on it in memory. It's less than ideal because I need to further refine the query afterwards, so much more data is transferred in comparison to doing everything in the database.

Upvotes: 1

Views: 954

Answers (1)

Raja Nadar
Raja Nadar

Reputation: 9499

Why not issue a single Select query and project out the results?

var devices = await DeviceConfigurationDbContext.UserGroupMembershipList
              .AsNoTracking()
              .Where(ugml => ugml.UserId == currentUser.Id)
              .Include(o => o.UserGroup)
              .ThenInclude(o => o.UserGroupAccessList)
              .ThenInclude(o => o.DeviceGroup)
              .ThenInclude(o => o.Devices)
              .SelectMany(ugml =>  ugml.UserGroup.UserGroupAccessLists
                                       .Select(ugal => ugal.DeviceGroup.Devices)
              .ToListAsync();

Try the above query and let us know if you see any issues.

If you still see the issues, i suspect that the npgsql adapter still has some wrinkles around the adapter, since I have followed some similar bugs in the npgsql branch in github.

Upvotes: 2

Related Questions