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
.
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.
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