Web API 2. Return Description instead of Int Join/Include?

Question

Hi I'm fairly new to API's Entity framework and Linq.

And I'm having a bit of trouble to do something I could easily do in SQL and web forms.

I have used EF database first (on a database I did not create and cannot alter) To bring back 3 tables the first two tables:

A list of models (fc_models) with a field containing an int (ModelSeq) which is the primary key
A list of parts (fc_linkedParts) linked to the models in the first table. it has the same field (ModelSeq) set up as a foreign key, and ModelSeq and Partnum set up as the primary key.
The third table is a list of Part classes, it contains a ClassId field and a Description field. As far as the database is concerned there is no link between this table and FC_linked parts but of course, in SQL I can simply join on the ClassId in both this table and the fc_linkedparts table

I have my data models that the entity framework created.

namespace DBF_Models
{
using System;
using System.Collections.Generic;

public partial class fc_Models
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public fc_Models()
    {
        this.fc_LinkParts = new HashSet<fc_LinkParts>();
    }

    public int ModelSeq { get; set; }
    public string ModelId { get; set; }
    public string ModelDesc { get; set; }
    public string Manufacturer { get; set; }
    public string Category { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<fc_LinkParts> fc_LinkParts { get; set; }
    }
}


namespace DBF_Models
{
using System;
using System.Collections.Generic;

public partial class fc_LinkParts
    {
    public int ModelSeq { get; set; }
    public string PartNum { get; set; }
    public string PartDescription { get; set; }
    public string Manufacturer { get; set; }
    public string PartClass { get; set; }

    public virtual fc_Models fc_Models { get; set; }
    }
}



namespace DBF_Models
{
using System;
using System.Collections.Generic;

public partial class PartClass
   {
    public string Company { get; set; }
    public string ClassID { get; set; }
    public string Description { get; set; }
    public string BuyerID { get; set; }
    public bool RcvInspectionReq { get; set; }
    public string CommodityCode { get; set; }
    public bool AvailForReq { get; set; }
    public decimal MtlBurRate { get; set; }
    public bool SplitPOLine { get; set; }
    public string NegQtyAction { get; set; }
    public string PurchCode { get; set; }
    public bool ConsolidatedPurchasing { get; set; }
    public bool GlobalPartClass { get; set; }
    public bool GlobalLock { get; set; }
    public byte[] SysRevID { get; set; }
    public System.Guid SysRowID { get; set; }
   }
}

