Question: materializing one entity from multiple rows

[natemcmaster]

Q: Is an intermediate type the only way to materialize one entity from multiple rows?

Simplified case that represents the fundamental problem in materializing scaffolding's IndexModel and ForeignKeyModel with EF.

Table: Countries

Name	Color
USA	Red
USA	White
USA	Blue

public class Country
{
    [Key]
    public string Name { get; set; }

    public ICollection<string> Colors { get; set; }
}

Possible workaround:materialize Country by hand from intermediate type such as CountryRow.

[divega]

@natemcmaster In your simplified example I see Colors is a collection of scalars which we don't support, but I am going to assume that this is just an artifact of the simplification.

Assuming this shape instead:

public class Country
{
    [Key]
    public string Name { get; set; }

    public ICollection<CountryColor> Colors { get; set; }
}


public class CountryColor 
{
    public string ColorName { get; set; }
    public string CountryName {get ; set; }
}
...
modelBuilder.Entity<CountryColor>().HasKey(cc = > new {cc.ColorName, cc.CountryName});

The queries would look something like this:

var Countries = context.Countries.FromSql("SELECT DISTINCT Name FROM Countries");
var CountryColors = context.CountryColors.FromSql("SELECT Name, Color FROM Countries");

[divega]

Would that work?

[natemcmaster]

@divega this solution is what I meant by "intermediate type". I was more asking if there were any plans to avoid this "middle man" type. If not, we can close and I can do the workaround approach.

collection of scalars which we don't support

I assume if we don't do a collection of scalars, a collection of related entity types is also out.

[divega]

I assume if we don't do a collection of scalars, a collection of related entity types is also out

That is supported, but I am sure you know it 😄 so I must be missing what you mean.

Let's chat in person, otherwise I am afraid I may be misunderstanding what this issue is really about.

[natemcmaster]

Discussed in person with @divega about different scenarios we support. We do not plan to support the scenario in the original question. Instead users should follow the suggestion outline above: see #3945 (comment)

[divega]

Also, from talking to @natemcmaster I came to the conclusion that part of the problem was that our domain model for the database schema uses many-to-many relationships, which EF7 RTM won't support directly.

At least for the case of IndexModel and ColumnModel we know that the relationship we are trying to model actually contains payload (e.g. whether a column participates in the index as ASC or DESC, among other details) so the fix could be to include the "join" entity in our domain model.