Performing a Join on EF with Linq or Lambda expressions

Linq

Here is an example on how to join two entities (wCompPlayers and wCompPlayerTeams) to search for a player given surname and team id.

public PlayerModel GetBySurnameAndTeamId(string surname, int teamId)
{
    return (
        from p in _context.wCompPlayers
        from pt in _context.wCompPlayerTeams
        where p.IdPlayer == pt.IdPlayer
            && string.Equals(p.Surname, surname) && pt.IdTeam == teamId
        select p
        ).ToList().Select(CreatePlayerModel).FirstOrDefault();
}

Notice that we use the ToList() method to force the query to be evaluated and to have a list of results. In this way all the next steps will be evaluated only using Linq, without adding complexity to the generated underlying SQL.


Lambda

Here is the same search made with a Lambda expression. Notice that in this case we are using the AsEnumerable() method as we was using the ToList() method in the previous example: all the next steps will be evaluated without adding complexity to the generated underlying SQL.

public PlayerModel GetBySurnameAndTeamId(string surname, int teamId)
{
    return
        _context.wCompPlayers.Join(_context.wCompPlayerTeams,
            p => p.IdPlayer, pt => pt.IdPlayer, (p, pt) => new { p, pt })
            .Where(m => string.Equals(m.p.Surname, surname) && m.pt.IdTeam == teamId)
            .AsEnumerable().Select(m => CreatePlayerModel(m.p)).FirstOrDefault();
}

The Join() method accepts 4 parameters:

  • The object to join
  • An expression which returns the original object’s key to use for the join
  • An expression which returns the joined object’s key to use for the join
  • An expression which returns the joined object
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s