Entity Framework core 1 not doing relation query

Question

I am using Entity Framework Core 1.0.0 RC2 final version.

I have 2 database models Country and State

public class Country
{        
    public string CountryCode { get; set; }
    public string CountryName { get; set; }
    public virtual ICollection<State> States { get; set; }
}

public class State 
{
    public string StateCode { get; set; }
    public string StateName { get; set; }
    public string CountryCode { get; set; }       
    public Country Country { get; set; }
}

The mapping from State is done as below for Country:

builder.ToTable("Country");
builder.HasKey(pr => pr.CountryCode);
builder.HasMany(m => m.States).WithOne(i => i.Country).HasForeignKey(m => m.CountryCode);

for State:

builder.ToTable("State");
builder.HasKey(pr => pr.StateCode);
builder.HasOne(m => m.Country).WithMany(m => m.States).HasForeignKey(m => m.CountryCode);

Now when I run the following linq query.

var query = _context.Countries
                    .Where(i => i.CountryCode == "USA")
                    .Select(m => new
                                  {
                                       m.CountryName,
                                       m.CountryCode,
                                       States = m.States.Select(x => new
                                                         {
                                                            x.StateCode,
                                                            x.StateName,
                                                            x.CountryCode
                                                         })
                                  }).AsQueryable();
  return query.ToList();

When I run SQL Server profiler, it shows:

SELECT [i].[CountryName], [i].[CountryCode]
FROM [Country] AS [i]
WHERE [i].[CountryCode] = N'USA'

SELECT [x].[CountryCode], [x].[StateCode], [x].[StateName]
FROM [State] AS [x]

The State query doesn't have any WHERE clause to check with the CountryCode. Also, shouldn't the two queries be combined?

What's the issue here?

Bassam Alugili Bassam Alugili · Accepted Answer · 2016-06-10T09:31:32

Unfortunately he load all States from the database and filtered them in the memory. Maybe you can test this behaviour with EF6 and open an issue in https://github.com/aspnet/EntityFramework

(QueryContext queryContext) => IEnumerable<<>f__AnonymousType2<string, string, IEnumerable<<>f__AnonymousType3<string, string, string>>>> _Select(
    source: IEnumerable<ValueBuffer> _ShapedQuery(
        queryContext: queryContext, 
        shaperCommandContext: SelectExpression: 
            SELECT [i].[CountryName], [i].[CountryCode]
            FROM [Countrys] AS [i]
            WHERE [i].[CountryCode] = N'USA'
        , 
        shaper: ValueBufferShaper
    )
    , 
    selector: (ValueBuffer i) => new <>f__AnonymousType2<string, string, IEnumerable<<>f__AnonymousType3<string, string, string>>>(
        (string) object i.get_Item(0), 
        (string) object i.get_Item(1), 
        IEnumerable<<>f__AnonymousType3<string, string, string>> _Select(
            source: IEnumerable<ValueBuffer> _Where(
                source: IEnumerable<ValueBuffer> _ShapedQuery(
                    queryContext: queryContext, 
                    shaperCommandContext: SelectExpression: 
                        SELECT [x].[CountryCode], [x].[StateCode], [x].[StateName]
                        FROM [States] AS [x]
                    , 
                    shaper: ValueBufferShaper
                )
                , 
                predicate: (ValueBuffer x) => (string) object i.get_Item(1) == (string) object x.get_Item(0)
            )
            , 
            selector: (ValueBuffer x) => new <>f__AnonymousType3<string, string, string>(
                (string) object x.get_Item(1), 
                (string) object x.get_Item(2), 
                (string) object x.get_Item(0)
            )
        )
    )
)

Entity Framework core 1 not doing relation query

1 Answers