n3tx n3tx - 1 year ago 85
C# Question

Trying to convert sql to linq with subquery

I am trying to convert this sql to linq:

SELECT [t0].[ThreadId] FROM [MessageParticipants] AS [t0]
SELECT threadId
FROM [MessageParticipants] AS [t1]
WHERE ([t1].[UserId] = 23)
AND ([t0].[UserId] = 3)

I have tried converting with LINQPad but I cant get it to work

This almost work:

var result = from mp2 in MessageParticipants
let x = (from mp in MessageParticipants where mp.UserId == 3 select mp.UserId)
where x.Contains(23)
select mp2;


The result query of the above LINQ (which is wrong)

DECLARE @p0 Int = 23
DECLARE @p1 Int = 3
SELECT [t0].[Id], [t0].[ThreadId], [t0].[UserId], [t0].[CreatedDate], [t0].[MessageId]
FROM [MessageParticipants] AS [t0]
FROM [MessageParticipants] AS [t1]
WHERE ([t1].[UserId] = @p0) AND ([t1].[UserId] = @p1)
) --> AND should be here not in the inner query

Maybe there are a better way ?

I am trying to find if user 23 and user 3 already have an ongoing conversation by finding there threadId.

So the MessageParticipants table looks like this:

Id, ThreadId, UserId
1 52 23
2 52 3
3 11 20

Answer Source

I believe your sql is a little off, you should use a join on the table to itself and then use a where for the id. Here is the updated sql and underneath the corresponding linq to get the thread id.

-- create in memory table for testing
DECLARE @MessageParticipants table (Id INT identity, ThreadId int, UserId int)
insert into @MessageParticipants(ThreadId, UserId) values (52,23), (52,3), (11, 20),(11, 3)

SELECT [t0].[ThreadId]
FROM @MessageParticipants AS [t0]
INNER JOIN @MessageParticipants AS [t1] ON t0.ThreadId = t1.ThreadId
WHERE [t1].[UserId] = 3 AND [t0].[UserId] = 23

Linq statement

var result = from m1 in MessageParticipants
    join m2 in MessageParticipants on m1.ThreadId equals m2.ThreadId
    where m2.UserId == 3 && m1.UserId == 23
    select m1.ThreadId;

var thread = result.FirstOrDefault();