API not returning JSON data in desired format

Question

I am using .Net Core Entity Framework 3.1 for a web catalog application for automobile parts.

I have one simple controller, that returns all motorParts for a particular motor.

Here is that simple controller:

[HttpGet("GetPartsByMotorId/{id}")]
public async Task<ActionResult<IEnumerable<MotorParts>>> GetPartsByMotorId(Guid id)
{

   var partsByMotor = await _context.MotorParts.Where(q => q.MotorId == id).OrderBy(c => c.partText).ToListAsync();

    return partsByMotor;
}

It is returning the data, but it is in an order that I do not want and with some data duplicated.

The JSON looks like this when returned from the API:

[
    {
        "partId": "54",
        "motorId": "3",
        "partText": "Iron Heads",
        "motor": {
            "motorId": "3",
            "motorText": "5.7 HEMI",
            "motorParts": [
                {
                    "partId": "54",
                    "motorId": "3",
                    "partText": "Iron Heads"
                },
                {
                    "partId": "8",
                    "motorId": "3",
                    "partText": "Aluminum Block"
                }
            ]
        }
    },
    {
        "partId": "8",
        "motorId": "3",
        "partText": "Aluminum Block",
        "motor": {
            "motorId": "3",
            "motorText": "5.7 HEMI",
            "motorParts": [
                {
                    "partId": "8",
                    "motorId": "3",
                    "partText": "Aluminum Block"
                },
                {
                    "partId": "54",
                    "motorId": "3",
                    "partText": "Iron Heads"
                }
            ]
        }
    }
]

The problem for me, is that it is listing a "part" ( partId, motorId, partText ) first, then listing the same "parts" again later on.

But I want it to just list the motor data first, then all the parts for that motor, like this:

[
    {
        "motor": {
            "motorId": "3",
            "motorText": "5.7 HEMI",
            "motorParts": [
                {
                    "partId": "8",
                    "motorId": "3",
                    "partText": "Aluminum Block"
                },
                {
                    "partId": "54",
                    "motorId": "3",
                    "partText": "Iron Heads"
                }
            ]
        }
    }
]

Here is the model:

public partial class MotorParts
{
    public Guid PartId { get; set; }
    public Guid? MotorId { get; set; }
    public string PartText { get; set; }

    public virtual MotorList Motor { get; set; }
}

And here is my database context for that model:

modelBuilder.Entity<MotorParts>(entity =>
{
    entity.HasKey(e => e.PartId);

    entity.HasOne(d => d.Motor)
        .WithMany(p => p.MotorParts)
        .HasForeignKey(d => d.MotorId)
        .OnDelete(DeleteBehavior.Cascade)
        .HasConstraintName("FK_carList_makeList");

    entity.ToTable("MotorParts");

    entity.Property(e => e.PartId)
        .HasColumnName("PartId");

    entity.Property(e => e.MotorId)
        .HasColumnName("MotorId");

    entity.Property(e => e.PartText)
        .HasColumnName("PartText");
});

So I was wondering if there is a way to get the JSON format I want without changing my model too much. Is that possible?

Thanks!

Svyatoslav Danyliv · Accepted Answer · 2020-12-07 19:33:18Z

1

You should provide correct transformation. Also this query needs client-side grouping.

var dataQuery = 
   from mi in _context.MotorParts
   where mp.MotorId == id
   select new 
   {
      Motor = new 
      { 
          motorId = mp.MotorId.Value,
          motorText = mp.Motor.MotorText 
      },
      MotorPart = new 
      { 
          partId = mp.PartId,
          motorId = mp.MotorId.Value,
          partText = mp.PartText
      }
   }

var partsByMotor = 
   from mp in await dataQuery.ToListAsync()
   group mp.MotorPart by mp.Motor into g
   select new 
   {
       motorId = g.Key.motorId,
       motorText = g.Key.motorText,
       motorParts = g.OrderBy(x => x.partText).ToArray()
   };

return partsByMotor.ToList();

answered Dec 7, 2020 at 19:33

Svyatoslav Danyliv

28.2k4 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Atli · Accepted Answer · 2020-12-07 22:16:26Z

1

Have you investigated ways to decorate your properties with attributes in such a way that you can control the order?
Much like can be achieved with the NewtonSoft Json Property Order. https://www.newtonsoft.com/json/help/html/JsonPropertyOrder.htm

edited Dec 7, 2020 at 22:16

answered Dec 7, 2020 at 21:48

Atli

312 silver badges7 bronze badges

Comments

jdewerth · Accepted Answer · 2020-12-07 23:18:25Z

Your desired return looks to just be a Motor object return that includes all related MotorPart objects. There is some issue here with your model design as you've got a MotorParts class that is supposed to represent a single individual MotorPart, correct? I can't tell if MotorParts is supposed to be a collection of objects itself, or whether multiple MotorPart objects are in a collection. What was your intention for the class? I assume it's meant to represent an individual MotorPart? If so, it should really be named MotorPart.

As it is, EF is treating each MotorParts object as an individual motor part and storing each in the MotorParts table. Was this just a naming mistake on your part? When you run the query, you're getting an List<MotorParts> collection returned. Each element in the list contains a reference to the related Motor object, which also has a reference back to the motor parts. This seems like a rather poor design choice, based on what you said the desired result is.

The simplest recommendation would be to remove the Motor property from your MotorParts class and just keep the MotorId property. That would remove the redundant reference back to the motor parts. Still, this query is returning a collection of objects. It is NOT returning a single Motor object containing the related MotorParts.

If you want to return the single Motor object along with all of it's MotorParts then you need to run the query against the Motors table instead.

Collectives™ on Stack Overflow

API not returning JSON data in desired format

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related