Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to select related entites in LINQ/Entity Framework

Posted on 2010-11-25
13
Medium Priority
?
486 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 1000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

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 …
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses
Course of the Month15 days, 11 hours left to enroll

580 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