Solved

How to select related entites in LINQ/Entity Framework

Posted on 2010-11-25
13
475 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

786 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