Solved

How to select related entites in LINQ/Entity Framework

Posted on 2010-11-25
13
478 Views
Last Modified: 2012-05-10
I have two entities, Gallery and File.  There are many galleries and each gallery can contain Files. A file can be present in multiple galleries.  In the database there is an intermediate table, Gallery_Files which just contains two foreign keys, to link the tables.

I need to select the files in a particular gallery. My Linq statement is iis currently :

Return (From c In Context.Files _
   Where c.Gallery.Id = GalleryId _
   Select c)

Open in new window


Where GalleryId is the primary key of the Gallery table/entity.  

This is incorrect as there is no Gallery related object, only a Galleries set.

Could someone provide a suitable linq statement?
0
Comment
Question by:pipelineconsulting
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
13 Comments
 
LVL 16

Expert Comment

by:Ady Foot
ID: 34214510
Hi there,

I do the following.
Dim gallery = (From c in Context.Files _
               Where c.GalleryID = GalleryID _
               Select c).Single

Dim file = (From f in Gallery.Files _
            Select f)

Open in new window

0
 
LVL 16

Expert Comment

by:Ady Foot
ID: 34214529
Sorry - what I've posted above is not what you're looking for.

Instead do the following.
var galleryfiles = from file in Gallery_Files 
                   join gallery in Galleries on files.GalleryId equals galleries.GalleryID 
                   select file;

Open in new window

0
 
LVL 16

Expert Comment

by:Ady Foot
ID: 34214554
Just for clarity.  I have a table called Artists and a table called ArtistGroups.  There can be any number of ArtistGroups and each ArtistGroup can contain an unlimited number of Artists.  In order to display the groups that a specific artist belongs to I use the following:
Dim db = New tempero_dbDataContext(My.Settings.TemperoConnectionString)

        Dim ArtistGroupMembers = From artistGroup In db.ArtistGroupMembers
                                 Join artist In db.Artists On artistGroup.ArtistID Equals artist.ID
                                 Where artist.ID = 102
                                 Select artistGroup

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:Ady Foot
ID: 34214602
An even simpler option:
Dim galleryFiles = From gf In db.Gallery_Files
                   Where gf.GalleryID = GalleryID
                   Select gf

For Each f As file In galleryFiles
   Debug.Print f.Files.Filename << or whatever field you wish to show
Next

Open in new window

0
 

Author Comment

by:pipelineconsulting
ID: 34214664
I'm not quite sure whether this is what I need and I'm starting to send myself slightly silly!  To confirm, the underlying database has three tables:

1. Galleries
2. Gallery_Files
3. Files

The files are linked to the galleries through the Gallery_Files table, which just has two columns, GalleryId and FileId.  

This is represented in the EntityModel by two entities, Gallery and File.  Gallery has a related property Files and File has a related property Galleries.  Gallery_Files is obviously ony represented in the EntityModel as an association rather than an entity in itself.

I can access the files in a Gallery through the Gallery.Files property and iterate through these.  However, fo various reasons I need to be able to retrieve an IQueryable collection of Files using just the primary key of the gallery rather than using the Gallery.Files property.

Is straightforward in regular SQL but this isn't an option for other reasons. I don't often use LINQ and am relatively new to EF so just a bit perplexed at the moment at the syntax for LINQ joins.
0
 
LVL 16

Expert Comment

by:Ady Foot
ID: 34214673
Hmm that's odd because I use LinqToSQL, not Entity Framework, but I have the exact scenario as you.  I have three tables:

1.  Authors
2.  AuthorGroups
3.  AuthorGroupMembers

AuthorGroupMembers has three columns; ID, AuthorGroupID, AuthorID.  

The join syntax I use is as I posted previously, but I've included below again.  For more help on the syntax fo LINQ joins you can use:
http://msdn.microsoft.com/en-us/vbasic/bb737929.aspx
Dim db = New tempero_dbDataContext(My.Settings.TemperoConnectionString)

        Dim ArtistGroupMembers = From artistGroup In db.ArtistGroupMembers
                                 Join artist In db.Artists On artistGroup.ArtistID Equals artist.ID
                                 Where artist.ID = 102
                                 Select artistGroup

Open in new window

0
 

Author Comment

by:pipelineconsulting
ID: 34214693
That must be the difference between the LinqToSql and EF Linq - I can't access the equivalent of your AuthorGroupMembers as I'm not querying the database itself, I'm querying the entities themselves
0
 
LVL 16

Expert Comment

by:Ady Foot
ID: 34214700
Yes I see.  I think that's going to be your problem.  You don't have an Entity for the Gallery_Files bit of your database.  Can you not run the query directly to the database and retrieve the data that way?
0
 

Author Comment

by:pipelineconsulting
ID: 34214710
That would be an option, but I'd prefer not to query the database directly as the rest of the data access is querying the entity model.

It is a comomn pattern so  I'm sure there is a simple solution, or at least there should be!
0
 
LVL 16

Expert Comment

by:Ady Foot
ID: 34214715
Yes but I think others will have an entity created for the Gallery_Files bit in the same way that I have in Linq to Sql.
0
 

Author Comment

by:pipelineconsulting
ID: 34214730
That might have to be the solution - the model generated from the DB structures the entities that way.

Strictly speaking, the intermediate table isn't and shouldn't be an entity as it is just a mapping table and the model generator correctly ignores it as an entity.  If I added in extra columns such as an Id column or something like a DisplayIndex column then it would become a valid entity as it contains "proper" data (this may well be a good idea!)
0
 
LVL 16

Accepted Solution

by:
Ady Foot earned 250 total points
ID: 34214735
>> If I added in extra columns such as an Id column or something like a DisplayIndex column then it would become a valid entity as it contains "proper" data (this may well be a good idea!)

I think so!  My AuthorGroupMembers table has its own ID and DateModified fields for that very reason.
0
 

Author Comment

by:pipelineconsulting
ID: 34214773
Cool. Not the solution I thought I needed but I think this will work out better
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question