Entity Framework 7 navigation property is null

entity-framework-core

Question

I am creating an Entity Framework 7 project to replace an Entity Framework 6 project.

I have an Item entity which belongs to a country. I then have a linq query that gets the count by country. Here is the query.

var results = allItems
                .GroupBy(g => g.Country)
                .ToDictionary(s => s.Key, s => s.Count());

This works with EF6 but throws an exception with EF 7 (the exception is at the bottom).

This is my entity:

public class Item
{
    [Key]
    public int id { get; set; }

    [Required]
    public int Countryid { get; set; }

    [ForeignKey("Countryid")]
    public virtual Country Country { get; set; }

}

In EF 7, with the debugger I see that the Country is null (that's the navigation property) but I do have the countryid. In EF 6, I have an object for the navigation property. In addition, I have unit tests using Moq and they work (the show the nav property).

I tried to add an include but I should not need that. I didn't need it in EF 6 or with the Mock.

Using include gives this:

var results = allItems
                    .Include(i => i.Country)
                    .GroupBy(g => g.Country)
                    .ToDictionary(s => s.Key, s => s.Count());

I get the same error.

Here is the error:

Expression of type 'System.Func2[Microsoft.Data.Entity.Query.EntityQueryModelVisitor+TransparentIdentifier2[Microsoft.Data.Entity.Query.EntityQueryModelVisitor+TransparentIdentifier2[FMS.DAL.Entities.ActionItem,Microsoft.Data.Entity.Storage.ValueBuffer],Microsoft.Data.Entity.Storage.ValueBuffer],FMS.DAL.Entities.MemberCountry]' cannot be used for parameter of type 'System.Func2[FMS.DAL.Entities.ActionItem,FMS.DAL.Entities.MemberCountry]' of method 'System.Collections.Generic.IEnumerable1[System.Linq.IGrouping2[FMS.DAL.Entities.MemberCountry,FMS.DAL.Entities.ActionItem]] _GroupBy[ActionItem,MemberCountry,ActionItem](System.Collections.Generic.IEnumerable1[FMS.DAL.Entities.ActionItem], System.Func2[FMS.DAL.Entities.ActionItem,FMS.DAL.Entities.MemberCountry], System.Func`2[FMS.DAL.Entities.ActionItem,FMS.DAL.Entities.ActionItem])'

Accepted Answer

Currently GroupBy is not implemented in EF7 the status of features can be found on the road map page here. https://github.com/aspnet/EntityFramework/wiki/Roadmap

A work around would be:

context.Countries.Select( x => new
{
    x.Id,
    Items = x.Items.Count
} ).ToDictionary( x => x.Id, x => x.Items );

public class Country
{
    public int Id { get; set; }

    //Add this property
    public virtual ICollection<Item> Items { get; set; }
}

//Generated SQL
SELECT (
    SELECT COUNT(*)
    FROM [Item] AS [i]
    WHERE [x].[Id] = [i].[Countryid]
), [x].[Id]
FROM [Country] AS [x]

This would require adding a Items property to country but would allow you to achieve what you are after all in Linq. You could alse go for writing the query in sql and executing with EF but may not be the best option.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why