ASP.NET MVC 2 linq to sql many to many relationship

Before anything, I would like to state I have done alot of searching, not just here, also on google. But answers never got me in the right direction, or I just did not understand it.

I am stuck on the following problem:

I have three tables:

Projects: PK id(int)

name(nvarchar 50)

ProjectImages: FK ProjectId(int)

FK ImageId(int)

Images: PK id(int),

path(nvarchar(MAX))

I'm using ASP.NET MVC 2.0 to design my own portfolio and I use LinQ to SQL to fetch/manipulate table data.

I'm faily new, but I have a decent understanding of C#, maybe my lack of knowledge is what got me here.

Anyway, the problem now is that the CMS I'm writing needs to be able to attach multiple images to one or more projects and each project can contain more than one image.

projects M:M images

I already fail at retrieving a project with its corresponding images using a query, and have tri开发者_JAVA百科ed multiple things found both here and on google.

I was thinking about inner classes, but im not sure where to start.

Do I need to make an inner class that models the intersection table? Perhaps a completely new one that inherits etc. I'm quite clueless. Especially after breaking my brain about it all day.

I hope someone can provide me with an answer without having to use plinqo or NHibernate, cause that would cause me to to start all over and I've already written plenty.

Have a look here:

Best way, but maybe a little hard to implement for simple projects:

http://blogs.msdn.com/b/mitsu/archive/2008/03/19/how-to-implement-a-many-to-many-relationship-using-linq-to-sql-part-ii-add-remove-support.aspx

Loads easier solution, which unfortunately only provides read-only access. Perhaps this suits your scenario and is therefore the best approach:

http://www.iaingalloway.com/2015/06/many-to-many-relationships-in-linq-to-sql.html

All of this is SQL related, no other code here (don't know ASP).

To retrieve all of the image paths for a specified project, you can use this SQL query.

select i.path
from Projects p
join ProjectImages pi on pi.ProjectIt = p.id
join Images i on i.id = pi.ImageId
where p.name = 'project'

To insert more than one image per solution you could do this:

--insert the images:
insert into images (name) path ('/path/to/image')
insert into images (name) path ('/path/to/second/image')

--Get the IDs of these images:
select id from images where path in ('/path/to/image','/path/to/second/image')
--let's say this gives you ids 10 and 11

--Get the project name:
select id from projects where name = 'project'
--let's say your project id is 1

--Insert into the joining table, once for each image (change for values above)
insert into ProjectImages (ProjectId,ImageId) values (1,10)
insert into ProjectImages (ProjectId,ImageId) values (1,11)

I'm not sure of a fancy way of doing the last part with a single SQL statement, but all of these should work.