Entity Framework: How to select specific columns from a related entity-CodePudding

I have a PlayerContext model that has many BlueprintContext models. Like so:

public class PlayerContext
    {
        public PlayerContext() { }

        [Key]
        public int id { get; set; }
        ...
        public List<BlueprintContext> Blueprints { get; set; }
        ...
    }

public class BlueprintContext
    {
        [Key]
        public int id { get; set; }
        public Dictionary<Coord3, Chonxels> BigData = new Dictionary<Coord3, Chonxels>() { };
        public Dictionary<BlueprintIngredient, int> recipe = new Dictionary<BlueprintIngredient, int>();
        public string Name { get; set; }
        public int CreatorId { get; set; }
        public PlayerContext Creator { get; set; }
    }

In BlueprintContext the BigData field can get really big. So when I load a PlayerContext I want the Blueprints but I only want the id, recipe, and Name fields (not the BigData).

Is there a way to load the PlayerContext and include the BlueprintContext fields I need without the BigData field?

Here is what I tried

        using (var db = new EfContext())
            {
                PlayerContext playerContext = db.Players
                    .Where(p => p.id == playerId)
                    ...
                    .Include(p => p.Blueprints.Select(b => new { b.id, b.recipe, b.Name}))
                    .AsNoTracking() // disables change tracking
                    .FirstOrDefault();

and I get this Exception:

System.InvalidOperationException: The expression 'p.Blueprints.AsQueryable().Select(b => new <>f__AnonymousType43`3(id = b.id, recipe = b.recipe, Name = b.Name))' is invalid inside an 'Include' operation, since it does not represent a property access: 't => t.MyProperty'. To target navigations declared on derived types, use casting ('t => ((Derived)t).MyProperty') or the 'as' operator ('t => (t as Derived).MyProperty'). Collection navigation access can be filtered by composing Where, OrderBy(Descending), ThenBy(Descending), Skip or Take operations. For more information on including related data, see http://go.microsoft.com/fwlink/?LinkID=746393.

I'm using Entity Framework Core.

CodePudding user response：

This is one reason to consider projecting your top level entity. For example if you want to display a summary detail of a player and their blueprints:

[Serializable]
public class PlayerSummaryViewModel
{
    public int Id { get; set; }
    public string Name { get; set; }

    // Any other fields the view actually needs.
    public IList<BlueprintSummaryViewModel> Blueprints { get; set; } = new List<BlueprintSummaryViewModel>();
}

public class BlueprintSummaryViewModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Recipe { get; set; }
}

Then project via Select or Automapper's ProjectTo:

using (var db = new EfContext())
{
    PlayerViewModel player = db.Players
        .Where(p => p.id == playerId)
        .Select(p => new PlayerSummaryViewModel
        {
            Id = p.Id,
            Name = p.Name,
            // ...
            Blueprints = p.Blueprints.Select(b => new BlueprintSummaryViewModel 
            {
                Id = b.Id,
                Name = b.Name,
                Recipe = b.Recipe
            }).ToList()
     }).Single();
}

With automapper since the properties are essentially 1 to 1 mappings with consistent naming, the mapping would essentially look like:

var config = new MapperConfiguration(cfg => 
{
    cfg.CreateMap<Player, PlayerSummaryViewModel>();
    cfg.CreateMap<Blueprint, BlueprintSummaryViewModel>();
};

using (var db = new EfContext())
{
    PlayerViewModel player = db.Players
        .Where(p => p.id == playerId)
        .ProjectTo<PlayerSummaryViewModel>(config)
        .Single();
}

Where config can be centralized for all view models or created on demand.

A reason why doing something like a selective include like you propose would be bad is that any method that accepts an entity should be able to expect that the entity passed is a complete representation of the domain state, or at least a complete-able representation. If you have a method that takes a Player entity, that method should not need the overhead to check whether that player does include Blueprints, or can include Blueprints, or might only get some pruned version of Blueprints. View models or DTOs ensure that the type passed around is fit for purpose and it is clear what is expected.

CodePudding user response：

You want table splitting, where two or more entities have a one to one relationship using shared primary key (PK is FK in dependent entity) and are backed by the same table. You then optionally load all but the principal entity. You do this buy mapping all the entities to the same table and configuring the one-to-one relationship using the PK as the FK:

public class BlueprintPrincipal
{
    public int Id { get; set; }
    // properties to always load
    public string Name { get; set; }
    ...
    
    // nav prop(s)
    public BlueprintDependent Dependent { get; set; }
}

public class BlueprintDependent
{
    public int Id { get; set; }
    // properties to selectively load
    ...
}

Configuration:

modelBuilder.Entity<BlueprintPrincipal>()
    .ToTable("Blueprint");

modelBuilder.Entity<BlueprintDependent>()
    .ToTable("Blueprint");

modelBuilder.Entity<BlueprintPrincipal>()
    .HasOne(bp => bp.Dependent)
    .WithOne()
    // shared primary key
    .HasForeignKey<BlueprintDependent>(bd => bd.Id);

Then you query as you would with any other principal-dependent relationship:

var depNotLoaded = dbContext.BlueprintPrincipals
    .Where(bp => bp.Name == "xyz");

var depEagerLoaded = dbContext.BlueprintPrincipals
    .Include(bp => bp.Dependent)
    .Where(bp => bp.Name == "xyz");