Monday, January 19, 2009

Many to Many with LINQ

Hey All, 

The LINQ to SQL/Entity Models released with the SP1 of Visual Studios does not innately support Many-To-Many DB realtions. This can be shown when working with the Designer, and trying to draw a new association between to table and selecting the relationship type. Notice it only has one-to-one and one-to-many?

However, you can still simulate it with the following LINQ statements : 

WebSiteMasterzDataContext webDb = new WebSiteMasterzDataContext();
            var query = from s in webDb.Staffs
                        join pro in webDb.Staff_Projects on s.StaffId equals pro.StaffId
                        where pro.ProjectId == ProjectId
                        select s;

            return query;

The code above effectively does a join between the Staff and Project table through the many-to-many table Staff_Projects.

Hope this helps, 

 - Tim

No comments: