Filtering data by Navigation Property in EF Core

.net entity-framework entity-framework-core

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.

1
0
5/31/2019 10:01:02 PM

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.

0
11/7/2018 12:16:19 PM

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow