Hello!
I am trying to implement a SQL query within LLBLGen Pro, and having difficulty. To set the background:
- My database has Conversations.
- Each Conversation belongs to a Company.
- Each Conversation is made up of multiple Messages.
I have the following SQL query that takes in a CompanyPK and returns information about the most recent Message in each Conversation for that Company:
SELECT c.[ConversationPk], c.[Subject], c.[IsUrgent], m.[Sender], m.[Body]
FROM MessagingApi.[Conversation] c
INNER JOIN MessagingApi.[Message] m on c.ConversationPk = m.ConversationPk
INNER JOIN MessagingApi.[Company] p on c.CompanyPk = p.CompanyPk
WHERE c.CompanyPk = @CompanyPk
AND m.MessagePk IN (
SELECT TOP (1) [MessagePk]
from MessagingApi.[Message] m2
where m2.ConversationPk = c.ConversationPk
ORDER by SentOnUtc DESC
)
This is the LLBLGen Pro Code that I have written so far:
var qf = new QueryFactory();
var q = qf.Conversation
.From(QueryTarget
.LeftJoin(qf.Message)
.On(ConversationFields.ConversationPk ==
MessageFields.ConversationPk)
.InnerJoin(qf.Company).On(ConversationFields.CompanyPk == CompanyFields.CompanyPk)
)
.Select(() => new MessageSummaryDto
{
ConversationPk = ConversationFields.ConversationPk.ToValue<Guid>(),
Subject = ConversationFields.Subject.ToValue<string>(),
IsUrgent = ConversationFields.IsUrgent.ToValue<bool>(),
Sender = MessageFields.Sender.ToValue<string>(),
Body = MessageFields.Body.ToValue<string>(),
})
.Where(ConversationFields.CompanyPk == CompanyPk)
.AndWhere(MessageFields.QueueType == queueType)
//This is where I am trying to implement the subquery
.AndWhere(MessageFields.MessagePk.In(
qf.Conversation
.From(QueryTarget
.LeftJoin(qf.Message).On(ConversationFields.ConversationPk == MessageFields.ConversationPk)
)
.Select(() => MessageFields.MessagePk.ToValue<Guid>())
.OrderBy(MessageFields.SentOnUtc.Descending()).Limit(1)
));
using (var adapter = AdapterFactory.CreateAdapter(_connectionString, _environment))
{
var result = await adapter.FetchQueryAsync<MessageSummaryDto>(q);
return result;
}
I understand that this doesn't work because the .Limit(1) is limiting it to a single entry, not the newest entry for each Conversation. But how do I write that part correctly?