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!

I know this doesn't really answer your question directly, but I personally would tend to back away from using ExecuteQuery like this. Instead, I'd create a view and bind to that instead. — Dave Markle
– Dave Markle, Commented Jun 6, 2012 at 19:19
@DaveMarkle I had originally thought about that but the problem is that there are literally hundreds of combinations for querying, which is what originally turned me more towards ExecuteQuery — Gordnfreeman
– Gordnfreeman, Commented Jun 6, 2012 at 19:22
I take that back, had a brain fart for a moment maybe I was thinking of stored procedures, that worked, and was much easier then I would have expected... — Gordnfreeman
– Gordnfreeman, Commented Jun 6, 2012 at 19:36

PeteGO · Accepted Answer · 2012-06-25 19:39:49Z

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.

answered Jun 25, 2012 at 19:39

PeteGO

5,8183 gold badges43 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DenNukem · Accepted Answer · 2018-08-02 01:03:14Z

0

Another option!

Execute the query twice: once with db.ExecuteQuery<t_events> and once db.ExecuteQuery<t_clients>. Now that you have both events and clients you can re-join them client-side by matching client_id.

answered Aug 2, 2018 at 1:03

DenNukem

8,3043 gold badges42 silver badges45 bronze badges

Collectives™ on Stack Overflow

Using JOIN with Linq to SQL ExecuteQuery Issue

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related