Databases
StackOverflow2013
Postsdbo
POLinq To Sql - return table result and count
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLinq To Sql - return table result and count
    primarykey
    data
    text
    <p>i'm very new to linq to sql and in need of a little assistance.</p> <p>Basically i'm building a message board in C#. I have 3 database tables - basic info is as follows.</p> <p>FORUMS forumid name</p> <p>THREADS threadid forumid title userid</p> <p>POSTS postid threadid text userid date</p> <p>Basically I want to bring back everything I need in one query. I want to list a page of THREADS (for a particular FORUM) and also display the number of POSTS in that THREAD row and when the last POST was for that THREAD.</p> <p>At the moment i'm getting back all THREADS and then looping through each the result set and making calls to the POST table seperately for the POST count for a Thread and the Latest Post in that thread but obviously this will cause problems in terms of hitting the database as the Message Board gets bigger.</p> <p>My Linq To SQL so far:</p> <pre><code> public IList&lt;Thread&gt; ListAll(int forumid) { var threads = from t in db.Threads where t.forumid == forumid select t; return threads.ToList(); } </code></pre> <p>basicaly i now need to get the number of POSTS in each thread and the date of the last post in each thread.</p> <p>Any help would be most appreciated :)</p> <p>EDIT</p> <p>Hi guys. Thanks for tyour help so far. Basically i'm almost there. However, I left an important part out of my initial question in the fact that I need to retrieve the user name of the person making the last POST. Therefore I need to join p.userid with u.userid on the USERS table. So far I have the following but just need to amend this to join the POST table with the USER table:</p> <pre><code> public IList&lt;ThreadWithPostInfo&gt; ListAll(int forumid) { var threads = (from t in db.Threads where t.forumid == forumid join p in db.Posts on t.threadid equals p.threadid into j select new ThreadWithPostInfo() { thread = t, noReplies = j.Count(), lastUpdate = j.Max(post =&gt; post.date) }).ToList(); return threads; } </code></pre> <p>UPDATE:</p> <pre><code> public IList&lt;ThreadWithPostInfo&gt; ListAll(int forumid) { var threads = (from t in db.Threads from u in db.Users where t.forumid == forumid &amp;&amp; t.hide == "No" &amp;&amp; t.userid == u.userid join p in db.Posts on t.threadid equals p.threadid into j select new ThreadWithPostInfo() { thread = t, deactivated = u.deactivated, lastPostersName = j.OrderByDescending(post =&gt; post.date).FirstOrDefault().User.username, noReplies = j.Count(), lastUpdate = j.Max(post =&gt; post.date) }).ToList(); return threads; } </code></pre> <p>I finally figured that part of it out with thanks to all of you guys :). My only problem now is the Search Results method. At the moment it is like this:</p> <pre><code> public IList&lt;Thread&gt; SearchThreads(string text, int forumid) { var searchResults = (from t in db.Threads from p in db.Posts where (t.title.Contains(text) || p.text.Contains(text)) &amp;&amp; t.hide == "No" &amp;&amp; p.threadid == t.threadid &amp;&amp; t.forumid == forumid select t).Distinct(); return searchResults.ToList(); } </code></pre> <p>Note that I need to get the where clause into the new linq code:</p> <pre><code> where (t.title.Contains(text) || p.text.Contains(text)) &amp;&amp; t.hide == "No" </code></pre> <p>so incorporating this clause into the new linq method. Any help is gratefully received :)</p> <p>SOLUTION:</p> <p>I figured out a solution but I don't know if its the best one or most efficient. Maybe you guys can tell me because i'm still getting my head around linq. James I think your answer was closest and got me to near to where I wanted to be - thanks :)</p> <pre><code> public IList&lt;ThreadWithPostInfo&gt; SearchThreads(string text, int forumid) { var searchResults = (from t in db.Threads from p in db.Posts where (t.title.Contains(text) || p.text.Contains(text)) &amp;&amp; t.hide == "No" &amp;&amp; p.threadid == t.threadid &amp;&amp; t.forumid == forumid select t).Distinct(); //return searchResults.ToList(); var threads = (from t in searchResults join p in db.Posts on t.threadid equals p.threadid into j select new ThreadWithPostInfo() { thread = t, lastPostersName = j.OrderByDescending(post =&gt; post.date).FirstOrDefault().User.username, noReplies = j.Count(), lastUpdate = j.Max(post =&gt; post.date) }).ToList(); return threads; } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser1112150
    1. USuser1112150
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COSince you're new to LINQ I highly recommend visiting http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b. It's very helpful when trying to figure out how to do this stuff.
      singulars
      1. POLinq To Sql - return table result and count
      1. USJamieSee
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload