C#•3w ago

Dynamic EF Core LINQ Queries for API Consumption

Soo, anyone have any thoughts on this? I have 2 servers, one is an ASP.Net 9 API using EF Core and the other is an Astro.js client consuming the API (via serverside code only). The API server collects a lot of data from various sources that I want to use to generate datasets/analytics in a dashboard. The API is extremely low maintenance (i.e. I basically only have to touch it if I want to add new EF Core migrations for new data) and is essentially only acting as a tool to populate a DB and manage its schema. I want to be able to pretty much query the data however I want without having to touch the API server, so I was thinking of either using GraphQL or creating/using some kind of JSON to EF Core LINQ builder. I'm not finding any existing libraries that really do what I want to be able to dynamically build EF queries from JSON/API requests, and I'd rather not have to develop anything custom as I'm sure that'd be a PITA. But with GraphQL—which I'm not entirely familiar with so correct me if I'm wrong—it seems that if I added new DB entities or properties to those entities, or if I wanted to add additional features like (e.g. pagination or new queries), I'd still have to update the API query layer to some extent—so basically, mostly low maintenance and moderately flexible compared to being able to dynamically generate a LINQ query directly. My ideal is if I need to pull new sets of data, or calculate different stats on the API DB data, all I have to do is add a new query on the client server only, and if I did need to add new types/sources of data on the API side, I would only need to touch the DbContext and services that collect the data while leaving the API query layer alone… I basically wish I could just build EF Core LINQ queries directly from my client server. Thank you for reading all that 🫠. Suggestions/ideas appreciated.

10 Replies

Angius•3w ago

You're describing the exact and only use case for GraphQL

ManaravakOP•3w ago

I figured. I'm sure I'm just misunderstanding how GraphQL works. If, for example, I had a basic QueryA query that fetches A entities that have associated B and C entities, and let's say I'm currently just getting a paginated list of A without the other children entities. Would I have to modify QueryA or create a new query to then be able to fetch a list of A entities which contains at least 1 B.Prop1 == SomeValue and C.Created < SomeDateTime, and then order by them the average of C.Prop1 while also included that average field with each A entity returned? Not sure if such a generic example makes sense?

Angius•3w ago

By query, you mean the EF query? If so, then no. The GraphQL library will basically kinda-sorta just translate the GQL query to an EF query and execute that

Angius•3w ago

https://chillicream.com/docs/hotchocolate/v15

Introduction - Hot Chocolate

Hot Chocolate is the most efficient, feature-rich, open-source GraphQL server in the .NET ecosystem, that helps developers to build powerful APIs.

ManaravakOP•3w ago

By query, you mean the EF query?

Yes and no. I was referring to this: https://chillicream.com/docs/hotchocolate/v14/fetching-data/fetching-from-databases Which I suppose is doing that EF query building like you say. I just wasn't sure if already having pagination and filtering and so on, allows me to literally filter and return data anyway I want without having to make changes to the query. Basically, do I have to specify that the query can filter certain properties of the parent entity or by its child entities? Or does GraphQL know how to do all that by default, such that if I add new entities or properties, it can handle all the filtering, counting, groupings, etc., without me having to modifiy the query itself. Hard to know if I'm even asking the right questions, since I'm so unfamiliar with GraphQL haha.

Fetching from Databases - Hot Chocolate v14

Hot Chocolate is the most efficient, feature-rich, open-source GraphQL server in the .NET ecosystem, that helps developers to build powerful APIs.

ManaravakOP•3w ago

Maybe a better way is to show. Let's say I have the following example EF queries. For some context, Fictions are individual web novels on a website called RoyalRoad and Records are entities collected every 15 min that reference a specific fiction when it is on a Top 50 type list at the time of collecting the data. The fiction can rise and lower its ranking in the list or fall off completely, and one thing I plan on doing is crunching some numbers for performance analytics and stuff.

var data = await context.Fictions
  .Where(f => f.Tags.Select(t => t.Name).Contains("Fantasy")
  .ToListAsync();

var data = await context.Fictions
  .Where(f => f.Tags.Select(t => t.Name).Contains("Fantasy")
  .ToListAsync();

Is the above fundamentally different than the below when it comes to the GraphQL query method setup (not the request itself, just the C# method I register)? Or would I need to make changes/make a new query method to also be able to do stuff like the below?

var data = await context.Fictions
  .Where(f => f.Tags.Select(t => t.Name).Contains("Fantasy")
  .Where(f => f.Records.MaxBy(r => r.Rank).Rank >= 5)
  .Select(f => new
  {
    f.Id,
    f.Title,
    ViewsGained = f.Records.MaxBy(r => r.Views).Views - f.Records.MinBy(r => r.Views).Views,
  }
  .ToListAsync();

var data = await context.Fictions
  .Where(f => f.Tags.Select(t => t.Name).Contains("Fantasy")
  .Where(f => f.Records.MaxBy(r => r.Rank).Rank >= 5)
  .Select(f => new
  {
    f.Id,
    f.Title,
    ViewsGained = f.Records.MaxBy(r => r.Views).Views - f.Records.MinBy(r => r.Views).Views,
  }
  .ToListAsync();

If that makes sense?

Angius•3w ago

GraphQL handles the querying. Your query would, basically, equal to

var data = await context.QueryTheDatabaseWithGraphQlAsync(theGraphQlQuery);

var data = await context.QueryTheDatabaseWithGraphQlAsync(theGraphQlQuery);

The GQL query will define the selecting, joining, ordering, and everything else The query would be something like

query {
  fictions(
    where: {
      tags: { some: { name: { eq: "Fantasy" } } }
      recordsAggregate: { max: { rank: { gte: 5 } } }
    }
  ) {
    id
    title
    viewsGained
  }
}

query {
  fictions(
    where: {
      tags: { some: { name: { eq: "Fantasy" } } }
      recordsAggregate: { max: { rank: { gte: 5 } } }
    }
  ) {
    id
    title
    viewsGained
  }
}

Might need some custom filters or aggregates for some stuff Like viewsGained here, would have to be a computed property or something like that I don't have much experience with GQL, alas

ManaravakOP•3w ago

Ah, ok perfect enough. I was thinking I would have to do lots of extra stuff to enable a request for the second EF query to work, and then again for each radically different kind of query. I just want to be lazy and have one single method I never have to touch again regardless of what data I want to pull 😂 Thank you! And by computed property you mean on the Entity itself right? Like having a property public int ViewsGained => Records.MaxBy(r => r.Views).Views - Records.MinBy(r => r.Views).Views in the FictionEntity class, right?

Angius•3w ago

Yeah, something like that Hot Chocolate calls it a resolver, if I'm not mistaken Or you could fetch maxView and minView, then just get the difference client-side

ManaravakOP•3w ago

Perfect. Really appreciate the help!

Gaming

Programming

Dynamic EF Core LINQ Queries for API Consumption

Did you find this page helpful?