I dont need all the information in the tables so I created some DTOs (plus I was follwoing a solution with a similar problem to what I had at the time)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace DBF_Models.Models
{
public class ModelDto
{
    public int ModelSeq { get; set; }
    public string ModelId { get; set; }
    public string ModelDescription { get; set; }
    public string Manufacturer { get; set; }
    public string EntityType { get; set; }
    public IEnumerable<PartDto> PartList { get; set; }
}
}


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace DBF_Models.Models
{
    public class PartDto
{
    public string SKU { get; set; }
    public string Description { get; set; }
    public int ModelId { get; set; }

    public string EntityType = "Part";
    public string Manufacturer { get; set; }
    public string Class { get; set; }

}
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Profile;

namespace DBF_Models.Models
{
public class PartClassDto
{
    public string ClassId { get; set; }
    public string ClassDescription { get; set; }
}
}

The code used to get the data in a nested Json

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Runtime.InteropServices.WindowsRuntime;
using System.Web.Http;
using System.Web.Http.Description;
using System.Web.UI.WebControls;
using DBF_Models;
using DBF_Models.Models;



namespace DBF_Models.Controllers
{
public class LinkedPartsController : ApiController
{
    private ErpDevReplicationEntities db = new ErpDevReplicationEntities();

    // GET: api/sp_fc_ModelList_test_Result
    [Route("api/LinkedParts/All")]
    public Object GetAll()
    {

        var models = db
            .fc_Models
            .Include("fc_LinkParts")
            .Select(t => new ModelDto()
            {

                ModelSeq = t.ModelSeq,
                ModelId = t.ModelId,
                EntityType = "Model",
                ModelDescription = t.ModelDesc,
                Manufacturer = t.Manufacturer,
                PartList = t.fc_LinkParts.Select(p => new PartDto()
                {

                    EntityType = "Part",
                    ModelId = p.ModelSeq,
                    SKU = p.PartNum,
                    Description = p.PartDescription,
                    Manufacturer = p.Manufacturer,
                    Class = p.PartClass
                })
            });




        return models;
    }

The output

[
{
    "ModelSeq": 3020,
    "ModelId": "ACW                 ",
    "ModelDescription": "ACW                 ",
    "Manufacturer": "Hobart Manufacturing Ltd",
    "EntityType": "Model",
    "PartList": [
        {
            "EntityType": "Part",
            "SKU": "HOB01-240051-11     ",
            "Description": "FILTER                        ",
            "ModelId": 3020,
            "Manufacturer": "Hobart Manufacturing Ltd",
            "Class": "600"
        },
        {
            "EntityType": "Part",
            "SKU": "HOB104329           ",
            "Description": "MECHANICAL SEAL ASSY 5/8\"     ",
            "ModelId": 3020,
            "Manufacturer": "Hobart Manufacturing Ltd",
            "Class": "186"
        },
        {
            "EntityType": "Part",
            "SKU": "HOB122674           ",
            "Description": "FLOAT ASSEMBLY                ",
            "ModelId": 3020,
            "Manufacturer": "Hobart Manufacturing Ltd",
            "Class": "201"
        },......

TLDR; This obviously only uses table 1 (fc_models) and table 2 (fc_LinkParts) What I need to achieve is link in the PartClass table or DTO so rather than the out put showing class as in Int it wil show the description.

Desired outpuy

[
{
    "ModelSeq": 3020,
    "ModelId": "ACW                 ",
    "ModelDescription": "ACW                 ",
    "Manufacturer": "Hobart Manufacturing Ltd",
    "EntityType": "Model",
    "PartList": [
        {
            "EntityType": "Part",
            "SKU": "HOB01-240051-11     ",
            "Description": "FILTER                        ",
            "ModelId": 3020,
            "Manufacturer": "Hobart Manufacturing Ltd",
            "Class": "Dishwasher"
        },....

I'm sure the solution will be simple and take less time, than writing this post did, but i'm stumped by the EF/Linq code.

Thanks for reading

Solution: works but not sure its the best way

So with the help of penleychan I was trying to use a Join which to me made sense and it was what I would do in SQL but I just could not figure out the syntax or really where to place it. If it was in SQL I would put in here

 PartList = t.fc_LinkParts.Select(p => new PartDto()
                {

                    EntityType = "Part",
                    ModelId = p.ModelSeq,
                    SKU = p.PartNum,
                    Description = p.PartDescription,
                    Manufacturer = p.Manufacturer,
                    Class = (from d in db.PartClasses
                        where d.ClassID == p.PartClass
                        select d.Description).FirstOrDefault()
                }) "JOIN WOULD GO HERE"

but could just not figure out the syntax to be able to access all the fields I would need to create the join.

So the solution I came up with I assume is working as a SQL subquery. I can't be sure as I don't know what SQL the Linq is converted to.

    // GET: api/sp_fc_ModelList_test_Result
    [Route("api/LinkedParts/All")]
    public Object GetAll()
    {

        var models = db
            .fc_Models
            .Include("fc_LinkParts")
            .Select(t => new ModelDto()
            {

                ModelSeq = t.ModelSeq,
                ModelId = t.ModelId,
                EntityType = "Model",
                ModelDescription = t.ModelDesc,
                Manufacturer = t.Manufacturer,
                PartList = t.fc_LinkParts.Select(p => new PartDto()
                {

                    EntityType = "Part",
                    ModelId = p.ModelSeq,
                    SKU = p.PartNum,
                    Description = p.PartDescription,
                    Manufacturer = p.Manufacturer,
                    Class = (from d in db.PartClasses
                        where d.ClassID == p.PartClass
                        select d.Description).FirstOrDefault()
                })
            });




        return models;
    }

TLDR; It works, but is there a better way?

There is no navigation property setup for `fc_LinkParts` on `PartClass` it's just a string. `public string PartClass { get; set; }` — penleychan, Nov 06 '18 at 17:24
No I'm aware of that. But as that class was created by entity Framework and mimics the table structure of the database, I was under the impression I could not change that class. — Rob Higginbotham, Nov 06 '18 at 18:00
If it was SQL I would just do a select from fc-listparts inner join on partclass where fc-listparts.classid = partclass.classid. even though the database doesn't know there is a link between the tables I do and can make one. Without having to alter the tables directly. I would assume I can do the same here. As I can not alter the database — Rob Higginbotham, Nov 06 '18 at 18:02
Ah in that case you can use `.Join()` see: https://stackoverflow.com/questions/21535248/ef-6-select-from-other-table-without-navigation-property — penleychan, Nov 06 '18 at 18:05
If figured it would be a join or you include. Would I put the join where I have class = p.partclass. Some thing along the lines of. Class =(from class c in db.partclass where c.classId== p.partclass select c.description).SingleOrDefault();. This linq syntax does flumexing me coming from SQL and .net webforms — Rob Higginbotham, Nov 06 '18 at 18:19

score 0 · Answer 1 · answered Nov 07 '18 at 10:05