This tutorial covers advance linq statements for querying related entities in entity framework. Before you continue to read this post please make sure you have a complete understanding of specifying related entities to include in the query results.
20 different queries will be covered in this tutorial. The following entity model will be used for these queries.
//1) The number of existing Players?
lbl_1.Text = db.Players.Count().ToString();
//Count method will return the number of records in the players table.
//2) The number of existing Transfers?
lbl_2.Text = db.Transfers.Count().ToString();
//3) The number of Transfers of the player with id "4"?
lbl_3.Text = db.Transfers
.Count(tr => tr.PlayerIdFK == 4)
.ToString();
//Count method can perform counting on particular records
//based on the specified condition
//4) The number of Transfers of the player "Nani"?
lbl_4.Text = db.Transfers
.Count(tr => tr.Player.FullName == "Nani")
.ToString();
//This counts only the transfers that involved
//a player with name Nani
//5) The number of players who have been Transferred to any Team?
lbl_5.Text = db.Players
.Count(pl => pl.Transfers.Count > 0)
.ToString();
//This counts only the players with any transfers
//6) The number of teams with any Transfers?
lbl_6.Text = db.Teams
.Count(te => te.Transfers.Count > 0)
.ToString();
//This counts only the teams with any transfers
//SELF-PRACTICE: THE NUMBER OF TRANSFERS
//WITH A COST HIGHER THAN 200000
//SELF-PRACTICE: THE NUMBER OF TEAMS WITH
//TOTAL TRANSFER COST HIGHER THAN 20000
//SELF-PRACTICE: THE NUMBER OF TEAMS
//THAT HAVE EVER TRANSFERRED THE PLAYER WITH ID 4
//7) The names of the existing Players in the system?
List<string> pnames = db.Players
.Select(pl => pl.FullName)
.ToList();
lbl_7.Text = String.Join(", ", pnames);
//Select method projects the property values in a new collection
//Here it projects the FullName values of Player items
//in a list of strings.
//8) The names of the existing Teams in the system?
List<string> tnames = db.Teams
.Select(t => t.TeamName)
.ToList();
lbl_8.Text = String.Join(", ", tnames);
//Select() method projects the name of the teams
//in a string list
//9) The names of the Players with NO Transfer?
pnames = db.Players
.Where(pl => pl.Transfers.Count == 0)
.Select(pl => pl.FullName)
.ToList();
lbl_9.Text = String.Join(", ", pnames);
//10) The names of the Teams with NO Transfer?
tnames = db.Teams
.Where(te => te.Transfers.Count == 0)
.Select(te => te.TeamName)
.ToList();
lbl_10.Text = String.Join(", ", tnames);
//SELF-PRACTICE: THE COSTS OF THE TRANSFERS
//INVOLVING THE TEAMS FOUNDED AFTER 1906
//SELF-PRACTICE: THE COSTS OF THE TRANSFERS
//INVOLVING PLAYERS WITH LICENSE YEAR SMALLER THAN 2000
//SELF-PRACTICE: THE NAMES OF THE TEAMS
//THAT TRANSFERRED A PLAYER WITH LICENSE YEAR BIGGER THAN 1998
//11) The sum of the Transfer costs?
lbl_11.Text = db.Transfers.Sum(tr => tr.Price).ToString("C");
//Sum will add up the numeric property values of the provided records
//12) The sum of the Transfer costs for the player with id "4"?
lbl_12.Text = db.Transfers
.Where(tr => tr.PlayerIdFK == 4)
.Sum(tr => tr.Price)
.ToString("C");
//This will Sum only the prices of transfers
//that involved Player with Id 4
//13) The sum of the Transfer costs of "Fenerbahce" btw 2000-2014?
lbl_13.Text = db.Transfers
.Where(tr => tr.Team.TeamName == "Fenerbahce" &&
tr.StartYear >= 2000 &&
tr.StartYear <= 2014
).Sum(tr => tr.Price).ToString("C");
//14) The sum of the Transfer costs of "Fenerbahce" Team
//excluding the player with id "4"?
lbl_14.Text = db.Transfers
.Where(tr => tr.Team.TeamName == "Fenerbahce" &&
tr.PlayerIdFK != 4)
.Sum(tr => tr.Price).ToString("C");
//SELF-PRACTICE: AVERAGE OF TRANSFER COSTS OF FENERBAHCE?
//SELF-PRACTICE: TOTAL TRANSFER COSTS OF FENERBAHCE FOR PLAYERS
//WITH LICENSE YEAR AFTER 2000
//SELF-PRACTICE: AVERAGE TRANSFER COSTS OF TEAMS FOUNDED AFTER 1905
//FOR PLAYERS WITH LICENSE YEAR BEFORE 2000
//15) The name of the player with the earliest license year
lbl_15.Text = db.Players
.OrderBy(pl => pl.LicenseYear)
.First().FullName;
//To get the player with the smallest license year, players are
//ordered by LicenseYear in ascending order, and First()
//method is used to read the only the first record
//16) The name of the Team with the highest Transfer cost?
lbl_16.Text = db.Teams
.OrderByDescending(te => te.Transfers
.Sum(tr => tr.Price)
).First().TeamName;
//SELF-PRACTICE: The name of the Player with the lowest Transfer cost?
//17) The names of the Players transferred to the Team with id "1"?
pnames = db.Players
.Where(pl => pl.Transfers
.Select(tr => tr.TeamIdFK)
.Contains(1)
).Select(pl => pl.FullName).ToList();
lbl_17.Text = String.Join(", ", pnames);
//This will filter Players by first obtaining a list of IDs of
//the teams involved in a player's Transfers, then it will
//check if this list of team Ids contains the wanted TeamId "1".
//if it does not contain, then that player will be filtered out.
//18) The names of the Players transferred to the Team "Fenerbahce"?
pnames = db.Players
.Where(pl => pl.Transfers
.Select(tr => tr.Team.TeamName)
.Contains("Fenerbahce")
).Select(pl => pl.FullName).ToList();
lbl_18.Text = string.Join(", ", pnames);
//This will filter Players by first obtaining a list of names
//of the teams to which a player is transferred before, then
//it will check if this list contains the wanted team name
//if it does not, then the player will be filtered out
//19) The names of the Teams to which
//Player id "2" has been transferred to?
tnames = db.Teams
.Where(te => te.Transfers
.Select(tr => tr.PlayerIdFK)
.Contains(2)
).Select(te => te.TeamName).ToList();
lbl_19.Text = string.Join(", ", tnames);
//20) The names of the Teams to which
//Player "Nani" has been transferred to?
tnames = db.Teams
.Where(te => te.Transfers
.Select(tr => tr.Player.FullName)
.Contains("Nani")
).Select(te => te.TeamName).ToList();
lbl_20.Text = string.Join(", ", tnames);
//SELF-PRACTICE: THE NAMES OF THE LEAGUES WITH TEAMS
//THAT SPENT MORE THAN 200000 FOR TRANSFERS
//SELF-PRACTICE: THE NAME OF THE LEAGUE IN WHICH
//THE PLAYER "Nani" PLAYS
//SELF-PRACTICE: THE NAMES OF THE LEAGUES IN WHICH
//THERE ARE PLAYERS WITH LICENSE YEAR AFTER 2000
Asp.Net (C#) and Entity Framework Code-First 