Mapping more than two tables using the Entity Framework

Question

I was looking at using the EF for a project I will be starting in the coming weeks.

I have three tables in a previously created database. (please see image attached) EDMX

When a CompanyNotice record is created at least one Location must be added (from the Locations table) into the CompanyNoticeLocations table.

e.g.

**CompanyNotice**
ID  CompanyID Date                      Heading Text   .....
2   9         2011-11-21 10:17:29.573   Lorem   Ipsum   1   1   1

**CompanyNoticeLocations**
CompanyNoticeLocationsID CompanyNoticeID LocationID
1                        2               4
2                        2               5
3                        2               1

Main question: Can anyone please tell me if I can use the EF to create an Entity called: CompanyNoticesWithLocations that just returns:

Heading
Text
List of LocationNames

Sub Question: I have tried doing this with LINQ without the multiple table to Entity mapping and I couldn't get that to work either:

using (var context = new ALEntities())
                {
                    var query = from c in context.CompanyNotices.Include("Locations")
                                select new 
                                { 
                                    c.CompanyNoticeHeading, 
                                    c.CompanyNoticeText,
                                    (from l in c.CompanyNoticesLocations select l.Location.LocationName)
                                };
                    ASPxGridView1.DataSource = query;
                    ASPxGridView1.DataBind();



                }

However I get an error:

Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access.

what if you do .toList to the inner select clause ? i am not sure what the property name would be for the collection though... — np-hard, Nov 21 '11 at 18:11
I still get the Invalid anonymous type error. I have also tried the LINQ .Aggregate() on the inner select and I got the same error message. — Seany84, Nov 21 '11 at 18:14

score 1 · Answer 1 · answered Nov 21 '11 at 19:39

1

this works for me

var query = from c in context.CompanyNotices.Include("Locations") 
                                select new  
                                {  
                                    c.CompanyNoticeHeading,  
                                    c.CompanyNoticeText, 
                                    locations = (from l in c.CompanyNoticesLocations select l.Location.LocationName) 
                            };

where locations would the name of the property of your anonymous type..

answered Nov 21 '11 at 19:39

np-hard

5,725
6
52
76

when I used this the output in my gridview column appears as "System.Collections.Generic.List`1[System.String]" under locations and not the list of actual locations. – Seany84 Nov 22 '11 at 11:22
the anonymous type created in this case would be a class with property names as companynoticeheading, companynoticetext, and a Colllection of strings, so your grid view would need another repeater type control in it. – np-hard Nov 22 '11 at 14:57

score 1 · Accepted Answer · answered Nov 21 '11 at 20:27

1

Your CompanyNoticesLocation table doesn't need to exist as a separate entity in the model. This should be represented as a many to many Association between CompanyNotice and Location. As long as the Association is setup correctly EF will map this to your underlying join table which you'll be able to see in the Mapping window.

Here's an example of a many to many mapping from one of my own models showing the Association mapping to the underlying join table called CandidateAnswer:

enter image description here

answered Nov 21 '11 at 20:27

Darren Lewis

8,338
3
35
55

I understand what you are suggesting here. However, when I remove the 'CompanyNoticeLocations' from my model/diagram I cannot see the mapping details for the entity any more. How can I create this many-to-many relationship? Thanks – Seany84 Nov 22 '11 at 11:32
1

Right click on the CompanyNotice or Location entities and create an Association called CompanyNoticesLocation. You can then set this up as a many-many in the property window (F4). – Darren Lewis Nov 22 '11 at 11:37
You may be interested in this other question I have regarding the EF: http://stackoverflow.com/questions/8228101/aggregating-one-side-of-a-many-to-many-and-bind-to-gridview-in-entity-framework – Seany84 Nov 22 '11 at 14:08

Mapping more than two tables using the Entity Framework

2 Answers2