-2

When using Entity Framework 6, how is the most efficient way to create an object or objects with additional data from other DbSet entities, when I have a DbContext or IQueryable<T>?

Here is some code:

If I have an Data class as follows:

public class Data
{
    [Key]
    public int id { get; set; }
    public string name { get; set; }
    public string data { get; set; }
    public int parentId { get; set; }
    public int otherDataId { get; set; }
}

And an OtherData class as follows:

public class OtherData
{
    [Key]
    public int id { get; set; }
    public string name { get; set; }
    public string data { get; set; }
}

In the Data class, the parentId is a foreign key reference to another Data object in the same DbSet, and the otherDataId is a foreign key reference to an OtherData object in a DbSet<OtherData>.

I would like to get all Data objects in the DbSet<Data>, with the additional DbSet data of the parent Data objects id and name and the OtherData object's id and name. I need this to be in one object to be sent from a webservice GET.

I am not sure on how to do this.

Do I need some code along the lines of:

var result = DbContext.Data.Select(x=> x...).Join(y=> y...) .. new { id = x.id... y.name.. }

Can I please have some help with this code?

Simon
  • 7,991
  • 21
  • 83
  • 163

2 Answers2

1

You can use a join and project the result. In the below snippet CombinedData is a another class with 2 string fields Name and OtherName. You can also use a view but I think the Join is less work. 

IQueryable<CombinedData> result = DbContext.Data.Join(
    DbContext.Data.DbContext.OtherData, 
    outer => outer.OtherDataId, 
    inner => inner.Id), 
    (outer, inner) => new { Name = outer.Name, OtherName = inner.Name} 
);
IanS
  • 15,771
  • 9
  • 60
  • 84
jackietrillo
  • 11
  • 1
  • Can you please explain the CombinedData properties? Also, I cannot compile DbContext.Data.DbContext.OtherData. – Simon Feb 26 '16 at 07:11
0

Depending on your overall architecture, this may be a good or a bad answer but often when faced with this in the past our teams would create a view in the database to combine the fields. Write your optimized query in the view and then treat it like any other table in your data layer.

You could accomplish the same end result using includes or joins or even writing out the expression in a cross-table query but in my opinion the view is the cleanest and most efficient method.

ohiodoug
  • 1,493
  • 1
  • 9
  • 12
  • Does this change the entities at all, or is just a way to combine current entities? I have done some searching, and am not sure how to find your example. Can you please point me to a web link? – Simon Feb 26 '16 at 06:51