Solved

How to select related entites in LINQ/Entity Framework

Posted on 2010-11-25
13
473 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
  • 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

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…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now