Entity Framework 6, existing Database. Related data but no Navigation property. I'm lost

Question

I'm working on a project with an existing databse. Didn't really have any problems until now as I can't understand how I could possibly write this query.

I have two Entities:

Contactperson.cs

public class ContactPerson
{
    public Guid ContactPersonID { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
    public string City { get; set; }
    public string ZIP { get; set; }
    public string StreetAndNumber { get; set; }
    public bool Active { get; set; }
}

ClientContactPerson.cs

public class ClientContactPerson
{
    public Guid ClientID { get; set; }
    public Guid ContactPersonID { get; set; }
    public int? RelationshipID { get; set; }
    public string Comment { get; set; }
    public virtual Client { get; set; }
}

On Client.cs the only relation to ClientContactPerson is this:

public virtual ICollection<KlientKontaktperson> KlientKontaktperson { get; set; }

Now, what I need to query is, I pass the ClientID to the controller:

public JsonResult GetContactperson(Guid Id)
{

    var ContactQuery = db.ClientContactPerson.Where(i => i.ClientID == Id).ToList();

    var contactperson = Mapper.Map<IEnumerable<ContactpersonDto>>(contactpersonQuery);

    return Json(kontaktperson, JsonRequestBehavior.AllowGet);
}

Now my JSON response looks like this:

{
    "ClientContactPerson": "e5b05f3a-4fa5-4489-9d0b-118c3d51697d",
    "LastName": null,
    "FirstName": null,
    "City": null,
    "ZIP": null,
    "StreetAndNumber": null,
    "Active": false
}

Now I can't even use automapper (I guess) since I cannot navigate to say ContactPerson and map the fields to my ContactPersonDto.

I know I'm missing something here but I have 0 clue how I could deal with such columns. Probably will be running into the same problem as I progress with working on the databse.

Hope someone can lead me in the right direction on this one. Thanks

Are you using code first or DB first? how was your entity classes generated? How does your tables look. Can you share the table diagram? — Harsh
– Harsh, Commented Aug 9, 2017 at 9:10
I used "Code fist from Database". The databse itself has 695 columns so the diagram is really confusing. Since I don't need every single column, I just copy the needed columns to a new project and then work with the certain columns :| — rawk
– rawk, Commented Aug 9, 2017 at 9:13
I might be missing something here, but can't you just write a LINQ query and project it straight to a ContactPersonDto object? — markpsmith
– markpsmith, Commented Aug 9, 2017 at 9:17

Harsh · Accepted Answer · 2017-08-09 09:20:27Z

1

You need to set up your entity and relations properly to access the related data. Add ContactPerson navigation property to ClientContactPerson class.

public virtual ContactPerson { get; set; }

Then you can eager load the data using Include(). Also set up the foreign key relation between the tables.

answered Aug 9, 2017 at 9:20

Harsh

3,7713 gold badges30 silver badges42 bronze badges

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

2 Comments

rawk Over a year ago

Aaaah thank you Harsh. I didn't know I can just add my own navigation properties. The generated classes were usually ok until this one so I thought there had to be another way. Thanks!

Harsh Over a year ago

@rawk That's the beauty of code first. You've got complete control now :)

Collectives™ on Stack Overflow

Entity Framework 6, existing Database. Related data but no Navigation property. I'm lost

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related