Entity framework code first, get access to foreign key value

Question

Say we have a project with this entities:

class User
{
    public Guid Id { get; set; }
    public List<Message> Messages { get; set; }
    ...
}

class Message
{
    public Guid Id { get; set; }
    public string Message { get; set; }
}

Now consider a scenerio where i want to get all the messages that a certain user posted, how can one achieve this without pulling the user information aswell? (without using the context.Users.Include(...) ) ? I know that the entity framework creates a column in the Message table that holds the Id of the user that posted this message, but how can i have an access to this value? as it is not a property in my original class.

DavidG · Accepted Answer · 2016-08-09 13:22:10Z

2

You can add a navigation property into the Message class:

public class Message
{
    public Guid Id { get; set; }
    public string Message { get; set; }

    public virtual User User { get; set; }
}

And then query your context like this:

var userMessages = context.Messages
    .Where(m => m.User.Id == 5);

This is the tidier way of doing it. Alternatively, you could start with the user, but this is a little more awkward:

var userMessages = context.Users
    .Where(u => u.Id == 5)
    .SelectMany(u => u.Messages);

Both methods will ultimately produce the similar SQL, something like this:

SELECT [Extent1].[Column1],
       [Extent1].[Column2],
       [Extent1].[Column3]
FROM [dbo].[Messages] AS [Extent1] 
WHERE 5 = [Extent1].[UserId]

edited Aug 9, 2016 at 13:22

answered Aug 2, 2016 at 14:34

DavidG

120k13 gold badges232 silver badges239 bronze badges

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

8 Comments

Hay Zohar Over a year ago

Hey DavidG! what is the difference between using the ForeignKey annotation against not using it?

DavidG Over a year ago

There really isn't a difference, if you want to be explicit, then feel free to add it in. You actually don't need the attribute as Entity Framework will, by convention, map User navigation property to an Id field called UserId.

Hay Zohar Over a year ago

Does this creates any connection between the two entities? will deleting one of them will cause the deletion of the other?

DavidG Over a year ago

You already have the connection between them, EF convention will create the foreign key.

DevilSuichiro Over a year ago

A foreign key constraint won't actually be created until you declare WillCascadeOnDelete. The FK annotation will be solely for EF to tell which join attribute to use (and create an index on the column if I'm not mistaken)

|

Yushatak · Accepted Answer · 2016-08-02 14:35:55Z

2

You can explictly define the FK so that you don't have to resolve the User object.

class Message
{
    public Guid Id { get; set; }
    public string Message { get; set; }

    public int UserID { get; set; }
    [ForeignKey("UserID")]
    public virtual User User { get; set; }
}

Then you operate on the User when you need to, but if you just want to pull by UserID or change the UserID you can do so.

answered Aug 2, 2016 at 14:35

Yushatak

7711 gold badge5 silver badges17 bronze badges

3 Comments

Hay Zohar Over a year ago

Hey Yushatak! what is the difference between using the ForeignKey annotation against not using it (but leaving the virtual on like DavidG posted)?

Yushatak Over a year ago

If you use the annotation you create an accessible key property (in this case UserID) which you can read/write/manipulate. If you do not use the annotation this happens behind-the-scenes and is not made available to you through EF to manipulate.

Yushatak Over a year ago

Apparently the manual FK is not necessary as the expression resolves to using the ID property anyway in EF (per DavidG).

Collectives™ on Stack Overflow

Entity framework code first, get access to foreign key value

2 Answers 2

8 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related