CODEWITHSUNDEEP
c#linqgraphqldynamic-linq
Mark McGookin
Mark McGookin

Reputation: 1032

Dynamic Linq nested Select on collection property

We are using GraphQL Dotnet in a dotnet core 5.0 API.

We have a relational data structure like this

Organisation --> (Zero or many) Employers --> (Zero or many) Departments.

In an attempt to optimise our database queries we are doing some parsing of the query and hoping to limit the fields returned by SQL using dynamic linq.

They have some pretty basic examples like this

 var resultDynamic = context.Customers
             .Select("new { City, CompanyName }")
             .ToDynamicList();

So you can select Customers.City, and Customers.CompanyName to a new dynamic object list.

This is not far off what we want to do, however, some of the properties we want to query are nested in collections.

for instance the following graphQL query

organisations {
  id,
  name,
  employers {
    addressLine1
    }
  }
}

Should return a list of organisations with id and name populated, and their employers with only the addressline1 populated.

If our query is Database.Organisations.Include(x => x.Employers); graphql parses this fine, and we return the correct information. However, if you inspect the SQL, it's getting every field for organisation, and every field for employers, and GQL is basically trimming the data client side.

Using dynamic linq I can do this

Database.Organisations.Select("new Organisation {id, name}");

and it works great, returns the organisation with id and name populated, and those are the only SQL fields in the SQL select statement.

But as soon as I try to Select the addressLine1 field of employers I hit a bit of a brick wall.

I can do this

Database.Organisatons.Select("new Organisation {id, name, employers}");

and it will only select the required fields from organisations, but all employer fields are selected (and GQL does the trimming client side) but I can't figure out a way to do employers.addressLine1 so that the subquery in sql only selects the one field.

Trying things like Organisations.Select("new Organisation {id, name, employers.name}"); just results in

No property or field 'name' exists in type 'List`1'

Which is obviously because I Employers is a List property and I am basically trying to get that (List).Name ... which doesn't exist.

Ideally I want to do this in this string based select format as i can build that up by parsing the GraphQL query and then I could only select the fields required from the child objects, but I have no idea what the format of that would be and can't find any examples.

Upvotes: 0

Views: 708

Answers (1)

Stef Heyenrath
Stef Heyenrath

Reputation: 9830

It should be possible with employers.Select(name).

Example in LinqPad:

void Main()
{
    Rooms.Dump();
    (Rooms.Where(r => r.Id == 3) as IQueryable).Select("new { name, RoomReservations.Select(CheckinDate) as CheckinDates }").Dump();
}

Results into: enter image description here

And the generated SQL looks like (only the 'name' and the 'CheckinDate' are selected in the query):

-- Region Parameters
DECLARE @p0 Int = 3
-- EndRegion
SELECT [t0].[Name], [t1].[CheckinDate], (
    SELECT COUNT(*)
    FROM [Reservations] AS [t2]
    WHERE [t2].[RoomId] = [t0].[Id]
    ) AS [value]
FROM [Rooms] AS [t0]
LEFT OUTER JOIN [Reservations] AS [t1] ON [t1].[RoomId] = [t0].[Id]
WHERE [t0].[Id] = @p0
ORDER BY [t0].[Id], [t1].[Id]

--> Note that I tried to make a GraphQL library which uses DynamicLinq, however not all logic is supported. You can take a look here: https://github.com/StefH/GraphQL.EntityFrameworkCore.DynamicLinq

Upvotes: 1

Related Questions