Creating nested child entities

Question

I am trying to do queries towards a SQL server and turn the results into models I've designed based on how I want the result to be translated to JSON data for my API.

This is my JSON Models that I am trying create and populate with data

public class JsonProject 
{
   public int ProjectId { get; set; }
   public int ProjectName { get; set; }
   public List<JsonJob> Jobs { get; set; }
}
public class JsonJob
{
   public int JobId { get; set; }
   public int JobName { get; set; }
   public List<JsonResource> Resources { get; set; }
}
public class JsonResource
{
   public int ResourceId { get; set; }
   public DateTime DateStart { get; set; }
   public DateTime DateEnd { get; set; }
}

This is my method for making the query which return data to the controller

//Param in is "resourceId"
var query = from rb in db.ResourceBooking
            join rrb in db.ResourceRoleBooking on rb.IdResourceBooking equals rrb.IdResourceBooking
            join job in db.Job on rrb.IdJob equals job.IdJob
            join project in db.Project on job.IdProject equals project.IdProject
            where rb.IdAddress == resourceId //all resources is stored as an address
            orderby rrb.DateStart
            group new {
              rb,
              rrb,
              job,
              project
            } by new {
              ProjectId = project.IdProject,
              ProjectName = project.Name
            } into g
            select new JsonProject {
              ProjectId = g.Key.ProjectId,
              ProjectName = g.Key.ProjectName,
              Jobs = g.Select( x => new JsonJob { // The Problem
                JobId = x.job.Id,
                JobName = x.job.Name,
                Resources = null  // null for now until I get JsonJob working
              }).ToList()
            };

A Job belongs to a Project, a Job has resources booked first as Role in ResourceRoleBooking, then these roles are linked to specific resources(humans) in ResourceBooking which is where I can search for the users Id.

The query is used when a user wants to see projects he is scheduled to do. The order of the joins is based on placing the WHERE clause at the top of the joins, which I believe will help on performance(?)

I try to group together the data, first for each project. When I try to create new JsonJobs within JsonProjects I run into trouble.

When I try to run this code, it results in a InvalidOperationException

)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

-To troubleshoot I tried turning the JsonProject.Jobs into an integer and using Jobs = g.Sum(x => x.job.Id) which worked. I could then see how projects with multiple jobs had a higher number. -If I try to use anonymous types (select new { ProjectId = g.Key.ProjectId, JobId = g.Select(x => new { JobName = x.job.Name }).ToList() it returns no results.

That error means that EF can't translate your query to SQL. Are any of your properties in the source objects (not JSON*) are computed? Can you show us classes `ResourceBooking`, `ResourceRoleBooking`, `Job`, `Project`? — trailmax, Mar 05 '20 at 14:32

PM_ME_YOUR_LOGS · Answer 1 · 2020-03-05T16:20:56.913

0

I think this question on includes vs joins will help provide some insight on your issue. In your situation, I would use an include for the JsonJob and eventually the JsonResources so they will retain their proper structure.

LINQ Include vs Join. Are they equivalent?

Edit: my comment formated

check out this documentation https://learn.microsoft.com/en-us/ef/ef6/querying/related-data

It has this example

var blog2 = context.Blogs
                       .Where(b => b.Name == "ADO.NET Blog")
                       .Include("Posts")
                       .FirstOrDefault();

so yours may look about like this

            .Include( x => x.JsonJob)
            .Select(JobId = x.job.Id,
                JobName = x.job.Name,
                Resources = null
              ).ToList()

edited Mar 05 '20 at 16:20

answered Mar 05 '20 at 14:24

PM_ME_YOUR_LOGS

31
5

Looks like you are right, I am looking to include jobs as a nesten child in projects. But I see no way of explaining the Include method the relationship between the two tables? I am confused by the complexity because I want Project to be the parent, but have to go through jobs to find the ResourceBooking table for making the WHERE clause. Should I join the jobs table to be able to do the WHERE, and then include it? And how do I specify the relationship between Project and Job like I do in join(`on [key] equals [key]`) – Mads Mar 05 '20 at 15:43
sorry, had to rewrite that comment. Check out my edit. You shouldn't need a select since this object based assuming you have a defined a many to one relationship in the table creation file. – PM_ME_YOUR_LOGS Mar 05 '20 at 16:26
But this is all based on having models explaining the relationship between the models, right? I haven't done that so far in the project because of the complexity of the database, and was hoping to avoid that and rather directly translate each query into models manually. That worked with `join` because you have `on` and `equals`, but it does not seem to exist for `include`? If I want to use include I would have to create correct models explaining the relationship between tables? – Mads Mar 05 '20 at 17:46

score 0 · Accepted Answer · answered Apr 15 '20 at 09:49

My solution to retrieve data the way I wanted was this:

context.Project
  .Where(proj => proj.Job.Any(
    jo => jo.ResourceRoleBooking.Any(
      resrb => resrb.DateEnd >= DateTime.Now.AddDays(-60) 
               && resrb.ResourceBooking.Any(
        reb => reb.IdAddress == idAdr
  ))))
  .OrderBy(x => x.StartDate)
  .Select(x => new JsonProject
  {
    ProjectId = x.IdProject,
    ProjectName = x.Name,
    Jobs = x.Job
      .Where(j => j.ResourceRoleBooking.Any(
        rrb => rrb.ResourceBooking.Any(
          rb => rb.IdAddress == idAdr
        )
      ))
      .Select(j => new JsonJob
      {
        JobId = j.IdJob,
        JobName = j.Caption,
        Resources = j.ResourceRoleBooking.SelectMany(
                      rrb => rrb.ResourceBooking,
          (parent, child) => new JsonResource
          { 
            DateStart = parent.DateStart,
            DateEnd = parent.DateEnd
          }).ToList()
      }).ToList()
  });

I could not use grouping as I initially thought, but ended up doing new queries within each object where I repeated my Where statement to only select the result I wanted.

I have not looked into performance of this yet, which I think is pretty bad with so many Where clauses within each other, but I will report back once I have tested this more.

Creating nested child entities

2 Answers2