How do I navigate through multiple relationships using LINQ to Entities?

Question

I'm just starting out with LINQ to Entities, and I'm trying to replicate a search we currently do in a stored procedure as a LINQ to Entities query. However, I can't seem to figure out the correct way to query properties of entities more than one "join" away from the starting point of my query.

For example, say I have tables Campaign, CampaignLocation, and Location, which have foreign keys from Campaign to CampaignLocation, and CampaignLocation to Location. Pretty standard many-to-many table configuration. When I start writing my LINQ query like this:

 var campaigns = from c in context.Campaign.CampaignLocation

that's as far in the association chain as I can go. There doesn't seem to be a "Location" property available on "CampaignLocation" so that I can filter on properties of the Location.

I tried using the LINQ join syntax like this:

 var campaigns = from c in context.Campaign
                 join cl in context.CampaignLocation 
                   on c.CampaignID equals cl.CampaignID

But there doesn't seem to be a "CampaignID" property on the "cl" alias. Which is really weird, there's a column named that on the table. Is it not on the model object since it's the foreign key to the Campaign table?

Where am I going wrong here, and what am I missing?

[UPDATE]

It looks like any integer columns I use for foreign keys are not being added as properties of the model objects. The foreign key relationships are displayed, but the ID properties aren't there. Is there any way to get the designer to add these properties to the model when I read the schema from the database?

Answer 1

The property is just an entity reference off of the table itself, do you have the Location relationship defined in your EF model for ComaignLocation<->Location? Then depending on what you are actually trying to do (join, etc..) I would consider using the linq join syntax, linq nested query (can form a join) or just using the proc you originally had. If there was a purpose to having a proc in the first place, just pull that into your EF Model.

Edit for Update:

Try opening the model and refreshing from the DB for those tables, the columns should be there, they can be manually removed however. Even with the columns in place you still have to define the relationships yourself inside the EFModel.

Answer 2

Do these work?

    Campaign campaign1 = context.Campaigns.Where(x => x.CampaignID == 1).Single();
    foreach (CampaignLocation campaignlocation in campaign1.CampaignLocations)
    {
        Response.Write(campaignlocation.Location.Name + "<br />");
    }

    Location location1 = context.Locations.Where(x => x.LocationID == 3).Single();
    foreach (CampaignLocation campaignlocation in location1.CampaignLocations)
    {
        Response.Write(campaignlocation.Campaign.Name + "<br />");
    }

(I suspect you're missing either a foreign key or an object reference...)

Answer 3

Alternative answer - You'll need to manually edit / recreate your .edmx file if you've changed the database since you created it?