EF/MVC get un-normalized list

Question

I have a model that has multiple many-to-many relationships. I like to get an un-normalized list of the data that this model is representing, including most of the data in the linked models.

The simplified models.

public class NewsItem :  IModel, IMultiTenant
{
    public int Id { get; set; }
    public String Title { get; set; }
    public DateTime PublishDate { get; set; }

    public virtual ICollection<Activity> Activities { get; set; }
    public virtual ICollection<StaffMember> StaffMembers { get; set; }
    public virtual ICollection<Member> Members { get; set; }
}
public class Activity : IMultiTenant, IModel, ILinkedStaffMember, ILinkedAdmin
{
    public int Id { get; set; }
    public String Description { get; set; }
    public virtual ICollection<StaffMember> StaffMembers { get; set; }
}
public class StaffMember : IMultiTenant, IModel, ILinkedActivity
{
    public int Id { get; set; }
    public String LastName { get; set; }
    public String FirstName { get; set; }
}
public class Member : IMultiTenant, IModel, ILinkedActivity
{
    public int Id { get; set; }
    public String LastName { get; set; }
    public String FirstName { get; set; }
}

Some sample data (that does not make any sense :-) ) :
NewsItem 1: Title "The blue team won", Linked Activities 1,3 Linked Members 3
NewsItem 2: Title "The red team won", Linked Activities 2 Linked Members 1,2
NewsItem 3: Title "The green team won", Linked Activities 1,2 Linked Members 2
NewsItem 4: Title "The oragne team won", Linked Activities 3 Linked Members 1,3

Activity1: Desc : "Football" linked staffmembers 1,3
Activity2: Desc : "Tennis" linked staffmembers 1,2
Activity3: Desc : "Basketball" linked staffmembers 2

Staffmember 1: Mike Rogers
Staffmember 2: Jim Morrison
Staffmember 3: Neil Young

Member 1: Neil Armstrong
Member 2: Youri Gagarin
Member 3: Frank De Winne
member 4: Dirk Frimout

Example what the result Is I'm looking for. When I retrieve a list of newsitems with an imaginary filter criteria that fits item 1 and 2. I like to have the followwing list returned.

"The blue team won", "Football", "Mike Rogers", "Frank De Winne"
"The blue team won", "Football", "Neil Young", "Frank De Winne"
"The blue team won", "Basketball", "jim Morrison", "Frank De Winne"
"The red team won", "Tennis", "Mike Rogers", "Neil Armstrong"
"The red team won", "Tennis", "Jim Morrison", "Neil Armstrong"
"The red team won", "Tennis", "Mike Rogers", "Youri Gagarin"
"The red team won", "Tennis", "Jim Morrison", "Youri Gagarin"

Any suggestions ?

I believe what you are asking for is all news items and oh yes btw include all navigational properties for each news items. For that the first part of the query will be : Var stuff = db.NewsItems (with no where clause). From there you will need to tell linq to include the peripheral data. Check this link out. https://www.google.com/search?q=linq+include&oq=linq+include&aqs=chrome..69i57j0l5.2368j0j7&sourceid=chrome&es_sm=122&ie=UTF-8 — JWP, Dec 26 '14 at 18:05
No it is not like that. Look at the sample data, it has 4 newsitem 'rows' but it only returns the first two. Because these two have some linked navigational properties. I want more rows to be returned so that I get a list all the data from the two 'selected' rows. The include (in my understanding) just makes sure that all data of the navigational properties gets loaded into memory now and does not wait until the lazy loading kicks in. — BrilBroeder, Dec 27 '14 at 15:11

score 0 · Answer 1 · edited May 23 '17 at 10:33

0

The way EF gets data from the database looks something like that. You could use EF to generate the query:

var res = context.Entities.Include(e=>e.ChildEntity)...

And then do this to pull the query string and run it yourself (Taken From This SO Question):

string sql = ((System.Data.Objects.ObjectQuery)result).ToTraceString();

edited May 23 '17 at 10:33

Community

1
1

answered Dec 26 '14 at 18:07

pquest

3,151
3
27
40

The include (in my understanding) just makes sure that all data of the navigational properties gets loaded into memory now and does not wait until the lazy loading kicks in. – BrilBroeder Dec 27 '14 at 15:12
Correct, which will cause the generated query to include a join – pquest Dec 28 '14 at 20:10

EF/MVC get un-normalized list

1 Answers1