Question
I have this small model:
class User
{
public int Id { get; set; }
public Community { get; set; }
}
class Community
{
public int Id { get; set; }
public Country { get; set; }
}
class Country
{
public int Id { get; set; }
public string Name { get; set; }
}
The DbContext is this:
public class SmallDbContext : DbContext
{
public DbSet<User> Users { get; set; }
}
I would like to create query that will retrieve the Users of a given Country
My bet using is this:
int countryId = 2:
var usersOfAGivenCountry = dbContext.Users
.Where(community => community.Country.Id == countryId);
But I'm not sure if I'm doing it right. Is this correct way to do it?
Also, I wonder how this will handle a null Community in a User.
Accepted Answer
Yes, this is the correct way. As some users say, the LINQ expression is translated to SQL correctly, with the correct WHERE and JOIN clauses. Also, it's not necessary to check for null when FirstOrDefault is used inside the LINQ to SQL query.
Popular Answer
It's almost that, i advice you to add condition in null case, and you have to declare the foreign keys in your models like :
class User
{
public int Id { get; set; }
public int CommunityId { get; set; }
[ForeignKey("CommunityId")] // facultative because it respects naming convention Modelname + "Id"
public Community { get; set; }
}
class Community
{
public int Id { get; set; }
public int CountryId { get;set; }
[ForeignKey("CountryId")] // facultative because it respects naming convention Modelname + "Id"
public Country { get; set; }
}
var usersOfAGivenCountry = dbContext.Users
.Include(p => p.Community)
.Where(community => community != null
&& community.CountryId == countryId);
And the line .Include(p => p.Community) is facultative if you enabled Lazy Loading mode, but i advice you to disable it
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow