CODEWITHSUNDEEP
c#asp.net-mvc-4join
F.Shahid
F.Shahid

Reputation: 11

Retrieve and iterate data using JOIN in C# MVC4

I am not getting how to iterate data that is retrieved using join. Table project images

[img_id]  INT           IDENTITY (1, 1) NOT NULL,
[proj_id] INT           NOT NULL,
[path]    NVARCHAR (50) NOT NULL,
PRIMARY KEY CLUSTERED ([img_id] ASC),
CONSTRAINT [FK_projimg_projects] FOREIGN KEY ([proj_id]) REFERENCES [dbo].[Projects] ([proj_id])

Table Projects

[proj_id]   INT            IDENTITY (1, 1) NOT NULL,
[proj_name] NVARCHAR (50)  NOT NULL,
[step1]     NVARCHAR (MAX) NOT NULL,
[step2]     NVARCHAR (MAX) NOT NULL,
[step3]     NVARCHAR (MAX) NOT NULL,
[step4]     NVARCHAR (MAX) NOT NULL,
[user_id]   INT            NOT NULL,
[materials] NVARCHAR (MAX) NOT NULL,
[tag]       NVARCHAR (50)  NOT NULL,
PRIMARY KEY CLUSTERED ([proj_id] ASC),
CONSTRAINT [FK_Projects_user] FOREIGN KEY ([user_id]) REFERENCES [dbo].[Users] ([user_id])

i retrieved the data using following query

var tutorial = from proj in de.Projects
    join image in de.projimgs
    on proj.proj_id equals image.proj_id
    select new {
       proj.proj_name,
       proj.materials,
       proj.step1,
       proj.step2,
       proj.step3,
       proj.step4,
       image.path,
    };

and now i want to iterate the data, each project containing multiple images, how do i show those images in single iteration of foreach loop. Can anyone help clearly. Thankx in advance.

Upvotes: 0

Views: 39

Answers (2)

Tim Copenhaver
Tim Copenhaver

Reputation: 3302

First, if you have your foreign keys set up as mapped properties, Rahul's answer is the easiest. If you actually need to do a join, your query isn't quite right.

Keep in mind what your SQL statement is doing. When you do an INNER JOIN, you're asking for one result per combination of values. Your query is equivalent (roughly) to:

SELECT proj.proj_name, proj.proj_name, proj.materials, proj.step1, proj.step2, proj.step3, proj.step4, image.path
FROM Project proj
INNER JOIN Project_Images image ON image.ProjectId = proj.Id

Given your select statement, you will get back multiple copies of the project - one for each image. You just loop over those results.

It sounds like what you wrote is incorrect and what you actually want is a group by:

var tutorial = from proj in de.Projects
    join image in de.projimgs on proj.proj_id equals image.proj_id
    group image by proj into groupedImages
    select new { Project = groupedImages.Key, Images = groupedImages };

Then you loop over it:

foreach (var project in tutorial)
{
    // Do what you want with project here
    foreach (var image in project.Images)
    {
        // Do what you want with image here
    }
}

Upvotes: 0

Rahul
Rahul

Reputation: 77866

Well you can iterate then in two foreach loop like

foreach(project p in tutorial)
{
   foreach(image in p.Images)
   {
       //Do your processing
    }
}

Upvotes: 1

Related Questions