Query from db with entity - one to one relationship

Question

I have two models, configured in a one-to-one relationship:

public class PointOfInterestModel
{
    [Key]
    public int Id { get; set; }

    [Required]
    public string Name { get; set; }
    public string Description { get; set; }

    [Required]
    public CoordinatesModel Coordinates { get; set; }

}

public class CoordinatesModel
{
    [Key]
    [ForeignKey("PointOfInterest")]
    public int Id { get; set; }

    [Required]
    public float Longitude { get; set; }

    [Required]
    public float Latitude { get; set; }

    public PointOfInterestModel PointOfInterest { get; set; }
}

So, they should exist in a one-to-one relationship. The PointOfInterestModel should only have one CoordinatesModel, and a CoordinatesModel should only have one PointOfInterestModel.

Now, I would like to query all my points of interest from my database, and get all the coordinates with them.

Here is what I think should work, but does not:

pointsOfInterestList = db.PointOfInterestModels.Include(x=>x.Coordinates).ToList();

So, what is wrong? Is it in the relationship, or is it in the LINQ query?

I have been trying to fix this for hours.

"should work, but does not" - what's happening? exception, wrong data, nothing? — Dmitry, Dec 06 '16 at 14:49
Oh yes, I get this exception: System.Reflection.TargetInvocationException. If i remove the include part of the linq query, I do not get that exception, but then, of course, the pointOfInterestModel.coordinates is null. — Andreas, Dec 06 '16 at 14:52
Is there something interesting "inside" this exception (something with names of you table/columns)? And what EF version you are using? — Dmitry, Dec 06 '16 at 15:09
Not anything that I can make sense of. I am using EntityFramework 6.1.3 — Andreas, Dec 06 '16 at 15:16
Never tried this type of entity linking before :) Can you create and post somewhere (github, bitbucket, ...) project (without extra code) to play with? — Dmitry, Dec 06 '16 at 15:23

score 0 · Answer 1 · answered Dec 06 '16 at 15:01

0

Check you database.

I suspect you will find extra CoordinatesModelId field in PointOfInterestModel table.

EF can't guess that PointOfInterestModel.Cordinates is the same reference as CoordinatesModel.PointOfInterest and creates new one.

Try to add one more attribute into PointOfInterestModel:

[Required]
[InverseProperty("PointOfInterest")]
public CoordinatesModel Coordinates { get; set; }

answered Dec 06 '16 at 15:01

Dmitry

16,110
4
61
73

There is no `CoordinatesModelId` field in the `PointOfInterestModel` table. That is also one of the things confusing me, but since I get no error on migration, I guess nothing should be wrong. – Andreas Dec 06 '16 at 15:04
Intresting... Can you add screenshot/script of this tables in DB? Does everything there "looks good"? – Dmitry Dec 06 '16 at 15:08
Here is my [Database Diagram](http://imgur.com/a/okPVY) from my database. My other relationships look fine. Under the "keys" dropdown under the `CoordinatesModel` table, I can see its foreign key to `PointOfInterestModel` table, but not the other way around. – Andreas Dec 06 '16 at 15:14

score 0 · Answer 2 · answered Dec 06 '16 at 15:17

0

I've never seen relationships written like this in an entity, maybe rewriting the foreign key attribute order can fix your issue. Instead of adding the attribute to the Id field, try and add it to a virtual object.

public class CoordinatesModel
{
    [Key]       
    public int Id { get; set; }

    [Required]
    public float Longitude { get; set; }

    [Required]
    public float Latitude { get; set; }

    [ForeignKey("Id")]
    public virtual PointOfInterestModel PointOfInterest { get; set; }
}

answered Dec 06 '16 at 15:17

Arth.Suthar

1
1
1

[That seems to fix the relationship](http://imgur.com/a/D0Bko) . But I still get the Exception when trying to query. – Andreas Dec 06 '16 at 15:31
I've never used the `include` linq query but most syntax suggest using quotes to look for what you want. You can see an example in this SO Example: http://stackoverflow.com/a/6761246/7257637 – Arth.Suthar Dec 06 '16 at 15:41
Using the quotes approach does not work. Same exception. – Andreas Dec 06 '16 at 15:53

Query from db with entity - one to one relationship

2 Answers2