Well why don't you just ask? :)
Let me try to pin down my understanding of your requirement. It seems to me that you are looking at a thread being a linear list (not a tree) of messages between two people. I would think that you might want to allow more people in than just two. That would be like Facebook insofar as someone posts a message and then any number of people can read it and then start adding comments. When you add a comment it puts you into the thread and you start getting status updates and e-mails telling you about activity in the thread and so forth. Assuming that is what you're after, then the schema I suggested to Big Mike is not exactly what you're looking for.
Consider instead the following:
The idea here is that every time a user starts a brand new thread/message, it starts with a new record in the THREAD table. The user is then added as a THREAD_PARTICIPANT and the content of the message is added to MESSAGE which points back to the containing THREAD. The FK from MESSAGE to USER indicates the author of the message.
When a user reads a message, they get an entry in the MESSAGE_READ_STATE table to indicate that they have marked the message read, either explicitly or implicitly, depending on how your requirements go.
When someone comments on the initial message in the thread, a second MESSAGE is added with an FK back to the original THREAD and the reply author (user) gets added to the THREAD_PARTICIPANT table. And so it goes as messages are added to the thread by one, two or even more participants.
To get the most recent message in any thread, just take the top 1 from MESSAGE sorted descending on create date (or an identity key) where the message FK is to the thread of interest.
To get the most recently updated thread for a user, get the THREAD related to the top 1 from message sorted descending on create date where the message is in a thread in which the user is a THREAD_PARTICIPANT.
I'm afraid I can never state these things in LINQ without breaking out LinqPad. If you are having trouble catching my drift from the above, I could flesh out the answer with table definitions and some SQL. Just ask in the comments.
EDIT: Clarification of Requirements and Implementation
Clarifying the requirements: Initially I was thinking about publicly posted messages with the opportunity for commenting, whereas Shane is after more of the direct message feature. In which case the initial recipient needs to be included in the THREAD_PARTICIPANT table at the outset.
For some clarity, let's put a few rows in tables. Here is the scenario, (in honour of Canada Day): User 1 DMs User 2 to ask about meeting for a beer. User 2 replies with a question about where to meet and User 1 answers. The tables would look something like this: (probably oversimplified)
EDIT #2: Access SQL for list of all messages in a thread, with read state...
Using @OP's schema, this SQL will get a list of messages in a given thread with an indication of whether a given user has read each message or not. Messages are in most recent first order.
SELECT Message.MessageId , Message.CreateDate , Message.Body , Login.Username , (SELECT MessageReadState.ReadDate FROM MessageReadState WHERE MessageReadState.MessageId = Message.MessageId and MessageReadState.LoginId = 2) as ReadState FROM (Message INNER JOIN Login ON Message.SenderLoginId = Login.LoginId) WHERE (((Message.MessageThreadId)=10)) ORDER BY Message.CreateDate DESC;
Note that the trick, if it's fair to call it that, is that the read state is picked up with a sub-select. This is necessary because part of the criteria for getting the read state requires a where clause that can't be satisfied with an outer join. Therefore you use the subselect to pin down which (possibly missing) value you want from the MessageReadState child table.
EDIT 3: SQL for getting all threads with latest message in each for a given user...
To get a list of all of the threads in which a given user has participated, sorted by most recent message first, with only the most recent message being displayed (1 message per thread) then you would use a similar query to the one above, except instead of filtering messages by their FK to the thread of interest, you filter the messages by a subquery that finds the latest message in each thread that the user of interest participated in. It would look like this:
SELECT Message.MessageId , Message.CreateDate , Message.Body , Login.Username , (SELECT MessageReadState.ReadDate FROM MessageReadState WHERE MessageReadState.MessageId = Message.MessageId and MessageReadState.LoginId = 2) AS ReadState FROM Message INNER JOIN Login ON Message.SenderLoginId = Login.LoginId WHERE ( Message.MessageId in ( SELECT Max(Message.MessageId) FROM MessageThreadParticipant INNER JOIN Message ON MessageThreadParticipant.MessageThreadId = Message.MessageThreadId WHERE MessageThreadParticipant.LoginId=2 GROUP BY MessageThreadParticipant.MessageThreadId ) ) ORDER BY Message.CreateDate DESC;
According to Joel Brown'answer, you can add
LAST_MESSAGE_ID column into THREAD table then getting all threads with last messages SQL is become very simple. You must update this column when every message send.
Getting all threads with latest message in each for a given user
SELECT * FROM THREAD T INNER JOIN MESSAGE M ON T.LAST_MESSAGE_ID=M.MESSAGE_ID INNER JOIN USER SENDER ON M.USER_ID=SENDER.USER_ID LEFT JOIN MessageReadState MRS ON M.MESSAGE_ID=MRS.MESSAGE_ID AND MRS.USER_ID=2
- thread messaging system database schema design
- LINQ Source Code Available
- Is it possible to update a sql database schema from an edmx in Visual Studio 2008?
- .NET 4 Code Contracts: "requires unproven: source != null"
- How get database table name with schema name from entity programmatically
- LINQ to SQL - Database Design issue
- creating Linq to sqlite dbml from DbLinq source code
- LINQ code to combine two database tables?
- Binding source thread in PLINQ
- updating data in many-to-many relationship in entity framework in code first existing database
- Windows Phone SQL Server CE - Retrieve underlying database schema for upgrade
- linq to entities changing database connection string in code
- how will i create multiple type id in one column database design
- Do database schema changes break linq to entities
- How can I check the number of calls to the database in LINQ query when using .NET Core and Code First?
- Solution For Updating LINQ to SQL Files After Database Schema Change
- Can't add a new record with an integer value into database by using linq from code C#
- How can I unit test my project, when most of it access a database with a poor schema that I cannot change?
- Retrieving Database Schema Linq to SQL VB.Net
- SQL Server Database is not updated via C# Code
- Model Design As Database Table Or Multi-Dimensional List?
- ASP.net Getting Max Date of database Date column How to avoid Null in Date Column C# My code Attached
- Code First EF: While searching database table is it possible to retrieve list of items it has in DataModel?
- source code for LINQ 101 samples
- How to have a database trigger code
- Simple database design and LINQ
- Querying database schema with LINQ to SQL
- Using LINQ to iterate through a database and generate a generic XML layout of schema + data
- trying to seed my database using code first migrations
- How to bind and save an object containing a list of objects to database context in ASP.NET MVC with EF code first?
More Query from same tag
- join excel and access using Linq query
- Entity Framework OrderBy On Joined Collection
- How to can I specify namespace to an XAttribute while having another namespace with the same value?
- 2 levels of joins LINQ
- Please explain this behaviour (is LINQ cached)?
- LINQ group List<object> on baseclass attribute
- Generic method to compare/filter two lists using expressions/lambda
- Is there an equivalent in C++ to LINQ with respect to DataTables?
- How to get dynamic type property?
- Get XML attribute Values by its Descendants
- LINQ how to query if a value is between a list of ranges?
- c# Find missing sequence number on object using linq
- Retrieving an item from a list based on selected value
- Sorting a DataTable by columns in a file
- Linq converted to SQL for .All()
- Which is the most efficient DataSet query operation?
- LINQ where to Match exact string
- Using Linq to remove from set where key exists in other set?
- Slow performance on LINQ query against SQL server
- EFcore Joining InMemory Table for duplication of entry
- Using LINQ, select first of each group, create new obj with specific columns
- How to add object values to viewmodel with List<object>
- Entity Framework - Eager loading Linq query
- JOIN using LINQ LAMBDA
- Sorting 50+-digit numbers in array
- LINQ Query returns multiple copies of first result
- how concatenate multiple rows in LINQ with two tables?
- How to use ToDictionary to convert a query result into a dictionary
- Foreign key is null (while it is not) when using linq
- Sorting a ListBox ListItems with LINQ?