Dapper.net Inner join with where parameter

dapper sql

Question

I have which will be to some a very simple sql query which is performed by dapper.net

  using (var sqlCon = new SqlConnection(Database.ReturnDatabaseConnection()))
        {
            var resultList = sqlCon.Query<UserProfile, UserAddress, UserProfile>(@"

                            UPDATE [User].[User_Profile]
                                SET ProfileStatus = 4
                            WHERE Id = @userId

                            SELECT u.Id [userId], u.Username, u.Age,
                                   u.ProfileStatus,
                                   a.Id [AddressId], a.Country, a.[State], a.City,
                                   a.Latitude, a.Longitude
                            FROM [User].[User_Profile] u
                            INNER JOIN [User].[User_Address] a on u.id = a.UserId", (u, a) =>
                             {
                                 u.UserId = a.UserId;
                                 return u;
                             },
                             splitOn: "UserId"
                             ).AsQueryable();


        }

What I'm trying to do is first update the status of the userprofile and then join the user profile on to the user address table retrieve a few columns and populate the userprofile class I have been following this tutorial and it seemed quite straight forward, the issue I'm trying to get my head around is how can I also include a where statement, which would be

where u.Id = @UserId 

and then include that in the above query?

Accepted Answer

WHERE follows the FROM and JOINs:

SELECT u.Id [userId], u.Username, u.Age,
       u.ProfileStatus,
       a.Id [AddressId], a.Country, a.[State], a.City,
       a.Latitude, a.Longitude
FROM [User].[User_Profile] u
INNER JOIN [User].[User_Address] a on u.id = a.UserId
WHERE u.Id = @UserId 

The grammar is here; note that the joins are technically part of the FROM, in terms of their position in that grammar.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why