I started studying LINQ as we finally convinced our web master to install framework 3.5 to the web server. LINQ is not that easy when it comes to performance issues, and it is also kinda suprising for me that linq to sql does not directly support many-2-many relation. I have a project that i am working on, having lots of tables in the database. A simplified table structure is below where you can see a many-2-many relation between websites, colleges and divisions. So now i have to write a query that will get info from websites tables, as well as i should get the college and division names for that sites.
The first thing i did is to create a partial class, and inside the partial class add functions such as:
public College GetSiteCollege()
{
WebsitesDataContext db = new WebsitesDataContext();
WebSiteCollege websiteCollege = this.WebSiteColleges.FirstOrDefault(c => c.WebsiteID == this.ID);
College college = (from c in db.Colleges
where c.ID == websiteCollege.CollegeID
select c).SingleOrDefault();
return college;
}
public Division GetSiteDivision()
{
WebsitesDataContext db = new WebsitesDataContext();
WebSiteDivision webSiteDivision = this.WebSiteDivisions.FirstOrDefault(c => c.WebSiteID == this.ID);
Division division = (from d in db.Divisions
where d.ID == webSiteDivision.DivisionID
select d).SingleOrDefault();
return division;
}
So now, when i need to bind the websites to a gridview, and lets say, the grid needs the colleges and divisions information for a site, the binding query has to call these functions for each row, which will end up querying the database #(websites) X (#(colleges)+#(divisions)) where # represents "Number of". As you can guess this is lots of query. So the next thing i did is to look for a good join :) to simply the database query. I havent found so many stuff about many-2-many relations with linq, so below is my code that i wrote, and i profiled this code, linq makes only 2 queries this time :) great...
WebsitesDataContext db = new WebsitesDataContext();
var webSitesData = from w in db.Websites
join c in db.Colleges on
w.WebSiteColleges.SingleOrDefault(x => x.WebsiteID == w.ID).CollegeID
equals c.ID
join d in db.Divisions on
w.WebSiteDivisions.SingleOrDefault(y => y.WebSiteID == w.ID).DivisionID
equals d.ID
select new
{
ID = w.ID,
SiteName = w.SiteName,
SiteUrl = w.SiteUrl,
CollegeAbbr = c.Abbr,
DivisionAbbr = d.Abbr
};