user6079392 user6079392 - 3 months ago 17
SQL Question

Entity framework code first, get access to foreign key value

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.

Answer

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]
Comments