CODEWITHSUNDEEP
sqlasp.net-mvclinq-to-sql
ajbeaven
ajbeaven

Reputation: 9562

How do I get this in Linq to Sql?

To give you a bit of context, my database tables look like so:

User
- UserID
- Name

Message
- MessageID
- SenderUserID
- RecipientUserID

View
- ViewID
- VieweeUserID
- ViewerUserID

Using a single LINQ to SQL statement, I want get all the users that have either "Viewed" or "Messaged" a user and put them into an object like this:

Interactor
- Name (the viewer or messager)
- NumberOfMessagesSent
- NumberOfTimesViewed

I've tried a for ages to get what I want but every time I seem to get crazy data so I'm obviously doing something wrong... please help!

Upvotes: 1

Views: 77

Answers (1)

Diego
Diego

Reputation: 20194

The name of the relationships on your model may vary (this is how LinqPad named them) but this is what I think you are trying to accomplish:

Users.Select(u => new Interactor { 
                                   Name = u.Name, 
                                   NumberOfMessagesSent = u.SenderIdMessages.Count,
                                   NumberOfTimesViewed = u.ViewerIdViews.Count 
                                 })
     .Where(i => i.NumberOfMessagesSent > 0 || i.NumberOfTimesViewed > 0);

And the generated SQL:

SELECT [t3].[Name], [t3].[value] AS [NumberOfMessagesSent], [t3].[value2] AS [NumberOfTimesViewed]
FROM (
    SELECT [t0].[Name], (
        SELECT COUNT(*)
        FROM [Message] AS [t1]
        WHERE [t1].[SenderId] = [t0].[UserId]
        ) AS [value], (
        SELECT COUNT(*)
        FROM [View] AS [t2]
        WHERE [t2].[ViewerId] = [t0].[UserId]
        ) AS [value2]
    FROM [User] AS [t0]
    ) AS [t3]
WHERE ([t3].[value] > @p0) OR ([t3].[value2] > @p1)

Upvotes: 1

Related Questions