LINQ to SQL Basic question - table relationships

Question

I'm playing with an image gallery in ASP.NET MVC, and trying to get my head around LINQ to SQL at the same time. I have 3 tables to hold image gallery data, with a many-many relationship through a link table. These are :

Gallery
(Id, Name, Description)

Image
(Id, Title, Description, FileName)

GalleryImage
(GalleryId, ImageId)

GalleryImage has FK relationships set up to the other two tables.

I want to be able to load my galleries, and display the images associated with each one, as well as have the ability to display a single image and list the galleries with which it is associated. Since I'm new to Linq to SQL, I don't know how to do this. Can anyone guide me, please?

Answer 1

Gallery.GalleryImages<GalleryImage>

You should be able to access your GalleryImage objects through that, and then on each GalleryImage you can call:

GalleryImage.Image

This assumes the relationships are represented in the DBML designer?

Answer 2

You need to access all this through your DataContext object.

Have a look in the generated c# file for your dbml and look for EntitySet collections marked with an Association attribute - there should be one in Gallary and Image, and 2 in GallaryImage. If the dbml is generated correctly, you should be able to do something like the following -

Off the top of my head, i'm pretty sure that the design surface will name the plural of Gallery as Gallerys instead of Galleries, so it's not a typo -

DataConext dc = new GalleryDataConext();
foreach (Gallery g in dc.Gallerys)
{
  Console.Writeline("gallery id " + g.Id.ToString());
  foreach(GalleryImage gi in g.GalleryImages)
   {
      Console.Writeline("galleryimage id " + gi.Id.ToString());
      foreach(Image i in gi)
       {
         Console.Writeline("image id " + i.Id.ToString());
       }
   }

Even without the associations, the following should work -

int GalID = 1;
GalleryDataConext dc = new GalleryDataConext()
var pics = from g in dc.Gallary
           join gi in dc.GallaryImages on g.Id equals gi.GallaryId
           join i in dc.Images on gi.ImageId equals i.Id
           where g.Id = GalID
           select i;

To get gallaries from a pic id, you'd do -

int PicID = 1;
var gals = from g in dc.Gallary
           join gi in dc.GallaryImages on g.Id equals gi.GallaryId
           join i in dc.Images on gi.ImageId equals i.Id
           where i.Id = PicID
           select g;

The above will return you an IQueryable<Gallary> and will do the sql query when you enumerate over it.

Answer 3

You need to create relationships among the tables. if you are sure you have created the relationships then may be child or parent properties of classes may be set to internet they should be public

Answer 4

Step 1: create the associations between the tables. You'll know this is done properly when

• class Gallery has property GalleryImages
• class GalleryImage has property Gallery and Image (note, singular)
• class Image has property GalleryImages.

http://msdn.microsoft.com/en-us/library/bb629295.aspx

Step 2: To get a list of galleries:

using (CustomDataContext myDC = new CustomDataContext)
{
  List<Gallery> result = myDC.Galleries.ToList();
}

Step 3: then the user clicks on a gallery and you want its images:

using (CustomDataContext myDC = new CustomDataContext)
{
  List<Image> result = myDC.Galleries
    .Where(g => g.Id = selectedGallery.Id);
    .SelectMany(g => g.GalleryImages)
    .Select(gi => gi.Image)
    .ToList()
}

Step 4: Then the user clicks on an image and you want its galleries:

using (CustomDataContext myDC = new CustomDataContext)
{
  List<Gallery> result = myDC.Images
    .Where(i => i.Id = selectedImage.Id);
    .SelectMany(i => i.GalleryImages)
    .Select(gi => gi.Galleries)
    .ToList()
}

---

If you just want to load the whole database, do this:

using (CustomDataContext myDC = new CustomDataContext)
{
  DataLoadOptions dlo = new DataLoadOptions();
  dlo.LoadWith<Gallery>(g => g.GalleryImages);
  dlo.LoadWith<GalleryImage>(gi => gi.Image);
  myDC.LoadOptions = dlo;

  List<Gallery> result = myDC.Galleries.ToList();
}

After doing that, the whole object graph will be loaded and connected for your use.