Using JOIN with Linq to SQL ExecuteQuery Issue

Question

I have been using Linq to SQL for a while now on one of my sites and over time the code I am using to query the database has gotten a little messy so I decided to re-write, originally my queries were all handled exclusively by Linq but recently there has been a demand for more advanced search features which has led me more towards using ExecuteQuery and handwriting my SQL statements the problem is that I cannot for the life of me get the Join statement to work properly.

I have two tables in my databases, t_events and t_clients. The only thing similar between the two tables is that they both have a clientid field (the id of the client the event is for). What I need to be able to do is pull all of the events into the page (which works fine) but I dont want to show the clientid on the page I need to show the client name. Originally I had a join clause that handled this nicely:

   var eve = from p in db.t_events
                          join c in db.Clients on p.clientid equals c.clientid
                          where p.datedue <= thisDay && p.status != "complete"
                          select new { p.eventname, p.datedue, p.details, p.eventid,         p.status, c.clientname };

With the redesign of the page however I am having issues recreating what linq has done here with the join. My current code:

  StringBuilder sqlQuery = new StringBuilder("SELECT * FROM dbo.t_events JOIN dbo.t_clients ON dbo.t_events.clientid=dbo.t_clients.clientid");
    var query = db.ExecuteQuery<t_events>(sqlQuery.ToString());
    foreach (var c in query)
    {       
            counter = counter + 1;
            MyStringBuilder.Append("<tr class='"+c.status+"'><td><a href='searchdetails.aspx?id="+c.eventid+"'>"+c.eventname+"</a></td><td>" +c.clientname+ "</td></tr>");
    }

in the foreach loop I have you can see I am trying to pull in c.clientname (which doesnt work) as it is not on the t_events database, changing this to c.clientid makes the code work, I am not sure what the issue is as taking that same SQL and running the query directly off the sql management tool works like a charm. Any ideas on this issue would be greatly appreciated!

FIXED!

DaveMarkle suggested using a view, which was by far a much easier way of doing this. I created a view that joins the two tables together with the fields I need and run my queries against it, simple and effective, I thank you!

Answer 1

Erm - so maybe we should have an answer here then so the question drops off the 'unanswered' list.

As Dave Markle stated.

Use a view.