Is it possible to store multiple values in one column from stored procedure that will be stored as H-CodePudding

I have a SQL table MainProperty modeled in C# with a one-to-many relationship with another table, like this:

public virtual ICollection<ForeignProperty> ForeignProperty { get; set; }

A stored procedure is called that returns a List<MainProperty>, but we're not populating the ForeignProperty on any of those records.

In that procedure, the two tables, MainProperty and ForeignProperty are joined, but nothing is done with that join.

Is there a way in SQL to return a record with it's ForeignProperty values in that record so that the model is populated properly in C#?

The issue is that a record could have more than 1 ForeignProperty values, and I'm not sure how to property store the multiple values in one column (user-defined table maybe?), and then turn that column of values into an ICollection in C#.

CodePudding user response：

Is there a way in SQL to return a record with it's ForeignProperty values in that record so that the model is populated properly in C#?

No. You'll have to fetch the ForeignProperty objects in a subsequent query. EF will "fix up" the relationships when you do. Alternatively you can use Lazy Loading and load each ICollection<ForeignProperty> on demand.