Solved

How to select related entites in LINQ/Entity Framework

Posted on 2010-11-25
13
474 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

910 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

24 Experts available now in Live!

Get 1:1 Help Now